Non Standard SQL in BIP 11g - Dynamic query implementation

SQL query is often used as a data source in BIP and often we have a requirement in which we have to use one table when the user selects a certain value in a parameter and select a totally different table when the user selects another value in the parameter. While we have discussed a method to do this using pipelined functions in http://obiee-oracledb.blogspot.com.au/2011/07/performance-enhancing-drug-called-table.html, this blog post talks about a BIP 11g method of achieving the same result.
We are essentially talking about writing dynamic queries in BIP. We will use BIP 11g's Non Standard SQL feature to achieve this objective.

We will follow the following steps:

1. Create 2 tables and feed data into them.
2. Create a BIP 11g data model that has a Non-Standard SQL using a parameter.
3. Create a simple BIP report and check the implementation.

Lets begin:

1. I had created 2 tables in the following post and I am going to use the same tables for this demo as well.

http://obiee-oracledb.blogspot.com.au/2014/08/smart-tables-in-obiee-11g.html

Let me share the DDL and DML again

        create table  smart_table_1 (column_1 varchar2(15 char));
        create table  smart_table_2 (column_1 varchar2(15 char));
        insert into smart_table_1 values ('Smart table 1');
        insert into smart_table_2 values ('Smart table 2');
        commit;

2.  Create a BIP data model and then create a LOV in it. I have created a Fixed Data LOV as shown below.

3. Create a parameter of Menu type that uses this LOV as shown below.

4. Create a dataset as shown below. Note that the Type of SQL is Non-Standard SQL. Following code is used in this data set.

create sql dataset with following query
$if{ (:param_table_name == smart_table_1) }$
select column_1 from smart_table_1
$else{
select column_1 from smart_table_2
}$
$endif$

5. Go to the data tab (highlighted in the screenshot below) and click on the View button (highlighted in the screenshot below). This will show you the data with the default selection of the parameter. Click on the Save As Sample Data button (highlighted in the screenshot below). The advantage of this step is that the creation of template becomes very easy. The displayed data is saved in XML format by BI Publisher. BI Publisher uses this data to figure out the columns available in the data set. Developers can then use these data columns to quickly create an online template.

6.  Save the data model. I named it Dynamic table datamodel.xdm

7. Create a BIP report that uses this data model. I named my report Dynamic table.xdo

8. Create a BIP template. I chose the Portrait basic template.

9. You will see Column 1 visible in the pane in the left hand side because you have saved the sample data. Drag a data table (highlighted below), use the Show drop down associated with the data table to remove the aggregate row from the table and then drag Column 1 into the data table as shown below.

10. Save the template. I named it Dynamic table template.

11. Now view the report. The default view will show 'Smart table 1'. Flip the value in the dropdown to 'smart_table_2' and the value in the report will change to Smart table 2.