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 the Product manager for InfoBurst, Infosol’s flagship product. With more than a decade of demonstrated success assisting large entities, he is continually helping clients with their implementations using technologies such as Business Objects, Tableau, Power BI, SAP HANA and BW. He is responsible for product development initiatives through vision, strategy and roadmap development for InfoBurst.

Check Also

BusinessObjects-Hell-Freezes-Over-Concert-Cover-Photo-900x434

The BusinessObjects Hell Freezes Over Tour

Twenty-five years ago on July 13, 1994 I went to see the legendary band, The ...