Post reply

Conditional Hiding/Visibility for column issue? Thanks

  • August 4, 2011 at 6:05 am

    #245533

    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

    #1363625

    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

    #1363663

    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

    #1363852

    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

    #1364241

    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

    #1364253

    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

    #1364311

    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

    #1364558

    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

    #1364616

    Just like the one that works for columns but substitute

    Fields!Cost_type.Value

    for

    LOW

  • August 8, 2011 at 6:48 am

    #1365170

    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

    #1365181

    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

    #1365273

    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