CALL US NOW:

1-401-727-2400 U.S.
1-905-532-0440 Canada

Hide/Show Columns in a List Using a Multi-Select Prompt

Business Problem:

The business requirements state that users need to select from a large list of attribute columns in a report to display on a list report. The maximum number of columns is known but due to the variety of users it is not known until run time which attribute columns a particular wants to see in the final results. The users want a multi-select Check Box prompt from which they can select any or all attributes to display in the report. They is a default set of attributes that will display if the user does nothing with the prompt.

Solution:

Using a multi-select check box prompt control each attribute column is be displayed to the user when the report runs. Some check boxes are already selected based on business requirements. Any column check box selected will be display on the report results. The displayed attribute columns together identify the fact grain and the measure columns are automatically aggregated to this grain.

 

The column values are determined by the user selection. If a column is not selected the data item expression will result in no value (i.e. NULL). This is required to ensure the correct fact measure rollup.

 

Render Boolean variables are used, one for each column, to control if the column is displayed or not. Render variables are applied to the whole list column rather than the header and body separately. This reduces the number of places where the control is set. Render variables in this case will work the same as if you used a Style Variable and set the No condition on the list column but require less effort to implement.

 

The solution is based on the Go Sales (Query) package.

Figure 1 – prompt control for selecting which columns to display on the report results.

 

  1. Create a list report in Report Studio. Add the following items to the list:
    1. [Sales (query)].[Sales staff].[Sales region], [Sales (query)].[Sales staff].[Sales region code]
    2. [Sales (query)].[Sales staff].[Country], [Sales (query)].[Sales staff].[Country code]
    3. [Sales (query)].[Sales staff].[City] (as Branch Name), [Sales (query)].[Sales staff].[Branch code]
    4. [Sales (query)].[Sales staff].[Manager], [Sales (query)].[Sales staff].[Manager code]
    5. [Sales (query)].[Sales staff].[Staff name], [Sales (query)].[Sales staff].[Sales staff code]
    6. [Sales (query)].[Retailers].[Retailer], [Sales (query)].[Retailers].[Retailer code]
    7. [Sales (query)].[Order method].[Order method type], [Sales (query)].[Order method].[Order method code]
    8. [Sales (query)].[Products].[Product line], [Sales (query)].[Products].[Product line code]
    9. [Sales (query)].[Products].[Product type], [Sales (query)].[Products].[Product type code]
    10. [Sales (query)].[Products].[Product], [Sales (query)].[Products].[Product number]
    11. [Sales (query)].[Sales].[Revenue], [Sales (query)].[Sales].[Quantity]
  2. Create a prompt page and open it for editing.
  3. Add a table to the page with 2 rows and 2 columns.
  4. Merge the cells in row 1.
  5. Add a Text Item to row 1. Text: “Attributes to Display”. Set the horizontal alignment as Center.
  6. Add a value prompt in row 2, cell 1. Parameter: “pDispAttIds”. Click Finish to bypass the filter and query dialogs. Set the properties of the prompt control as:
    1. Required: No
    2. Multi-select: Yes
    3. Select UI: Check box group
    4. Width: 135px
    5. Height: 170px
    6. Name: “pDispAttIds”
    7. Results Select All Text: blank
    8. Results Deselect All Text: blank
    9. Static Choices:
      1. Use: “a”, Display “Region Code”
      2. Use: “b”, Display “Country Code”
  • Use: “c”, Display “Branch Code”
  1. Use: “d”, Display “Manager Id”
  2. Use: “e”, Display “Sales Rep Id”
  3. Use: “f”, Display “Retailer Id”
  • Use: “g”, Display “Order Method Code”
  • Use: “h”, Display “Product Line Code”
  1. Use: “i”, Display “Product Type Code”
  2. Use: “j”, Display “Product Number”
  1. Default values: a, c, f (each is a separate entry)
  1. Copy the above prompt into row 2, cell 2. Change the properties as below:
    1. Parameter: “pDispAttDescs”
    2. Name: “pDispAttDescs”
    3. Static Choices:
      1. Use: “a”, Display “Region Name”
      2. Use: “b”, Display “Country Name”
  • Use: “c”, Display “Branch Name”
  1. Use: “d”, Display “Manager Name”
  2. Use: “e”, Display “Sales Rep Name”
  3. Use: “f”, Display “Retailer Name”
  • Use: “g”, Display “Order Method”
  • Use: “h”, Display “Product Line”
  1. Use: “i”, Display “Product Type”
  2. Use: “j”, Display “Product Name”
  1. Default values: a, e, h (each is a separate entry)
  1. For both cells in row 2 set the padding property with 5px on each side. The result should look like:
  2. Create a set of Boolean variables, one for each attribute column.
    1. vDispRgnCd – ParamValue(‘pDisplAttIds’) CONTAINS ‘a’
    2. vDispRgnNm – ParamValue(‘pDisplAttDescs’) CONTAINS ‘a’
    3. vDispCntryCd – ParamValue(‘pDisplAttIds’) CONTAINS ‘b’
    4. vDispCntryNm – ParamValue(‘pDisplAttDescs’) CONTAINS ‘b’
    5. vDispBrnchCd – ParamValue(‘pDisplAttIds’) CONTAINS ‘c’
    6. vDispBrnchNm – ParamValue(‘pDisplAttDescs’) CONTAINS ‘c’
    7. vDispMgrId – ParamValue(‘pDisplAttIds’) CONTAINS ‘d’
    8. vDispMgrNm – ParamValue(‘pDisplAttDescs’) CONTAINS ‘d’
    9. vDispSlsRepsId – ParamValue(‘pDisplAttIds’) CONTAINS ‘e’
    10. vDispSlsRepNm – ParamValue(‘pDisplAttDescs’) CONTAINS ‘e’
    11. vDispRtlrId – ParamValue(‘pDisplAttIds’) CONTAINS ‘f’
    12. vDispRtlrNm – ParamValue(‘pDisplAttDescs’) CONTAINS ‘f’
    13. vDispOdrMthCd – ParamValue(‘pDisplAttIds’) CONTAINS ‘g’
    14. vDispOdrMth – ParamValue(‘pDisplAttDescs’) CONTAINS ‘g’
    15. vDispPrdctLineCd – ParamValue(‘pDisplAttIds’) CONTAINS ‘h’
    16. vDispPrdctLine – ParamValue(‘pDisplAttDescs’) CONTAINS ‘h’
    17. vDispPrdctTypCd – ParamValue(‘pDisplAttIds’) CONTAINS ‘i’
    18. vDispPrdctTyp – ParamValue(‘pDisplAttDescs’) CONTAINS ‘i’
    19. vDispPrdctNbr – ParamValue(‘pDisplAttIds’) CONTAINS ‘j’
    20. vDispPrdctNm – ParamValue(‘pDisplAttDescs’) CONTAINS ‘j’
  3. Now edit the list. For each column body in turn, select the select then using the Ancestor button go up a level to select List Column. In the properties add a Render Variable. From the list select the variable that matches the column.
  4. The Data Item Expression for each attribute column must be modified so that if the column is not selected for display then the value is NULL. This ensures that the measure value totals will be correct no matter which columns are display.
  5. Test the results by running the report.


Figure 2 – Default prompt selection

Figure 3 – Results with default prompt selection

 

Figure 4 – changed prompt value selection


Figure 5 – Results with changed selections

Click here for Report Studio Report Specification

 

 

 
 
 
 
 

Contact Us

E-mail: cci.info@lighthousecs.com

USA
Phone: (401) 727-2400
Fax: (401) 727-4998

Canada
Phone: (905) 532-0440
Fax: (905) 532-0683