Counting Groups

[ddnh]

I am trying to figure out how to get a total count of groups in my report header. I have two groups (group1, group2). I want the count of group2 in my header. The only way I have managed to even get the count of group2 is by creating a running total. I can place this in the report footer and it gives me the accurate count. Is there a way to get this number in the report header?

Group1
Group2
detail
detail
Group1
Group2
detail
Group2
detail
detail

In this case, my total would be 3 (there are 3 instances of group2. How do I get this count in my report header?

I'm using Crystal 9. I am using record and group selection (If I use only record selection, I can't get my date parameters correct)

Any suggestions?

 

[synapsevampire]

Try a formula containing:

distinctcount({table.group2field})

or

count({table.group2field})

depending upon which one you want.

-k

 

[synapsevampire]

This may be more complex if you have repeating group2s within the different group1s, and you want all of them.

Let's hear if that's the case.

-k

 

[ddnh]

I don't have repeating group2s. They are all unique.

I already had that formula in the report header, forgot to mention it. When I count the field itself, it counts the number of records. The problem is, the number of records does not equal the numger of group2s. I'll try to explain further. Right now there are 345 records {table.group2field}. When I use distinctcount({table.group2field}) in the report header I get 345. The problem is that on the report itself, only 15 groups show up (due to the date parameter). I want the 15 in the report header.

If I move my date parameter from the group selection formula to the record selection formula, the count ends up correct (it counts the numbers of records)....but it ignores the date parameters entered. The only way I get the correct data on the report is to put the date parameter in the group selection formula. Since this happens....345 records are pulled, even though I only want 15...but only 15 show on the report, which is correct. So maybe my problem is with data retrieval rather than counting?

 

[synapsevampire]

Perhaps you can post examples of what you're using for the date parameter (or anything that seems to be a problem), and provide some example data and expected output.

As you're discovering, text descriptions aren't very clear, and since you're familiar with the data, you won't look at it, nor describe it very objectively.

-k.

 

[ddnh]

OK, I'll put tons of info.

Record selection formula:
Left({table.Description},4) = "Inv-" and
{table.ProductClass}= "AIDS" or {table.ProductClass} = "ART" or {table.ProductClass} = "CUR"

Group selection formula:
{table.OrderDate}>={?Beginning Order Date} and {table.OrderDate} <={?Ending Order Date}

Parameters:
{?Beginning Order Date}
{?Ending Order Date}

Group1: table.ProductClass
Group2: table.ItemID

Group Header 1: Group1
Group Header 2: Group2
Detail: Item Name, Order Date

There are a possible 345 records in the databse that match the record selection...345 Item IDs. If I enter 2/2/04 to 2/4/04 as the parameters, only 15 records should qualify. the way I currently have my selction criteria, the data on the report is perfect (15 Item IDs), but it's obvious that the report is pulling in 345, but only showing 15. I know this because of the number of Item IDs that exist in the group tree. My only issue is when I try to count the Group2s (the Item IDs). Using the running total in the report footer, I get 15. This number needs to be in the report header. It seems to me that I could use the distinctcount({table.ItemID}) formula if my report were pulling in the correct data to begin with, rather than just showing the correct data. That's what I meant by the data retrieval question in my last post.

This is what I want to see:

Report Header:
Item List Summary (text object)

15 records generated where order date is between 2/2/04 and 2/4/04 (15 is from: DistinctCount of table.ItemID, dates are from parameters)

AIDES (group header 1)
12345 (group header 2)
Audio Making 2/3/04 (detail)
ART (group header 1)
9056 (group header 2)
Tree By Lake 2/3/04 (detail)
9079 (group header 2)
Big Sky 2/4/04 (detail)

etc etc where there are 15 instances of group header 2 (15 different group2s)

hope that's enough detail...

 

[synapsevampire]

Example data (not given) and Expected output would have sufficed, but thanks.

Why are the dates in the group selection? An odd sort of set up for this report.

Move the date criteria to the record selection, and make sure that you get it to pass to the database by checking the Database->Show SQL Query

Sometimes they won't pass because the date type is a datetime and the parm is a date type.

And if something fails, don't say "it doesn't work", state what erros are produced, and what the resultant Database->Show SQL Query is.

Try:

Record selection formula:
)
{table.OrderDate}>={?Beginning Order Date}
and
{table.OrderDate} <={?Ending Order Date}
)
and
(
Left({table.Description},4) = "Inv-"
)
and
(
{table.ProductClass} in ["AIDS", "ART", "CUR"]
)

The only thing that might not pass to the database would be the LEFT function. In that case you can create a SQL Expression to substring the first 4 characters and use that in the record selection formula to get it to pass.

-k

 

[ddnh]

Now I'm pretty confused, but encouraged nonetheless.

Earlier in the day I tried putting the dates in the record selection, as I explained in earlier posts. I just didn't explain how. I originally had my record selection like this:

Left({table.Description},4) = "Inv-" and
{table.ProductClass}= "AIDS" or {table.ProductClass} = "ART" or {table.ProductClass} = "CUR" and
{table.OrderDate}>={?Beginning Order Date} and {table.OrderDate} <={?Ending Order Date}

when I did this, I got data for all dates...not just the dates entered in the parameters. That's why I moved the date parameter to the group selection (because that's the only way I got the data on the report within the specified date range). Now when I tried it your way, the date parameters work, but the "CUR" data is not included. If I remove the LEFT...selection (that you thought might not pass) everything works perfectly, including getting the count of 15 in the header.

So....now I'm just curious why your way worked and mine didn't. Does the order in the record selection matter?

Also, I'm not sure what you mean by the SQL substring. I don't have a lot of SQL experience. If it's easy, can you help me out? If it's complicated, I'll go ahead and research it or ask a co-worker.

THANK YOU VERY MUCH for everything so far!!









If I enter in the record selection exactly as you wrote I get the following error:
"The remaining text does not appear to be part of the formula.

 

[ddnh]

please ignore the "CUR" and Left questions. I realized that those are issues with the data and have nothing to do with the selection criteria. Also please ignore the error message at the bottom. I started to write that early on...but realized the error was due to some missing ). I just forgot to remove it from the post.

I would still love to hear about the order in the selection criteria. Is it necessary to put the date first?

 

[lbass]

Your original problem is that you did not have parentheses around your "or" statements. Without parentheses, CR will treat the following something like:

(Left({table.Description},4) = "Inv-" and
{table.ProductClass}= "AIDS") or

({table.ProductClass} = "ART") or

{table.ProductClass} = "CUR" and
{table.OrderDate}>={?Beginning Order Date} and {table.OrderDate} <={?Ending Order Date}

...where a record will be included if it meets a criterion in any one of the three "sections" above. So, if you meant that each record has to be one of "AIDS","ART", "CUR" and meet all other criteria, then SV's formula should work. Otherwise, you would need to rewrite your formula to:

Left({table.Description},4) = "Inv-" and
(
{table.ProductClass}= "AIDS" or
{table.ProductClass} = "ART" or
{table.ProductClass} = "CUR"
)
and
{table.OrderDate} >= {?Beginning Order Date} and {table.OrderDate} <= {?Ending Order Date}

-LB

 

[ddnh]

Thanks. It all makes sense now.