counting all records using a formular or sql

[tonygr]

Hi all,
I have a report which uses the following

{UsageTickets.Centre} = "EE" and
{UsageTickets.DATE} = {?Start Date & End Date} and
{UsageTickets.HEAD} like ["MCARD", "PCARD", "SCARD"] and
not ({ticketdescription.usage_catagory} like ["12", "24", "25", "26", "27", "28", "MISC"]) and
isnull({UsageTickets.MEMBER}) = true

What I would like to do is to display the percentage of
null values against the total "mcard","pcard","scard" records regardless of the above select.
can this be done?
Any Help greatly appreciated
Tony

 

[dgillz]

Are you saying you want the TOTAL count of the nulls, including the nulls that did not make it to the report due to failing on of the other tests in the selction criteria, divided by the TOTAL count of the "MCARD", "PCARD", "SCARD" records, including the "MCARD", "PCARD", "SCARD" records that did not make it to the report due to failing one of the other tests of the selection criteria above?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[tonygr]

Thank you for your prompt reply,
I would like to count all records with "MCARD", "PCARD", "SCARD" null values and not null values as the total then the above selection as a percentage of this total.
Thanks again
tony

 

[dgillz]

Tony,

Please reread my question carefully. My concern is your statement:

[red]"regardless of the above select"[/red]

For example if the field {UsageTickets.Centre} had a value of "XY", it would be excluded from the report based on the selection formula. Are you saying you want it INCLUDED in the counts?

see the ambiguity of "regardless of the above select" ?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[tonygr]

Hi Again,
Sorry about that "Regardless of above statement"
I guess what I am trying to do may not be possible on one report. i.e. i have a statement and it pulls records to display thats ok but I was hoping to have another select criteria held within a sql or formula field which would run independant of the above, but display along side the first select as a percentage of the second select.
I have played around with this formula (basic syntax):

if isnull({UsageTickets.MEMBER}) = true then
formula = "Not Swiped"
else
formula = "Swiped"
end if

and removed from the original select:
isnull({UsageTickets.MEMBER}) = true

this kinda works inserting as a group header gives both totals, but this is not quite what I was after.
I hope I have explained a bit better, I can see what I want in my minds eye just having difficulty explaining.
Thanks again
Tony

 

[dgillz]

Please answer this question with a YES or a NO:

[red]For example if the field {UsageTickets.Centre} had a value of "XY", it would be excluded from the report based on the selection formula. Are you saying you want it INCLUDED in the counts?[/red]

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[tonygr]

sorry for the waffle. no in the first column(or select formula)
yes in the second column
thanks

 

[dgillz]

So if you want to count records that are excluded form the report by the record selection formula. you have 2 choices:

1) Modify the record selection formula to include those records, then conditionally suppress the sanme records. This means these suppressed records will evaluate in any group or grand totalling, so you may need to create running total fields in lieu of inserting totals.

2) Use a subreport to count these records, then pass the subreport value to the main report with a shared variable, then calculate the percentage with yet another formula.

Both are doable, but without more knowledge of your database I cannot say which is best.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[tonygr]

Thanks Again,
I Think it will make more sense to go with your suggestion "1" it will save me time in the long run.
Thanks for your suggestions and support.
Regards
Tony