In SSRS, can you group multiple parameter values into one?-CodePudding

I am relatively new to SSRS but have been working with SQL for years. I have been tasked with creating a report that reflects shipped items based on their status. For example, I have x number of items with varying statuses including "IN_TRANSIT", "RECEIVING", "SHIPPED", "WORKING", and "CLOSED". The requestor is asking if I can provide the following options in a report drop down:

"IN_PROCESS" Status filter including all statuses except "CLOSED". "CLOSED".

Essentially, they want to be able to view all non closed statuses, closed, statuses, or all. Right now, I have it set so you can individually select all statuses, essentially getting them the data they want, just not with the "right" parameters.

My question is, does SSRS provide a way to essentially 'group' the non-closed statuses into one inside the report so that when they select "IN_PROCESS" it sends those non-closed statuses to the SQL query I have built in? The problem with using SQL for this is that the dataset I created to generate the dropdown options provides "CLOSED" and "IN_PROCESS" as it's output options, but when they select "IN_PROCESS" (sending that value to the filter in the report), since it's not an actual status, nothing comes back.

If more information or clarification is required, please let me know.

Thanks ahead of time!

CodePudding user response：

You can create a new column in your SQL query and use a CASE statement to give the value of IN_PROCESS or CLOSED for the applicable status. Then you will just need to the filtering condition to match the SSRS parameter to the new column.

CodePudding user response：

Depending on how often this case is likely to be reused should help determine how to approach it. If it sounds like it might become a regular process.... "Oh can we have another report with the same filter but showing xyz " then take the time to setup correctly and it will save time in the future.

Personally I would add a database table, if possible, that contains the status names and then a status group name (ignoring fully normalising for the sake of simplicity here).

CREATE TABLE StatusGroups(Status varchar(10), StatusGroup varchar(10))
INSERT INTO StatusGroups VALUES
    ('IN_TRANSIT', 'In Process'),('RECEIVING, 'In Process'),('SHIPPED', 'In Process'),('WORKING', 'In Process'),('CLOSED' 'Closed')

Then a simple view

CREATE MyNewView AS 
    SELECT t.*, g.StatusGroup 
      FROM MyTable t 
          JOIN StatusGroups g on t.STATUS = g.Status

Now change your report dataset query to use this view passing in the report parameter like this...

SELECT * 
    FROM MyNewView 
    WHERE StatusGroup = @myReportParameter

Your dataset for your report parameter's available values list could then be something like

SELECT DISTINCT StatusGroup FROM StatusGroups

This way if you every add more status or statusgroup values you can add an entry to this table and everything will work without ever having to edit your report.