how to create a sales index column in crosstab?

1 Answer 40 Views
CrossTab DataSource SQL Report Designer (standalone)
Faye
Top achievements
Rank 1
Iron
Faye asked on 03 Sep 2021, 08:02 AM

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?

 

1 Answer, 1 is accepted

Sort by
0
Faye
Top achievements
Rank 1
Iron
answered on 04 Sep 2021, 07:57 PM

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

Tags
CrossTab DataSource SQL Report Designer (standalone)
Asked by
Faye
Top achievements
Rank 1
Iron
Answers by
Faye
Top achievements
Rank 1
Iron
Share this question
or