SSRS: optional parameter, grouper, column display.... is it possible

Question

SSRS: optional parameter, grouper, column display.... is it possible

mario17

SSCertifiable

Points: 6079
More actions
October 28, 2013 at 9:12 am

#285251

Hi,
I'm trying to make one of parameter and grouper in my SQL optional, is it possible, for now I'm thinking:
1. in SSRS make parameter UseParam1 = Y/N
if @UseParam1= Y, then fill drop down menu for Param1 with all list (like we do for cascade.). Not sure is it possible hide parameter based on some condition??
2. in SQL no problem with Selection
.. where @UseParam1 = N or (@UseParam1 = Y and Param1 in item1,item2,item3)
3. in SQL
select col1, col2,
case when @UseParam1 = Y then ParamCol else 'NoGroup' end col3 --/ is it possible completely eliminate this column based on @UseParam1 ???????
from t1
group by col1, col2,
case when @UseParam1 = Y then ParamCol else 'NoGroup' end..
4. and then in SSRS I tried to set Visisbility for tablix column based on @UseParam1, and that's where it breaks, this column part of group, I Played a bit with all setups with no success.
Is it possible? appreciate your leads.
Best
Mario

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

mario17 (10/28/2013)
Hi,
I'm trying to make one of parameter and grouper in my SQL optional, is it possible, for now I'm thinking:
1. in SSRS make parameter UseParam1 = Y/N
if @UseParam1= Y, then fill drop down menu for Param1 with all list (like we do for cascade.). Not sure is it possible hide parameter based on some condition??
2. in SQL no problem with Selection
.. where @UseParam1 = N or (@UseParam1 = Y and Param1 in item1,item2,item3)
3. in SQL
select col1, col2,
case when @UseParam1 = Y then ParamCol else 'NoGroup' end col3 --/ is it possible completely eliminate this column based on @UseParam1 ???????
from t1
group by col1, col2,
case when @UseParam1 = Y then ParamCol else 'NoGroup' end..
4. and then in SSRS I tried to set Visisbility for tablix column based on @UseParam1, and that's where it breaks, this column part of group, I Played a bit with all setups with no success.
Is it possible? appreciate your leads.
Best
Mario

First, what do you mean by "grouper"? Are you talking about implementing some sort of dynamic grouping? (e.g. you select ParameterA and it groups by ParameterA) Second, you cannot hide a parameter based on a condition. Parameters are either visible or they are not; this is an SSRS limitation. (FYI).

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

mario17 SSCertifiable Points: 6079 More actions · Answer 2

Tx, yes I mean dynamic groupers, I think it can be replaced with just literal so column will be there but no actual grouping will happened.

Best

Mario