Distinct Count Conditions

[CIBS]

Hi

I am trying to create a report based on the job number and the status. The report is set up and is running fine. What I want to do is count all the distinct job numbers but I want it to exclude certain job no. based on the status filed

Also I should mention that the status feild is a sum (@Status) that contains the following calculation

if {Status.Status} = 1
Then "UK"
else
if {Status.Status} = 2
Then "Scotland"
else
if {Status.Status} = 3
Then "Ireland"
else
if {Status.Status} = 4
Then "Walse"
else
if {Status.Status} = 5
Then "Non-UK"

An example of my table as it stands is below

Job No. Status
56781 Walse
56854 Walse
57543 UK
57543 UK
67976 Scotland
78543 Ireland
78543 Ireland
78841 UK
79005 Non-UK
79006 Non-UK

Distinct Count = 8

What I want is to do is a distinct count but do not want to include "Non-UK" Job No.'s so I should get a count of 6

I have used a command to not display the "Non-UK" but it still counts them in the distinct job no.'s

the suppression command I have in place is

Status.Status in ["Non-UK"]

Please Help, As I am going going to be creating a cross-tab summary of this report

Kind Regards

Stephen

 

[CIBS]

Hi

Sorry to be a pain but I have thought that in the summary is there any way of getting it look and the call number and if it has being counted in the previous date then don't count it. As I have being looking at the summary and the Toal is fine, So if you look at an earlier reply from me. the one with the table with Ray Z, in it I did a job across 2 days

 

[lbass]

You are not being clear. If you are using a distinctcount the row total WILL only count the call number once, even if it spans two days. I thought you wanted to count it multiple times so that the sum of the columns would equal the row total.

-LB

 

[CIBS]

Sorry about the confusion when starting this report this was the intention, but since then things have had to change to reflect the work. In the main report the job is fine spanning across 2 or more days but in the summary we want it to count only the first instence and against the date it was first logged against so for example

Ray, Z.

12/11 56778, 67771, 67777
13/11 67777, 78456
14/11 78765, 87900, 88000

So I should get something like below

12/11 = 3
13/11 = 1
14/11 = 3
Total = 7

Currently It looks like this

12/11 = 3
13/11 = 2
14/11 = 3
Total = 7

is there anyway of minipulating the formula to reflect this

 

[lbass]

Create a SQL expression {%mindate} like this (field explorer->SQL expression ->new):

(select min(`Call_Receive_Date`)
from Call A
where A.`Call_Number` = Call.`Call_Number`)

Then use a record selection formula like this:

{Call.Call_Receive_Date} = {%mindate}

Then only the first date per call will be returned to the report.

-LB

 

[CIBS]

Hi

Hope you had a good Xmas

Just tried the above but I get the following error message

Error in compiling SQL Expression:
Query Engine: 'ORA-00911:invalid character'.

I got this when in put the data in to the new created SQL

 

[lbass]

The syntax/punctuation will be specific to your database. If you are using Oracle, try:

(select min("Call_Receive_Date")
from "Call" A
where A."Call_Number" = "Call"."Call_Number")

You can also go to database->show SQL query and observe the punctuation used there and then use that in the SQL expression.

-LB

 

[CIBS]

Hi

I have done and just previous mentioned and showed the SQL query.

this is what it has reported

SELECT DISTINCT "THREAD_EVENT"."START_DATE_TIME"



FROM "ORACLE"."PERSON_ROLE_LINK" "PERSON_ROLE_LINK", "ORACLE"."PERSON" "PERSON", "ORACLE"."THREAD_EVENT" "THREAD_EVENT", "ORACLE"."CALL" "CALL", "ORACLE"."CALL_EVENT" "CALL_EVENT", "ORACLE"."CUSTOMER" "CUSTOMER", "ORACLE"."SYMPTOM" "SYMPTOM", "ORACLE"."THREAD" "THREAD"



WHERE ("PERSON_ROLE_LINK"."PERSON_ID"="PERSON"."PERSON_ID") AND ("PERSON_ROLE_LINK"."PERSON_ROLE_LINKOID"="THREAD_EVENT".
"PERSON_ROLE_LINKOID") AND ("CALL"."CALLOID"="CALL_EVENT"."CALLOID") AND ("CALL"."CUSTOMEROID"="CUSTOMER"."CUSTOMEROID") AND ("CALL"."SYMPTOM_ID"="SYMPTOM"."SYMPTOM_ID") AND ("CALL_EVENT"."CALL_EVENTOID"="THREAD"."CALL_EVENTOID") AND ("THREAD_EVENT"."THREADOID"="THREAD"."THREADOID") AND ("THREAD_EVENT"."START_DATE_TIME">=TO_DATE ('10-12-2007 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND "THREAD_EVENT"."START_DATE_TIME"<TO_DATE ('17-12-2007 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) AND ("PERSON_ROLE_LINK"."AREA_ID">='LONDON' AND "PERSON_ROLE_LINK"."AREA_ID"<='YORK')

I am now gett the following error message

Error in compiling SQL Expression:
Query Engine: 'ORA-00936:missing expression'.

I have tried manipulating IBASS last statement to see if there is any difference but there is non. The manipulating is below.

Select distinct ("CALL"."RECEIVE_DATE_TIME")
from "ORACLE"."CALL" "A"
where ("A"."CALL_NUMBER" = "CALL"."CALL_NUMBER")

I based this on viewing the SQL Query