Fiscal year and weekly totals 4

[lholodnak]

Crystal 10, oracle

I have a report where I need to calculate the total work orders completed by fiscal year and also for each week. I originally thought to create a subreport with the fiscal year date range and then in the main report use a parameter to select the week I want to report on. Is there another way of doing two different date selections in one report, rather than using a subreport? I'm trying to make the reports as simple as possible for other users. Thanks, Lisa

 

[RWahlert]

Howdy:

I've written several reports detailing YTD sales and MTD sales with comparisons to the previous years sales for the same period. Example May 2004 versus May 2003 along side 2004 YTD sales and 2003 YTD sales.

I used the dateadd function many times for the formulas. You might want to check that out.

What version of Crystal are you using?

 

[lholodnak]

I'm using version 10.0, I will look at what you suggest. I'm new to crystal reporting and not familiar with all that it can do. thank you, Lisa

 

[lbass]

You could use a record selection formula that limits records to the fiscal year and work orders to those that are completed. Then insert a group on {table.date} and choose "Print for each week". You could then insert a count on the work order field at both group and report levels. The first would give you weekly totals, and the grand total would be for the fiscal year you specified.

If you only want to view a particular week, while also showing the fiscal year total, you could create a discrete date parameter and use that in the section expert to suppress the group header, footer, and detail section except for the week starting with the parameter date.

-LB

 

[Jameson55]

I used your solution from Feb 8, 04 that had me create the forumula {table.date}-dayofweek({table.date},crMonday)+1 and then change the group name as suggested and it worked great! Especially in my cross tab and chart!

The only new question I have - is it possible to have a subreport in a cross tab report? I'm pulling sales information and the AR table only has their number - I have to link it to a separate table that has their name. Any ideas? thanks - Jamie

 

[lbass]

If I understand you correctly, I don't think you need a subreport. You would link the ID (number) field in table2 to the ID field in table2. You can either then use {table2.name} for the row (or column) field, or use the {table1.ID} field, highlight the field and choose "group options" and then choose "customize group name"->"use a formula as group name"->x+2 and enter {table2.name}.

-LB

 

[Jameson55]

sorry, I forgot that the sales rep number is actually a formulated field pulled out of the line item job cost reference number - that's why I had to use a subreport. THe only way I can see around this is to create a formula with the names "hard coded", but that would mean that it would have to be manually maintained if we add reps. thanks anyway - Jamie

 

[lbass]

You might consider using the sales rep number in the crosstab and then create a subreport with number and name that acts as legend or key for the crosstab.

-LB