Applying Client List Filters to Custom Reports

The process below demonstrates how the Service Detail With Charges report was updated to apply Client List Filtering. The process outlined uses Report Builder and provides detailed steps so the same method may be applied to a custom report. There are four primary tasks.

1. Add the new hidden Parameter.
2. Update the Query in Datasets with Client-related information.
3. Repeat for any SubReports.
4. Upload the edited reports to the EchoVantage SSRS report server.

Add the Hidden Parameter

The new hidden Parameter must be added and then moved up in the Parameter list to display just after DatabaseName.  With your report open in Report Builder,

1. Right-click on Parameter and select Add Parameter

 2. Add the Parameter with the following information.

• Name = userId  The Name value is case sensitive and must be entered exactly as shown.
• Prompt = Any value is acceptable as the Parameter is hidden.
• Choose Hidden for Select parameter visibility
• Select OK

Move New Parameter

Use the blue "up" arrow to move the newly added Parameter up in the list. It must come after DatabaseName.

Edit Datasets Related to Client Information

Expand Datasets and make a note of any Client-related information sets. Any Dataset that is Client related needs the Parameter added and the Query altered. In the Service Detail With Charges report, both the Detail and the ClientData Datasets have Client-related information. The steps below walk through updating both Datasets.

 1. Right-click on Details, select Dataset Properties, and then select Parameter.

• Select Add.
• Enter the Parameter Name of @userId
• Enter the Parameter Value of [@userId]. Use either the drop-down list or the function button (fx) to locate the Parameter Value.
• Select Ok.

2. Right-click on Details and then select Dataset Properties.

• The Query page displays by default.
• Review the Query as text.
• Make a note of any references to Client data, for example JOINs to the Clients table.
• The From clause needs updating in the example.
  • Add the following SQL immediately after any reference to the Clients table or after a Join to the Clients table
  • LEFT JOIN VClientFilter_All f ON c.id = f.Client AND f.appuser = @userId
  • The provided query uses c for the Client’s table alias (c.id).
  • The query may need to be updated to match the Client's table alias in your Query.
  • 
  • In the Service Detail With Charges report, the Client's table alias is cl. The Query above was updated.
• And the Where clause needs updating in the example.
  • Add the following SQL immediately after Where
  • If.Staff IS NOT NULL OR NOT EXISTS(SELECT 1 FROM Staff s1 JOIN ClientListFilters cf ON s1.id = cf.staff AND s1.AppUser = @userId)) AND
• • 
• Select OK after the query updates are complete.

3. Right-click on ClientData, select Dataset Properties, and select Parameter. 

Repeat step 1 above.

• Select Add.
• Enter the Parameter Name of @userId
• Enter the Parameter Value of [@userId]. Use either the drop-down list or the function button (fx) to locate the Parameter Value.
• Select Ok.

4. Right-click on ClientData and then select Dataset Properties. The Query page displays by default.

Repeat Step 2 above.

• Review the Query as text.
• Make a note of any references to Client data, for example JOINs to the Clients table.
• The From clause needs updating in the example.
  • Add the following SQL immediately after any reference to the Clients table or after a Join to the Clients table
  • LEFT JOIN VClientFilter_All f ON c.id = f.Client AND f.appuser = @userId
  • The provided query uses c for the Client’s table alias (c.id).
  • The query may need to be updated to match the Client's table alias in your Query.
  • 
  • In the ClientData Dataset, the Client's table alias is also c - no query edit was needed.
• And the Where clause needs updating in the example.
  • Add the following SQL immediately after Where
  • If.Staff IS NOT NULL OR NOT EXISTS(SELECT 1 FROM Staff s1 JOIN ClientListFilters cf ON s1.id = cf.staff AND s1.AppUser = @userId)) AND
• • 
• Select OK after the query updates are complete.

5. If the report has any SubReports, the hidden Parameter must be added to the Go to report action.

• Right-click on the SubReport's HERE link and select Text Properties
• 
• Then select Actions.
• Add the new hidden Parameter Name and Parameter Value.
• 
• Note the location of this Parameter in the list. When the Parameter is added to the SubReport .rdl file, it must be in the exact location in that report's Parameter list.

 Update the SubReport .rdl File

If your custom report has any SubReports, these must also be updated. The process is the same except for one critical distinction; the hidden Parameter must be listed in the same order that it is called from the main report on the Go to report Action page (see number 5 above). The example below details the changes made to the Service Detail With Charges for Export SubReport.

1. Open the Service Detail With Charges for Export SubReport in Report Builder.

2. Add the hidden Parameter.

• Right-click Parameter and select Add Parameter
• Add the hidden Parameter Name and Parameter Value as shown in the image below.
• 
• Select ok

3. Review the Parameter list to ensure the new Parameter displays in the list in the exact location as listed in the Use these parameters to run the report section of the Go to report Actions. (Step 5 above.)

• On the SubReport Parameter list, the userId parameter is last after the program.
• 
• On the Go to report Actions, userId is also last on the list after the program.
• 

4. Edit DataSets related to Client information. For this SubReport, these are the same actions as Steps 1-4 of the Edit DataSets related to Client information section above.

• Step 1 – Add Parameter to the Details DataSet.
• Step 2 - Alter the Query for the Details DataSet.
• Step 3 – Add the Parameter to the ClientData DataSet.
• Step 4 – Alter the Query for the ClientData DataSet.

Upload Edited Reports to EchoVantage

When all necessary edits are complete for applying Client List Filters to reports, the updated report .rdl files must be uploaded to the EchoVantage SSRS report server.