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

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

dynamic-webi-report-tech-tip-1

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”

dynamic-webi-report-tech-tip-2

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

dynamic-webi-report-tech-tip-3

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

dynamic-webi-report-tech-tip-4

dynamic-webi-report-tech-tip-5

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

dynamic-webi-report-tech-tip-6

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

dynamic-webi-report-tech-tip-7

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

dynamic-webi-report-tech-tip-8

dynamic-webi-report-tech-tip-9

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

dynamic-webi-report-tech-tip-10

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.

dynamic-webi-report-tech-tip-11

 

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

Rajesh Ponnurangam is a Senior Business Intelligence Consultant with Infosol. He is a SAP certified associate with extensive experience in design and development of Business Intelligence applications for apparel, retail, consumer goods and manufacturing industries. He specializes in Business Objects solutions, SAP HANA and BW data modeling. He has a proven track record in designing strategies for ETL, BI application development, implementation, rollout and support.

Check Also

The Secret Behind the Naming of IBIS

The Secret Behind the Naming of IBIS

I was recently talking to Paul about the IBIS conference and I asked him how ...