SQLTeam.com | Weblogs | Forums

Calculating percent from summed columns in report footer


#1

I have a report that sums cesarean deliveries by doctor. in the report I was able to right click the column and insert total. I added a count to the charts to get my delivery count. I have a percent column to the right of the total deliveries and total c-secs. I was able to calc the percentage in the datasource ((CESAREAN_DELIVERY*100)/NULLIF(TOTAL_DELIVERIES,0)) AS CESAREAN_PRCNT,
I would like to do the following;

  1. List item
    display the percentage in the report as an actual percent. right now it displays as
    TOTAL DELIVERIES CESAREAN DELIVERIES % C-SECS
    3 1 33
    When I format the number to a percent under text box properites it displays as 3300.00%. I tried to change my calc in the stored proc, the decimal places in the format. not working.

  2. List item
    I would like to create an PRECENTAGE expression of the sums of TOTAL DELIVERIES and C-SECS. it doesn't calculate correctly for me.
    =(Sum(Fields!TOTAL_DELIVERIES.Value, "DataSet1")/Sum(Fields!CESAREAN_DELIVERY.Value, "DataSet1")) it displays as 3.47368421052632. if I format the number to 2 decimal places it will display as 3.47. if I format it as a percentage it displays as 347.37?????

how do I get the percentage to display correctly????


#2
  1. In your expression to calculate percentage, omit the multiplication by 100. Then, use format it ias percentage under text box properties. If you just did that, the division will be integer division, so 1/3 will give you zero. To avoid that, you should convert the numerator and/or denominator to floating/decimal. For example like this:

CAST (CESAREAN_DELIVERY AS FLOAT)/NULLIF(TOTAL_DELIVERIES,0) AS CESAREAN_PRCNT

  1. You are dividing the total deliveries by the cesarians. It should be the other way around. So something like this, and then format as percentage.

=(Sum(Fields!CESAREAN_DELIVERY.Value, "DataSet1")/Sum(Fields!TOTAL_DELIVERIES.Value, "DataSet1"))

You should add something to avoid division by zero if you TOTAL_DELIVERIES is zero.


#3

Crappy formatting on the site. In my previous reply the second numbered item is supposed to be 2.