Problem counting in groups

[BobWman]

We have data like this. (The x to the right is added for clarity - it identifies pending records, see below):

Group Submitted Executed
-------- ---------- ----------
Group 1 200103 200104 x
Group 1 199904 Null
Group 1 200103 200103
Group 1 200103 Null x
Group 2 200103 200105 x
Group 2 200103 200104 x
Group 2 200104 200105
Group 2 200105 200105
Group 2 200105 200107

The report should look like this (for 200103).
This means March of 2001.

Group Submitted Executed Pending
-------- --------- -------- -------
Group 1 3 1 2
Group 2 2 0 2

Pending = Submitted during the month but not executed during the month (Executed is Null or later).

We are really struggling to get the counts for the groups, especially the pending data.

Any help wouls be greatly appreciated.

Thanks

 

[synapsevampire]

If you defined what Submitted Executed Pending mean, this would probably be simple, but since you left it up to my imagination

I'm guessing that you're struggle has to do with conditional counting.

You can create Running Totals and use the evaluate use a formula area to qualify what is counted.

You can also create formulas with the filtering criteria in them and count them.

Here's an example of a formula that you might use:

If not(isnull(executed)) then
1
else
0

Now you can sum this and get a count.

Hopefully I've indirectly resolved for you.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[BobWman]

You are correct - it has to do with conditional counting. The Submitted and Executed fields define months in a YYYYMM format. We are counting the number of submissions and executions for a specific month(by group). If an item is submitted in or before the month, but not executed until after the month(or never executed), then it was pending at the end of the month.

Thanks for your input.

 

[BobWman]

In fighting this problem, we have discovered that Crystal Reports is not interpretting the value of the fields correctly. The report runs from a view, and the view displays Null values when it is run alone. But within Crystal Reports, the value is a string zero - "0".

I have used the Isnull function in SQL Server to send over various Non-Null values, but whatever I do, the report interprets the value as a string zero.

This is the part of the view that now creates '999999' if the JPAExecuted Field is null. But if you browse the field values in Crystal, all you see is '0', no matter what value the view creates. This could be the reason we could not get our previous counts to work correctly.

MonthExecuted = Convert(Varchar(6),
IsNull(Convert(CHAR(4),Year(J.JPAExecuted)) + Right('0'+
Ltrim(Rtrim(Convert(Char(2),Month(J.JPAExecuted)))),2),'999999')
),

Does anyone know what is happening?

Thanks.