MSBI (SSIS/SSRS/SSAS) Online Training

Thursday, May 14, 2009

SSRS-Advanced Matrix Reporting Techniques

Hi,
By using SSRS tool we can develop different reports as per the client requirements.
Even customer also easily understood reports without having any business idea by attending the graphical representation.
Q. I have the following SQL that I want to display in a report with totalsgrouped by year, quarter, month and week.select case statecode when '0' then 'Qualified' when '1' then 'Open'when '2' then 'Disqualified' end as 'Status',datepart(year, createddate) as 'year', datepart(quarter, createddate) as'quarter', datename(month, createddate) as 'month', DATEPART(week, createddate) - DATEPART(week, DATEADD(month,DATEDIFF(month, 0, createddate), 0)) +1 as 'week',count(*) as 'enqtotal'from bo_maingroup by datepart(year, createddate), datepart(quarter, createddate),datename(month, createddate), DATEPART(week, createddate) - DATEPART(week, DATEADD(month,DATEDIFF(month, 0, createddate), 0)) +1, statecodeorder by datepart(year, createddate), datepart(quarter, createddate),datename(month, createddate), DATEPART(week, createddate) - DATEPART(week, DATEADD(month,DATEDIFF(month, 0, createddate), 0)) +1, statecodeI have added a matrix to my report, and then added column groups foryear (=Fields!year.Value), quarter (=Fields!quarter.Value), month(=Fields!month.Value) and week (=Fields!week.Value). Then I have a rowunder these columns showing =Fields!Status.Value to the left and=Fields!enqtotal.Value underneath the column groups. I now need to add a row with the total of each enqtotal for eachyear/quarter/month/week combination. But upon right clicking my=Fields!enqtotal.Value cell, there is no option to add a subtotal.This is my first report matrix, so could somebody tell me what I amdoing wrong? If you need further explanation than please let me know.
A. See the below link for more information.
http://www.simple-talk.com/sql/sql-server-2005/advanced-matrix-reporting-techniques/

No comments: