How to tie the column visibility expression in SSRS to current year and current month?

0
votes

I have an SSRS report that has all 12 months as columns and one more column called "Year". I already have a condition set that takes the current month of the report run date and displays only the months that have passed. For example, the expression looks like this for March =IIF(Month(today()) < 3, True, False).

Now I have to tie this to the Year column as well. The condition is- if the Year column has years prior to the current year, the condition above should not apply. So, if I am running 2020 data today, all 12 month columns should appear. If I am running 2021 data, I should only see the January column. Can some one please help me with this? And yes, there will only be one year in the "Year" column. The data will never have more than one year. Thank you.

Edit 1:

If the report is run for 2020, the data should look like this on the report. enter image description here

If it is run for 2021, only the YEAR and JANUARY columns should appear. Starting February of this year, YEAR, JANUARY, AND FEBRUARY should appear.

1
reporting-servicesssrs-2012ssrs-2008-r2ssrs-tablixboolean-expression
Can you edit your question and share a sample of your dataset output. Also share you report design. If your data is normalised then you could use a matrix rather than a table and you would not need any conditions to hide columns as the data would not be available. – Alan Schofield
Please check my edit – Susie Dent
Is this what the data looks like directly from your dataset query or is this the layout in the report output, or both? – Alan Schofield
This is what the data looks like from the query dataset. – Susie Dent

1 Answers

1
votes

As your dataset is not normalised, you will have to set the column visibility on each column as you are doing already.

Try using the following expression. (using March as an exmaple)

=(Month(today()) < 3 AND MAX(Fields!Year.Value, "DataSet1") = YEAR(Today())

Note: The "DataSet1" reference is the name of your dataset. It is case sensitive and must be enclosed in quotes as shown above.

There is no need for the IIF() expression as the above will return true if either condition is met.

Alternative

If your data was normalised (you may be able to edit your dataset query) and looked something like this...

Year  Month    Amount
2021  1        10
2021  2        15

Then you could simply use a matrix instead of a table, and have a column group by month. There would be no need to set visibility on anything as the data simply is not there so no column would be rendered for the months with no data.