This recipe combines the techniques learned in prior recipes to achieve a business requirement. A report shows sales data by country and product line. Users can choose to see data for one or more countries.
However, we need to implement a security mechanism such that a user can choose only those countries to which he is supposed to have access. This is determined by the user groups he belongs to.
Create a simple list report with Country, City, Product line, and Sales Quantity as columns.
- We will start by adding a filter for countries. For that, go to Query Explorer and insert a new detail filter. Define it as:
[Country] in ?Countries?
Ensure that this filter is mandatory.
- Now, add a new prompt page. Drag a value prompt on to it.
- Follow the prompt wizard to set the following:
- Link it to existing parameter called 'Countries'.
- Create a new query for this prompt and call it 'Countries'.
- Go to Query Explorer and open the Countries query subject.
- Add a detail filter and define it as:
[Country] in (#CSVIdentityNameList(',')#)
- Run the report to test it. You will see that you can see only those countries in the value prompt to which the user is supposed to have access. For my account, I can see only 'Spain' as I have set the membership accordingly.
This recipe simply combines the technique we learnt in prior recipes. We use CSVIdentityNameList() macro to retrieve the user group information.
We use this macro in the prompt query in order to restrict the values coming through in the value prompt. Whatever values are selected by users are then passed as a standard parameter for filtering to the report query.