cross tab running total

[back2tek]

Hi all,
I have a simple report that I would like to have in cross-tab format. I am running Cr9 on SQL server 2000. The table is like
Sale_date date,
Product_id char(3),
Quantity

The product_id has the product codes.

I need to report on the count of each product per day.
The output will be like :
Product Mar 28 Mar29 Mar 30 etc
Pr1 5
Pr2 10
pr3 20

I am not sure how to use running total in a cross tab layout,

Any help would be greatly appreciated.

Steve

 

[back2tek]

Crosstab gurus, where are you?

Thank you

 

[lbass]

Do running totals appear as an option in the field list within the crosstab expert in CR 9?

-LB

 

[back2tek]

Yes . It appears as an option. I have also XI just installed on another machine..

 

[lbass]

I guess I'm unclear on what you want the running total on. Please show a sample that uses at least three columns, and explain a little more what you mean to show.

-LB

 

[back2tek]

Thank Lbass, What I am trying to do is the following:

The Sales table has the insurance policy sales and looks like

policy type char(3) [ values are 1 for deluxe, 2 for medical, 3 for car insurance, etc]

duration char(3) [values are 1 for (9-15 days), 2 for (16-30 days), 3 for over 30 days)

country char (3) [values are 1 for USA 2 for Canada)

I would like to have a cross-tab that will display sales per day and total per week and looks like:

Summary 03/01/07 03/02/07 ....week-to-date
Deluxe Policy 15 30
Medical 3 6
CAR 8 16
9-15days 12 24
16-30days 4 8
over 30 days 10 20
USA 16 32
Canada 11 22


The challenge I am facing is
1-to list the policy even if there is no sale for the day.
2-the week to date running total

Hope this helps

 

[lbass]

Given your proposed layout and the need to show all policies even when there are no records (you can't use an inserted crosstab for this), I think you should just create a series of running totals in the report footer where you set the evaluation formulas to match the criteria for each column and row, including the week to date, e.g., for a deluxe weektodate running total, you could use an evaluation formula like:

{table.policytype} = 1 and
{table.salesdate} in WeekToDateFromSun

...assuming you were writing this for some date in the current week.

-LB

 

[back2tek]

That's a lot of manual work..On a second thought if I would display what was sold only that day, How would I set up the row fields in the cross-tab.

Thanks in advance

 

[lbass]

What do you mean? Just for one date? No week to date?

You would insert three separate crosstabs, since you don't want the three fields nested. Use one of the three fields you mentioned as the row field, and add your summary field. Add the date field as your column or restrict the data to one date in your record selection formula.

-LB

 

[back2tek]

Thank you LB. Greatly appreciated.