Show quarterly and YTD in report

[karlrmac]

Hello,
I have a CR that is set to show items purchased for a particular period of time. Management wants the report to show quarterly data (all items, cost, etc) but they also want a group to show its YTD spend also. I already have the report broken by group, showing total and item. The YTD just needs to be a number, not showing all the item purchases.
My thought is run the report from Jan 1 to whatever date we are at, show only the items from the quarter the report is for and then have a total to show the YTD information next to the quarterly total. A cross tab won't work b/c the YTD needs to be next to the quarter total spend in the report, that is located at the end of the group items.
I hope this makes sense, but I don't know quite how to get the information to show.
Thanks and I'll come back and post as questions or solutions are provided. - Karl

 

[jmd0252]

Well,, you could run the YTD totals, as an sql command, and join the 2 tables, using the ytd as your basis for your join. because if it is in the ytd nnumbers, it may or may not be in the other numbers, so be sure and make provisions, if the other numbers are null ( ie not there).

 

[karlrmac]

thanks for the reply. so i can run the report as a YTD and then break out the quarter? i have to do all this in crystal, nothing directly with the tables. this report has 10 tables that generate the data i have for the items. Is there a way to show only a quarters of items, but show the total YTD purchases?

 

[lbass]

One way would be to allow all records for the year into the main report in your record selection formula, and then use GROUP selection to limit the display to those in the current quarter, using a formula like this:

year({table.date}) = year(currentdate) and
datepart("q",{table.date})=datepart("q",currentdate)

This would display all records in the current quarter only.

You could then insert the usual summary (sum) on {table.amount} at the report footer level and it would include the non-displayed records. For the quarterly summary, you can just insert a sum at the quarter group level.

-LB

 

[karlrmac]

Again, thanks for the information. the report now only returns the current quarter, which is 2nd quarter, but it does not show year to date. i assume there is no year to date b/c the report is only showing 2nd quarter, no simply hiding 1st quarter, so there is no date on the report from 1st quarter.
is there a way to generate the report, have all information, but only make visible the current quarter? kinda like suppressing any thing outside of the current quarter? this way i could still use the suppressed data to create the year to date totals, but only show the information in the current quarter.
thanks again for your help. - Karl

 

[lbass]

Did you try my suggestion? Or were you trying the earlier one. I was suggesting you use a record selection formula like this:

year({table.date})= year(currentdate)

Then add a group selection formula like this:

datepart("q",{table.date})=datepart("q",currentdate)

This would SHOW only the current quarter, but if you place the field you want to summarize in the detail section and insert a summary on it (NOT a running total), the undisplayed records will contribute to the total. A running total would only summarize the displayed records. If you have a group on quarter, an inserted summary would show the group total, and the grand total summary would show year to date.

-LB