grouping by formula in footer

[seabubble]

Hi, I have a report that shows region, distinct count, a count of records that match a value and a percentage (this is the formula)

I have grouped the region first as I want to sort the records.

Then I count the number of items at that region. (distinct count)

Then I count the problems raised this week. I have a formula which says the following items /problems *100 to get the percentage. This percentage result is in the group footer. What I want is to sort this percentage starting with the highest. but the group wizard does not show my formula. Whats hapening!
Thanks in advance.

 

[Madawc]

If you use running totals, they are working out at the same time as the Crystal report formats the line. This means you can show them in the group footer but not the group header, where they will be zero if you are resetting them for each group.

If you use summary totals, they are taken directly from the data and can be shown in the header. They can also be used to sort groups, or to suppress them.

If you want to get the summary totals for a database field, right click and choose Insert and Summary

It helps to give your Crystal version, since newer versions have extra options, and some extra problems. I use Crystal 8.5

Madawc Williams (East Anglia, UK)

 

[lbass]

Try creating the percentage using a SQL expression, so that it will be available for topN sorting. Please see thread767-879287 or thread767-874782 for examples.

-LB

 

[seabubble]

Thanks Madawc and lbass for getting back. I have gone over my report and think I have done too many formulas to get the percent. I am sure that it can be done in a more easier way.
This is what I have .

two tables asset and problem
I select all the asset records where the asset is routers. then I group them by region. I then list all problems from the problem table where asset and problem item is = router.
I then do a count
if ({@Create_Date} >= {?Start time} and {@Revised closed time} <= {?End Time}) and
{problem.Type} like "Router" then 1 else 0

This gives me a count of all records in the given time from the problem table. Now I have a distinct count of items that match router from the asset table and in the detail section a 1 next to records that match the above formula. I then do a running total of 1's for each group in the group footer.
Say
items problems
10 2

I want to get the average percentage of problems per router. So i made a fomula that say's 2/10*100 the problem is that because the 2 is a running total I can not get it in the group list to sort by. Am I getting confused ofer a simple sum? Have I confused you more ! ! sorry

 

[lbass]

If you don't have duplicate records due to the join, then you can right click on your formula (the one that returns a 1 or 0) and insert a summary (SUM, not count) in order to get the result you want.

However, this formula is not what is preventing you from being able to do the group sort. The percentage formula will not be available for group sorting regardless. That is why I suggested the SQL expression solution, which I think is the only way you will get the sort you want without using a view.

-LB

 

[seabubble]

OK, thanks I will have a go! Might take sometime tho.

 

[seabubble]

Hi again. LB thanks for help earlier. I am now looking at the thread Thread767-874782 above. i have put the following in the details section on the report.

(select distinct count("AST_Asset"."Asset_ID_") from AST_Asset where "AST_Asset"."Type" = 'ROUTER')

and it shows the same distinct count number in all the details, as I expected. What I would like is it to show the distinct count of assets for each region. I can then work out how to add another sql expression to get all the records that were opened for that region.
Do I put something like this in the expression

and "AST_Asset"."Region" = [look at the group name on the report] ) how do I do this?

 

[lbass]

Try copying the following into your SQL Expression formula area:

(select distinct count("AKA"."Asset_ID_") from AST_Asset AKA where "AKA"."Region" = "AST_Asset"."Region" and
"AKA"."Type" = 'ROUTER')

If this works with no errors, you should still test the calculations to see if they are what you would expect.

-LB

 

[seabubble]

Hi LB, Thanks again for your help. Just to let you know I have bought two crystal manuals so my questions should slow down a lot!
I am getting confused with the AKA stuff I copy your line into the sql expression and get...

the column prefix 'AST_Asset does not match with a table name or alias name used in the query.

What one of the AKA's should type over with my table name

Steve

 

[lbass]

Have you verified that when you double click on a field in the list in order to add a field in the SQL expressions editor that it looks like: "AST_Asset"."Region"? Is "AST_Asset" your table name?

The expression syntax that I gave you works when I test it, although the punctuation that works for me is:

(select distinct count(AKA.`Asset_ID`) from AST_Asset AKA where AKA.`Region` = AST_Asset.`Region` and
AKA.`Type` = `Router`)

-LB

 

[seabubble]

yep the table is called AST_Asset.

When I paste your line in I get syntax errors until I change the quotes (I'm using CR10), I have tried single and double quotes. When I do not get any more syntax errors I then get the message about the column prefix AST_Asset does not match with a table name or alias name used in the query. The table name is deff AST_Asset . This is what my code looks like now.
(select distinct count("AKA"."Asset_ID") from AST_Asset AKA where "AKA"."Region" = AST_Asset."Region" and
"AKA"."Type" = 'Router')


If I change it to this
(select distinct count("AST_Asset"."Asset_ID_") from AST_Asset AKA where AKA."Region" = AST_Asset."Region" and
AKA."Type" = 'Router')

I get Column AST_Asset.Region is invalid in the select list because it is not contained in an aggregate function and there is no group by clause. If you are getting fed up with this one prehaps someone else will pick it up.

Cheers Steve

 

[lbass]

You have a group on {AST_Asset.Region} in your report, right? And you are entering this formula in the SQL Expressions editor, not the "Show SQL Query" area, right? What version of CR are you using?

I'm not sure I can offer anything more--as I said earlier, syntax for SQL Expressions vary, and I just don't know why this is not working for you. Perhaps someone else will jump in and help.

-LB

 

[seabubble]

Thanks LB for all your help sorry to be a pain. The answer is right to all your questions above and it's V10. I will continue to try with this problem.
Cheers again.

Steve