on 04-22-2014 1:32 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
User | Count |
---|---|
83 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.