0

First it groups by department then it sorts by Departments. It sorts again by total cost within the each departments. I am supposed to display total for units and costs for each department. Only supposed to display total for 10 barcodes. It shows the total but is showing the total for all the barcodes that exists within that department. For example: Total units for the Dept A should be 19 but it is displaying 57 because there more than 10 barcodes in department A. I tried, top N as shown here
enter image description here

Tried RunningValue but it gives me running total. I need total for each departments separately. 

     =RunningValue(Fields!Units.Value, Sum, Nothing)

I also looked the similar question but did not work.How to get total of top 10 sales in SSRS 2012. Here is my current output:

enter image description here

Output should be:

For Dept A: Total Units should be 19 and total cost should be: 144.40

For Dept B: Total Units should be 378 and total cost should be : 6097.70

Here is my design view: using the following to aggregate: 

 =Sum(Fields!units.Value)
 =Sum(Fields!totalcost.Value)

enter image description here

Community
  • 1
  • 1
Everest
  • 37
  • 1
  • 11
  • Can you add a screenshot of your table in design view? I think you are not aggregating your per-barcode values which is what is confusing you. – iamdave Aug 30 '16 at 10:42
  • Your table design screenshot doesn't match the two formulas above it. The `Total Cost` may be correct, but as you can see the text `[units]` in your Units detail row, you definitely aren't using the formula posted there: http://imgur.com/kYzr1fd – iamdave Aug 30 '16 at 15:14

2 Answers2

1

If I understand correctly you use TOP N for show top 10 record for Department

and Sum() in Dept A show 57 because it's sum all rows of department (more than 10 records)

I Suggest you use Query for select only 10 record for each Department something like this code below

SELECT q.Dept
    ,q.Barcode 
    ,q.Unit
    ,q.TotalCost
FROM (
    SELECT t.Dept
        ,t.Barcode 
        ,t.Unit
        ,t.TotalCost
        ,DENSE_RANK() OVER (PARTITION BY t.Dept ORDER BY t.TotalCost) AS [r] 
    FROM YourTables t
) q 
WHERE q.r BETWEEN 1 AND 10
Chanom First
  • 1,136
  • 1
  • 11
  • 25
  • Yes Chanom that is correct but I can not change the query. We have a common StoredProcedure that we call to query data for all the clients. I just need to modify the report for this particular client. If I change the query, it is going to mess up the report for other clients. – Everest Aug 30 '16 at 11:39
  • @Everest - can you post sample data that you get with StoreProcedure and image with Query in DataSet – Chanom First Aug 31 '16 at 05:02
  • Sorry the data and proc are from the real client. not able to post but we made it work. please check the comment above. Thank you though. – Everest Aug 31 '16 at 16:32
1

Assuming your source query is correct, your table summary is also correct. Because you have [units] instead of [sum(units)] in your Units detail field, you are only showing the first row in the group's value. Change this expression and your table will add up correctly.

I would advise you look at the source data before assuming that something is or isn't wrong. If your source data doesn't manually add up to the values you are expecting, either the source data is wrong or your expectation is wrong.

iamdave
  • 12,023
  • 3
  • 24
  • 53
  • Source query is returning the correct data. Units and totals units on each row are fine. I am having trouble with Totals at the bottom of each departments: I just need to display the totals of all the rows that is shown on the report above. – Everest Aug 30 '16 at 14:02
  • @Everest I can guarantee you that your individual `units` values are one row's worth and not all from the group. What happens to your table when you change `[units]` to `[sum(units)]`? – iamdave Aug 30 '16 at 14:52
  • @Everest If that is the case then something is either wrong with your source data or you are not fully explaining the logic behind your table. I have created a test table with multiple rows per group, which correctly sums up the top 10 rows when used in a filter, so I am 100% confident that you are doing something else in your report, given that the functionality in SSRS works when done correctly. Any chance you could upload your report file somewhere? – iamdave Aug 30 '16 at 15:06
  • It is a big report and data and column are from the real client so could not post everything. My co-worker made it work with the help of the custom code. It basically iterates through the data that you see on the row and accumulates and displays total at the end of each department. At the end , it also resets the totals to zero so the running total would not continue on to the next department. It does the same for all the departments. Thank you for trying to help. – Everest Aug 31 '16 at 16:30