Crosstab : changes between 2 columns 1

[Gus01]

hello

in a crosstab is there way to display the changes between 2 columns :

2008 2009 2010
Values 10 12 15
Changes - +2 +3
% - +2% +2,5%

Thanks

 

[lbass]

You could do this in an inserted crosstab, but you would have to have all values in either a vertical or horizontal display, not in a combination as you show. You could easily get this display in a manual crosstab, and the effort would be about the same. Is there a reason you are using an inserted crosstab? Also your percentage increases are incorrect. Should be 20% and 25%.

-LB

 

[Gus01]

Thank you for your reply.
And sorry for the percentage increase, manual calculation ...

I don't know the number of years to display, so I don't think I could use a manual crosstab.

I can not show the increase between two columns,
1) I have to filter to select only certain rows
formula f1 : If {A.Code} = 10 Then {A.Price}
2) there may be several rows for a year and I can not use Previous to calculate the difference between two years

 

[lbass]

With either method you would have to know at least the maximum number of years for the report, and in this case I would recommend using a manual crosstab.So what would the maximum number of years included in the report? The specific years don't matter.

-LB

 

[Gus01]

True, the maximum number of columns are the 7 latest years;

So I have to create a formula for each year?
@ValueN (N= 0 to 6)
if {A.Year} = Maximum({A.YEAR}) - N Then
{A.Price}
Else
0

How to calculate the increase ?

 

[lbass]

I wouldn't use maximum in the conditional formula, because then you won't be able to summarize it easily. Let's say you have a parameter for the starting year. Then you would create formulas like this:

//{@Yr1}:
if {A.Year} = {?StartYr} then {A.Price}

//{@Yr2):
if {A.Year} = {?StartYr} + 1 then {A.Price}

//{@Yr3}:
if {A.Year} = {?StartYr} + 2 then {A.Price}

Then you would place these in the detail section and insert sums on them at the group or grand total level and then suppress the detail section. For each formula, create two others, e.g., (the following assumes there are no groups):

//{@Yr3-Yr2):
if maximum({A.Year}) >= {?StartYr}+2 then
sum({@Yr3})-sum({@Yr2})

//{@Yr3pct}:
if maximum({A.Year}) >= {?StartYr}+2 then
(sum({@Yr3})-sum({@Yr2}))%sum({@Yr2})

Format each of the formulas to "suppress if zero" (format field->number tab->customize). Add these formulas below the inserted summaries.

If you have a group, you would have to enter the group condition into the summaries.

-LB

 

[Gus01]

Very good! it works perfectly, thank you.

It remains to determine the first and last year to display.
I can not use subreport, because this report itself may be inserted in another report.
Do you know if there is a way of knowing the first and last year of data (sorting + first / last record ?) in the subreport itself, or should I pass this information with parameters?

Thank you

 

[lbass]

I'm not sure I'm following the question--"pass this information with parameters" from where?

You can check first and last years, by using a formula:

minimum({A.year})& ' to ' & maximum({A.year})

-LB

 

[Gus01]

Unfortunately, I get "Load report failed" when I use the formula minimum({A.year}) to replace the {?StartYr} parameter.

To retrieve the first and last year in the Main report, I thought that creating a first subreport which return to the Main report these years.

Main report
firstSubreport (to know the first and last year)

mySubreport with manual crosstab

(I don't know what is the best for passing those years between the two Subreports).

 

[lbass]

I wasn't suggesting that you replace the parameter. You don't need a subreport. Why do you even have the crosstab in a subreport? Why do you need to know the maximum and minimum years? You said the maximum number of years was 7, so just create sets of formulas for 7 years, as I showed you, using the {?StartYr} parameter.

-LB

 

[Gus01]

Thank you for your reflection.

The crosstab is a part of a main report and is found in several reports. The choice of a subreport facilitates the update.

I do not know in advance the number of years to display. But the width of the page displays only the last 7 years. There is different cases.

For example, if the years are 2003 to 2010, the only knowledge of the first year (2003) show: 2003-2009, missing 2010.

If the years are 2006 to 2010, the only knowledge of the last year 2010, to calculate the first year: 2004 = 2010-7 + 1, then the years 2004 and 2005 are displayed but empty.

So knowing minYear and maxYear, I compute the first year to display:
firstYear = If maxYear - 7 + 1 > minYear Then maxYear - 7 + 1 Else minYear

I think using shared variables to pass min and max Years between the first subreport and the crosstab subreport thru the main report ?

 

[lbass]

What is it that is determining the number of years? You seem to be saying that the years are not determined by the data--so are you using parameters for the years? Or are you saying the years in the container report (or another subreport) are determining the years? And I am still unclear on where you want or need to use this information. If you create the formulas, you will get zeros if there is no matching data. If the issue is that "missing years" within the range are not showing the zeros because of the "suppress if zero", then make the suppression if zero conditional, by using a formula, e.g., for the fifth year summary:

maximum({A.Year}) < {?StartYr} + 4

-LB

 

[Gus01]

The years to be displayed are determined by the data in the database.

To know the range of years, I can use a formula with minimum or maximum({A.Year}), but as you say, I can not use this formula in a summarize field, and manual crosstab uses summary fields.

So I am looking for the value of the first year, for replacing the {?StartYr} in your formula - "Let's say you have a parameter for the starting year")

Why I need minimum AND maximum years ?
The 'missing year' is not a problem of 'suppress if zero'.
Assuming that the manual crosstab is scheduled for the last 3 years.
This occurs if the years in the database are from 2006 to 2009 (ie 4 years)
If I only use the first year to condition crosstab (as in your sample), because only 3 columns are provided, this will display the years:

2006 2007 2008 (the first 3 years)

or should have been displaying

2007 2008 2009 (the last 3 years)

 

[lbass]

I am not following your logic here. First, why can't you use a parameter? If you want the last three years, you could use a parameter value of 2007--or you could change the formula to use the ending year. And I meant for you to create formulas for the maximum number of years--if that is 7, then you have 7 sets of formulas.

If you are running this report against one database, then you would know the minimum and maximum year up front. Since you don't know this, it must be that you are running the report for different groups with varying date ranges or something. If you don't want to use a parameter, then you could create a SQL expressions for minimum year {%minyr} and maximum year like this:

(
select min(`year`)
from table B
where B.`ID` = table.`ID`
)

...where {table.ID} represents the field that distinguishes different groups--I don't know if these are accounts, or what, and where "table" represents your table name (A?) and "B" represents an alias table. The punctuation depends upon your database.

{%maxyr} would look similar:
(
select max(`year`)
from table B
where B.`ID` = table.`ID`
)

You could then use {%minyr} and {%maxyr} in the formulas and you would still be able to insert summaries.

-LB

 

[Gus01]

That is correct, the report can be run on different databases with different customers.
Each customer has its own set of data, with ranges in different years at each customer.

Since the information of minimum year (and maximum) to be displayed is contained in the database, I do not wish to enter a parameter to the user.

I'll test it with SQL expressions.
Thank you very much for your assistance