I had received a couple of emails on how to have a table equivalent to DUAL(of Oracle Database) in BI EE. One of the major advantages of the Dual table in an Oracle Database is that you can supply some values in a select statement and the dual table will print it out for you(of course i am stating a very basic use here. There can be a variety of uses depending on how you use the dual table). Now why do we want a similar one in BI EE. Both the emails stated that in BI EE it is not possible to create a report that will print out some dummy values without including a dummy column and hiding the column. Say for example, i need a report that will just print a presentation variable or a simple dummy value like ‘Test’. If you just enter ‘Test’ or the ‘@{variable}’ in the only column that you have in the report you would get the following error while viewing the results.
How do we avoid this error. There are 2 approaches. One is to create a dummy column containing a presentation layer column and hide that column. This will bypass your “Query not referencing any table error”.
But both the emails suggested not wanting to use this approach. Then in that case use the 2nd approach which is to use a single column formula like the one below
CASE WHEN 1=0 THEN PRODUCTS.TOTAL_NAME ELSE ‘Test’ END
where PRODUCTS.TOTAL_NAME will be a column from the presentation layer. The trick is to reference a column within the query but which will never be used. In the above case, 1=0 will never be true and hence ‘Test’ will always be printed. In this case you dont have to include another column and hide that column. But again this is not an exact substitute to the dual table since the query never hits the dual table. Instead the same query (using the above case statement) will be fired on the PRODUCTS table since we have used one of its columns. But till we get a similar dual functionality(i dont think we ever will 
) use any of the above 2 mentioned methods.
) use any of the above 2 mentioned methods.
P.S: Advanced New Year Wishes to Everyone!!!
Posts
