How to formulate group expression in tablix?
I am developing a tablix in an RDL file in SSRS 2008R2. I have a column that should group on Quarter Date. Here is my current expression:
="Q" & Fields!Quarter_Date.Value & ": " &
iif(Fields!Quarter_Date.Value=3,"7/1-9/30",
iif(Fields!Quarter_Date.Value=4,"10/1-12/31",
iif(Fields!Quarter_Date.Value=1,"1/1-3/31",
iif(Fields!Quarter_Date.Value=2,"4/1-6/30","Invalid Date"))))
When I test out my sproc for this tablix, all of the Quarter_Date values = 1 or 2 in SSMS. But when I run this report for the same date range, I get one column for Quarter 1 and another column for "Invalid Date". Why would this be? Also, I
added filters on all of the tablices and column groups to only include rows where Quarter was IN 1,2,3,4. But even this didn't fix this problem.
Ryan D
April 16th, 2012 7:06pm
Hi Ryan,
In the above expression instead of using nested IIF statements, consider using SWITCH statement. Refer to the following URL:
http://www.sql3d.com/archive/2011/01/avoid-nested-iifs-statements-in-reporting-services-expressions-by-using-the-switch-function/
Also, please check if there are any NULL values in the Quarter_Date column in your database which might be causing this issue of additional column "Invalid Date".
Best Regards,
Datta
----------------------------------------------------------------------------------------------------
Dattatrey Sindol
My Blog: Datta's Ramblings on Business Intelligence 'N' Life
The information provided here is "AS IS" with no warranties, and confers no rights.
Please mark the post as answered if it solves your problem.
Free Windows Admin Tool Kit Click here and download it now
April 17th, 2012 5:24am
Thanks, but note that I said all of the Quarter_Date values = 1 or 2 in SSMS; no NULL values.Ryan D
April 17th, 2012 10:41am
Hi Ryan,
In the above expression instead of using nested IIF statements, consider using SWITCH statement. Refer to the following URL:
http://www.sql3d.com/archive/2011/01/avoid-nested-iifs-statements-in-reporting-services-expressions-by-using-the-switch-function/
Also, please check if there are any NULL values in the Quarter_Date column in your database which might be causing this issue of additional column "Invalid Date".
Best Regards,
Datta
----------------------------------------------------------------------------------------------------
Dattatrey Sindol
My Blog: Datta's Ramblings on Business Intelligence 'N' Life
The information provided here is "AS IS" with no warranties, and confers no rights.
Please mark the post as answered if it solves your problem.
Free Windows Admin Tool Kit Click here and download it now
April 17th, 2012 12:22pm