Row Cumulative calculation in a Crosstab 1

[jonav]

Hi,

I have a Crosstab with Fiscal year as column , Bex structure as row and Amount as Summary.

I'm trying to do a cumulative calculation for specific rows in a crosstab.

Pls guide.

Thanks
JN

 

[lbass]

For specific values of Bex structure? Where do you want to display the cumulative amount? Within each inner cell or within row total cell?

-LB

 

[jonav]

Hi Ibass,

Attached is the snapshot of the report.

http://www.mediafire.com/?svzvaryh2qcbh8

I need to display cumulative values for only 2 rows.

1. Cumulative Total Discounted Project Cost cash flows
for this we can consider the "Total Discounted Project Costs Cash flows" values as cumultive calculations and print in "Cumulative Total Discounted Project Cost cash flows row"

2. Cumulative Net Discounted After tax Cash flows
Consider the "Total Discounted After Tax Cash flows" values as cumultive calculations and print in "Cumulative Net Discounted After tax Cash flows"

Pls help.

thanks,
JN

 

[lbass]

Your link does not display anything, so I can't follow your response.

-LB

 

[jonav]

http://www.mediafire.com/file/v8s3inc21mqlzzt/Cumulative

calc.doc
or

http://www.mediafire.com/?v8s3inc21mqlzzt

or

[URL unfurl="true"]http://www.mediafire.com/?v8s3inc21mqlzzt[/URL]

Pls find the link above and help me out.

Thanks
JN

 

[lbass]

It looks like you have multiple row fields, not just one on Bex...

If you do, please identify them.

-LB

 

[lbass]

Also, I cannot see what you are intending to show cumulatively--are there multiple pages like this where you want those particular values to accumulate? If so, please show multiple pages.

I'm having trouble seeing this in Word format. Any chance of showing it in pdf format?

-LB

 

[jonav]

In Bex the structure is a single column with multiple rows. Tht's how they define it actually.

When you select a Structure in crystal report and dragged it will display as a column with mulitple rows.

I tried with calculated member but still confused what would be the grivalue formula would be.

Pls advice.

thanks
JN

 

[jonav]

Sedning you the full projects in PDF format.

<a href="

http://www.mediafire.com/?idzv34zm5pmlzcd"

target="_blank">

http://www.mediafire.com/?idzv34zm5pmlzcd</a>

or

[URL unfurl="true"]http://www.mediafire.com/?idzv34zm5pmlzcd[/URL]

Thanks
JN

 

[lbass]

Okay, but I'm not sure how you achieved the formatting like that. Did you see my last post?

-LB

 

[jonav]

It is Crosstab

Row: Structure
Column : Fiscal Year
Summary : Amount Key figure.

it is just a simple reports but complexity lies under the rows.

Can you suggest what would be the formula if I want to achive a cumulative for a row.

I know you helped me on column wise and it worked fine.

Here i'm thinking of adding a row instead but need your help in writing a calculated formula.

Thanks,
JN

 

[lbass]

I understand that. Please try to answer my question:

Also, I cannot see what you are intending to show cumulatively--are there multiple pages like this where you want those particular values to accumulate? If so, please show multiple pages.

In the Word document, did you manually enter the cumulative rows to show me?

I haven't learned how to insert calculated members in CR2008 (Ken Hamady has a monograph on this), but you could do this using variables in formatting areas, like this:

Right click on the summary field in preview mode->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar cumul;
if gridrowcolumnvalue("table.structure")="Total Discounted Project Costs Cash Flows" then
cumul := cumul + currentfieldvalue;
false

The field you are using for the crosstab row should replace "table.structure"--just remove the curly brackets and replace them with double quotes as shown above.

If you want the cumulative value to overwrite the current value, then in the same format field screen, go to display string->x+2 and enter:

whileprintingrecords;
numbervar cumul;
totext(cumul,0); //0 for no decimals

-LB

 

[jonav]

Hi LB,

Here is the reply to your questions.

Also, I cannot see what you are intending to show cumulatively--are there multiple pages like this where you want those particular values to accumulate? If so, please show multiple pages.
---> Yes.. the calculation should be dynamic and it should display deoending on the projects the user runs.
sent you the pdf of multiple pages of a report.

In the Word document, did you manually enter the cumulative rows to show me?
--> I did not enter anything manually in crosstab. each and every values coming from backend.


I will try with the formula that you mentioned and let you know the result.

Thanks again,
JN

 

[jonav]

Hi Ibass,

Used the formula that u mentioned above but it is display the same values as in Total Disocunted Proejct cas flows.. It is not adding up as cumulative.

Pls let me know if I'm missing anything

thanks,
JN

 

[lbass]

You will have to recount exactly how you implemented this.

What this would do is add the value from that row on subsequent pages for the corresponding row.

-LB

 

[lbass]

You could also upload the actual report saved with data and I could take a look at what you did.

-LB

 

[jonav]

Report with Cumulative Formula :
[URL unfurl="true"]http://www.mediafire.com/?oe9h5cf3k3woi99[/URL]
or
<a href="

http://www.mediafire.com/?oe9h5cf3k3woi99"

target="_blank">

http://www.mediafire.com/?oe9h5cf3k3woi99</a>

Report without Cumulative:
[URL unfurl="true"]http://www.mediafire.com/?0totohio366c6je[/URL]
or
<a href="

http://www.mediafire.com/?0totohio366c6je"

target="_blank">

http://www.mediafire.com/?0totohio366c6je</a>

Cumulative caculation should be only for 1 row "Cumulative Total Discounted Project Cost Cash Flow" the values shld be taken from
"Total Discounted Project Costs Cash Flows"

Fiscal year: 2000 2001 2002 2003 Total
"Total Discounted Project Costs Cash Flows": 10 10 10 10 40
"Cumulative Total Discounted Project Cost : 10 20 30 40 40
Pls let me know if this can be achieved.

Thanks,
JN

 

[lbass]

I was unable to open any of the links--the download wouldn't complete, but I think I see now what you are looking for--I didn't understand that you wanted to accumulate ACROSS the row.

From your Word document (which I could open), it looks like the actual data in the cumulative row is the same as in the row you want to accumulate, so select an inner cell of the crosstab and then right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar cumul;
if gridrowcolumnvalue("table.structure")="Cumulative Total Discounted Project Cost Cash Flows" then
cumul := cumul + currentfieldvalue;
false

The field you are using for the crosstab row should replace "table.structure"--just remove the curly brackets and replace them with double quotes as shown above.

Change the formula in the format field->common tab, select Display String->x+2 to:

whileprintingrecords;
numbervar cumul;
if gridrowcolumnvalue("table.structure")="Cumulative Total Discounted Project Cost Cash Flows" then
totext(cumul,0) else //0 for no decimals
totext(currentfieldvalue,0)

If you are not seeing the accumulation, then provide the actual formulas you are using, as I did test this. Make sure you are using the exact wording to match the row name, as I noticed you left the "s" off "Flows" in your description, and that would cause the formula to fail.

-LB

 

[jonav]

TQ IBass,

It looks like working but still the values are rolling accross all the pages.

Cumulative should not be passing to another Project number page as I have grouped the cross tab by Project number.

Pls advice.

thanks,
JN

 

[lbass]

You need a reset. Select the row total cell->right click->suppress->x+2 and enter:

whileprintingrecords;
numbervar cumul := 0;

-LB