Automating what columns get hidden (visibility = false) in a matrix

Question

Post reply

Automating what columns get hidden (visibility = false) in a matrix

pharmkittie

SSCertifiable

Points: 5775
More actions
August 10, 2015 at 4:17 pm

#398868

I have an SSRS report (using Visual Studio 2013 against an instance of SQL Server 2014). I mention this in case there are any new features that will make it easier or possible to do what I need.
I'm using a matrix because I have four rolling quarters of data. The users want to see the previous quarter data in the last column, with the three previous columns containing the oldest quarter to next newest quarter. That makes sense to me and they'll always sort that way if I concatenate the year and the quarter (e.g. 2014Q3, 2014Q4, 2015Q1, 2015Q2) but I am looking for a way to automate the columns with indicators that should be hidden based on the system date.
In other words, the rightmost column of data would be:
=IIf(((DatePart("q", Today)=3) and Fields!Qtr.Value = 1),False, True) This snippet is from a visibility property on a gauge panel. The IIf(((DatePart("q", Today)=3) is the relevant part.
My questions is: If I have a separate column to the right of each quarter's data value (as an indicator within a gauge panel), how can I hide all but the latest quarter's indicator? I believe that I can manage it by setting each indicator column through a hard coded formula similar but with more and/or clauses, without hardcoding the values for quarter? I'd like it to compare against the system date to see that the visible column for a report run in quarter three n(like now) should have the quarter2 data's separate column with indicator visible and all the others hidden.
Here's a snip showing separate columns for numeric value and indicator.
Thanks in advance.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Mohamed I. SSCrazy Points: 2774 More actions · Answer 1

Hi

Can you please post a screenshot of your matrix region and grouping settings if possible ?

Thanks.

:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:

pharmkittie SSCertifiable Points: 5775 More actions · Answer 2

Thanks Mohammed. I am using formulas like this to control visibility of the indicators:

IIf(((DatePart("q",Today)=3) and Fields!Qtr.Value=2), False,True)

and this to control the visibility of the columns containing the values:

IIf(((DatePart("q",Today)=3) and (Fields!Qtr.Value=2 or Fields!Qtr.Value=1)), False,True)

This is working great (I put the code in the column's visibility to shrink the width to zero). It works great but I don't know how to automate it further so the Qtr.Values and DatePart("q", Today)=3 doesn't have to have the quarter values hardcoded )the "=3" and the "Qtr.value = 2 or Qtr.Value = 1".

Would the way to do it be prompting the user for the latest quarter's data that they want and then using that to do what I've hard coded? For example, I could run a report to get quarters 1 and 2 regardless of when I run the report. If so, how? If not, how could I do it?

Thanks!

pietlinden SSC Guru Points: 63428 More actions · Answer 3

What if you put a parameter in the place of the literal numeric values?

IIf(((DatePart("q",Today)=@theQuarter) and Fields!Qtr.Value=(@theQuarter-1)), False,True)

pharmkittie SSCertifiable Points: 5775 More actions · Answer 4

pietlinden (8/12/2015)
What if you put a parameter in the place of the literal numeric values?
IIf(((DatePart("q",Today)=@theQuarter) and Fields!Qtr.Value=(@theQuarter-1)), False,True)

Thanks pietlinden, I will do this. By tomorrow I will post again if I have another question about or if I am able to finish it correctly. I will mark your suggestion as the answer. Thanks again.