cancel
Showing results for 
Search instead for 
Did you mean: 

Crosstab - column subreports

Former Member
0 Kudos

Hello,

I'm searching for same advice, hint ... I need to make column subtotals in crosstab

I have crostab like this

                    january 2011      February 2011    March 2013     December 2014     TOTAL

product1               5                         6                    7                         8                    26

product2               1                         2                    3                         4                    10       

Product can be more, and usualy I have data from every month this abowe is only example, also time interval is set by user (report parameters)

everything works OK but now I need to make a subtotal after every year :

                    january 2011      February 2011       subtotal             March 2013     subtotal       December 2014          subtotal         TOTAL

product1               5                         6                      11                       7                   7                       8                              8                26

product2               1                         2                      3                       3                    3                       4                              4               10      

I thing it must be done by adding calculate member, but I can't reach data from dataset since I have crostab in header.

Could someone explain me how to do this ?

Thanks for any help

Accepted Solutions (1)

Accepted Solutions (1)

abhilash_kumar
Active Contributor
0 Kudos

Hi Jakub,

Here's what you need to do:

1) While in the Preview Mode, right-click the header cell of the first column in the crosstab (In the example above, it is the January 2011 column) > Select Calculated Member > Insert Column.

A blank column with zero values is inserted.

2) Right-click the header cell of this Blank Column and select Calculated Member > Edit Header formula and type in this text:

"Subtotal"

3) Right-click the header cell again and select Calculated Member > Edit Insertion Formula. Type in this code:


GetColumnGroupIndexOf(CurrentColumnIndex) = 1

AND

(

    if CurrentColumnIndex  <> GetNumColumns - 2 then

        Year(GridRowColumnValue("Date")) <>

        Year(GridRowColumnValue("Date", CurrentColumnIndex + 1))

    else

        Year(GridRowColumnValue("Date")) = Year(Maximum({Date}))

        AND

        Month(GridRowColumnValue("Date")) = Month(Maximum({Date}))

)

Note: In the above code, replace "Date" with the Date field used as Column in the Crosstab. The double-quotes are necessary and make sure to remove any parenthesis that CR automatically adds when a field is used in a formula.

Similarly, in the part where it reads Year(Maximum({Date})), replace {Date} with the Date field in the Crosstab however, this time use the parenthesis as in the code.

This should insert the Subtotal Column before a new year starts.

4) Next, right-click one of the zero values in the Subtotal column > Calculated Member > Edit Calculation Formula and use this code:


local currencyvar x;

local numbervar i;

local numbervar cindex := CurrentColumnIndex;

for i := 0 to cindex-1 do

(

    if year(GridRowColumnValue("Date",i)) =

       year(GridRowColumnValue("Date",cindex-1))  then

    x := x + GridValueAt(CurrentRowIndex, i, CurrentSummaryIndex);

);

x;

Let me know how this goes.

-Abhilash

Former Member
0 Kudos

Hi Abhilash,


many thanks for your replay, I tried your solution and I found several pitfalls:


  Year(GridRowColumnValue("Date"))  -> unfortunately I can't use date from my SQL becouse ->


FORMATVALUE(h.recordtime USING 'YYYY-MM') -> returns me string not date, if you know about same way how to format datetime as date only with year and month, without changeing datatype it would be very helpfull. I'm using default SCX database (not Oracle, MSSQL, I dont know exactly what type of db it is)

In Calculation Formula on row

  x := x + GridValueAt(CurrentRowIndex, i, CurrentSummaryIndex); 

I'm getting error

CurrentSummaryIndex is out of bound, when I add CurrentSummaryIndex+1 it's ok (But I dont know why +1 maybe you can explan more code I don't want you bother with similar situation)

after this

CurrentRowIndex Argument #1 'GridValueOf' is out of bound and here I haven't any ideas.

At the and I must mention that in my example data in crosstab was prices but real data in my table are datetime(I feel really sorry for bad example) in milisecound so I changed:

local currencyvar x;  -> local dateTimeVar x;  Is that ok ?

I was looking for <code></code> tags for code pieces same as you but I'm out of luck if you give me same hint next time I will use it

abhilash_kumar
Active Contributor
0 Kudos

You should, before trying anything else, bring in the dataetime field 'as is' without converting to String.

In the Crosstab, add the datetime field as a Column > Click Group options > Where it says ' The section will be printed for', choose 'For Each Month'.

Once, this is done, all the other formulae should work fine as I have this working on my machine.

If it doesn't work, please attach the report with saved data.

-Abhilash

Former Member
0 Kudos

Hello again,

Yes it works as you said but still getting index errors.

I would like to send you report but I can't attach rpt file,which format you want ?

abhilash_kumar
Active Contributor
0 Kudos

Change the extension of the report from .rpt to .txt and click the 'Use advanced editor' link in the reply window here. You should then see an 'Attachment' button.

-Abhilash

Former Member
0 Kudos

here you are

abhilash_kumar
Active Contributor
0 Kudos

Find attached the updated report.

If you can't open the attachment, here's what you need to do:

1) Right-click the header cell for the Subtotal > Calculated Member > Edit Columnvalue formula > use this code:

cdatetime(1899,01,01,00,00,00)

2) Right-click one of the zero values in the Subtotal cell and select Calculated Member > Edit Calculation Formula > use this code:


local numbervar cindex := CurrentColumnIndex;

local numbervar i;

local currencyVar x;

local currencyVar y;

for i := 0 to cindex-1 do

(

    if GridRowColumnValue("DATUM", i+1) = cdatetime(1899,01,01,00,00,00) then

    (

        x := x + GridValueAt(CurrentRowIndex, i, CurrentSummaryIndex);

        y := x;

        x := 0;

    )

    else if year(GridRowColumnValue("DATUM", i)) =

       year(GridRowColumnValue("DATUM", i+1)) then

    (

        x := x + GridValueAt(CurrentRowIndex, i, CurrentSummaryIndex);

        y := x;

    )

);

y;

Have a great day!

-Abhilash

Former Member
0 Kudos

Hi Abhilash,


THANKS A LOT for this one you really made my day !!!!!

Answers (0)