Percentage of GrandTotal in Crosstab

[Joel]

Is there a builtin function of any type in the crosstabs to generate % instead of count or sum? (val / grandtotal or rowcount)?  ie =(Fields.myColumn/rowcount)

I want my report to look like this
        txt3      txt4     txt5
txt1   3%    10%    35%         6,256
txt2   10%   15%    27%         8,456
       2,123  5,999  6,590     14,712


What is the best way to handle this?

[Peter]

Hello Joel,

To get a percent of the grand total you will have to use the Function Exec in a CrossTab's Textbox Expression value.

The Exec(scope, expression) function executes the specified expression in the given data scope. It is used mostly with aggregate expressions as a second parameter. This function allows changing of the data scope in which the expression is evaluated. Scope parameter is the name of the data scope we need to evaluate the expression against. 

For example your expression have to look like the following one:
= Sum(Fields.myColumn)/Exec("crosstab1",Sum(Fields.myColumn))

Best wishes,
Peter
the Telerik team

[Travis]

Very cool!  I have a couple more situations involving percentages.  I have a cross tab report which has a Year column specified.  There can be multiple years, thus multiple columns.  I want to get the percentage change from one year to the next:

Year1  Year2  Year3
--         %5       %2
--         %3       %10

Another scenario is I need to get cumulative percentage change from year 1 ( hopefully this one makes sense)

Year1  Year2  Year3
--         %5       %7
--         %3       %13

Are these 2 scenarios supported somehow using the Exec(scope,expression) or perhaps another function?

[Peter]

Hi Travis,

Up to your first requirement - we don't have a previous function,however this is in our TODO list. In the meantime as a workaround you can use the report events. I am not sure about your concrete requirement and data but the following sample code snippet should give you an idea:

private decimal previous;
private object year;
private void textBoxFact_ItemDataBound(object sender, EventArgs e)
{
    var textbox = (sender as Telerik.Reporting.Processing.TextBox);
    if (textbox.DataObject["OrderDate"].Equals(year))
    {
        previous -= decimal.Parse(textbox.Value.ToString());
    }
    else
    {
        year = textbox.DataObject["OrderDate"];
        previous = decimal.Parse(textbox.Value.ToString());
    }
    textbox.Value = previous;
}

private void ProductSales_ItemDataBinding(object sender, EventArgs e)
{
    previous = 0;
    year = string.Empty;
}

To accumulate values in specified scope our suggestion is to utilize the RunningValue function 

All the best,
Peter
the Telerik team

[Alberto]

= CDbl(Sum(Fields.NumeroODD)) / CDbl(Exec("crosstab1" , Sum(Fields.NumeroODD)))

have to use CDbl()