MonthToDate Issue

[gyfu]

Hey guys,
I have a question here which seems easy enough yet, I could not put it together after reading the posts here.

I need to create a report as such.
Report is group by Item ID and I was trying to use running totals for the counts below.

# of Items today # of Items MTD # of Items YTD

MTD, would mean that if I select the 22nd of July, the report should give me the count from 1st July to the 22nd of July.

YTD, would mean from the 1st of Jan to the date selected. In this case, 22nd July.

1) Do I need to select all data for a year. Right now, my select expert is saying this,
{@ItemDate} <= {?ReportingDate}

2) how do I use in the evaluate portion of the running total to produce the right numbers?


Thanks.


-- gyfu --
Crystal Ver 10
Microsoft SQL

 

[lbass]

You could use a record selection formula of:

{@ItemDate} in date(year({?ReportingDate}),01,01) to {?ReportingDate}

This would allow you to look at other years as well as the current year, depending upon your parameter selection.

If you are only concerned with the current year, you could use formulas like:

if {@ItemDate} in YeartoDate then 1//or MonthToDate or = Currentdate

For other years, use the dates as used in the record selection formula. Then right click on the formulas and insert a summary (Sum, not count). If you have record inflation, you would need to use running totals, and then you would use formulas like the following in the evaluation section:

{@ItemDate} in MonthtoDate

-LB

 

[gyfu]

lbass,
I did use the formula as you mention above. The record selection formula looks great. I needed that, just didn't know how to put it in.

However, the running totals are not working for the monthtoDate.

I get just zero count. When i browse ItemDate, I do get the necessary dates that suppose to be included in the count. this was the same thing I tried previously and it didn't work, so I thought probably something else was wrong.


thanks.

-- gyfu --
Crystal Ver 10
Microsoft SQL

 

[lbass]

How do you have the running total set up? You should be selecting {itemID}, count, evaluate using a formula:

{@ItemDate} in {?ReportingDate}-day({?ReportingDate})+1 to {?ReportingDate}//for monthtodate based on parameter date

Or, for year to date, you would use:

{@ItemDate} in date(year({?ReportingDate}),01,01) to {?ReportingDate}

Reset on change of group (or never if you are evaluating at the report level). The running total should be placed in a footer, not header section.

The monthtodate and yeartodate functions would only work if the parameter date were the currentdate--my mistake in suggesting this earlier.

-LB

 

[gyfu]

Thanks LBass,


Those solutions work great. You Da Man!!


-- gyfu --
Crystal Ver 10
Microsoft SQL