Need Running Total In Group Header

[Former Member]

I have a report where I need to perform a distinct count of records with a specified status level for multiple group levels. I am using a Running Total to perform a conditional count. The problem is that in order to display the multiple group levels in the proper order, I have to post the values in the group headers, where the running total hasn't yet calculated any value.

I tried creating a formula for the detail record that applies the condition, and then performing a distinct count on that formula, but it is counting 0 (if the condition fails) as a value, giving me a count 1 too big. I would just subtract 1 from the distinct count, but I can't count on there not actually being a valid zero value.

Fuskie

Who wonders if he is overthinking this...

[former_member260594 · Accepted solution]

Fuskie,

Can you create a formula in the details section that outputs 0 or 1 depending on the condition and then use a summary on that formula?

Otherwise to display a Running Total in the Group Header you would have to insert a GH section above the existing and put a subreport in there that does the calculation and pass the value back via shared variable.

[Former Member · Accepted solution]

I thought about the subreport idea, but I would have to have a subreport for each group level header which would just about kill report performance. I also thought about regular group summaries. The problem is that if I set a formula to 1 if the summary condition is met, and then sum on that formula, I get a count instead of a distinct count. If I put in an additional conditional such as FieldID<>next(FieldID), then I can't use it in a summary function.

Fuskie

Who wonders if there is a way to get DistinctCount to ignore zero values...

[former_member260594 · Accepted solution]

Fuskie,

Rather than use the next function what if you put the values into an array if they are not already in it. If the value is not in the array then output 1 else 0. You should be able to do this whilereadingrecords and then summarize the result

[Former Member · Accepted solution]

I do not know how many elements there might be in the array. There could be any number of values for groups 1 through 4.

Fuskie

Who has also looked at conditional summary totals but they are only valid for dates, time and boolean...