August 4, 2011 at 6:05 am
Hi,
I have made a calculated column in my query which categorizes these costs as low, med, high.
|--------------------|
| Cost |
|--------------------|
|LOW | MED | HIGH|
|-----|-------|------|
|100 | 500 | 1010 |
|105 | 600 | 1200 |
|499 | 900 | 2000 |
|-----|-------|------|
anything below 500 :low
500 to 1000 :Medium
>1000 :High
I also have parameter at report level cost_type which has multiselect as low, med, high.
Now in my SSRS report I am trying to hide the column based on option chosen:
for instance I if I choose LOW in my multiselect the other 2 columns MED and HIGH should Hide and so on. also If I choose LOW and HIGH then MED should not be on the report.
*** now I am unable to include this hiding properties , for instance for the first column (LOW )I tried visibility>Hidden> (if cost_type =low, false,true) and so on for other MED and HIGH column,
NOT working...the report comes blank but if I remove this visibility thing> and select all> LOW , MED, HIGH everything is fine and report shows everything..
so basically I need to have this conditional hiding for all 3 columns.
Thanks for your help.
August 4, 2011 at 7:16 am
Why don't you just use your parameter as a constraint on your dataset query rather than trying to hide/show your data?
August 4, 2011 at 8:00 am
Thanks.
I can (low, med, high) in one single column annd do the sorting but here I need to separate them in 3 columns because thats the way client wants it...:( ..
August 4, 2011 at 11:37 am
SQL_Nw (8/4/2011)
Thanks.I can (low, med, high) in one single column annd do the sorting but here I need to separate them in 3 columns because thats the way client wants it...:( ..
If you are only showing one column at a time, how will the client know if you built it with 3 columns that have Hide features or one column that simply shows the selected parameter?
A matrix may be your answer, then it will only show columns when there is data for those columns.
August 5, 2011 at 5:34 am
Thanks Daniel,
Its actually going to be a multi-select parameter and user wants the flexibility to choose for instance LOW + MED , Only LOW, ALL & So on.... I figured one column wont be of much help.
August 5, 2011 at 6:01 am
For each of the three columns I am using visibility /Hiddden condition as for instance for the LOW column :
=iif(Parameters!Cost_Type.Value="LOW", false, true)
When I choose 'LOW' in my report i the multi select I get : ERROR MESSAGE
The hidden expression for the the table 'table1' containes an error: Overload resolution failed because no public'=' can be called with these arguments:
'Public Shared Operator=(a as String, b as String) as Boolean':
Argument matching parameter a can not convert from 'Object()' to String'.
August 5, 2011 at 6:53 am
SQL_Nw (8/5/2011)
For each of the three columns I am using visibility /Hiddden condition as for instance for the LOW column :=iif(Parameters!Cost_Type.Value="LOW", false, true)
When I choose 'LOW' in my report i the multi select I get : ERROR MESSAGE
The hidden expression for the the table 'table1' containes an error: Overload resolution failed because no public'=' can be called with these arguments:
'Public Shared Operator=(a as String, b as String) as Boolean':
Argument matching parameter a can not convert from 'Object()' to String'.
Now, I see...the expression helps a lot.
In a multi select expression where you can have more than one value you can do a scalar comparison like that. Try this instead
=IIF(InStr(JOIN(Parameters!Cost_Type.Value,","),"LOW"),False,True)
August 5, 2011 at 11:50 am
Thank you sooooo much Daniel. That worked like magic,
but now I face a new problem, I want to Hide/show "row" based on multi-select parameters., but I cant
although the columns are hiding/showing perfectly, the rows aren't .so basically if I choose "Medium" , though only "Medium" column becomes visible, table rows show other records which have cost type of "LOW"and "HIGH"
as you know My stored proc has a caluculated column cost _type which categorizes the cost as Low, medium, high.
I want to Hide/show row based on multi-select parameters. I wrote: Row> Visibility>Hidden
=IIF(Fields!Cost_type.Value =Parameters!Type.Value,
False,True)
and that also throws error, looks like some multi select issue again. How do i do that
Thanks again !!
August 5, 2011 at 2:02 pm
Just like the one that works for columns but substitute
Fields!Cost_type.Value
for
LOW
August 8, 2011 at 6:48 am
Thanks,
As you said, I entered in the Row's Visibility> Hidden>expression
=IIF(InStr(JOIN(Parameters!Cost_Type.Value,","),Fields!Cost_type.Value),False,True)
But now if I choose LOW as an option in the multiselect for instance, it hides all rows and I dont get to see the even the rows having cost_type low. same is the case with other options
Am I missing something here? Thanks !
August 8, 2011 at 7:01 am
Looking at your original post I am not sure how Low, Med, high, relates to your row values. Your columns have a clear relationship to them, but how are your rows set up and how does modifying their visibility help?
August 8, 2011 at 8:57 am
I just used this and it seem to work fine...I hope I am doing it right
=IIF(instr(Join(Parameters!Cost_Type.Value,", "),Trim(Fields!Cost_Type.Value))=0,True,False)
Thanks a bunch !!
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy