Quarterly sales report

[franklin1232]

My boss wants a report that sums up all quoted and booked dollars for the year by quarter. Problem I had was the data isn't related so I used a subreport. Then a shared variable to pass the total quoted dollars back to the main report. The main report has two groups. Group1 is customer number and group2 is shipdate by quarter. I have two problems.

If there isn't any data for a particular quarter there is no header because there is no group. How can I get all quarters, maybe even through next year, to show. I thought I could use a formula to create a string header, but as soon as I show any report or group headers the columns get all out of wack.

Second is I only want the customer's name to appear in quarter 1. The report is formatted in columns so quarter 1 would be the row header, but suppress if duplicated doesn't work because they are in seperate groups and I haven't had any luck using a counter to suppress it.

Thanks

 

[Madawc]

In Crystal 8.5, I'd do the following:

To show all possible quarters, do a running total for each and put them in the report footer. You can extract quarters based on currentdate.

For the customer name, right-click [Format Text] on the field and then set a formula for suppress if the quarter is not 1

Madawc Williams
East Anglia, Great Britain

 

[franklin1232]

Got it working, thanks. One more thing though. When I suppress the customer name it creates a large gap in the columns after quarter 1. Is there a way I can move the total field left after the first quarter group formats?

 

[GJParker]

place 2 copies of your total fields on the report and use a formula based on the quarter to suppress the ones you don't want to see.

HTH

Gary Parker
Systems Support Analyst
Manchester, England

 

[franklin1232]

madawc are you still out there? I could use a little more detail on getting all quarters to show up. I am really new to crystal and I am not sure how to get this to work. Could you give more detail.

Thanks again.

 

[Madawc]

If you group on quarter, it will show all the quarters that have details, and ignore the others.

You can create a running total for each possible quarter, to show up the zero totals. Running totals are under Field Explorer and it's best to learn their function by trial and error, do a couple of experimental reports just to see what the results are.


Madawc Williams
East Anglia, Great Britain

 

[franklin1232]

I am not sure how putting a running total in the report footer will work. My first group is customer and second group is date by quarter.

Q1 Q2 Q3 Q4
Company A $$$$ $$$$ $$$$ $$$$

So I have a running total field that totals all the values for each quarter for company A, but if there is no Q2 data for Company A then Q2 is show nothing and the report pulls Q3 or Q4 over to fill the space. If I am understanding you the running total field should show $0.00 for Q2 if there is no data in that group.

 

[GJParker]

franklin

if you are only after summary data for each customer, create 4 formulas 1 for each quarter i.e.

//Quarter1
If {table.Quarter} = 1 then
{table.value}
else
0

you can then place these 4 formual in your details section and the sum at customer level to give you a report aomething like this
Q1 Q2 Q3 Q4
Customer1 ## ## ## ##
Customer2 ## ## ## ##

HTH



Gary Parker
Systems Support Analyst
Manchester, England

 

[franklin1232]

So your saying I shouldn't group on date by quarter at all, just by customer. Then use a formula to sum only values for a quarter. I have a subreport that I use a shared variable to pass unrelated data to the main report. Do you see any problems with this?

 

[GJParker]

If you need to show valuse for each quarter even when no records exist, yes this would be one way of approching the problem, this shouldn't have any bearing on your sub-report.

HTH



Gary Parker
Systems Support Analyst
Manchester, England