Cumulativve Yearly Total in Crystsl reports Crosstab 1

[jonav]

Hi,

I'm new to this forum and found very informative solutions in the site.

Coming to my question:

I have designed a Crosstab report in Crystal reports 2008 with SAP BW data source and trying to print Total of countries.

Problem i'm faving here is: I want to print Present + previous years total as well
for ex: if I have Yrs from 2009 to 2011
and crosstab total should display 2009 as first year total and then in another page 2009+2010......+2011
on each page.

Is it possible to achieve this functionality.
I'm grouping on Year.

Pls let me know your views.
thanks
JN

 

[lbass]

Pleasen explain the setup in the crosstab expert: rows, columns, and summaries (field and summary type).

-LB

 

[jonav]

setup in the crosstab expert: rows, columns, and summaries (field and summary type).

Rows: Bex Structure - Line items
Columns: Years and Countries
Summary: Amount

 

[jonav]

here is the attachment

 

[lbass]

In rereading your post, it sounds like it might be easiest to insert the additional crosstabs in subreports where you change the date range in the selection formula.

-LB

 

[jonav]

Crosstab columns are dynamic. say for 2009 it might 5 countries and for 2010 it might be 4.. Where excatly do you want me to insert a Sub report and can you explain me pls?.

I'm giving a Release Year as Range in Paramter values.

 

[lbass]

In what report section is your crosstab currently placed?

Am I right in thinking you might want crosstabs like this:

2011 - page 1

2010 to 2011 - page 2

2009 to 2011 - page 3

-LB

 

[jonav]

Report is in Group Header Section1 where Year is a group.


2009 in page1
2010 in page 2
2011 in page 3

with grand total like
2009 Total in page 1
2009+2010 Total in page 2
2009+2010+2011 total in Page 3

 

[jonav]

Hi,

Is this functionality possible in Crystal reports 2008?.

Pls let me know.

thanks
JN

 

[lbass]

I'm not following what you are looking for. Are you saying that you want a cumulative subtotal per country from previous pages in the page footer?

-LB

 

[jonav]

Total of Previous years to print in Group hearder itself..As Crosstab is in Yearly Group header. I sent a snpshot also.

Let me know if you need more details

It is a Yearly Cumulative total in each page.

Like first page of 2009 should prnt with 2009 total
next page of 2010 should print the 2009 total + 2010 total in "Total" column of 2010.
2011 should print in next page with 2009+2010+2011 Total.

I put the Crosstab in Group Header where Year is a group by.

Pls help.
Thanks
JN

 

[lbass]

Are you saying you want to REPLACE the existing total year column with a cumulative total across the years? For each row? Or are you talking about an additional column?

Or are you talking about some grand total amount only that would appear in a cumulative manner?

-LB

 

[jonav]

An additional column with Grand Total in cumulative manner.


Thanks,
JN

 

[lbass]

Okay, insert a subreport that uses the same tables and selection criteria and link it to the main report on the date field. Place the sub in the group header next to the original crosstab. Then go into the sub->report->selection formula and change the date clause in the selection formula to:

year({table.date}) <= year({?Pm-table.date})

Insert a crosstab in a report header section and suppress all other sections of the subreport. In the crosstab add the same row fields and summary field as the crosstab in the main report, but omit the column field. In the customize style tab, uncheck "show row totals on left" and "show column totals on top" (if that's the way your main report is set up). Then run the report and make sure that the crosstab rows in the sub are aligned with the ones in the main report.

Go into the section expert of the main report->Group header and then check "relative positions". This will cause the crosstab to always maintain its position next to the right hand column, even if the number of columns varies.

Next, move the sub to the left so that it overlaps the original crosstab enough that the new (sub) column is next to the main report row total. Then go into the sub and suppress the row labels in the crosstab. Then go to the customize style tab->format grid lines and remove the lines around the row labels. You can then create a formula in the field explorer:

if year(minimum({table.date})) = year(maximum({Orders.Order Date})) then
totext(year(minimum({table.date})),0,"") else
totext(year(minimum({table.date})),0,"")+"-"+
totext(year(maximum({table.date})),0,"")

Delete the subreports "total" label->right click->edit and drag the new formula into the column label area.

This will create the appearance of one crosstab, but allow the accumulation across the years.

-LB

 

[jonav]

Actually I dont have a Date.. Only a Year as Parameter.

Pls advice.

 

[lbass]

You mean you have a year field? Is it numeric? Just substitute it, by linking on the year field and then changing the sub's selection formula clause to:

{table.year} <= {?pm-table.year}

Change the label field to:

if minimum({table.year}) = maximum({table.year}) then
totext(minimum({table.year}),0,"") else
totext(minimum({table.year}),0,"")+"-"+
totext(maximum({table.year}),0,"")

-LB

 

[jonav]

Actually I already have a Crosstab in Group Hearder and tried with above formula in Subreport. Somehow i'm not able to it though?.

Is there anything that I'm missing?.

Pls advice.

 

[lbass]

The crosstab is in the group header of the main report, but you shouldn't be grouping in the subreport. Just place the subreport crosstab in the report header of the sub. After selecting the crosstab column label->right click->edit and delete the "Total" and with the cursor active, drag the new formula into the label text box.

-LB

 

[jonav]

too many arguments has been given to this function"

for the below formula:

if minimum({ZGUL_M01_ZBOBJ_ZGUL_M01_QLLPRELYEAR_V7.[ZGUL_THYR]-[1ZGUL_THYR]}) = maximum({ZGUL_M01_ZBOBJ_ZGUL_M01_QLLPRELYEAR_V7.[ZGUL_THYR]-[1ZGUL_THYR]}) then
totext(minimum({ZGUL_M01_ZBOBJ_ZGUL_M01_QLLPRELYEAR_V7.[ZGUL_THYR]-[1ZGUL_THYR]}),0,"") else
totext(minimum({ZGUL_M01_ZBOBJ_ZGUL_M01_QLLPRELYEAR_V7.[ZGUL_THYR]-[1ZGUL_THYR]}),0,"")+"-"+totext(maximum({ZGUL_M01_ZBOBJ_ZGUL_M01_QLLPRELYEAR_V7.[ZGUL_THYR]-[1ZGUL_THYR]}),0,"")

"{ZGUL_M01_ZBOBJ_ZGUL_M01_QLLPRELYEAR_V7.[ZGUL_THYR]-[1ZGUL_THYR]}" Year field in report

 

[lbass]

If the field is already a string, remove the totext(,0,"").

-LB