Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

simple table with grouping Expand / Collapse
Author
Message
Posted Wednesday, August 20, 2014 6:45 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 4:45 PM
Points: 308, Visits: 839
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?
Post #1605393
Posted Wednesday, August 20, 2014 8:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:01 AM
Points: 10,282, Visits: 13,266
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1605458
Posted Wednesday, August 20, 2014 9:15 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 4:45 PM
Points: 308, Visits: 839
will try, thanks
Post #1605491
Posted Wednesday, August 20, 2014 9:23 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 4:45 PM
Points: 308, Visits: 839
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?


  Post Attachments 
groupby.PNG (13 views, 5.68 KB)
Post #1605649
Posted Thursday, August 21, 2014 6:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:01 AM
Points: 10,282, Visits: 13,266
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1605778
Posted Thursday, August 21, 2014 8:01 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 4:45 PM
Points: 308, Visits: 839
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!
Post #1605831
Posted Thursday, August 21, 2014 9:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:01 AM
Points: 10,282, Visits: 13,266
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2


  Post Attachments 
Where Group Panes are.jpg (4 views, 97.36 KB)
Finished Report Design.jpg (3 views, 94.57 KB)
Post #1605887
Posted Thursday, August 21, 2014 2:23 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 42, Visits: 305
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?


  Post Attachments 
RS- table adding fields.bmp (5 views, 179.75 KB)
RS - table add group.bmp (8 views, 238.93 KB)
RS - table group options.bmp (7 views, 111.43 KB)
RS - table with group and total.bmp (6 views, 154.82 KB)
Post #1605995
Posted Sunday, August 24, 2014 9:12 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 4:45 PM
Points: 308, Visits: 839
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;





  Post Attachments 
DDL.rdl (5 views, 35.33 KB)
Post #1606946
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse