question

ChristopherJack-1763 avatar image
0 Votes"
ChristopherJack-1763 asked Joyzhao-MSFT edited

SSRS totaling in groupings

Hi,

I am trying to total a column in a grouping in SSRS but it seems to be totaling based on other columns.

See below

144545-screenshot-2021-10-28-153158.png

Any help appreciated.

I have tried =Sum(Max(Fields!Backorders.Value, "SKU")) but I get the error

Scope not valued for nested argument.


The normal Sum does not work either.

See below for groupings.


144578-capture.jpg

144558-capture.jpg

Thanks


Any help appreciated

Chris


sql-server-reporting-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I conducted a test. Since I am not sure about your final needs, I will show the two solutions I think you want in the "Answer" below.

0 Votes 0 ·
Joyzhao-MSFT avatar image
1 Vote"
Joyzhao-MSFT answered Joyzhao-MSFT edited

Hi @ChristopherJack-1763 ,
I'm not sure if you just want to get these Total values, because during my test, I selected [Landed Cost]——Insert Row——Inside Group-Below, and then used it in the newly created row: =Sum(Fieilds !FieldName.Value) can get the total of each column.
Taking [Retail Rice] as an example, there are a total of 8 rows of data in its detail row, which are four rows of value "60" and four rows of value "100", so the expression =Sum(Fieilds!FieldName.Value) is used when we calculating the value of eight rows.

144782-01.jpg

If you want to get the total in the figure below, you can also select [Landed Cost]——Insert Row——Inside Group-Below to create a new group inside row.

144793-02.jpg

Then use the following expression,please refer to the following images:

144738-03.jpg
144816-04.jpg
144804-05.jpg

Hope this helps!
Best Regards,
Joy


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


01.jpg (127.7 KiB)
02.jpg (135.8 KiB)
03.jpg (46.4 KiB)
04.jpg (46.4 KiB)
05.jpg (41.5 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

That works perfectly.. thank you!

0 Votes 0 ·

Hi @ChristopherJack-1763 ,
Thanks for your feedback.
If If the answer is the right solution, please click "Accept Answer" . Thank you.
Best Regards,
Joy

0 Votes 0 ·
cooldadtx avatar image
0 Votes"
cooldadtx answered cooldadtx commented

At just a quick glance I'd say you shouldn't be using MAX. Max will give you the maximum value of the given field but you want the sum, not the max value. Without knowing more about your dataset then try this =Sum(Fields!Backorders.Value, "SKU"). That should sum the Backorders fields given the SKU scope. Are you using scopes? If you put this expression inside the group itself then you shouldn't need a scope IIRC. =Sum(Fields!Backorders.Values)

Refer to the documentation for more help on grouping totals.

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @cooldadtx ,

I tried that, the same issue.

Please see the updated question.

Thanks

0 Votes 0 ·
cooldadtx avatar image cooldadtx ChristopherJack-1763 ·

When you say the normal sum doesn't work either do you mean you get an error as well or you get the wrong result? If the wrong result then what are you getting?

For the simplest of datasets then applying SUM within a group row just sums the columns for that group. If you have subgroups then it will do the same at the lower levels as well.

You might try adding a temporary column into your report. Within the group you want the total for right click the column and select Summation and then Backorders. It should generate (and properly fill) the total for that group. For example in a test dataset I use it is simply =Sum(Fields!Quantity.Value) and that totals the items in the current group. If you are not getting a proper summation then the issue may be with the grouping and not the expression itself.

1 Vote 1 ·

Thanks @cooldadtx

I get the same value as the sum Max.. no error.
Not really understanding why I need to add a column as surly it should just sum the two fields rather than the 8?

0 Votes 0 ·
Show more comments