Counting If Then Formula on ClientID -

[karenkay68]

I am a newbie using Crystal 8.5. Working with Magic DB need to create a formula which counts the number of incidents with a particular ClientID (NAME1234) and if there are more than 5 incidents with the particular client print out those particular incidents. How can I do this?

Thanks,
Karen.

 

[synapsevampire]

Create a parameter for the ClientID, using the same data type of the ClientID.

Group by the ClientID

Insert the Incident ID into the details and select insert->summary->Count

Now go into the Record Selection Formula->Record and place:

{table.ClientID} = {?My ClientID Parameter}

Now go into the Record Selection Formula->Group and place:

count({table.IncidentID},{table.ClientID}) > 5

If you want to enter more than one client ID at a time, change the parameter to Allow Multiple Values and it will allow for numerous to be entered.

Adjust the names fo the fields to match yours.

-k

 

[rosemaryl]

Dear Karenkay68,

I am going to assume you are using MS SQL as your backend as opposed to Oracle. If you are on oracle let me know and I will adjust any syntax, if necessary.

SV's solution will work well for you if you want to select a single client record.

Info:

MagicTSD Reports Configuration utility does not support multiple value parameter fields. You can manually type in multiple values after selecting the initial value from the popup, in this case Client ID, but would then have to manually type each new value separated by a comma. This introduces the possibility of error.

If your point is to create a client report that lists all those clients that have more than a grand-total of 5 calls, you have several options:

Solution I:
Create a calculated field in the Clients view in the DBADMIN tool that counts the incidents per client:

(Select Count([C].[SEQUENCE]) from [_SMDBA_].[_TELMASTE_] C WHERE [Base].[SEQUENCE] = [C].[CLIENT ])

With the above solution, you could bring that forward to the Incident view as a virtual off of the client foreign key or you could simply link clients to Incidents in Crystal and pull the field.

Solution II:

You can create a SQL Expression in Crystal, this solution will require no db downtime as the first solution would to add the calculated field. However, calculated fields are good solutions when you might want to use the field in a business rule at some point in time.

Create a new report using the Incidents View.

Create a SQL Expression in Crystal:

Insert/Field Object/SQL Expression, name it Client Call Count and insert the following:

(Select Count([C].[SEQUENCE]) 
from [_SMDBA_].[_TELMASTE_] C WHERE Incident."Seq.Client" = C.CLIENT )

Save and close.
This will give you total calls by the client as an expression.

Now, go to Report/Edit Select Formula/Record and enter this:

{%Client Call Count} >=5

This will now select all clients with 5 or more incidents.

Now, you can Insert a Group in the report and select Client ID, in the details section put the Incident Information like Incident #, Open Date & Time, Subject ID and so on....

Solution III: (SV's without the parameter prompt for client)
You could also create a report on Incidents by Client ID, insert a count of Incidents, and enter a group select where Count({Incident.Incident #},{Incident.Client ID}) >=5

However, if you have lots of Incidents and lots of Clients, then the report will process very slowly.

Hope that helps, let me know if you need more help.

Regards,


ro


Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.