Setting default parameter values

[Al]

Hi,

My report uses a multi-value parameter to filter report data and the parameter values are built dynamically from a datasource. By default the report opens as blank because there is no default value set, ideally all of the parameter values should be selected by default ie. to show all data.

I know this is possible by customizing the report controller as per my post here, but I was wondering whether there is a technique for this using only the report designer? a setting on the parameter to "select all values by default" would be the perfect solution.

 

[Stef]

Hello Al,

Below is a quote from my response in your other support ticket on the same question, where we should consider the expression is required in the context of the Report Server application.

"This adds new information to your previous threads about using a custom function.
The Report Server is not designed for loading external assemblies with data and functions.

Yes, it can be configured manually by copying the DLL on the server in the corresponding executing folders, and adding the settings in Report Server\Telerik.ReportServer.Web\Web.config (for the Report Server preview) and Report Server\Services\Telerik.ReportServer.ServiceAgent.exe.config (for the Scheduler service) - both require restarting the Report Server web site in IIS and the Windows Service of the scheduler. Note that we do not recommend these manual settings.

The approach we can suggest you to select all values is to make the multivalue report parameter's AllowNull to true and interpret it as 'select all' in the data-retrieval method e.g. How to use MultiValue Report Parameter in a SQL query.
You will have all values selected initially. Then the editor of the multivalue parameter has 'clear selection' and 'select all' options."

Regards,
Stef
Telerik by Progress

[Al]

Hi Stef,

This post was not actually related to the report server, it was a more generic question about to handle setting default values for multi-value parameters in the standalone designer. The reports created with the designer may be used in the html viewer or the report server so I wanted to solve both problems.

Anyway, thanks for the reply, I'll look into your suggestion as it is much simpler than hooking external dll's or writing custom functions in the controller.

[Al]

Hi Stef,

There is a problem with your suggestion here: suggestion. When using sql filtering like this:

SELECT * from HumanResources.Department

WHERE (@SelectedValues IS NULL) OR DepartmentID IN (@SelectedValues)

 

Then the parameter only works when it is null or when one value is selected, if you select two or more values are selected then the report fails. The issue has been mentioned here: Multi-value problem

In that same post you suggest using filters rather than direct sql as above - is this the recommended solution then for handling this problem?

 

 

[Stef]

Hello Al,

Thank you for this point. The KB article will be edited for the case when the multivalue report parameters is evaluated against NULL.
The SQL query would be:

SELECT * from HumanResources.Department
WHERE (COALESCE(@SelectedValues, NULL) IS NULL) OR DepartmentID IN (@SelectedValues)

The above uses the COALESCE function. You can also use the report-side filtering through the data item's Filters collection.


Regards,
Stef
Telerik by Progress

[Al]

Thanks Stef,

Do you recommend using one method over the other or are both the 'coalesce' and 'filter' methods ok?

[Stef]

Hello Al,

Both methods are OK. From performance point of view, filtering data on retrieval is better, as you will save from pulling all records and processing them with the report's settings.

For more details, check Filtering data.

Regards,
Stef
Telerik by Progress