HELP! How to sum up tablix cell values to show them in Totals

I have a report with Tablix.

The values in it’s cells are being calculated using expressions. How to calculate the totals for such columns?

(Default totals like sum(DS.myField) doesn’t help as its not based on expression, therefore doesn’t equal the total of summed up cell values)

What I’ve tried so far:

To follow https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d4a3969a-f3fc-457b-8430-733d7b590ff6/use-reportitems-to-sum-all-textboxes-in-a-tablix?forum=sqlreportingservices

and https://msdn.microsoft.com/en-GB/library/ms156028.aspx

but for some reason the custom code that I write in the Report properties simply disappears when I reopen the VS. And also all the functions I write they are not not recognizable by the compiler in the Expressions, e.g if I write “Code!MyFunc()” - I get an error that MyFunc doesn’t exist.

I guess the above is some bug of VS(SSRS) & AX integration

There should be some way to sum up tablix cells !

Please help

You can use expression in sum.

Hi Martin

I appreciate your response, but in my case there is a grouping in the tablix, therefore the expressions in cells look like iif(sum(DS.FieldA)/something, ValueA, ValueB), where sum is for group

As such following your suggested approach Totals expression would look like

Sum (iif(sum(DS.FieldA)/something, ValueA, ValueB))

where inner SUM looses it’s sence as it should be in my case taken per group whilst here it just becomes a normal sum

Sum has a parameter for this scenario. Let me quote the documentation:

scope

(String) Optional. The name of a dataset, group, or data region that contains the report items to which to apply the aggregate function. If scope is not specified, the current scope is used.

That sounds great, but … as I use “First” in the expression, I get:

““Textbox1 uses a first, last or previous aggregate in an outer aggregate. These Aggregate functions cannot be specified as nested aggregates””

This is in accordance with the documentation https://msdn.microsoft.com/en-us/library/dd255270.aspx

where is says

"

  • Expression must not contain First, Last, Previous, or RunningValue functions.

"

As such i can’t use my formulas in the totals ((

It could be wonderful if I could use custom code for the running total(as I’ve described at the beginning), but still have got no clue, why I cant(code written in the Report parameters doesn’t get saved for some reason)

Embedding code directly in reports isn’t supported, but data methods are. Please consult Report Data Method Overview [AX 2012].

Thank you Martin!