Weekly and YTD Data

[cuecat]

Using Crystal 8.5 on W2K box, Access 2000 database.

My query is a list of production data for a plant, each day how many widgets planned, produced, and how long it took to produce. (This will also be measured against a standard, but that later.)

For every week I total how many widgets each shift produced and how long it took, and widgets/hour. This is a summary field in a group that is grouped by date field, for every week. This summary data exists in the week footer. (Above the week footer I have the shift footer, that sums widgets produced for the week for each shift.)

Question: how do I show YTD summary in the week footer along with the widgets produced totals? I also want to chart week and YTD data, so it can't be a running total - it doesn't work, already tried it.

So what I want is, for each week:

Shift widgets produced
1 100
2 200

Week Total: 300
YTD Total: 1000 (total up to this week was 700)

 

[rosemaryl]

Dear cuecat,

Pull data for the entire year.

Group the way you currently are grouping or if you want to create a group by Week Number (1 through 52):

datediff("d",Date(Year(currentdate),1,1),{YourDateField)/7)

gives you the week number from 1 to current.

Now you only want to show current week data so suppress all groups not equal to current week.

(datediff(&quot;d&quot;,Date(Year(currentdate),1,1),{Yourdatefield})/7) <> (datediff(&quot;d&quot;,Date(Year(currentdate),1,1),currentdatetime)/7)


Show year to date (grand) totals in the Report Footer.

Sorry I couldn't take more time with it, but I think that will work.

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[cuecat]

Rosemary,

Thanks for the post, but you didn't read my question. I need YTD data NOT in the Report Footer, but as part of my weekly totals. Each week I need to print weekly totals and weekly YTD totals.

Thanks.

-Jonah

 

[rosemaryl]

Dear Jonah,

So Sorry, you are right. Didn't read the post correctly.

I will think about it over the weekend and get back to you on Monday. Once again, my apologies.

My initial thought though is to write a stored procedure. Do you have the option of writing a stored procedure?

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[rogar]

Create a query in your access database for YTD totals, something like SELECT WidgetID, SUM(WidgetsProduced) From WidgetTable Where DateProduced >= FirstOfThisYear GROUP BY WidgetID.

 

[cuecat]

Thanks all.

For future reference, here's what we ended up doing. Turns out Crystal charting is still pretty weak (as of 8.5). No good way to get Crystal to calculate YTD data from daily data AND chart the data. We had to go the manual route.

1. Instead of trying to do YTD, we allow the user to enter from: and to: dates.
2. Not charting in Crystal - client will export data to Excel and chart in Excel.

So we created some queries that tally the data from date x to date y, and shoves the data in a table. The client can then export that data to Excel at his/her leisure.

Far from a perfect solution, but the customer agreed to it. Thanks to Dr. Hammerman for his help. (BTW, he grave a great talk to the Dallas CUG last month. He's a wealth of info and fun to talk to if you ever get the chance.)