August 20, 2014 at 6:45 am
I would like a table which has 3 columns, Division and two types of Leads, and I need the leads grouped by distinct division.
Because underlying data brings in month, my leads data is causing multiple rows to appear for the same division, but I need a true rollup to the Division level.
I can't use pivot. I have to use a table for formatting reasons.
What I've tried.
pulled in a table item. added division name to left column, sum(sent_leads) in second, sum(referrals) in third. But the sent_leads will roll up to Division (ie. one row per division) only if I remove the Month column from the underlying dataset which I need to keep for other charts I will be making in the same report.
underlying dataset
org_name
, division_name
, count(sent_leads)
, count(referrals)
, year
, month
How (without using a pivot) can I get the table to group by a category?
--Quote me
August 20, 2014 at 8:22 am
Have you added a Row Group on Division? Just doing the Sum() on the detail row isn't going to add grouping. You need to explicitly add a group. Check out this article for how to work with grouping in SSRS.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 20, 2014 at 9:16 am
will try, thanks
--Quote me
August 20, 2014 at 9:23 pm
can you provide a step by step for pulling in a table item, adding three columns, and getting the group by the one column for the remaining two?
For me the steps went like this:
-added table item
-first column division_name
-second column sent_leads
-third column referrals
-add sum to both sent_leads and referrals
-highlighted both sent_leads and referrals, right clicked, added parent group on division_name
I've attached result. I need the sent_leads to sum up to 1002 for Central, and 337 for East....a true roll up. I've attached image of what I'm getting.
Can someone tell me where in my steps I missed something?
--Quote me
August 21, 2014 at 6:37 am
This, http://msdn.microsoft.com/en-us/library/dd220419(v=sql.105).aspx walks you through how to work with groups in SSRS.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 21, 2014 at 8:01 am
but the article/s totally gloss over how to do certain things, like when the last link says
"In a tablix data region, click in the table, matrix, or list to display the Grouping pane. "
I have clicked in the table and there doesn't automatically pop up a grouping pane.
A note box says "If you do not see the Grouping pane, on the View tab, click Grouping." In my View tab there is no grouping.
Also, "Drag dataset fields to the Row Group and Column Group pane to create parent or child groups"
In a table there is no row group and column group, just headers and rows, so, what gives with all these links. Can you tell me how to do it with my simple example?
I am NOT using Report Builder, I am using SSRS 2008!
--Quote me
August 21, 2014 at 9:15 am
What version of SSDT/BIDS are you using (2008, 2010+)?
The grouping pane typically is at the bottom of the report design screen. It isn't a popup. See the attached image. To add the total right-click on one of your data cells and select Add Total which will add a total row below but within gthe group., repeat with other cells that you want totaled.
The image Finished Report Design shows what the designed looks like with the totals added. At least my interpretation of what it should look like.
Grouping and totaling is one area where I don't think they made the product better with the 2008R2 release. It isn't as intuitive or simple as it used to be.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 21, 2014 at 2:23 pm
polkadot (8/20/2014)
can you provide a step by step for pulling in a table item, adding three columns, and getting the group by the one column for the remaining two?For me the steps went like this:
-added table item
-first column division_name
-second column sent_leads
-third column referrals
-add sum to both sent_leads and referrals
-highlighted both sent_leads and referrals, right clicked, added parent group on division_name
I've attached result. I need the sent_leads to sum up to 1002 for Central, and 337 for East....a true roll up. I've attached image of what I'm getting.
Can someone tell me where in my steps I missed something?
I think you have the steps right to start with. When you add a parent group, the box has an option for Group Header and a Group Footer - make sure to check those so that you can put totals in there.
- add a table item
- fields can be dragged from the dataset, or in table click on little box in the detail row to get the list of fields
- right click on details row, add group - Parent Group, check options for header and footer row
- remove first column that adding the group created
- take grouping field out of detail row
- put it in header row
- put total expressions in footer row
Does that help?
August 24, 2014 at 9:12 pm
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;
--Quote me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply