Running Totals

We are trying to create a report that shows a Week to Date, Month to Date, and Year to Date

Week to Date Month to Date Year to Date

Item Number

I've tried using an if statement (if date = current week, Qty, 0) and then sum the data but I get an error message that reportitems can only be summed in the page header and footer. I've also tried running totals, but it doesn't like the iif statement. Any ideas on how to do this?

Nancy

Nancy,

Here's an example of running values with a IIF statement. I believe you can accomplish what you're trying to do the same way.

RunningValue(IIf(Fields!elca_employer_number.Value<0,Fields!elca_employer_number.Value,nothing),count,nothing)

Ham

|||

I have the Week to Date and Year to Date working but the period (or month to date) is still causing problems.

The data comes in at the weekly level, so I was able to use:

=iif(Fields!WEEK.Value=Sum(Fields!CurrentWeek.Value, "Current"),Sum(Fields!Ordered.Value),0)

The Year to Date is was able to do a simply sum, but for the Period to Date totals I'm still stuck.

When I try:

=RunningValue(iif(Fields!Period.Value=Sum(Fields!CurrentPeriod.Value, "Current"),Sum(Fields!Ordered.Value),0),sum, Group3)

I get an error - The value expression for the textbox contains an agrregate funtion in the argument to another aggregate function.

I'm trying to evaulate if the Period in my main dataset is equal to the Period in the Current dataset. That part is easy. Then I need to sum that up and display the data on one line

WTD PTD YTD

Item 5 50 500

In Crystal Reports I would have created a field called Period_Ordered that evaluates the if Period=Current Period, then I would have summed the Period_Ordered field in my group footer. Visual Studio doesn't seem to allow this. Very frustrating.

|||

NCL,

Okay we have a partial answer,

On the this statement: RunningValue(iif(Fields!Period.Value=Sum(Fields!CurrentPeriod.Value, "Current"),Sum(Fields!Ordered.Value),0),sum, Group3) you have to remove the "SUM" values because you cannot aggregate inside of an aggregate function.

I do not think you need runningvalues because you are looking at your final totals. I believe this is all you need now. IIF(SUM(Fields!Period.Value,"Current")=Sum(Fields!CurrentPeriod.Value, "Current"),Sum(Fields!Ordered.Value),0)

Ham

|||

On the first one: the sum(fields!CurrentPeriod.Value,"Current") is coming from the dataset. The dataset only contains one line of data. It takes the current date and pulls back the valid Fiscal Week, Period and Year. Not sure why only the sum is available to add to my expression. Maybe that is a different issue.

I tried the second option but it's not calculating the data correctly. It's not summing all of the weeks in current period.

If Current dataset is pulling back today - 1/23/2007 Current Week =30 Current Period = 7

Report pulls:

Item Week Period Year Qty Current Week Current Peroid to date Year to Date

Details:

99999 1 1 2007 5 0 0 5

99999 6 2 2007 10 0 0 15

99999 29 7 2007 10 0 10 25

99999 30 7 2007 5 5 15 30

What I need the report to show in the item group footer is:

99999 5 15 30

Any ideas?

|||

N,

On your question for

What I need the report to show in the item group footer is:

99999 5 15 30

I going to go on the assume that you are return a field name period and a second field name currentPeriod

Fields!Item.value

Fields!CurrentWeek.value

Sum(IIF(Fields!Period.value = Fields!CurrentPeriod.value, Fields!Qty.value,0))

Sum(Fields!Qty.value)

I hope this helps.

Ham

sql