Input Controls provide your WebI reports with the interactivity you need for the proper selection of information, but this functionality comes at a price. When the amount of data handled is high, performance issues may appear during developments. In order to help with this issue, we recommend the following design technique, which can be used to improve the performance of your interactive navigation.
Follow this 8-steps process to become familiar with this solution:
- Create a WebI document with two Queries: a) One Query containing the "cube" with the data we want to monitor together with a dimension [Link1] containing the word "LINK" and b) Another Query with two dimensions: [Input Controls List] containing a single list* of the dynamic object to use and an object (Link2) containing the word "LINK" (* A derived table containing hardcoded values can be used)
- Create a Merged Dimension (e.g. [LINK]) that unifies the 2 linking dimensions)
- Create a detail variable called ICF (Input Control Flag) with the formula [Input Control List] and use [Link1] as associated dimension
- Create a dimension variable called X-Axis. This will contain the dynamic object to be used in the reports. It consists of basically a big IF sentence, using the [ICF] from the 2nd query and the objects we want to use as axis from the 1st Query. See as an example: =IF([ICF]="Business Unit";[Business Unit];IF([ICF]="Customer Type";[Customer Type];...))
- Create components in a report (e.g. a table and a graph), containing the X-Axis and the measure we wish to show.
- Create the input control with the following features: a) Use the [Input Control List] dimension b) In dependencies, select the components (tables, graphs, cells, etc.) that will be affected by the input control.
- Create the following filter in every component to be effected: [LINK] Equal to {‘LINK’}
- Last but not the least, in Document properties "Extend Merged Dimension Values". This is very important for the correct functionality of the solution.
If we want to take this to the next level and have full dynamic reports we could have 2 additional input controls, e.g. one additional Y-Axis and one measure. To achieve this it would be enough to create 2 additional queries with new Input Control Lists and follow the steps 2-7 for them. In the case of the measure it is better to use sum/if clauses in step 4. Once implemented, this solution will allow easy scalability as new content can be quickly added with few modifications in the front-end interface.
If you have any questions or feedback about this solution, please feel free to leave a comment below or send an email to info@clariba.com.