# Help with my expression in report builder

• ### 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

• 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 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 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:

• 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 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 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