Hi,

I am newbie to SSRS Report Builder 3.0 so please be patient with me. I am trying to create a report to query Remedy Ticketing System and it uses Oracle Database installed on a Unix OS...My end objective is to do some calculation on the ticket metrics (i.e. percentage of tickets closed within SLA, etc) however i am stuck from the very beginning where i am not able to make my expression work...

To begin with. my query statement...

Select Distinct TICKET_NUMBER, Assigned_Group, Category, REGION, TICKET_STATUS,EXCEEDED_SLA, TO_CHAR(date '1970-01-01' + (TICKET_CREATION_DATE/60/60/24), 'DD/MON/YYYY HH:MI:SS AM')TICKET_CREATION_DATE
from ARADMIN.Request
where (Assigned_Group like 'HOTS' OR Assigned_Group like 'PAID' OR Category like 'HVD%') and TICKET_CREATION_DATE > (TO_DATE('31/DEC/2014 11:59:59 PM','DD/MON/YYYY HH:MI:SS PM', 'NLS_DATE_LANGUAGE=AMERICAN') - DATE '1970-01-01')*60*60*24
order by ticket_creation_date

the data type of the oracle table:

Here is the Query Result of the DataSet:

Basically Ticket_Status has values from 0 to 4 although from the screenshot above you only see 2 and 4...

Ticket_Status 0 - Means Open Ticket

Ticket_Status 1 - Means Work In Progress Ticket

Ticket_Status 2 - Means Pending Ticket

Ticket_Status 3 - Means Awaiting Closure Ticket

Ticket_Status 4 - Means Closed Ticket

I created a chart column using the expression below but it did not work...what i mean by it does not work is that the chart column output shows all Ticket_Status total count including 0, 1, 2, 3, & 4 (i.e. total count of tickets are 206)...using the expression i was only expecting the total count for Ticket_Status 4  (i.e. 180 tickets) but i guess i am doing something wrong with the expression syntax...

=Count(Fields!TICKET_STATUS.Value=4) 

i also used the expression below but it did not work...

=Count(Fields!TICKET_STATUS.Value="4")

i converted the Ticket_Status to CHAR on the dataset using the TO_CHAR(Ticket_Status) AS Ticket_Status then i tried the expression below but did not work...

=Count(Fields!TICKET_STATUS.Value="4")

i tried again the expression below but did not work'

=Count(Fields!TICKET_STATUS.Value=4) 

i cannot proceed to the arithmetic calculation if i cannot filter my dataset with the correct value...again i am a newbie with SSRS & report builder and i would appreciate it very much if you can guide/help me :-)

Hi Danny,

Per my understanding that you can't get the expect result by using the expression "=Count(Fields!TICKET_STATUS.Value=4) " to count the the TICKET_STATUS which value is 4, the result will returns the count of all the TICKET_STATUS values(206) but not 180, right?

I have tested on my local environment and can reproduce the issue, the issue caused by you are using the count() function in the incorrect way, please modify the expression as below and have a test:
=COUNT(IIF(Fields!TICKET_STATUS.Value=4 ,1,Nothing))
or
=SUM(IIF(Fields!TICKET_STATUS=4,1,0))

If you still have any problem, please feel free to ask.

Regards,
Vicky Liu

Hi Vicky,

sorry for the late reply i was very busy at work with other task (not related to SSRS/Report Builder). I confirm that your suggestion =COUNT(IIF(Fields!TICKET_STATUS.Value=4 ,1,Nothing))  works and thank you very much i really appreciate it :-)

Now my next step is to calculate the percentage and i tried the below expressions however my syntax is not correct as it is giving me error. Basically I would like the expression below to calculate the percentage of Request Ticket Closed Within SLA. This Time to calculate the percentage, i only use one dataset (Request_Tickets Dataset) as i dont want to create a lot of Datasets.

Ticket_Status=4 Means Request Ticket is Closed

Exceeded_SLA<> 1 Means the Ticket did not Breached SLA. 

=CountDistinct(IIf(Fields!TICKET_STATUS.Value, "Request_Tickets")=4 And (Fields!EXCEEDED_SLA.Value, "Request_Tickets")<>1,(Fields!TICKET_NUMBER.Value, "Request_Tickets"),Nothing))/CountDistinct(Fields!TICKET_NUMBER.Value, "Request_Tickets")

I used CountDistinct to total the number of Request Tickets that are Closed Within SLA then I divide it with the Total Number of REquest Tickets regardless of the status of the ticket (i.e Closed Within SLA, Closed Breached SLA, etc).

Below is the screenshot of the errror.

Again i am hoping if you can guide/help me. Thanks in Advance.