RunningValue in a chart - scope issues

I am working on a straightforward table and chart to present a graph of
cumulative revenue.
My data set returns revenue per month, and Iâ'd like to display a chart that
shows the month by month sum of revenue.
In my table I use the following to create a column with the appropriate
values:
=RunningValue(Fields!Actual_Revenue.Value, Sum, "table1")
In my chart, I have a category group for the â'monthsâ' and a Value set for
â'Cumulative Revenueâ' with the following formula:
=RunningValue(Fields!Actual_Revenue.Value, Sum, "chart1_month")
This returns a chart that is exactly the same as the â'actual_revenueâ' â' i.e.
no running sum. Logically, Iâ'd assume I should expand the scope beyond the
â'monthâ', however I get errors whenever I try to use just â'chart1â' or Nothing
as the scope.
Any thoughts?
SmittyHi Smitty,
a chart is very similar to a matrix. A chart series grouping is equivalent
to a matrix row grouping. What you want in a chart RunningValue is that it
resets on series / rows (rather than on categories / columns - because this
is what you have right now).
RunningValues inside a matrix or a chart cannot use the Nothing scope or a
dataset scope right now. They must use a grouping scope inside the chart.
There are two cases:
(1) the chart does not have any chart data series grouping at all:
Add a new "fake" series grouping, i.e. based on a constant group expression
value, e.g. =1. On the "fake" series grouping, set the grouping label
expression to ="" (i.e. empty string), so that the legend does not show the
fake group. Use the name of this "fake" series grouping for the scope name
of the RunningValue.
(2) the chart already has one or more existing data series groupings:
Follow all the steps described under (1) above. Then apply the following
additional steps:
The "fake" group must be the outermost series group if other groups are
present - i.e. in the chart properties dialog / data tab, the fake group has
to be on the top of the list of series groups (use the arrows to change the
order of groups in the list).
Hope this helps,
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Smittoid" <Smittoid@.discussions.microsoft.com> wrote in message
news:11ADA552-C14D-403E-8538-56178B8836AE@.microsoft.com...
>I am working on a straightforward table and chart to present a graph of
> cumulative revenue.
>
> My data set returns revenue per month, and I'd like to display a chart
> that
> shows the month by month sum of revenue.
>
> In my table I use the following to create a column with the appropriate
> values:
>
> =RunningValue(Fields!Actual_Revenue.Value, Sum, "table1")
>
> In my chart, I have a category group for the "months" and a Value set for
> "Cumulative Revenue" with the following formula:
>
> =RunningValue(Fields!Actual_Revenue.Value, Sum, "chart1_month")
>
> This returns a chart that is exactly the same as the "actual_revenue" -
> i.e.
> no running sum. Logically, I'd assume I should expand the scope beyond the
> "month", however I get errors whenever I try to use just "chart1" or
> Nothing
> as the scope.
>
> Any thoughts?
>
> Smitty
>|||BTW: I forgot to mention that only RS 2005 supports the usage of
RunningValue in charts. It is not supported on RS 2000. But in your case,
you are running on RS 2005.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:ObT$SWT0FHA.2652@.TK2MSFTNGP14.phx.gbl...
> Hi Smitty,
> a chart is very similar to a matrix. A chart series grouping is equivalent
> to a matrix row grouping. What you want in a chart RunningValue is that it
> resets on series / rows (rather than on categories / columns - because
> this is what you have right now).
> RunningValues inside a matrix or a chart cannot use the Nothing scope or a
> dataset scope right now. They must use a grouping scope inside the chart.
> There are two cases:
> (1) the chart does not have any chart data series grouping at all:
> Add a new "fake" series grouping, i.e. based on a constant group
> expression value, e.g. =1. On the "fake" series grouping, set the grouping
> label expression to ="" (i.e. empty string), so that the legend does not
> show the fake group. Use the name of this "fake" series grouping for the
> scope name of the RunningValue.
> (2) the chart already has one or more existing data series groupings:
> Follow all the steps described under (1) above. Then apply the following
> additional steps:
> The "fake" group must be the outermost series group if other groups are
> present - i.e. in the chart properties dialog / data tab, the fake group
> has to be on the top of the list of series groups (use the arrows to
> change the order of groups in the list).
>
> Hope this helps,
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
>
> "Smittoid" <Smittoid@.discussions.microsoft.com> wrote in message
> news:11ADA552-C14D-403E-8538-56178B8836AE@.microsoft.com...
>>I am working on a straightforward table and chart to present a graph of
>> cumulative revenue.
>>
>> My data set returns revenue per month, and I'd like to display a chart
>> that
>> shows the month by month sum of revenue.
>>
>> In my table I use the following to create a column with the appropriate
>> values:
>>
>> =RunningValue(Fields!Actual_Revenue.Value, Sum, "table1")
>>
>> In my chart, I have a category group for the "months" and a Value set for
>> "Cumulative Revenue" with the following formula:
>>
>> =RunningValue(Fields!Actual_Revenue.Value, Sum, "chart1_month")
>>
>> This returns a chart that is exactly the same as the "actual_revenue" -
>> i.e.
>> no running sum. Logically, I'd assume I should expand the scope beyond
>> the
>> "month", however I get errors whenever I try to use just "chart1" or
>> Nothing
>> as the scope.
>>
>> Any thoughts?
>>
>> Smitty
>

Running Value

Hi

In my report I have the total column,under the total i have two sub fields:no , Row%and i have another column Cumulative total sub fields are no,***%

For the Row % under total i write like this:

=Round((Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)/Sum(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)*100,2)

For the *** % under cumulative total the expression is:

=RunningValue((Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)/Sum(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)*100,sum,"AgeByGender")

But i am getting this error:

The Value expression for the textbox '*** %' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions cannot be nested inside other aggregate functions.

How to get the cm % for the Cumulative total

Please help me

Thanks in advance

You can not 'sum' inside of other aggregate, you need to remove the sum function

=RunningValue((Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)/Sum(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)*100,sum,"AgeByGender")

Try without "Sum"

(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value/Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)*100

Ham

|||

Hi,

I want the SUM inside the runningValue,It is necessary.

Thanks

Running Value

Hi

In my report I have the total column,under the total i have two sub fields:no , Row%and i have another column Cumulative total sub fields are no,***%

For the Row % under total i write like this:

=Round((Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)/Sum(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)*100,2)

For the *** % under cumulative total the "AgeByGender")

But i am getting this error:

The Value

I solved the problem with the following expression.

For Cumulative %:The formula i used is:

Round(RunningValue((fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value),sum,"AgeByGender") /Sum((fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value), "AgeByGender") *100,2)

This expression solved the problem.

Thanks

Running value

Hi

In my report I have the total column,under the total i have two sub fields:no , Row%and i have another column Cumulative total sub fields are no,***%

For the Row % under total i write like this:

=Round((Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)/Sum(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)*100,2)

For the *** % under cumulative total the expression is:

=RunningValue((Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)/Sum(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)*100,sum,"AgeByGender")

But i am getting this error:

The Value expression for the textbox '*** %' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions cannot be nested inside other aggregate functions.

How to get the cm % for the Cumulative total

Please help me

Thanks in advance

Mahima

You will need to remove the "SUM" inside the running value statement.

=RunningValue((Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)/(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)*100,sum,"AgeByGender")

Ham

|||

Hi,

I need the cum%(Cumulative %) thatswhy i added SUM inside.Without using Sum inside Running value,How to get the Cumulative %,Any work around .

Thanks in advance

|||

Mahima,

I'm missing a piece of Info to help you. Where are you trying to place your cumulative total - Is this a group footer, table footer, or Matrix?

Thks

Ham

|||

Hi,

This Cumulative Total % is a column in a Table.

|||

Hi,

Any one there,Please help me on this issue.

Thanks

|||

Mahima,

I meant to answer but I'm in the middle of a SSRS production release with my client. I did want to say that you could use the ReportItem!Field.value to replace the SUM(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value) - if you are not using the them in a report column the created a calculated field then use the calculated field in replace the SUM values.

|||

Hi,

Thanks for replying.Can you explain a bit clear.I tried in this way,We have a % column i.e textbox67,In Cum%,i used like that:(ReportItems!TextBox67.Value,Sum,"dataset1").But iam getting error.report items use only in header footer.

Where to calculate that value separately.

Thanks

|||

Okay,

My bad, I now remember why I asked if it was in the header. On the Dataset, on click, Add new calculated field, place your expression, then use in your runningvalue statement.

Ham

|||

Hi,

I did the same before.I created a calculated field and using those calculated field in the RunningValue,But the problem is When i click on View report button,It is displaying the following message,An internal error occured,See ebetlog for details.And closing the application(Visual Studio).Here what is the problem.

The exception is Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogexception occured in devenv.exe.

Thanks

|||

WOW,

It looks like a lot of things are not going right here. Is your data valid?

Ham

|||

Hi,

My data is valid.It is working fine before adding the Calculated field.Any other alternative.

Thanks

|||Please post your expression, I would like to try an verify it.|||

Hi,

In the Dataset named "DataSet1" fields are Male,Female,Unknown.

In the report,i have the following columns

Male Female Unknown Total Total% CumulativeTotal Cumulative%

Total=Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value

Total%=Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value/Sum(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value)

CumulativeTotal=RunningValue(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value,Sum,"Dataset1")

CumulativeTotal%=RunningValue(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value/Sum(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value),Sum,"DataSet1")

this Cumulativetotal% is giving error,So i created the 'calculated filed' named as "Percent" with the expression:Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value/Sum(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value)

and try to use the Percent value like this:

RunningValue(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value/Fields!Percent.Value,Sum,"DataSet1"),This is closing the Visual Studio and giving the exception.

Thanks

|||

In your calculated field,

Only place this Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value in calculated fields

in your expression use RunningValue(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value/Sum(mycalculatedfields),

I hope that this works for you.

Running Totals at group level

Hi,

I have a report that groups data by day - I have created a running value to

show cumulative sales for Monday, Monday+Tuesday, Monday+Tuesday+Wednesday etc.

I have a group below this level that expands out the customer. I

wish to create a cumulative sales value for that customer for that day of the

week. i.e. Customer A Monday value, Customer A Monday+Tuesday

value.

When you use RunningValue with scope Nothing, ie.

RunningValue(Fields!nett_value.Value, Sum, Nothing), the value returned on

Tuesday is Total Monday value plus each customer Tuesday value cumulative

adding. If you use scope at customer group level it cumulatively

adds that day’s customer totals.

I need to recreate:
Daily

Cumulative

Monday

100 100

Customer A 50

50

Customer B 25

25

Customer C 25

25

Tuesday

100 200

Customer A 50

100

Customer B 25

50

Customer C 25

50

Any ideas?

Thanks

While I am having fun trying to work this out...

Any help would be great!!!

Thanks!