CODEWITHSUNDEEP
sqlsql-serverreporting-servicesbids
s.bramblet
s.bramblet

Reputation: 199

SSRS expandable/collapsible columns

I need to create an SSRS report that has columns for almost each day of the month. This results in a report that is too wide for the screen and difficult to print.

I have modified the report to group by week with the days of that week below it. I would like to know how to expand/collapse the weekly columns.

Here is an example of the report:

Week 1          | Week 2           | Week 3
3/1 | 3/2 | 3/3 | 3/8 | 3/9 | 3/10 | 3/16 | 3/17 | 3/19
data| data| data| data| data| data | data | data | data
data| data| data| data| data| data | data | data | data
data| data| data| data| data| data | data | data | data

Can I somehow expand and collapse based on the Week group so that Week 1 expands and collapses Week 2, Week 2 expands and collapses Week 3, etc.? Week 1 would always be visible since it would expand and collapse Week 2.

Is this even possible?

Thanks in advance!

UPDATE: Here are my current groups:

Static <- Parent
-Static <- Child
Week <- Parent
-ID (essentially Date) <- Child

Week is defined as an expression as you suggested: =iif(DatePart("ww",Fields!Date.Value)=53,1,DatePart("ww",Fields!Date.Value))

The static "groups" are a static column that had a group which I deleted so that I could put field names in a static position.

How can I set visibility to toggle one week based on the previous week? If I set visibility for the Week group to toggle by Group1 then I get one toggle on the top Static group that toggles all the Week groups, not individually.

current groups

report sample

Essentially I want a toggle on Week 9 group that will expand/collapse Week 10... Week 10 should have a toggle that expands/collapses Week 11, etc.

Upvotes: 1

Views: 10682

Answers (2)

gruff
gruff

Reputation: 411

Old post but maybe still relevant.

I see how you only get the one toggle button.. I would do it like this:

Set an action property to the (Week 9, 10..) expression which runs the report with an increase on the parameter value.....

  1. Create a parameter in your report called WeekNumber with a text value
    • You can give it available values of (Select Distinct ReportWeek...)
    • And Default value of (Select Min(ReportWeek...)
      • This is all where the Select field ReportWeek is whatever expression is generating your Week 9, 10, etc
  2. Open the text properties and under action select Go To Report
  3. Select the report you are already in
  4. Pass a parameter Titled WeekNumber and a value with the expression:
    • =DateAdd("d", 1, Parameters!WeekNumber.Value)
    • You can just add 1 to the parameters if you're using a number to calculate the week #...
  5. Finally; You can set this for all of the Weeks which you want, and you would set the Visibility to Hidden = true if Fields!ReportWeek.Value > Parameters!WeekNumber.Value

This way, every time you click on one of the weeks, the parameter would increment and the next field would display. You could add in a text box which appears when Parameter = Max(ReportWeek) letting the user know

Pic showing parameter passing:

EDIT I realized that the pic and description for iterating the parameter are not the same, let me explain.. If the parameter value is a date (as in your example) you should use the dateAdd function, if you're just using a digit for the week, use the Param + 1 technique showed in the picture

Hope this helps anybody

Upvotes: 1

Habeeb
Habeeb

Reputation: 1040

enter image description here

In Group give expression to get a week. give sub group as date

Also change the properties of group and change visibility.

enter image description here

Upvotes: 3

Related Questions