How to create a parameter for multiple columns in SSRS

Question

I have created a report, the columns are like this below:

Date        
Sponsorsource    
totaldeal   
totalnewcardeal    
totalusedcardeal

Now, I want to create a parameter "dealtype". The Parameter dealtype has 3 labels:

totaldeal, 
totalnewcardeal, 
totalusedcardeal

If I select "totaldeal" in parameter "dealtype, the report will show like this:

Date        
Sponsorsource    
totaldeal

However, when I created parameter "dealtype", I added 3 lables and values of totaldeal, totalusedcarsdeal, totalnewcarsdeal in section of "specify value''

When I preview the report, I select "totaldeal" in parameter "dealtype", The column and values of totalusedcarsdeal and totalnewcarsdeal still showed up, like this:

Parameter dealtype= totaldeal
Report:
Date        
Sponsorsource    
totaldeal   
totalnewcardeal    
totalusedcardeal

Can anyone help me one it? Thank you very much!!

This is long process but here s hint - 1) Show hide column based on the selection set the expression for visibility. 2) If parameters are passed depending on the selection use the [Conditional where clause.](http://stackoverflow.com/questions/18629132/conditional-where-clause-in-sql-server/18629782#18629782) — Mahesh, Mar 16 '15 at 07:29

score 0 · Answer 1 · edited May 16 '19 at 21:24

0

It sounds like you haven't restricted the columns you return. You could try this:

SELECT DATE
    ,Sponsorsource
    ,CASE @dealtype
        WHEN 'Total Deal'
            THEN totaldeal
        WHEN 'Total New Car Deal'
            THEN totalnewcardeal
        WHEN 'Total Used Car Deal'
            THEN totalusedcardeal
        ELSE NULL
        END AS 'Deal Type'
FROM Deals

edited May 16 '19 at 21:24

Jean-François Fabre

137,073
23
153
219

answered Mar 16 '15 at 08:05

sams

439
1
4
17

score 0 · Answer 2 · answered Mar 24 '15 at 21:51

For any column that you want to show or hide based on your parameter, you would right click on the column (the grey rectangle above the column) and select Column Visibility. Click on Show or Hide Based on Expression. In the totaldeal column expression, enter

=IIF(Parameters!dealtype.Value = "totaldeal", False, True)

And do the same for the other columns - changing the "totaldeal" to the column name that you put in your parameter selection list (totaldeal, totalnewcardeal, totalusedcardeal).

How to create a parameter for multiple columns in SSRS

2 Answers2