Cross tab grouping question

[danausten]

Hi,

CR10
SQL Server 2000

I am trying to create a cross tab that shows by month the quarter to date total i.e.

JAN 05 will show a cumulative total for NOV 04, DEC 04 & JAN 05

FEB 05 will show a cumulative total for DEC 04, JAN 05 & FEB 05

MAR 05 will show a cumulative total for JAN 05, FEB 05 & MAR 05

and so on.

Anyone any ideas?

Thanks in advance,

Dan Austen

 

[synapsevampire]

I don't think that you want quarters in the traditional sense, what you want is a rolling 3 month period.

Try a formula for the record selection formula->record such as:

if month(currentdate) = 1 then
{Orders.Ship Date} in cdate(year(currentdate)-1, 11,1) to currentdate
else
if month(currentdate) = 2 then
{Orders.Ship Date} in cdate(year(currentdate)-1, 12,1) to currentdate
else
{Orders.Ship Date} in dateserial(year(currentdate),month(currentdate)-2,1) to currentdate

-k

 

[danausten]

Thanks for the reply - you are correct in saying that I want 3 months rolling. Howevere, I need the 3 months rolling for each month in a given date range e.g.

JAN FEB MAR APR MAY ...
Customer 1
Customer 2
Customer 3
Customer 4
Customer 5
...

Is this possible in a cross tab?

 

[synapsevampire]

Place the date in the cross-tab as the group, select for each month, and then use the Options tab->Customize Group Field Name and place:

monthname(month({table.date}))

-k

 

[lbass]

I think you would have to create a manual crosstab for this, since each record would need to be evaluated multiple times. You could use formulas like:

//{@Feb}:
if {table.date} in dateserial(year(currentdate),month(currentdate)-2, 01) to dateserial(year(currentdate),month(currentdate)+1, 01)-1 then {table.amt}

//{@Jan}:
if {table.date} in dateserial(year(currentdate),month(currentdate)-3, 01) to dateserial(year(currentdate),month(currentdate), 01)-1 then {table.amt}

etc.

You would group on customer and then insert summaries on these formulas at the group level, and then suppress the detail section.

-LB

 

[danausten]

lbass,

You are right, each record will be evaluated multiple times but I need to show all months for a given date range so a manual cross tab won't work as I won't know how many months to create formulas for.

synapsevampire,

I think I need to somehow use your record select foumula in the group part...

 

[danausten]

I've been looking at the Business Objects solution for creating a manual running total for rows in a cross tab & was thinking that I could modify this to do a 3 month rolling total but can't seem to figure it out. Can anyone help?

global numbervar RowNumber;
global numbervar RunningTotal;
global numbervar array SummaryValues;
global datetimevar ColumnValue;

if GridRowColumnValue ("@date") <> ColumnValue then RowNumber:=0;
ColumnValue := GridRowColumnValue ("@date");
RowNumber := RowNumber + 1;
RunningTotal := 0;
Redim Preserve SummaryValues[DistinctCount({account.SUBTYPE})];
RunningTotal := SummaryValues[RowNumber] + ToNumber(CurrentFieldValue);
SummaryValues[RowNumber] := RunningTotal;
ToText(SummaryValues[RowNumber],0)