Setting default parameter values

7 posts, 0 answers
  1. Al
    Al avatar
    217 posts
    Member since:
    Oct 2006

    Posted 06 Jan 2017 Link to this post

    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.

     

  2. Stef
    Admin
    Stef avatar
    3610 posts

    Posted 06 Jan 2017 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  3. Al
    Al avatar
    217 posts
    Member since:
    Oct 2006

    Posted 09 Jan 2017 in reply to Stef Link to this post

    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.

  4. Al
    Al avatar
    217 posts
    Member since:
    Oct 2006

    Posted 10 Jan 2017 Link to this post

    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?

     

     

  5. Stef
    Admin
    Stef avatar
    3610 posts

    Posted 10 Jan 2017 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  6. Al
    Al avatar
    217 posts
    Member since:
    Oct 2006

    Posted 10 Jan 2017 in reply to Stef Link to this post

    Thanks Stef,

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

  7. Stef
    Admin
    Stef avatar
    3610 posts

    Posted 11 Jan 2017 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Back to Top