none
Reproduce an Excel Pivot table in SSRS 2008

    Question

  • I'm trying to reproduce an Excel Pivot table in SSRS 2008, in Report Designer.

    I may not be going about this the right way, but so far I'm going with the option of having 2 reports, the high level (pivot table equivalent) report and a detailed level report (the one you get when you double click on a number in the excel pivot table).

    In the high level (pivot) rdl I've created 2 groupings (Year & Month) based on a date column in the underlying view. =Year(Fields!CensusDate.Value) and =Month(Fields!CensusDate.Value). And there are counts in the other columns. So Year & Month displayed to the left in the matrix and the rest as columns.

    The idea is to click on a count for a particular Year and Month and it opens those particular results up in the detail report. Something that in excel was fairly simple, but I'm having trouble with in SSRS.

    Can anyone please help me with the jump to the detail report or suggest a better solution for this requirement? I found a good tutorial on the jump part (http://www.mssqltips.com/tip.asp?tip=1283), but not sure how to make sure it filters it to the selected Year/Month.

    Wednesday, March 02, 2011 5:43 AM

Answers

  • Fixed it. I'm sure this can be done several ways and mine may not necessarily be the best one, but what I ended up doing was:

    On the high level report, added 2 calculated fields. I know you can add those as a separate dataset and then link it to your main set, but I've just added them to the main set directly. One for the start date fxStart and one for the end fxEnd. I then used an expression that would create the dates in string format:

    For fxStart:

    =CDate(CStr(Year(Fields!CensusDate.Value)) + "-" + CStr(Month(Fields!CensusDate.Value)) + "-01")

    For fxEnd:

    =DateAdd(DateInterval.Day, -1, DateAdd(DateInterval.Month, 1, CDate(CStr(Year(Fields!CensusDate.Value)) + "-" + CStr(Month(Fields!CensusDate.Value)) + "-01")))

    I then added 2 parameters @StartDate and @EndDate to the other, Detail, report.

    Now for the jump. On the High level report Count fields I used a standard 'Go to report' option and selected the Detail report, with the parameter StartDate linked to fxStart and EndDate to fxEnd. I could have put the above code directly in those fields, but thought the separate calc fields would be a bit more transparent.

    That approach worked well and the benefit of it is that you can run the Detail report independently now (rather then always having to pick the data through the High level report as well as being locked into using months), using the parameter fields.

    Simple fix really. I think the fact I started with the Year/Month group fields put me onto the wrong track to start with.

    • Marked as answer by A. Bettik Wednesday, March 30, 2011 6:21 AM
    Wednesday, March 30, 2011 6:21 AM

All replies

  • The link you have shared itself is having the solution. Look at the topic "Parameterized Solution" in which it shows how to pass parameters to the report .. based on the parameter the report data will be filtered..in you case the parameters will be year and month.

    Wednesday, March 02, 2011 8:58 AM
  • ".in you case the parameters will be year and month"

     

    Sure, I'm seeing the open up report etc. The bit I'm wondering about is what to enter as parameters. My Year and Month are groupings (using the expressions I mentioned earlier).

    How do I use parameters to ensure that when I click on the field the records for that particular year and month show only?

    Thursday, March 03, 2011 3:09 AM
  • For that you should keep the filter at the dataset level at the report , so that when the parameters passed it will filter the records accordingly. This is typically passing the parameter to a report. If the report you are having dont have the parameters , you have to add them and pass it from the parent report. For more details on adding parameters take a look at below link

    http://msdn.microsoft.com/en-us/library/ms155917.aspx

    Thursday, March 03, 2011 4:32 AM
  • I appreciate the help Sorna, and I've had a look at the link you sent me, but I don't quite understand still. The dataset has a date column in it and I'm grouping that by Year (parent) and then by Month (child) as explained previously.

    I tried something different now, and added them as calculated fields in the dataset and grouped on those. The results seem to be the same as I had before in the high level report. Is this a good approach though?

    So the high level report looks something like this (hopefully it renders ok):

    Year    Month    Count1    Count2    Etc

    2009    Nov        1010      792

               Dec        1024      876

    2010   Jan         1022      866

              Feb         1015     796

    Etc

    So, when I click on 1024, I want to see, in the Detail report, those 1024 records with dates in Dec 2009.

    I tried the url approach, but it doesn't filter and gives me all results.

    I'll keep looking into this, but any further help you could give me would be much appreciated.

    Thursday, March 03, 2011 5:54 AM
  • Fixed it. I'm sure this can be done several ways and mine may not necessarily be the best one, but what I ended up doing was:

    On the high level report, added 2 calculated fields. I know you can add those as a separate dataset and then link it to your main set, but I've just added them to the main set directly. One for the start date fxStart and one for the end fxEnd. I then used an expression that would create the dates in string format:

    For fxStart:

    =CDate(CStr(Year(Fields!CensusDate.Value)) + "-" + CStr(Month(Fields!CensusDate.Value)) + "-01")

    For fxEnd:

    =DateAdd(DateInterval.Day, -1, DateAdd(DateInterval.Month, 1, CDate(CStr(Year(Fields!CensusDate.Value)) + "-" + CStr(Month(Fields!CensusDate.Value)) + "-01")))

    I then added 2 parameters @StartDate and @EndDate to the other, Detail, report.

    Now for the jump. On the High level report Count fields I used a standard 'Go to report' option and selected the Detail report, with the parameter StartDate linked to fxStart and EndDate to fxEnd. I could have put the above code directly in those fields, but thought the separate calc fields would be a bit more transparent.

    That approach worked well and the benefit of it is that you can run the Detail report independently now (rather then always having to pick the data through the High level report as well as being locked into using months), using the parameter fields.

    Simple fix really. I think the fact I started with the Year/Month group fields put me onto the wrong track to start with.

    • Marked as answer by A. Bettik Wednesday, March 30, 2011 6:21 AM
    Wednesday, March 30, 2011 6:21 AM