InfoSol We're passionate about inspired business intelligence
Related Articles
“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
