Difference between two summaries in a Crosstab

[tjw2006]

Hi, I've got a crosstab which is showing orders per week and summarizing by month, I want to be able to compare each month's summary to the previous month's summary to do some conditional to do some conditional highlight i.e. if one month's summary is greater than the previous month's, then it is highlighted in green.
What I can't figure out is how to reference a previous summary in a formula. The data is laid out as below:

Jan Feb
Week: 1 2 3 4 Month Total Week: 1 2 3 4 Month Total
Company 1 10 20 60 40 130 20 10 30 50 110
Company 2 40 25 55 30 150 25 25 50 30 130

Thanks.

 

[lbass]

I'm sorry, but I have tested this and it works, so please try once more. It compares the January total with the February total in the same row.

I think you are misunderstanding how the prev variable is working. Take a look at the formula. The prev variable is set to the curr variable BEFORE the curr variables is set to the current value. That means that prev will hold the value from the previous summary in the row, thus allowing the comparison. First you have to get the formulas in the correct places so that the cnt variable is working correctly. Once it is, and you use the display string feature to see its value, you should see:

Jan Feb
Week: 1 2 3 4 Month Total Week: 1 2 3 4 Month
Total (this row suppressed)
Company 1 10 20 60 40 [red]1[/red] 20 10 30 50 [red]1[/red]
Company 2 40 25 55 30 [red]2[/red] 25 25 50 30 [red]2[/red]
Company 3 10 20 60 40 [red]3[/red] 20 10 30 50 [red]3[/red]
Company 4 40 25 55 30 [red]4[/red] 25 25 50 30 [red]4[/red]
//etc.

Please identify the exact cells where you placing the formulas and show your current formulas again, if you would like help troubleshooting this.

-LB

 

[lbass]

I have a attached the mockup of the report I used to develop this solution, so you can explore it and compare it to how you set up yours.

-LB

 

[tjw2006]

Hi, thanks for the example. I'm doing exactly the same now as you have done in your example report and it's still not behaving in the same way - the major problem I think is the fact that the cnt variable is not resetting to zero, even though I have the reset statement in the column totals field as you have it. I can't see why this is not resetting at the moment.

 

[tjw2006]

I didn't think this would make a difference to a crosstab, but I have got the crosstab in a group header, but I can't think why that would stop the counter from resetting for a column total on 'whileprintingrecords'.

 

[lbass]

What field is being grouped on?

Does the crosstab extend beyond one page vertically? Or cross vertical pages? This could potentially disrupt the count.

-LB

 

[tjw2006]

Yes, the crosstab is running over several pages at the moment.

 

[tjw2006]

At the moment, the counter is resetting on each change of page, so if I could get it so that the counter resets at the start of each column, that would be a lot easier to work with.

 

[lbass]

I can't think of a way to prevent the crosstab cnt variable from resetting on a new page. Why is this extending beyond a page? I thought you had only about 15 or so instances of the row field. You could potentially reduce the crosstab size by unchecking "show cell margins" so that it stay on one page. Or format the section to start on new page, so that it never crosses pages.

You could also hardcode the comparison, instead of using the cnt variable, but that would require that you know the group instances (that they are not dynamic). If this is a possibility, let me know.

-LB

 

[tjw2006]

Ok, almost got it! I messed about with the crosstab position and page margins so that the crosstab only displays 22 rows per page and basically set the cnt variable back to 1 when it reached 23, this effectively resets the cnt variable to 1 at the top of each total column. Now, this is working fine, up until the change of group, because the last page does not necessarily show 22 rows it depends on how many records are in each group, so is there a way to also reset the cnt variable to 1 on a change of group?

Thanks.

 

[tjw2006]

Ok, I was slightly mistaken in my last post - what is throwing out the cnt variable is the fact that crosstab on the last page of each group may not have the right amount of rows i.e. 23, which then throws out the calculation. All I need is a way of referencing how many rows there are in a crosstab on a page - is this possible?

 

[lbass]

I don't understand what you are doing. What group is the crosstab placed in (what field is being grouped on)? When you say you are limiting the number of rows shown in the crosstab--do you mean that there ARE only 22 row instances? If all row instances are on one page, then my original reset suggestion is the way to go.

-LB

 

[tjw2006]

Managed to get it sorted out finally!! The situation was that I had a few companies trading to many companies, the few companies were used for the groups and the many were used as the rows in the crosstab, so the crosstab was running across multiple pages. I managed to limit the number of rows in the crosstab on each page to 22, so when the cnt variable reached 23, it got reset to 1. Unfortunately, this was a problem when you got to the end of a group, where the count of rows on the last page were often less than 22, so what I had to do is add some filler rows via the backend SQL, to bring the count of rows on each last page to 22, this ensured that the comparison formula between summaries remained consistent.

Many, many thanks again lbass for all your help and suggestions - I couldn't have done it without you.

Cheers.