SSRS 2012 limit values returned from parameter dataset

Question

Running SSRS2012. We report using cubes, so I cannot change the MDX/Query code.

I have a simple query from a Client cube: Client.ClientID, Client.ClientName, Client.ClientAddress

I have used the Query Designer to add ClientID as a parameter.
SSRS has automatically:

• created the parameter in the Report Designer @ClientClientID
• created the parameter dataset
• added the parameter under the parameter tab in the Client dataset

This is expected behaviour and the report runs as expected, prompting me to choose a value from the list. If I do, it returns the data for the single client I'm after.

This is my problem.

Although it works, the list of values returned is very long and so is difficult to use. Users will only be running this report for one client at a time so they'd like to type in the single client id and run the report.

I've tried everything I can think of.

The closest I've come is adding another parameter @PreFilter and using that as a filter on the parameter dataset.

However, to the user it's terrible. They're prompted to type the client id into @PreFilter, then they have to wait an age (as the parameter dataset is filtered) and then they select the exact same client id from the @ClientClientID parameter prompt.

I've even specified the default value of @ClientClientID to be @PreFilter, but they're still prompted to Select a value.

There must be a better way!

Answer 1

Sounds to me like a data modelling issue. Probably the ClientId in parameter is taken directly from the fact table instead of from Client dimension. If the data is properly modelled e.g. star schema having separate facts and dimensions the retrieving of client data should be faster.

Answer 2

Finally - a much better way.

1. From the Report Design Page
2. Ensure that the Report Data pane is showing (if not, click on the View menu and click on Report Data option)
3. From the Report Data menu, expand the Parameters folder (click the + button)
4. There will be a parameter that has been automatically created when you click "Parameter" against the ClientID in Query Designer (along with the auto-created parameter dataset).
5. Right click this parameter (@ClientClientID)
6. Select Parameter Properties
7. Set the Available Values option to "None"
8. Set the Default Values option to "None"
9. Click OK
10. From the Report Data pane, expand the Datasets folder (click the + button)
11. Right click on each dataset (except for the auto-created parameter dataset)
12. Click on Dataset Properties
13. Click on Parameters menu item (left hand side)
14. Under the Parameter Name you will see ClientClientID listed.
15. Under the Parameter, you will see a drop down box with @ClientClientID. You will need to change this
16. Click on the Fx button next to @ClientClientID
17. Replace =Parameters!ClientClientID.Value with ="[Client].[ClientID].&[" + Parameters!ClientClientID.Value + "]"
18. Click OK You should be good to go.