I have often seen some people asking how to dynamically change a column in an OBIEE report. This is something that actually took me quite a bit of time to figure out as well. I came across a few tutorials that did something close but didn't actually do what I wanted. The end goal of this tutorial is to help you create a View By drop down that will dynamically modify the result set or graph based on the seleceted choice. The example we are going to use is maintenance cost report. This report shows the Total Actual Cost by organization or company. But what if we also wanted to view this report by department, or work order type or any other parameter in your data. Instead of making your users have more than one report, we can achieve this by creating a View By prompt and dynamically updating the column in the report.

The first thing we need to do is create a prompt that will fill our view by variable.

Here I have added one column. In this case the Organization column.

Select the following options

• Operator - is equal to / is in
• Control - Drop-down List
• Show - SQL Results
• Default to - SQL Results
• Set Variable - Presentation Variable
• Label - View By

Now we need to set some of these values. For the Show column we are going to use the following SQL Result

                    SELECT 'Organization' FROM "Subject Area"."Table Name" 
                    UNION 
                    SELECT 'Department' FROM "Subject Area"."Table Name" 
                    UNION 
                    SELECT 'Asset' FROM "Subject Area"."Table Name"   
                    UNION 
                    SELECT 'Work Order Type' FROM "Subject Area"."Table Name" 
                    UNION 
                    SELECT 'Project Name' FROM "Subject Area"."Table Name" 
                    

Next populate the Default to field to

                    SELECT 'Organization' FROM "Subject Area"."Table Name"                     
                    

Below the Set Variable field set the Presentation Variable to V_VIEWBY or any other variable name you wish to use

And lastly set the Label column to View By or whatever you would like the prompt to display as

If we preview our prompt it should look something like this.

That's all we need to do to create the prompt. Next we need to create a new answer and use the new Presentation Variable that we just created to dynamically update the result set in our Answer. To do this create a new Answer using the first column in your View By and any other fields you want in the report. In our case this is the Organization column and the Total Actual Cost field.

Click edit formula on the Organization column or the column you wish to be dynamic and add the foloowing case statement.

                   CASE '@{V_VIEWBY}' 
                   WHEN 'Organization' THEN "Table Name"."Column Name"
                   WHEN 'Department' THEN "Table Name"."Column Name" 
                   WHEN 'Asset' THEN "Table Name"."Column Name"
                   WHEN 'Work Order Type' THEN "Table Name"."Column Name" 
                   WHEN 'Project Name' THEN "Table Name"."Column Name"  
                   ELSE "Table Name"."Column Name" END   
                   

Next, check the Custom Heading checkbox and enter View By. There are ways to get the column header to be dynamic as well but I don't believe it is necessary as the View By prompt is already telling you what the column is. By adding this case statement we are saying that the column will evaluate the V_VIEWBY variable and then select the appropriate column based on the value. Now add both the prompt and the new report to a dashboard and try it out. The report should change based on the value in your View By drop down.