Help needed to calculate % Total
in [Reports]
|
Prev: Can you expand/collapse columns based on a reportitem/field values
Next: Fields cannot be used in query parameter expressions
From: Dinesh on 22 Jan 2010 05:29 Hi All, I am working on SSRS 2008. I am facing a issue where I have to % Total based on distinct group based values. Below is the table which I need to achieve. Row No Length Total % Total 1 :15 2 33.33% 2 :30 3 100% 3 :15 4 66.66% 4 :20 6 75% 5 :20 2 25% In the above table I need to calculate % Total group by length values. For For first row Length is :15 and we have another row of length :15 so % Total will be based on all :15 length row totals. So % Total for first row will be %Total = 2/(2+4) = .33333 For 2nd row Length is :30 so % Total will be % Total = 3/3 = 1 For 3 rd row Length is again :15 so % Total = 4/(2+4) = .66666 For 4th row Length is :20 and we have another row of same length. So % total will be based on all total sum for length : 20 % Total = 6/(6+2) = .75 Similarly for 5th row % Total = 2(6+2) = .25 I need to achieve this task through VB code. As I can not calculated it in SQL query or stored procedure as per my current requirement. If any one have any idea please let me know Any help will be appreciated. Regards Dinesh
From: Uri Dimant on 24 Jan 2010 06:53
Dinesh Try using CASE expression even in SQL Server :-) SELECT CASE WHEN No=15 THEN 2./(2+4) WHEN No...... ................. END '%Totals' And do not forget to add dot '.' after 2 otherwise you will get an INTEGER "Dinesh" <dinesht15(a)gmail.com> wrote in message news:580b1055-2e8b-4049-8aa3-8ded23ed3106(a)b2g2000yqi.googlegroups.com... > Hi All, > > I am working on SSRS 2008. I am facing a issue where I have to % > Total based on distinct group based values. > Below is the table which I need to achieve. > Row No Length Total % Total > 1 :15 2 33.33% > 2 :30 3 100% > 3 :15 4 66.66% > 4 :20 6 75% > 5 :20 2 25% > > > In the above table I need to calculate % Total group by length values. > For For first row Length is :15 and we have another row of length :15 > so % Total will be based on all :15 length row totals. So % Total for > first row will be > %Total = 2/(2+4) = .33333 > For 2nd row Length is :30 so % Total will be > % Total = 3/3 = 1 > For 3 rd row Length is again :15 so > % Total = 4/(2+4) = .66666 > For 4th row Length is :20 and we have another row of same length. So % > total will be based on all total sum for length : 20 > % Total = 6/(6+2) = .75 > Similarly for 5th row > % Total = 2(6+2) = .25 > > I need to achieve this task through VB code. As I can not calculated > it in SQL query or stored procedure as per my current requirement. > > If any one have any idea please let me know > Any help will be appreciated. > > Regards > Dinesh > |