InfoSol We're passionate about inspired business intelligence
Related Articles
In Design mode of Web Intelligence, you can change the sort order of columns in a block. However, this option is not available in Reading mode. We can accomplish some basic sorting tasks using an input control and the steps below will detail how this is done.
We’ll start off with a block that displays a few dimensions and the revenue measure. The default sort order is controlled by the first dimension displayed in the block, which in this case is Product Group. The goal is to be able to dynamically sort on product group, city, state or zip as a report consumer that does not have access to Design mode.
We need to create a variable that will be used in an input control. To create the variable, right click on Variables in the Available Objects from the Side Panel and select New. Alternatively, you can use the Data Access tab and select New Variable under the Data Objects subtab.
Name the variable Sort By, qualified as a dimension with the formula =””. Optionally, you can type in one of the values for the sort options that you want displayed in the input control, such as =“Product Group”. Click OK.
To create the Input Control, navigate to the Input Control option on the Side Panel and click New. Select the Sort By variable we just created and click Next.
Select one of the Simple Selection options. I chose Radio Buttons in this example.
Create a custom list of values in the Input Control Properties by clicking the ellipsis (3 dots) to the right of the “List of values” option.
If you defined the “Sort By” variable as =””, then you will need to add all available sorting options manually by typing into the “Type values here” box and then use the enter key or the right arrow to move the values to the LOV. If you defined the “Sort By” variable with an actual value, then move that value to the right and follow the same procedure as above to manually add the other values.
The LOV should look similar to the screenshot below, but you can choose to order the sort options in the input control any way you like.
Click OK when you are finished to return to the Input Control Properties. Next, we will uncheck the “Allow selection of all values” box, as we only want to allow selection of one sort option. We will need to set a default value for the input control since we are not allowing selection of all values. Click the drop-down arrow next to Default value(s) and choose “Select values”, type in “Product Group” in the “Type values here” box and move the value to the right to set it as the default. Click OK.
The definition of the input control should look similar to the screenshot below.
Click Finish to complete set up of the input control. It should now be displayed in the Side Panel on the left side of the screen and is functional. However, we have not hooked it up to anything yet!
The next step is to create another variable that will determine the dimension that is displayed based on the selected value from the input control. Name this variable Selected Sort and qualify as a dimension with the formula below. It is important to note that the object names in the variable definition ([Product Group], [Customer City], [Customer State], [Customer Zipcode]) must exactly match the actual object names in the Available Objects panel that contain the data in your report block. However, these object names do not have to match the labels in the “Sort By” input control.
=If [Sort By] = “Product Group” Then [Product Group]
Else If [Sort By] = “Customer City” Then [Customer City]
Else If [Sort By] = “Customer State” Then [Customer State]
Else [Customer Zipcode]
Insert a column as the first column to the left of Product Group in the block. Display the new Selected Sort variable in the new column. If the default value you selected in the input control was “Product Group” then this will display product group values.
Navigate back to the Input Controls screen and test out your new input control by selecting each sort option. Note how the sort order changes in the block as you are interacting with the input control. Since the default sort order is controlled by the first column of the block, we are able to dynamically change the sort using an input control.
You may be thinking….well this is nice, but I don’t want to see the Selected Sort column in the report! The final step is to hide the column. To accomplish this, right click on the Selected Sort column in the block and select Hide/Hide Column.
To make hidden dimensions visible again, click anywhere in the block and select Hide/Show Hidden Objects.
If you change your mind later and either want to remove or add a sort option, simply modify the custom list of values for the Sort By input control and the variable definition for Selected Sort.
To modify the input control, navigate to the input control definition pane in the Side Panel. Click on the wrench in the top right of the Sort By input control to edit the definition.
In the input control editor, click on the ellipsis to the right of the custom list of values. Don’t forget to modify the Selected Sort variable definition to match the changes you made to the custom list of values for the input control.
You can choose to sort by a dimension that is not displayed in the block, however, this might be confusing for your users if they can’t visually see the data that is being sorted by.
You can also use this same input control to dynamically display a dimension in a chart. In the example below, a bar chart is displaying the Selected Sort dimension and the Revenue measure.
The default chart title can be modified to dynamically display the selected sort option. Right click on the chart title and select Format Chart Title.
Under the General options for the Title chart area, select Custom Title and change the Title Label to:
=”Revenue by ” + [Sort By]
Next, we’ll add an ascending sort to the chart to display the total revenue from highest to lowest.
You are now ready to give your users an easy and flexible method to sort the report. Hopefully you found this tech tip useful or it inspired you to experiment with other uses for input controls!
