How to add percent for columns

Question

Hi All,

I need to have cumulative percent across my columns

Below is my report outline and report output

I need cumultiive percent between A/B

followed by B/C followed by C/D and so on

hence the column will be

Advisor

Total Application

A

B

A/B percent

B

C

B/C percent

C

D

C/D percent

and so on

Any helpon this will be greatly appreciated

Thanks

 

---

Pradnya07

Answer 1

Hi,

Could you just clarify a little.  Calculating the ratio of A->B, then B->C and so on is simple enough for any given row, but where are you needing the cumulative totals?

If you're talking about cumulative in terms of advisors going down the table, you could use the runningTotal function in SSRS to sum A for all advisors so far and the same for B, then divide one by the other for the ratio.

It might be easier depending on exactly what you're after to perform an unequal self join in the SQL dataset to get the running totals you're after.  There's a nice example of running totals here:

http://stackoverflow.com/questions/2529367/can-somebody-explain-the-running-total-and-sql-self-join-in-this-tutorial-to-me

If you could clary withe regards to exactly what data is being totalled and where the calculations need to be performed I may be able to help further.

Regards

James

---

Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Answer 2

Hi James;

Thanks for the reply

i just need the ratio A/B in percent , B/C in percent and so on that the heading

but this field is dynamic which is called new_evidenceforoutputidname

Pradnya

---

Pradnya07

Answer 3

I see what you're getting at.  I think we can do it.

Don't worry about the new dynamic field name new_evidenceforoutputidname, you just need to put an expression in the header:

I think you can just use = count(claimPeriodB)/count(claimPeriodA) to get your ratio.  It should total all the entries for all the handlers for each claim period.

Regards
James

---

Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Answer 4

thanks james for your reply

but claim period A, B,C... are dynamic as well count(new_claimperiodidname)

please check my thread above regarding the report format 

---

Pradnya07

Answer 5

Hi Simran,

Apologies, I thoguht we were looking at a table, not a matrix arrangement.  I've just had a look around and found this post essentially gives the answer:

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/79a3f878-2f13-4047-83b1-7cc8c5df2ad2

You can't query dynamic columns in a matrix, but there is a solution:  Swap the columns and rows round, so the advisors are across the top of the chart (I suggest you change the text direction so the names are spelt vertically rather than horizonatally to conserve width).  The claim numbers will be in the rows (though still dynamic), but you can use the 'previous()' function to access the line above.   This will allow you to do the comparisons you're after.

Previous() function documentation:

http://msdn.microsoft.com/en-us/library/ms156372(SQL.105).aspx?PHPSESSID=7c61ntavm1dmu84rl10kephll5

Kind regards
James

---

Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.