none
Matrix/Tablix subtotals

    Question

  • I am using this T-SQL referring to AdventureWorks2008R2:

    SELECT     DATENAME(yy, sh.OrderDate) AS Year, DATENAME(mm, sh.OrderDate) AS Month, CONVERT(VARCHAR, CAST(SUM(sd.LineTotal) AS Money), 1) 
                          AS TotalMonthSales
    FROM         Sales.Customer AS sc INNER JOIN
                          Sales.SalesOrderHeader AS sh ON sc.CustomerID = sh.CustomerID INNER JOIN
                          Sales.SalesOrderDetail AS sd ON sh.SalesOrderID = sd.SalesOrderID
    GROUP BY DATENAME(yy, sh.OrderDate), DATENAME(mm, sh.OrderDate)
    ORDER BY Year DESC, Month DESC

    I am having a few problems at this stage getting what I would like in a matrix in VS2008. I did this before through dragging and dropping, but for some reason can't repeat it again...

    I dragged from the DataSet the Year and Month to the Rows part of the matrix and the TotalMonthSales to the Data part. All is well with this but I am trying to get subtotals of each year as well as the total off all the years put together (i.e. 2008-2005). In the Tablix Data part AddTotal is blanked out in the TotalMonthSales area.

    In my previous test version all the formulas in the Data part showed a Sum, but as mentioned I did all this by dragging and dropping and am not sure how to repeat this now. Also, the Year and Month are showing in a different (reverse) order to the above T-SQL in SSRS.

    Any ideas?

    Friday, December 28, 2012 10:31 AM

Answers

  • Many thanks for the suggestions and screenshots. I think I understand what the problem is at this stage. My original SQL for the Total Sales per Month was

    SUM(Sales.SalesOrderDetail.LineTotal) AS TotalSalesPerMonth

    I can actually get the totals by dragging and dropping as you mentioned above.

    This was changed to format it correctly (like in Excel) to:

    CONVERT(VARCHAR, CAST(SUM(sd.LineTotal) AS Money), 1) AS TotalMonthSales

    The matrix does not recognise the second bit of SQL as numerical (or there may be another reason for this) which is why there are no options to SUM etc., so I don't know what is acceptable in terms of presentation i.e. is it acceptable to provide figures with no 1000 (,) separators like in Excel. It should also be to two decimal points.

    The other remaining issue is that the matrix shows the years in reverse i.e. from 2005 to 2008 rather than from 2008 to 2005 but this is OK (i.e. correct) in SSMS.

    Many thanks for any advice.

    To recap, here is the original code:

    SELECT     YEAR(Sales.SalesOrderHeader.OrderDate) AS Year, MONTH(Sales.SalesOrderHeader.OrderDate) AS Month,
               SUM(Sales.SalesOrderDetail.LineTotal) AS TotalSalesPerMonth                      
    FROM       Sales.Customer INNER JOIN Sales.SalesOrderHeader
               ON Sales.Customer.CustomerID = Sales.SalesOrderHeader.CustomerID
               INNER JOIN Sales.SalesOrderDetail
               ON Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID
    GROUP BY MONTH(Sales.SalesOrderHeader.OrderDate), YEAR(Sales.SalesOrderHeader.OrderDate)
    ORDER BY Year desc, Month desc

    P.S. I also noted that the LineTotal column in the AdventureWorks database has no Data Type and is a calculated column itself as:

    (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0)))
    so this may be what is causing this problem.


    Friday, December 28, 2012 3:01 PM

All replies

  • Try this:

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

    Regards,Eshwar.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, December 28, 2012 11:29 AM
  • Hi,

    Please follow the below steps to implement your subtotals:

    - Create your Tablix

    - Drag and drop year and month fields to the first column and check that the corresponding Groups are created as shown in the picture below

    - Drag the Amount field to data cell in the Tablix

    - Create the year subtotal by clicking on the month group -> Add Total as shown below

    - Do the same thing for the general total by clicking on Year group then Add total

    Hope that it will resolve your issue :)


    MVP SQL & BI Specialist http://wissemhabboub.unblog.fr

    Friday, December 28, 2012 11:47 AM
  • Many thanks for the suggestions and screenshots. I think I understand what the problem is at this stage. My original SQL for the Total Sales per Month was

    SUM(Sales.SalesOrderDetail.LineTotal) AS TotalSalesPerMonth

    I can actually get the totals by dragging and dropping as you mentioned above.

    This was changed to format it correctly (like in Excel) to:

    CONVERT(VARCHAR, CAST(SUM(sd.LineTotal) AS Money), 1) AS TotalMonthSales

    The matrix does not recognise the second bit of SQL as numerical (or there may be another reason for this) which is why there are no options to SUM etc., so I don't know what is acceptable in terms of presentation i.e. is it acceptable to provide figures with no 1000 (,) separators like in Excel. It should also be to two decimal points.

    The other remaining issue is that the matrix shows the years in reverse i.e. from 2005 to 2008 rather than from 2008 to 2005 but this is OK (i.e. correct) in SSMS.

    Many thanks for any advice.

    To recap, here is the original code:

    SELECT     YEAR(Sales.SalesOrderHeader.OrderDate) AS Year, MONTH(Sales.SalesOrderHeader.OrderDate) AS Month,
               SUM(Sales.SalesOrderDetail.LineTotal) AS TotalSalesPerMonth                      
    FROM       Sales.Customer INNER JOIN Sales.SalesOrderHeader
               ON Sales.Customer.CustomerID = Sales.SalesOrderHeader.CustomerID
               INNER JOIN Sales.SalesOrderDetail
               ON Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID
    GROUP BY MONTH(Sales.SalesOrderHeader.OrderDate), YEAR(Sales.SalesOrderHeader.OrderDate)
    ORDER BY Year desc, Month desc

    P.S. I also noted that the LineTotal column in the AdventureWorks database has no Data Type and is a calculated column itself as:

    (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0)))
    so this may be what is causing this problem.


    Friday, December 28, 2012 3:01 PM