Running total on field within a crosstab

[Former Member]

I would like to add up values for a column within a cross-tab that is not a summarized field but just a regular row (see screenshot below). Can I apply a running total somehow?

[abhilash_kumar · Accepted solution]

Hi Aaron,

If it's a numeric field that has been added to the crosstab as a Row, then you can simply Insert a Summary on that field and place the summary below the Crosstab.

Go to Insert Summary > Choose the field that is used as the 2nd Row in the Crosstab > Choose Sum and the Summary Operation. Move the Summary to a section below the Crosstab.

OR

While in the Preview Mode, right-click the 2nd row of the Crosstab > Select Format Field > Common tab > click on the Formula button beside "Display String" and use this code:

numbervar rt;

rt := rt + CurrentFieldValue;

totext(CurrentFieldValue,1,"")

Create another formula with the code below and place the field below the Crosstab:

WhilePrintingRecords;

numbervar rt;

Hope this helps!

-Abhilash

[Former Member · Accepted solution]

Abhilash,

Using the second method you provided, it is close, but still not working as expected. The first page of the report seems to total fine. However, subsequent pages seem to keep adding up the total from previous Groups, even though the Group the crosstab is in has changed. Do I need to reset the variable at some point?  If so, I'm not sure where or how to reset it correctly.

Aaron

[abhilash_kumar · Accepted solution]

What do you mean by subsequent pages? Is the Crosstab place on the Group Header?

When do you want to reset the variable?

-Abhilash

[Former Member · Accepted solution]

Yes, the crosstab is within a Group Header. I seemed to have gotten this to work a bit better, but still not working. What I've done is set a formula that resets the variable to 0 and placed it in the Group Footer:

whileprintingrecords;

numbervar rt;

rt := 0

This seems to work fine when the crosstab data is located on a single page. However, when the crosstab spills over onto another page, the variable shows 0. So I'm confused as to why a page change would reset it when I expected it to occur only when the Group changed.

Aaron

[abhilash_kumar · Accepted solution]

Could you try placing it to the group header instead?

If that doesnt help, would you be able to share the report with saved data?

Change the reports extension to .txt before attaching.

-Abhilash

[Former Member · Accepted solution]

Placing the reset formula in the group header produces the same result. Just to clarify, when I said it was returning 0 on the spilled over page that was because the values were 0. If the spilled over contents were to add up to 57 that is what the value would be returned. So, I'm still confused as to why a page change would reset it when the reset formula is in the group footer (or header)?

[Former Member · Accepted solution]

To further clarify my problem if there is any confusion. The below picture shows the field in question. Above the arrow is Page 1 and below is the spilled over contents on Page 2 of the same Group data. The total should be 2756, but it shows 163 which is only the values on the spilled over page.

[abhilash_kumar · Accepted solution]

Hi Aaron,

Yes, I have a sample here and it's doing the same thing to me. I have no clue why that should happen though.

I modified the same formula to include the GridRow functions and that does not seem to work too - which is very strange.

I then added another row in the crosstab that appears just before the "Total" column and used the GridRow functions. This works great!

The only issue I'm having at the moment is, when I add 3 rows to the crosstab, the row disappears for no reason (I've seen this happen even with simple structures and I haven't been able to find out why). Sometimes the row might appear on another machine but there's no guarantee about that too.

So, I have something that works, however only with a crosstab that has two rows. I see from the screenshot that there are 3 rows in your crosstab. Would you be able to share your report with saved data so that I can play with it?

Another thing that came to my mind is, since the crosstab itself is on a group, would you be able to create a summary on the field used as the row in the crosstab and place it on the Group Footer?

-Abhilash

[JWiseman · Accepted solution]

hey Aaron & Abhilash,

if a cross-tab spans more than one page then any variables that you write in dialogues such Display String will be reset.

the nature of the writing of variables in a row (vs. a summary cell) are a bit different as well as they will write from bottom to top vs. top to bottom which is what you'd expect. so in that particular case you'll see results that you're not expecting for things like manual running totals.

and as Abhilash mentioned a summary should give you the number you're looking for anyway.

jamie

[abhilash_kumar · Accepted solution]

Hi Jamie,

Yes, sometime ago when I was playing around with the calculated members I realized that it writes values the other way round.

I still feel the Calculated Member and the Grid Value functions have not been implemented fully yet. I have been through many annoying times when the cell wouldn't do/show what you want it to do although you know the grid function you used is correct (I use the tool tip text formula to verify!).

There are times when you add a Calculated Member and it does nothing!

Jamie, could you give this a shot on your machine if you have some time (apparently I don't have CR on my machine at work )

- Create a crosstab with 3 rows, 1 col and a summary

- Suppress the 'Subtotals' and move the Grand Totals to the bottom

- Right-click the 1st field you used in the Row and insert a 'Child'

- I played around with the insertion formulas for sometime and it didn't insert the child for me

- When I remove the 3rd field from the row of the crosstab, the child still doesn't insert, however works fine when I try on a new report altogether!

-Abhilash

[Former Member · Accepted solution]

Abhilash and Jamie,

Thanks for checking into this for me and spending the time. I first tried creating a summary on the field. However, this does not work. The problem is that those values I'm trying to total are actually DISTINCT values in the recordset I'm returning. So while the crosstab shows only 1 value, in reality there are many records of that same value associated with the actual crosstab data I'm summing there (i.e. the dates you see in the first screenshot above). So when I do a summary on it the total starts to double or triple or more depending on how many dates are in the crosstab. I hope I'm making my self clear.

So, since the summary doesn't work and the running total in the crosstab doesn't work it looks like I'm out of options at this point. If you have any other ideas please let me know. Otherwise I'll just mark this reply as the answer (which is there is no answer).

Aaron

[abhilash_kumar · Accepted solution]

Before you close the thread, would you be able to share the report with saved data please?

-Abhilash

[JWiseman · Accepted solution]

hey Aaron,

what i like to do in these cases (where you're trying to get distinct counts on a granular field) is to first create a formula that appends all of the values together from highest level to lowest level. after that, you create a distinct count on that formula.

for example, a formula may look like:

{countryfield} + {cityfield} + {customername} + totext({datefield},'yyyyMMddHHmmss'} + totext({orderid},0,"")

now if you do a distinct count on that formula, you get a result at the granularity needed.

i hope this helps,

jamie

[Former Member · Accepted solution]

Abhilash,

I'm reluctant to give the full report as it contains some data that my company wouldn't necessarily want out there. So I'd have to heavily modify the report which I don't have time to do right now.

However, it sounds like you recreated my problem in your Mar 13, 2013 6:53 AM post, so I'm not sure what else you'd get out of it at this point.

Thanks again for all your help on this.

Aaron

[abhilash_kumar · Accepted solution]

Hi Aaron,

Please find attached a sample report. After you download the file, extract and change its extension to .rpt.

There are blank cells and unfortunately, there is no way you can get rid of them. The running sum, however shows up fine at the bottom just above the Grand Total.

Let me know if this works for you. Here's what I have done anyway:

1) Make sure you have the Subtotals showing on the report

2) While in the Preview Mode Right-click the blank space at the top-left corner of the Crosstab and select Advanced Calculations > Calculated Member

3) While in this windows click New. From the Group Level drop-down, select the field that is the 2nd row in the crosstab and select the Insertion Evaluation as "Before"

4) Next, click on the Edit Insertion formula and use this code:

GetRowGroupIndexOf(CurrentRowIndex) = 1

5) You should see that a blank row with its heading as 0 (zero) should appear after the 2nd field in the crosstab

6) Right-click this zero value and select Calculated Member > Edit Header formula and use this code:

local numbervar rt;

local numbervar i;

for i := 0 to CurrentRowIndex-1 step 2 do

(

    rt := rt + GridLabelAt("database_field", i); //replace database_field with the field used in the 2nd row of the crosstab. The quotes have to be there and the curly braces around the field has to be removed too. E.g: GridLabelAt("Order.Order ID", i)

);

totext(rt);

7) Right-click each of the Subtotals > Format Field > Suppress

That's how far I've been with this project!

Let me know if this helps.

-Abhilash

Crosstab_RunningSum.txt.zip

[Former Member · Accepted solution]

Abhilash,

I wasn't able to successfully use a Calculated Member to get that to work. So, I'm just going to mark this topic as being done with for now. I will come up with another report to meet my users' request.

Thanks again. I really appreciate your help on this.

Aaron

[JWiseman · Accepted solution]

hey Abhilash, i hope you're doing well.

yes, i've seen this behaviour as well in crosstabs. taking out or adding a new field in a crosstab can do odd things too, especially if the crosstab has seen a lot of edits. i end up just creating a new crosstab as well.

cheers,

jamie

[abhilash_kumar]

Hi Aaron,

Where would you like to show the Sum?

-Abhilash

[Former Member]

Abhilash,

The resulting sum would be in a new section below the crosstab. It doesn't need to be directly in the crosstab itself, like the other totals are.

Thanks,

Aaron