none
Help with my expression in report builder RRS feed

  • Question

  • Hi guys!

    Wonder I you could help, I new to expressions and trying to write a multiple condition expression. At the moment I have setup a percentage calculation for the summary of the data in the report (which works). It basically looks at the field 'sec' which is the total seconds for a call, the expression gives a summary of the percentage of calls that were under 60 seconds.

    =formatpercent(SUM(IIf(Fields!sec.Value  <"60",1,0))/count(Fields!sec.Value),1)

    Now I just want to add an extra parameter of the report for the field 'Origin_of_Call', so it does exactly what did before but only for records where the value of 'Origin_of_Call' = "Landline". I've tried a few different combination but the computer is saying no.

    I'm sure this is probably straight forward but I'm a newbie.

    Any help would be greatly appreciated.

    Andy

    Wednesday, June 24, 2020 7:17 AM

Answers

  • So, to get percentage of calls less than 60 sec. we 

    SUM(IIf ( ( Fields!sec.Value  <60) AND ( Fields!Origin_of_Call = "Landline" ), 1, 0) /

    SUM(IIF( Fields!Origin_of_Call = "Landline" , 1, 0))

    -----------

    The only little issue is that we need to add a check for 0 in the second expression to avoid division by 0. I'm not familiar with SSRS, but hopefully you know how to add some extra check.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by ANDY_MEL Wednesday, July 1, 2020 2:29 PM
    Thursday, June 25, 2020 2:14 PM

All replies

  • Just to confirm - you only want the percentage of calls less than 60 seconds for those calls where the Origin_of_Call = "LandLine"?

    If that is the case:

    =IIf(Fields!Origin_of_Call.Value = "LandLine", formatpercent(SUM(IIf(Fields!sec.Value < 60, 1, 0)) / count(Fields!sec.Value), 1), Nothing)

    A couple of notes:

    1. You don't need to format the value in an expression - you can format the text field instead.
    2. The sec field should be defined as a numeric - therefore you don't need to enclose it in quotes.  If it is actually a string value then you need to correct that in the query generating the value.
    3. Depending on how you want the results to show - change the Nothing to an actual value.  For example - you can return 0.00 or 1.00 to show either 0 percent or 100 percent.


    Jeff Williams

    • Proposed as answer by Naomi N Wednesday, June 24, 2020 7:37 PM
    Wednesday, June 24, 2020 4:27 PM
  • hi

    I am not aware of your function formatpercent. I just add and operator for satisfy your condition

    =formatpercent(SUM(IIf ( ( Fields!sec.Value  <60) AND ( Fields!Origin_of_Call = "Landline" ), 1, 0 ))/count(Fields!sec.Value),1)

    Thanks and Regards
    Laxmidhar sahoo

    Wednesday, June 24, 2020 5:03 PM
  • Hi Jeff,

    I tried running this but it just comes back with zero?.....

    Not sure what has happened there.

    Andy

    Thursday, June 25, 2020 1:29 PM
  • Hi Laxmidhar,

    This looks like it has half worked, it works till the count section then it ignores the "Landline" parameter we just inputted and divides the total by the overall total of records in the report.

    I've tried adding the parameter rule to count section kind of like the extract below but I don't think its the correct where clause to use as the report again  just counts all of the records in the report.

    =count(Fields!IncRef.Value, "ControlCalls")

    Any ideas?

    Thanks

    Andy

    Thursday, June 25, 2020 1:35 PM
  • Can you please post again your problem (e.g. what are you trying to achieve) and your exact expression you've tried?

    Basically, your condition should be simple extra IIF right before count or SUM as both people showed. 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, June 25, 2020 1:40 PM
  • Hi,

    Certainly. Basically what we have is a list of calls that have been received. A record for each of the calls received include :

    • Telephone number
    • Where they are calling from e.g.(landline)
    • Time of call started
    • Time of call ended
    • Then another column which calculates the number of seconds the call lasted ('Time of call ended' - 'Time of call started')

    The report shows all of the data in detail along with a summary at the start of the report which has a breakdown of:

    • Number of calls received
    • Percentage of calls received that lasted less than 60 seconds

    The report has a date parameters and so far works great.

    Someone wants to know if I can add summary with the Percentage of calls received that lasted less than 60 seconds just for landlines. Without having to filter the entire report.

    Once I can do this one I will be able to customise any similar requests.

    Hope this makes sence.

    Andy


    • Edited by ANDY_MEL Thursday, June 25, 2020 2:05 PM
    Thursday, June 25, 2020 2:04 PM
  • So, to get percentage of calls less than 60 sec. we 

    SUM(IIf ( ( Fields!sec.Value  <60) AND ( Fields!Origin_of_Call = "Landline" ), 1, 0) /

    SUM(IIF( Fields!Origin_of_Call = "Landline" , 1, 0))

    -----------

    The only little issue is that we need to add a check for 0 in the second expression to avoid division by 0. I'm not familiar with SSRS, but hopefully you know how to add some extra check.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by ANDY_MEL Wednesday, July 1, 2020 2:29 PM
    Thursday, June 25, 2020 2:14 PM
  • hi @

    ANDY_MEL

    Is the above post solved ?

    Thanks and Regards

    Laxmidhar sahoo

    Thursday, June 25, 2020 3:30 PM
  • Fab! had to correct 2 tiny things but it works.

    Thanks :)

    Wednesday, July 1, 2020 2:28 PM
  • Hi Laxmidhar,

    Sorry for the delay I have had some time off. Yes it is now sorted, with a few adjustments to Naomi's code it now works.

    Thanks for everybody's help, you have all been a massive help! :)

    Thanks

    Andrew

    Wednesday, July 1, 2020 2:29 PM