5 Easy Steps in Designing a Dynamic Y-Axis Web Intelligence Report Leveraging Derived Tables in IDT

“Create a sales report by state, by city, by store, by year, by quarter, by month…” and the list goes on. This is a very common report requirement from a business. The usual procedure for designing this report in Web Intelligence is to create different reports for each dimension or create one report with separate tabs. What If your end user is not a power user and would like to view this report in a much simpler fashion? What if the report by is more than five dimensions? What if your users don’t like switching between tabs or reports? This article shows five easy steps in designing a dynamic Y-Axis web Intelligence report leveraging Derived tables in IDT and the input controls in Web Intelligence.

The article uses a simple e-staff universe with sales by dimensions – Region, State, City and Employee

Step 1: Create a derived table in the data foundation layer of the universe with a dummy SQL statement which would display the “Report by” list of values

The SQL statement shown in this example is for Microsoft SQL server 2008. The SQL syntax does change based on the underlying database. For ex: Oracle syntax would be “Select ‘Region’ “Report_By” from dual”


A stand-alone report by table is now created as shown below.


Step 2: Save the data foundation layer and in the business layer, drag the “Report_By” Field to the list of dimensions and create a new field named “Report Selection” with a SQL case statement as shown below


Step 3: Publish the universe and create a web intelligence report by bringing in the “Report_by” and Report Selection fields and the measures

Step 4: Create an input control on “Report By” and display a vertical table with “Report Selection” and “Total Salary”


Now your end user has one report with one single tab which works for all the “report by” dimensions

Step 5: The Column title will change dynamically when the “Report_By” field value is dragged on the title for the “Report Selection” column

If you feel input controls are a bit confusing for your end user, you can create a report with a prompt which takes the value for the “Report By” field.  This drastically improves the performance, since the refreshed web intelligence query displays only the records for the selected dimension.



If you liked this blog post, you can should also check out my follow up tech tip: Creating a Performance Optimized Dashboard Leveraging Derived Tables in IDT

About Rajesh Ponnurangam

Check Also

Let's Speak BO Webinar - Upcoming Event

Webinar: Recent Upgrades from BusinessObjects BI 4.2 to BI 4.3 on September 5, 2023

The majority of BusinessObjects customers are upgrading to BI 4.3 this year. Some have already …

Leave a Reply

Your email address will not be published. Required fields are marked *