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.
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.
- Edited by Josh Ashwood Wednesday, July 24, 2013 12:04 AM asdfasf
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.
In Reporting Services, if you want to calculate the total value for multiple columns, we can use the expression:
If the columns belong to a column group, we can right-click the group name on the Column Groups>Add Total>After
TechNet Community Support
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.