Crosstab Question - % instead of total

[mike250]

Hi. I've created a Crystal Crosstab report that pulls retail sales analysis data from a SQL Server in 3 categories, or crosstabs. The purpose for this report is trending.
1. Retail sales total for each month.
2. Customer count total for each month.
3. Average Transaction total for each month.

The idea is to run this report over a 2 year period to show year over year and month over month sales, customer count and avg. trx for that time period.

Everything works peachy. I created a custom view and used crystal crosstabs and some formulas to get my desired results. The report would look something like this:

            Jan        Feb        Mar      ...  Total
Year 2001   11674.21   25873.92   19192.10 ...  $total
Year 2002   9191.12    21823.01   25912.56 ...  $total
            $total     $total     $total   ...  $total

My problem is I do not want the totals for each column. (month) I know I can supress these, but I need something else. I need % or - for the time period. So for this store, in January, it would show -22% for the month of January over last year.

Now the date selection is a parameter, so the years will always be different. The idea is that not more than 3 or 4 years are ever selected at any one time.

Anyone have an idea of what I need to do?


Michael Doig

http://www.coastart.net/mike

 

[IanWaterman]

Mike

I have done reports like this and the only way I have achieved it is doing a manual cross tab usng variables. You can then use the monthly variable totals to calculate your percentages.

For your report you will need to create 24 variables, one for each month x 2 for the years.

Ian Waterman
UK Crystal Consultant

 

[mike250]

Thank you. I will see what I can do here.
I've never created a manual cross tab or even heard of one. I have done a little research on what it is and will take a stab at it!