SSRS: How to show a percentage of overall total in a group header

Question

I have a report that shows customer appointments, by sales rep for the past 3 months grouped by customer sector. In the Customer Sector group header, I want to show the percentage of the overall total of customers in this sector that the appointments represent.

For example, my report shows that 6 appointments were made by John Smith for customers in the 'Defence' sector in the past 3 months. John Smith also made 4 appointments for customers in the 'Local Government' sector.

I know that I have a total of 10 Defence customers and 20 Local Government customers. How can I show the percentage of customers seen in that sector in the past three months?

So I currently show:

Sales Rep: John Smith (10 appointments)
     Sector: Defence (2 customers)
          Customer 1 (4 appointments)
                Appointment 1 -- Blah
                Appointment 2 -- Blah
                Appointment 3 -- Blah
                Appointment 4 -- Blah
          Customer 2 (2 appointments)
                Appointment 1 -- Blah
                Appointment 2 -- Blah
     Sector: Local Government (2 customers)
          Customer 3 (3 appointments)
                Appointment 1 -- Blah
                Appointment 2 -- Blah
                Appointment 3 -- Blah
          Customer 4 (1 appointment)
                Appointment 1 -- Blah

And I want to show:

Sales Rep: John Smith (10 appointments)
     Sector: Defence (2 customers - **20% of total Defence customers**)
          Customer 1 (4 appointments)
                Appointment 1 -- Blah
                Appointment 2 -- Blah
                Appointment 3 -- Blah
                Appointment 4 -- Blah
          Customer 2 (2 appointments)
                Appointment 1 -- Blah
                Appointment 2 -- Blah
     Sector: Local Government (2 customers - **10% of Local Government customers**)
          Customer 3 (3 appointments)
                Appointment 1 -- Blah
                Appointment 2 -- Blah
                Appointment 3 -- Blah
          Customer 4 (1 appointment)
                Appointment 1 -- Blah

I'm a SSRS noob, so don't know how I can achieve this. Do I add a dataset to the report that has the total number of customers in each customer sector?

If I do that, how can I use the total for say, Defence, in the expression I currently show in the Customer Sector group header to work out the percentage of total customers seen in that sector?

Due to database restrictions, I can't create any tables, views or stored procs in the source database, so all the work has to be done in the report.

I'm using SSRS 2005.

Many thanks!

Answer 1

I think what you want to accomplish is probably done most easily by adding %customers as a column in your SQL query for the current data. It depends on how many datasets you have right now. If you post your SQL for your datasets I'd be happy to advise; without it I'm going to have to guess. This is probably close enough to lead you on the right path.

If you have the data above being pulled into a big denormalized result of the form:

SELECT 
     SalesRep
   , Sector
   , Customer
   , Appointment
   , (SELECT count(*) FROM customers WHERE s.sector = sector) as numCustomerPerSector
FROM SalesReps sr
INNER JOIN Sectors s
   ON ...
...
WHERE 1=1

I'm sure this is not the optimal solution but it will work.