0

I would like to get percentage for a given field value. I am achieving this in excel by using formula. Is there a way to write down formula in SSRS?

I attached images here.

I need this

I need this

In excel i achieved by using formula, how to get this one in SSRS? I achieved in excel by this, How to get this one in SSRS

Hari
  • 192
  • 2
  • 12
  • I got the partial answer here. How to get the sum of percentage? Thanks for your help in advance. – Hari May 31 '16 at 14:12

2 Answers2

1

Yes, It's possible.

Use below expression

=Round(100 * (ReportItems!Number.Value / ReportItems!Number1.Value),2)

Here, ReportItems!Number.Value is the textbox name of Name Texbox and ReportItems!Number1.Value is the textbox name of Total Textbox.

See the below Image, It's working fine.

enter image description here

To Get Sum of percentage.

I would suggest to follow below things,

Go to Report properties, then Go To Code and then create the following function.

Public Sum_Perc As Integer = 0 
Public Function PercentageSum(ByVal value As Integer) As Integer
    Sum_Perc = Sum_Perc + value    
 Return Sum_Perc
End Function

Now, beside Name - Create Placeholder and set font to white as we don't required to show calculation on this cell. Do like below imageenter image description here.

Now, Set Expression like below image and text like this,

=Code.Sum_Perc

enter image description here

Hope, It will be helpful to you. Thanks

Pedram
  • 6,256
  • 10
  • 65
  • 87
  • Thanks for your help. I never used Report items. Please could you suggest me some websites to learn more about report items. It worked perfectly. How can i get Sum of percentage? – Hari May 27 '16 at 16:17
  • I got the partial answer here. How to get the sum of percentage? Thanks for your help in advance. – Hari May 31 '16 at 14:12
  • of course to get sum - you must have to use your own logic. This can be achieved thru custom code. I've read answer and I hope it will be working fine! Seems there may be chances of issue - while using lookup_Sum. Let me know if you need more help. – Pedram Jun 01 '16 at 04:11
  • My sum is working on one report it's not working on another one. I am trying your way. Did you renamed your placeholder to sum_perc? why do we have to create placeholder, can't we achieve this one in the same cell? It's not working for me. please help me. – Hari Jun 01 '16 at 15:16
  • You have to follow what I already mentioned above. Need to use place holder. as on details row we are getting first sum value, so from all those 3 rows we need to calculate total sum in percentage. Please understand the whole scenario why we are doing like this. Hope you will understand why I have used place holder. – Pedram Jun 01 '16 at 15:32
  • @hari - can you please update me, this solution is working or not? – Pedram Jun 05 '16 at 13:47
  • Hey @Pedram, It's not working. I fixed the issue by using SQL. I am appreciating your help. Thanks – Hari Jun 06 '16 at 17:00
  • Don't know why it's not working there. It should definitely work. I have checked completely and it's working properly. – Pedram Jun 06 '16 at 17:06
  • i got 98% on one report (in excel i got 100%). On another report sum is starting from zero for every page. i.e., if one page total is 42%, on the next page it will start from 0 not from 42%. BTW I am using 2008 R2, if does it matter. – Hari Jun 06 '16 at 17:46
0

I used this custom code to get the percentage sum

Public Total_lookup_Sum As Integer = 0 
Public Function Lookup_Sum(ByVal value As Integer) As Integer
    Total_lookup_Sum = Total_lookup_Sum + value    
 Return Total_lookup_Sum 
End Function

and i used this expression in tablix

=round(100*code.lookup_Sum(Reportitems!Textbox34.value),2)

source here

Hari
  • 192
  • 2
  • 12