Formula in a cross-tab that uses a value from a previous column 1

[Razor1973]

This is an extremelly simplified model of my environment, but should explain my problem.

I basically have the following fields in my table:

Year (numeric)
Employee (text)
Sales (numeric)

I want to create a cross-tab that has Employee as a row header (no problem), Year as a column header (no problem), and then, for every year, I want to see Sales and --here comes the problem-- the growth rate based on the previous year.

This is what my report should look like:

            2004             2005             2006
        Sales   Growth   Sales   Growth   Sales   Growth
John      100      N/A     150      50%     100     -33%
Susan     200      N/A     200       0%

In this example, no Growth is shown in the first year, as there is nothing to compare to (it would be ideal if I could hide the Growh column altogether) and Susan was no longer an employee in 2006.

Any feedback would be greatly appreciated.

Thanks in advance!

 

[lbass]

What is your CR version?

-LB

 

[Razor1973]

XI SP2

 

[lbass]

One way of doing this is to eliminate the column field and instead use formulas for the summary fields:

//{@2004 Sales}:
if year({table.date}) = 2004 then {table.amt}

//{@2005 Sales}:
if year({table.date}) = 2005 then {table.amt}

//{@2006 Sales}:
if year({table.date}) = 2006 then {table.amt}

Create another formula to use as a holder for the percentage display:

//{@0}:
whilereadingrecords;
0

Insert {@0} as a summary field three times and use the arrow key to position them after each year summary. Go to the customize style tab->summaries->choose "horizontal" and "show labels". In preview mode, change the label from {@0} to "Growth". Change the year labels to remove the "@" sign.

Next, select an inner cell for the 2004 summary, and if you want to show growth for the column totals also, then simultaneously select the column total for 2004->right click format field->common->suppress->x+2 and enter:

whileprintingrecords;
numbervar y2004 := currentfieldvalue;
false

Repeat for the 2005 and 2006 summaries, only changing the variable names to y2005 and y2006, respectively.

Then select the {@0} summary for 2004 (and the column total if you want the growth also shown there)->right click->format field->common->display string->x+2 and enter:

whileprintingrecords;
"NA"

Then select the {@0} summary for 2005 (and the column total if you want the growth also shown there)->right click->format field->common->display string->x+2 and enter:

whileprintingrecords;
currencyvar y2004;
currencyvar y2005;
totext((y2005-y2004)%y2004,2)+"%"

Next select the {@0} summary for 2006 (and the column total if you want the growth also shown there)->right click->format field->common->display string->x+2 and enter:

whileprintingrecords;
currencyvar y2005;
currencyvar y2006;
totext((y2006-y2005)%y2005,2)+"%"

These formulas are formatted to show 2 decimal places. Change the "2" to 0 if you want no decimals.

-LB

 

[Razor1973]

lbass, first of all, thank you so much for taking your time in creating such a detailed response. It shows a great deal of dedication to helping others.

Before I go further into the details of your alternative, I see what may not qualify it as a solution to my problem and that's the fact that it seems you are assuming the years will always be 2003, 2004 and 2005 as in my example, but that was just an example. These years can be any years and could be either 1 or 15. I need to show all 15 years of history and break pages horizontally if need be.

Keeping that in mind, do you still think a twist of your solution may still work?

Thanks a lot again!

 

[lbass]

This is probably just about as (or more) complicated as using a manual crosstab. You could consider that. You would insert a group on {table.employee} and then create conditional formulas like {@2004 Sales} as you did earlier, only change them to make them dynamic, like:

//{@twoyearsago}:
if year({table.date}) = year(currentdate)-2 then {table.amount}

//{@lastyear}:
if year({table.date}) = year(currentdate)-1 then {table.amount}

//etc. You would then insert summaries on these, suppress the detail section, and drag the groupname into the group footer. For the growth formulas, you would set them up like this:

//{@growthtwoyrsagotolast year}:
(sum({@lastyear},{table.employee})-sum({@twoyearsago},{table.employee})) % sum({@twoyearago},{table.employee})

//etc.

You could also make the column labels dynamic, by replacing the text labels with formulas:

//{@twoyearsagolabel}:
totext(year(currentdate}-2,0,"")

Not sure what determines the number of years included, but you if it is a parameter, then you could create the 15 columns and then use the parameter to suppress unneeded columns.

-LB

 

[Razor1973]

lbass, thanks again. Maybe I didn't make myself clear enough. I am sorry.

You are assuming these years will be contiguous and I will have some sort of date range parameter. No. The data returned will contain the years and it could be 1998, 2001, 2002, 2004 and 2006 or something like that, so there may not be any data for "last year".

Nothing determines the number of years included, only the data once it's returned. When I said 15, I meant any number of years could be returned.

Thanks again.

 

[lbass]

Well, you could return the years for the report by inserting a subreport in the report header to collect the years in a comma-delimited string. You could then reference the string elements in the conditional formulas in the main report to be used in a manual crosstab.

The names of the variables don't have to reference the actual years, so you could change y2004, y2005, y2006 to col1, col2, col3. It's just that you'll have to set up separate comparisons per column.

-LB

 

[Razor1973]

And how many of those colX do you make? If you specify a number, then you're not allowing for unlimited years, as my report requires.

Also, say you know for a fact they will not be more than 20 years and you create 20 fields. How do you place them on a letter-size report so that, if the number of years goes over the amount of years that will fit on a page horizontally, the rest will show on a second page and so forth?

 

[Razor1973]

lbass, no-can-do, right?

 

[lbass]

Sure there's a way, although virtual pages are only available for inserted crosstabs. You can create a group based on a set number of columns, for example.

-LB