Prev: Multi-level grouping drill down SSRS report
Next: Help needed to calculate % Total
From: OriginalStealth on 21 Jan 2010 14:25
I have a report that is driven by accounting periods. The columns are 1, 2,
3, Q1, 4, 5, 6, Q2...YTD. Currently when the report opens all that is shown
is Q1, Q2, Q3, Q4, and YTD and the user can expand from there. What I want
to happen is, if we are in Q2, I want columns 4,5,6, Q2 to be expanded. So
the report should read Q1, 4,5,6, Q2, Q3, Q4, YTD. Is this possible?

Thanks in advance
From: Andrew Karcher [SQL] on 21 Jan 2010 15:25
Original Stealth,

Certainly can.

I created the following dataset:

SELECT 'Q1' AS Expr1, 1 AS Expr2, 500 AS Expr3
UNION ALL SELECT 'Q1' AS Expr1, 2 AS Expr2, 300 AS Expr3
UNION ALL SELECT 'Q1' AS Expr1, 3 AS Expr2, 200 AS Expr3
UNION ALL SELECT 'Q2' AS Expr1, 4 AS Expr2, 700 AS Expr3
UNION ALL SELECT 'Q2' AS Expr1, 5 AS Expr2, 800 AS Expr3
UNION ALL SELECT 'Q2' AS Expr1, 6 AS Expr2, 1000 AS Expr3
UNION ALL SELECT 'Q3' AS Expr1, 7 AS Expr2, 200 AS Expr3
UNION ALL SELECT 'Q3' AS Expr1, 8 AS Expr2, 200 AS Expr3
UNION ALL SELECT 'Q3' AS Expr1, 9 AS Expr2, 400 AS Expr3
UNION ALL SELECT 'Q4' AS Expr1, 10 AS Expr2, 400 AS Expr3
UNION ALL SELECT 'Q4' AS Expr1, 11 AS Expr2, 500 AS Expr3
UNION ALL SELECT 'Q4' AS Expr1, 12 AS Expr2, 600 AS Expr3

I then created a table and added Expr2 and Expr3 in my detail row and
created a Row Group based on Expr1 (Quarter)

Then in the Group Properties for the Detail row on the visibility I
did the following:

1. Set the "Display can be toggled"
2. Set the initial visibility for the group to be the following
expression:
=IIF(Fields!Expr1.Value = Parameters!Quarter.Value, False, True)

I used a parameter instead of a calculation based on the date just for
simplicity reasons. so if the value of my parameter equals "Q2" only
the three detail rows from Q2 will be shown.

I did this is SSRS 2008, but I do not see any reason why this should
not work in other versions as well.

I can send you the full .rdl if the above does not explain how I
accomplished it. Let me know if you have any other quetions or that
does not solve your problem.

Cheers,
Andrew Karcher
SQL Server MVP

On Thu, 21 Jan 2010 11:25:01 -0800, OriginalStealth
<OriginalStealth(a)discussions.microsoft.com> wrote:

>I have a report that is driven by accounting periods. The columns are 1, 2,
>3, Q1, 4, 5, 6, Q2...YTD. Currently when the report opens all that is shown
>is Q1, Q2, Q3, Q4, and YTD and the user can expand from there. What I want
>to happen is, if we are in Q2, I want columns 4,5,6, Q2 to be expanded. So
>the report should read Q1, 4,5,6, Q2, Q3, Q4, YTD. Is this possible?
>
>Thanks in advance
 | 
Pages: 1
Prev: Multi-level grouping drill down SSRS report
Next: Help needed to calculate % Total