asked on

Multi-Column Report with 3 Groupings.

Hi

I am trying to generate a columnar report (in Crystal Reports 11) which would appear as follows:

01/04/2005 08/04/2005 15/04/2005 Week-On-Week

Company A
State 1 30 50 40 -10
State 2 40 30 60 30
Company B
State 1 50 60 70 10
State 2 40 80 50 -30

I am retrieving the data from 2 tables (Sales and Dealers) , using a command in Crystal.

Sales
------------------
DealerCode
WeekEnding
NumberSold

Dealers
--------------------
DealerCode
CompanyName
State

Here is the command:

SELECT d.CompanyName, d.State, s.WeekEnding, s.NumberSold
FROM sales s
LEFT JOIN dealers d
ON s.DealerCode = d.DealerCode
AND s.WeekEnding BETWEEN '2006-04-01' AND '2006-04-30'

The data then needs to be grouped by CompanyName, State and WeekEnding to get the SUM of NumberSold.

How can this be achived?

I can do the basic structure using a cross-tab, but cannot figure out how to add the 'week-on-week' and other calculated columns, and it's formatting seems inflexible.

I have seen tutorials on how to create a columnar report using 2 groupings, but cannot figure out how to do it for 3 groupings!

Any assistance would be greatly appreciated.

Thanks

wykabryan

I would suggest the use of Running Totals for this report.

You can leave your group 1 and 2 as is, then set up your running totals.

Running Total Name: Date1
Field To Summarize: NumberSold
Type of Summary: Sum

Evaluate-
Use a Formula: Specify the date range that you want to sum, for example to achieve last week you would put LastFullWeek. Something like this
s.WeekEnding in LastFullWeek

Reset -
On Change of Group - Switch to group2

Do this for each of your columns that you have. Than you can calculate the Week - On - Week by using a formula to subtract running totals.

Hope this helps.

aus_colm

ASKER

Wykabryan - Thanks so much for your response. I am new to Crystal and am having some frustrations. I am unable to get the report with Company/State down the side and date in columns, as shown above now. Can you point me in right direction or should I start new thread??

Thanks heaps!

wykabryan

Creating the Company/State groups:

First Group will be Company:
Go to Insert>Group
When the report is printed, the records will be sorted and grouped by: Company(whatever the column name is)
You can then choose ascending or descending.

Second group will be State:
Go to Insert>Group
When the report is printed, the records will be sorted and grouped by: State(whatever the column name is)
You can then choose ascending or descending.

At this point you can then create the running totals like I posted above.

Hope this helps.

aus_colm

ASKER

I have 8 weeks worth of data - the data is returned from database as follows:

Company ---- State ----- WeekEnding ---- WeekTotal ----
CoA ---- NSW ---- 01/05/2006 ---- 10 ----
CoB ---- VIC ---- 08/05/2006 ---- 20 ----
CoB ---- QLD ---- 08/05/2006 ---- 30 ---- etc

I have grouped by Company and State but cannot get the dates to line up in columns. Do I need to create 8 variables for each of the week and assign at run-time???

Help!!!

Mike McCracken

How are you placing the fields on the report?

What alignment is specified?

mlmcc

aus_colm

ASKER

Hi

I want the Company and State on the left and dates across the top in columns - with the corresponding totals below - as in example above.

I have grouped by Company and State , but am stumped as to how to get dates into the columns.

Many Thanks!

Mike McCracken

Have you tried a cross tab report?

mlmcc

aus_colm

ASKER

yes. but need to add additional columns. So is it not possible in Crystal ?

Mike McCracken

I think a crosstab will work but I am not sure about adding other columns to it.

mlmcc

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial