Monday, September 13, 2010

Groups in SSRS 2008

In Report Designer, Groups are created to organize data on the report or to calculate aggregate summaries. An understanding how to define groups and use group features helps you design reports that are more concise and communicate summarized data in a more compact format.

We also use Groups in SSRS reports to provide Drill-down features. In this article, I'll walk you through SSRS Groups to create a report shown in below image:

STEP1: Add new report in Report Server project and rename it to GroupsInSSRS.rdl.
Create new Shared DataSource for database AdventureWorksDW2008R2.
Click here to download AdventureWorksDW2008R2 database.

STEP2: Create a new dataset dsMain with Shared Data Source. Use below query for this dataset:
D.CalendarYear AS [Year]
,D.CalendarQuarter AS [Quarter]
,D.EnglishMonthName AS [Month]
,D.FullDateAlternateKey AS [Date]
,P.EnglishProductName AS [ProductName]
,C.FirstName + LastName AS [CustomerName]
,ST.SalesTerritoryRegion AS [SalesRegion]
,ST.SalesTerritoryCountry AS [SalesCountry]
,F.SalesOrderNumber AS [OrderNumber]
FROM FactInternetSales F
JOIN DimProduct P
  ON P.ProductKey = F.ProductKey
JOIN DimCustomer C
  ON C.CustomerKey = F.CustomerKey
JOIN DimDate D
  ON D.DateKey = F.OrderDateKey
JOIN DimSalesTerritory ST
  ON ST.SalesTerritoryKey = F.SalesTerritoryKey

STEP3: Drag and drop a table control and select CustomerName, OrderNumber, and SalesAmount fields in the table.

STEP4: Select table detail row --> Right click --> select Add Group --> Row Group --> Parent Group

STEP5: Select ProductName in Tablix group window and check add group header check box. Click OK and sace changes.
Now you can see Product Group in the table as shown below:
Delete Group1 Column and rename Customer Name as Particulars. Select ProductName in Product Group as shown below:

STEP6: Select ProductName group and add one more group for SalesCountry in similar way mentioned at step5. Delete the group column and select SalesCountry in CountryGroup as shown below:

STEP7: In the same way add Groups for the following Fields:
  • SalesRegion
  • Date
  • Month
  • Quarter
  • Year
Finally table will look like one shown in below image:
STEP8: we are done with report Groups. You can Preview the report:

STEP9: Format Groups: This is the final step. Without formatting the tabular data look meaningless and its very difficult to understand the report. You can do following formatting to make it easier to understand:
GroupLeft IndentBackgroundColor
(detail row)

Toggle Groups:
Select Quarter Group and click on Group Properties. Select Visibility in Group Properties and select Hide option button. Check Dispaly can be toggled by this report item  check box and select Year from combo box.

Similar way toggle other report items.
Also Toggle Order Number column with Particular text box.

Now click on Preview and generate the report:

Thats all about Report Groups...

1 comment:

  1. Hi,

    How do i hide and unhide the column based on row toggle...

    My report looks something like this

    Category Product OrderId Amount

    Bevrages Cooldrinks ORD123 120

    So if i group on Category and display the Sum of amount, on hide of row product and OrderID will be blank. So in that case when it is + i want ti display Category and Amount and when it is - i want to display all columns


Here are few FREE resources you may find helpful.