I have created a cross tab report which returns the total turnover/day for a specified period of time then graphs the running total for the period. I use this as a way of comparing performance against last year or several years and as part of forecasting.

 

I have 3 parameter fields

S Date - The start date for the period (Date Time format)

F Date - The finish date for the period  (Date Time format)

Customer - what customers do I want to return data for (String Format)

so my record selection formula is as follows:

 

(if {?Customer} = "*" then
    True
Else
{salesorders.scustomer} = {?Customer};) and
{salesorders.sdate} >= {?S Date} and {salesorders.sdate} <= {?F Date}

 

I know I could setup a ?Date parameter field for the date and allow a range value, but I've done it the long way round for the moment (both ways give same result - tested)

 

I have a running total field #Price

 

this is the running total of my sprice field from the database which is the line value  for a sales order line.

 

My cross tab is set up so the collumns are the date and there is a row displaying the sum of sprice and then below it the running total #Price

 

This allows me to see the total sales turnover for each day and the cumulative for that point in the Date range.

 

Then my chart plots #price against the date thus profiling the cumulative sales turnover for the period.

 

When I run the report it runs fine for every month and every date range except if I run it for January 2011 as a whole or start or end my range at a certain point. I've run an extract and pivot table in excel on the same data with no issue, it's like something in crystal reports XI can't handle Jan 2011. it starts fine on day 1 then for some reason takes the cumulative turnover from the 19th to the 31 and adds this to day 2 then carrys on normally till the 19th then subtracts some turnover then starts again for the 19th to the 31st.

 

If I un suppres the row total fields the top row )sum of sprice adds up to the correct total so I know it's returning the correct data but for whatever reason the running total just doesn't work for Jan 2011 - Weird!!!

 

Any suggestions?