I've managed to do in SSRS what I've been asking to get help with.

The steps taken seem unconventional - nowhere did I find the directions to do it this way:

1. drag table onto design surface

2. select division, sentleads, referrals into adjacent columns on same row

3. now delete the detail row and leave only the header row (yes!)

4. in remaining row, which is the header, reclick on the sentLeads and Referrals column and reselect. This will automatcially sum() them.

5. add a new column to left of Division and leave it empty.

6. right click on Division and click add parent group, use Division for group by. Now there are two Division columns.

7. delete the right most Division column

8. insert a new row, above the existing row 'outside the group' and use this as the header column

Preview shows the distinct divisions with sum of sentLeads and sumReferrals which is great. Now I need to add calcuated columns.

I need a % of Total to the right of SentLeads, which is the sum of sentLeads for a given division, divided by the total SentLeads for all divisions. For Central this would be 40% and for East 60%

Can someone please show me either a better way to accomplish what I have done, or help with the calculated columns, utilizing what I have done?

The attached rdl shows a table created with wizard, which has problem I had earlier of not being able to sum by division. It also has a second table I created with above method with desired result....however no way to build an expression to take Sum(SentLeads)/Total SentLeads.

DDL for the data set query is as follows

create table Example

(Division varchar(20),

activity_date date,

SentLeads int,

Referrals int)

insert into Example values

('Central', '2013-10-09', 15, 2),

('Central', '2013-10-15', 14, 1),

('Central', '2013-10-21', 13, 3),

('Central', '2013-11-02', 21, 4),

('Central', '2013-11-06', 12, 2),

('Central', '2013-11-20', 15, 1),

('Central', '2013-12-06', 25, 3),

('Central', '2013-12-21', 20, 2),

('Central', '2013-12-22', 19, 1),

('East', '2013-10-09', 20, 2),

('East', '2013-10-15', 23, 1),

('East', '2013-10-21', 19, 3),

('East', '2013-11-02', 30, 5),

('East', '2013-11-06', 31, 3),

('East', '2013-11-20', 32, 1),

('East', '2013-12-06', 29, 2),

('East', '2013-12-21', 23, 2),

('East', '2013-12-22', 20, 1);

---query to use in SSRS report

select Division, activity_date, SentLeads, Referrals from Example;