how to create a sales index column in crosstab?

[Faye]

Hi everyone, I'd appreciate any input on the following problem:

I have a crosstab with "sales period" as column group, and the detail value is sum of sales.

my report structure is pretty much identical to this demo: https://demos.telerik.com/reporting/product-sales

the only difference is that I will only compare 2 periods, and never more. that should make things hopefully easier. (in other words: a 'SELECT DISTINCT period' on the datasource will always return 2 values.)

Now I would like to show a sales index by comparing period1 to period2 in an additional column.

how can I do that?

 

[Faye]

After further research I managed I found out that the solution is a custom conditional aggregate sum function, which I used as follows to get my sales index:

=   100 * 
(ConditionalSum(Fields.LineTotal, Fields.PERIOD = Parameters.period1from.Value + "-" + Parameters.period1to.Value)) 
/ 
(ConditionalSum(Fields.LineTotal, Fields.PERIOD = Parameters.period2from.Value + "-" + Parameters.period2to.Value))

Source for the conditional aggregate sum function:

https://www.telerik.com/blogs/dynamic-sorting-of-reporting-crosstabs-using-a-custom-aggregate-function

 

And to make the function accessible in the standalone designer, I followed this article:

https://www.telerik.com/blogs/how-to-use-user-defined-functions-in-the-stand-alone-telerik-report-designer-tool