SSRS : Hide column by parameter
Hello, I tie up columns with parameters.what ever column are selected only those column should be displayed. Now my parameter are string data type. I write this expression in column-->propertyvisibility--> hide-->Expression =iif(len(Parameters!VALUEDATE.Value(1)) > "0",false,true) I am gettin error saying "Index was outside the bounds of the array" kindly help me !!!
February 15th, 2008 1:16am

Have you defined the VALUEDATE parameter as multi-valued and does it contain 2 values in there?
Free Windows Admin Tool Kit Click here and download it now
February 15th, 2008 1:52am

Hi Monika, It clearly gives that your array index is out of the array boundary. How many parameters are there in your multivalued array and just check , Is this parameter "Parameters!VALUEDATE.Value(1)" is your first parameter in the array , If so then give this index as "Parameters!VALUEDATE.Value(0)" because SSRS array index numbering starts from 0. Hope it will work fine for you. Anyway please let me know if you get the solution. Thanks Mahasweta
February 15th, 2008 9:57am

Yes VALUEDATE parameter have multi-valued parameters,it can have any value for example.... I am passing each column parameter. first column = Parameters!VALUEDATE.Value(0) Second Column = Parameters!VALUEDATE.Value(1) . . . . . thirty Column = Parameters!VALUEDATE.Value(29) Now user can select any parameter lets say 2 , 5 ,7, 23. so only this 4 column should be generate. Another issue is the report format is ------------------------------------------------------------------------------------- Position -------------------------------------------------------------------------------------- Column1|Column2| Column3|Column4|Column5..............|Column30| So you see the report header is the fixed string. and sub column are dynamic column.
Free Windows Admin Tool Kit Click here and download it now
February 15th, 2008 11:50pm

Your approach seems correct; the only possible issue that seems evident is that the parameter VALUEDATE is not getting populated. What I would have done in a similar situation is that make a very simple report, place a text box on it and assign the text box the value of Parameters!VALUEDATE.Value(1). But use the same code from the front end that is being used for the original report and check if the values are being passed correctly in this report. If this goes through fine then the possiblity of values not being assigned to parameters are ruled out and then you have some other place to scratch your head on!! The error message is quite evident; so the only probable thing that can be done is to unit test each possible scenario that can cause this. And it may very well turn out that the issue was something else.
February 16th, 2008 2:41am

Hi there I played around with this for a while, doing stuff like this (in this case we are looking for the 4th value): Code Snippet =IIF(Parameters!VALUEDATE.Value.Length >= 4, Parameters!VALUEDATE.Value(Parameters!VALUEDATE.Value.Length-1), "less than four") ... and it seemed to work okay. I did see the error that you are talking about if I didn't check the length of the multi-select parameter value, though. For example, it doesn't work consistentlyto check Value.Count. Have you tried it this way? I think you should do your visibility of the columns the same way. IOW, check the Value.Length to see if a column should be hidden because the user has chosen fewer values than the one that column is supposed to represent. Regards, >L<
Free Windows Admin Tool Kit Click here and download it now
February 17th, 2008 11:06am

Hello, Finaly this issue is resolved.Have to add a code in visibility --> expression =iif(Parameters!Date.Value.Length = "your column number", False, iif(Parameters!Date.Value.Length > "your column number", (iif(Parameters!Date.Value (Parameters!Date.Value.Length-1) > "0",False,True)) ,True) ) and it works perfectly.Courtesygoes logicof Lisa code , Thank you Lisa! Happy Coding!!
February 20th, 2008 4:29pm

Glad to help. I must admit I was surprised when I experimented that this property seemed to be the best way to do it. >L<
Free Windows Admin Tool Kit Click here and download it now
February 20th, 2008 9:05pm

I'm glad to find this info, but it doesn't work completely for me.Why can't i see this property ".length"? Parameters!SubColumn.Value. only allows these values: Equals, GetHashCode,GetType,ReferenceEquals,ToString.
March 20th, 2008 8:59pm

Oh, it works all right (assuming this is a multi-valued param). Just because intellisense doesn't show a property doesn't mean it isn't there. Try it and see. Just put Code Snippet=Parameters!SubColumn.Value.Length in some textbox expression to prove it to yourself, then go on to figure out what the real problem is <s> >L<
Free Windows Admin Tool Kit Click here and download it now
March 21st, 2008 7:59am

Thanks for thetip about intellisense!I won't trust it totally from now on. So this visibility aspect iscoming along, but i'm seeing that if, say, subcolumn string values 2, 5, and 7, are chosen from the multivalue param list before viewing the report, then the first 3 columns appear in the report, not the 2nd,5th, and 7th. Is thathow this should work? What could i be doing wrong? Here's how the visibility/hidden expression is set for the 5th column in the report table: =iif(Parameters!SubIteration.Value.Length = "5", False, iif(Parameters!SubIteration.Value.Length > "5", (iif(Parameters!SubIteration.Value (Parameters!SubIteration.Value.Length-1) > "0",False,True)) ,True) )
March 22nd, 2008 1:44am

The purpose of the original code, in any case, was to find the "first X columns", not the columns that match certain string values that equate to numbers. The contents of each column were dynamic in the original poster's report. So the first, second, and third column *would* always have the correct results for the poster's cases, if the user picked three choices (whatever they were) from the list of possible columns. You're trying to solve a different problem and it is probably simpler to solve. Inyour case it has nothing to do with the *length* of the set of values that was chosen by the user, it sounds like, but what the values actually were that were chosen. >L<
Free Windows Admin Tool Kit Click here and download it now
March 22nd, 2008 7:17pm

I am kind of looking to do the same thing using a chart instead of rows/columns. Would it work similarly?
April 4th, 2008 1:34am

Hi Monika, I have a problem. Please help me. I have different fields in a cross-tab report like - Company, Sector, Region, Branch and then execId. I want to display only those fields to which a user belongs. Example - If a director logs in then I want to display all fields, if a Manager logs in then I want to display Branch associated to that manager and execs under that branch and so on. Also, Company, Sector, Region, Branch and execId are groups. Visibility of one depends on the visibility of the previous group. I tried to use your idea of writing expression in visibility property, but it fails. If I try to hide Company group for a sales person(exec) then it hides all the fields as Company is the first group. Could you please let me know how can I do that in Reporting services? I can get their networkId when they log in. Regards, Ashish
Free Windows Admin Tool Kit Click here and download it now
April 7th, 2008 4:06pm

try this code: hiding and showing in a multi-values parameters in ssrs=IIF(instr(Join(Parameters!Parametername.value, ", "),"ColumnName")=0,True,False)the only things you need are changes :Parametername , ColumnNamehope it helps
October 21st, 2009 11:40pm

Hi, Sorry to re open the once close thred, I am stuck with an issue and am seeking help. Any suggestions are highly appreciated. Your thread looks similar to what I am looking for. My issues is Data Source :SSAS Report Type :Matrix (Drill Down) I have a report Year :2010 Jan Feb March April Diff(last2 months) Account Sub Acc1 1 2 1 2 (2-1)=-1 Sub Acc2 2 1 1 1 0 Account2 Sub Acc2 3 3 1 1 0 Sub Acc 1 4 1 4 -3 I am able to create the report create totals , sub totals etc, my issue is the population or calculation of the last column “Avg” last column depends on the parameter (EndMonth), it is always EndDatevalue –EndDate-1.value. Thanks, Devi
Free Windows Admin Tool Kit Click here and download it now
January 14th, 2011 9:01pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics