|
opensourcederry
Starting Member
12 Posts |
 Posted - 2011-11-23 : 11:47:33
|
| Hi all,I have a SSRS telecoms report which groups by date, Businessarea (area) , client (MainClient) and telephone queue (subq)The report shows stats for a telephone system and essentially shows telecoms statistics such as calls offered and calls answered etcSo the simplified data set arriving at ssrs is like thisDate Area Client Subq CallsIn01-01-2011 Admin Acme ComplaintsLine 7201-01-2011 Admin Acme SalesLine 3401-01-2011 Admin WidgetLtd WidgetLtd 3201-01-2011 Admin WidgetLtd WidgetLtd 63A note about the above. You'll notice Acme has 2 individiual subq's - this is due to that client wanting to see the breakdown of their individual calls q's. WidgetLtd however are not interested in this, they just want to see a total per day for Widgetinc so the subq's are the same. At the Client grouping level I also have a calculated expression which returns a 1 or a 0 if a condition is met. Basically I do a sum, compare it against a value and return a 1 or 0. The reason I return a 1 or 0 is that I want to keep a running total. The expression works fine and it is below. =IIF(IIF((Fields!ClientName.Value = "SomeClient" AND Fields!area.Value = "CCM"),(IIF(SUM(Fields!CallsAnswered.Value) > 0 OR SUM(Fields!CallsOffered.Value) > 0,((SUM(Fields!CallsAnswered.Value)+SUM(Fields!DefaultInIvr.Value))/(SUM(Fields!CallsOffered.Value)-SUM(Fields!DisconInIVR.Value))*100),0)),(IIF(SUM(Fields!CallsAnswered.Value)+SUM(Fields!CallsAbd.Value)>0,(((Sum(Fields!CallsAnswered.Value)+Sum(Fields!CallsAbd.Value))-(SUM(Fields!CallsAnsAftThres.Value)+SUM(Fields!CallsAbdAftThres.Value))) / (SUM(Fields!CallsAnswered.Value)+SUM(Fields!CallsAbdAftThres.Value))*100),0)))>=80,0,1)In business terms the expression is a comparison of grade of service, if it is met then we return a 0, if no then a 1, this 1 means a failure.Also, some clients have different calculations to work out their grade of service so the expression above also has logic so say then if this client then do this calculation or else this calculation and regardless if it's less than 80 then return 1, else 0. Now, as I said the expression works fine at the client level.However, I am experiencing problems when trying to keep a running total over time of the 1's returned by the expression. I need to be able to say as the moth goes on that Client X has failed it's grade of service level x amount of times this month. So if at client level we have a failure on the 1st of a month then the running total will be 1, if we also fail on 2nd month then it will show 2 and so on.I place the expression inside the runningtotal function and at first it appears to work ok, the expression is below, I have also scoped it to work at client level.=RunningValue((IIF(IIF((Fields!ClientName.Value = "SomeClient" AND Fields!area.Value = "CCM"),(IIF(SUM(Fields!CallsAnswered.Value) > 0 OR SUM(Fields!CallsOffered.Value) > 0,((SUM(Fields!CallsAnswered.Value,"MainClient")+SUM(Fields!DefaultInIvr.Value,"MainClient"))/(SUM(Fields!CallsOffered.Value,"MainClient")-SUM(Fields!DisconInIVR.Value,"MainClient"))*100),0)),(IIF(SUM(Fields!CallsAnswered.Value)+SUM(Fields!CallsAbd.Value)>0,(((Sum(Fields!CallsAnswered.Value,"MainClient")+Sum(Fields!CallsAbd.Value,"MainClient"))-(SUM(Fields!CallsAnsAftThres.Value,"MainClient")+SUM(Fields!CallsAbdAftThres.Value,"MainClient"))) / (SUM(Fields!CallsAnswered.Value,"MainClient")+SUM(Fields!CallsAbdAftThres.Value,"MainClient"))*100),0)))>=80,0,1)),Sum,"MainClient")The problem I have is this. The expression seems to count the subqs beneath it. Going by the data set example above and looking at the client Acme, you can see that this has 2 subq's but instead of showing 1 at the client grouping level to indicate a service fail, it shows 2 as it is counting the 2 subq's beneath it. Anyone any ideas as to how I can isolate this expression so as to work at the client level only and ignore the subq's beneath it? The expression itself works fine at client level, it's when I put it within a running total that the expression seems to count the sub's beneath it.Thanks in advance.osd |
|
SSRS RunningValue issue