none
How to Calculate Row-wise Data Totals into a column in SSRS 2008 Matrix Report ?

    Question

  • Hi All,

    Below is sample report data, In this report I want to show Total Inventory column such that Inventory Avl till date i.e from 1st june to 3rd june for respesctive materials is to come in Total Invetory column i.e. For Item A 2 +3 + 4 = 9, Item B 1 + 5 + 5 = 11 and so on. I have tried every possible ways to achieve this (in SQL script, used SSRS expression, Although I am getting total inventory but getting wrong values) but got stucked every time. I am using sql 2008 and SSRS 2008. 

    Tuesday, July 23, 2013 6:26 AM

All replies

  • You can try RunningValue(Fields!InventoryValue.Value, Sum, ColumnGroup) - if this is a tablix, where InventoryAvl is a group. Is this is tablix, or a group.

    But I recommend you calculate these types of columns in your underlying SQL query. It bypasses all these issues and performs better.

    If you provide some sample data, and the DDL we might help write the SQL for you.


    Thanks! Josh



    • Edited by Josh Ashwood Wednesday, July 24, 2013 12:04 AM asdfasf
    Wednesday, July 24, 2013 12:02 AM
  • Thanks Josh, I am getting values but value is double of original, reason is there is one more column before Inventory AVL order type (past_order and current_order) so in the sql table I am getting past_order tuple as 2, 3, 4 = 9 (for past_order)  same is reflecting 2, 3, 4 = 9 (for current_order) resulting Total Inventory for Iem A is 18 insted of 9. I dont know how to tackle this in sql script or in report.  
    Wednesday, July 24, 2013 7:40 AM
  • Please show the SQL you are using and some sample data so we can try to solve this in the underlying

    query.


    Thanks! Josh

    Wednesday, July 24, 2013 8:05 AM
  • Hi Bhushan,

    In Reporting Services, if you want to calculate the total value for multiple columns, we can use the expression:
    =Fields!ColumnA.Value+Fields!ColumnB.Value+Fields!ColumnC.Value

    If the columns belong to a column group, we can right-click the group name on the Column Groups>Add Total>After

    Regards,


    Charlie Liao
    TechNet Community Support

    Wednesday, July 24, 2013 8:28 AM
    Moderator
  • hi charlie,

     I have tried this as well but the problem remains same, I am getting values double because of above explanation.

    Wednesday, July 24, 2013 12:56 PM
  • Hi Bhushan

    I think the problem here is that we don't understand your issue clearly

    Whatever the issue is let us have a look at the query, and sample data if it is needed to understand the issue. 

    These things are often a lot easier to understand and solve in T-SQL.

    Or please try to explain why this doubling up is happening a little more clearly.



    Thanks! Josh

    Wednesday, July 24, 2013 1:11 PM