Dimensions as Input Control- Dynamic Control
I am not sure whether this topic has been covered or not for Webi Reports. I checked and did not found any links to it hence mentioning it in here.
I had a requirement of a report showing revenue and margin based on some dimensions. What I wanted was to have those dimensions as input control in the report.
Like we have the values of certain dimensions as input control, instead I succeeded in getting number of dimensions as input control.
Below are the steps to achieve the same.
- Create a WebI Report or use any existing report.
- Create a New Variable as (Dimension) and hard code with some value.
- Now, Go to Input Controls and click on New to create an Input Control.
- In the properties tab check on Radio Buttons in the Simple Selection Pane:
- Then select the tab (marked in red) in the List of Values and make sure “Custom” is selected
- Once you have selected the tab you will get another window where you can put in all the dimensions which you would like to be available for your selection.
- Once you have all the dimensions selected please select Ok twice as the properties pane also needs to be closed.
- Once you have done this you will see the Input Control Available with the dimensions, but when you check the radio button it would not work as these are hard coded values.
- To make these values work we need to create one more variable and give it a name as “Selected Dimension” or any other name that suits.
- In this we need to write a formula which would help the Input Controls to work:
=If ReportFilter([Select a Dimension]) = “Sales Office” Then [Sales Office]
ElseIf ReportFilter([Select a Dimension]) = “Division” Then [Division]
ElseIf ReportFilter([Select a Dimension]) = “MR Number” Then [MR Number]
ElseIf ReportFilter([Select a Dimension]) = “Material Group” Then [Material Group]
- Once the variable is created drag it to the report body and then try to check the Input Control radio buttons and they should work for this column and the measures would also change based on your selection.
Remember to remove all the dimensions from the report body which you have used in the Input variable or else it would get confusing.
Now you have the input variables working for you:
Selected below is “Sales Office”
“Division” is selected:
To ensure the header name also changes along with the selection:
- Create a variable say “Name” and write a formula again:
=If ReportFilter([Select a Dimension]) = “Sales Office” Then “Sales Office”
ElseIf ReportFilter([Select a Dimension]) = “Division” Then “Division”
ElseIf ReportFilter([Select a Dimension]) = “MR Number” Then “MR Number”
- Copy the formula and paste it in the header of the field which you have dragged to the report i.e. the “selected variables”
Once this is done whenever you would select a dimension from the Input Control the header name would change dynamically.
Note: Please ensure that spellings match while writing the formulas or else the values might not come properly.
Hope this helps.
Great Webi trick!!!
I'll add that it can also be used in charts and with measures.
Thanks. Yes it could be used for charts and measures as well.
I am using it for charts and tables both in one go.
Grt explanation , but if we need to select more than dimension at a time then how will this work?
once you enter the Input control properties you will find two panes on the left hand side. In the "Multiple Selections" pane you have "check box" as a selection, select that and perform the same trick by customizing the List of Values.
once the whole step is complete you would have multiple selection option using check boxes.
Hope this answers your question.
Great Webi trick !
You've been added to Webi 4.x tricks : summary for a better visibility. Keep posting !
Thank You William.
I will keep posting.
Good and Helpful post! Sandeep
Great trick Sandeep!!!