Grouping Row

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Reporting Services » Reporting Services » Grouping Row

Grouping Row

Rate Topic

Display Mode

Topic Options

Author

Message

Jysafe Lerroy

Posted Thursday, November 08, 2012 9:28 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 10, 2013 1:51 PM
Points: 15, Visits: 66

Hi,

My SQL statement gives me the example below output. What I want to do is sort by person and display like the second display below.


SQL Output

Person #         Person Name        PersonIdent       StartDT                 EndDT                              PS
1234                 Smith, David            TMS                2010-01-04 08:30     2010-01-04 18:30         Central

16592               Howard, Rhonda       TMS                2010-01-02 09:45      2010-01-02 16:00        Central
16592               Howard, Rhonda       TMS                2010-01-04 09:00      2010-01-04 19:15         Central

13718               Cooper, Robyn         QMR                2010-01-02 09:45       2010-01-02 16:00          Eastern 
13718              Cooper, Robyn         QMR                 2010-01-04 10:00        2010-01-04 15:00         Eastern
13718               Cooper, Robyn        QMR                2010-01-04 16:00         2010-01-04 19:00           Eastern

16437                Arwood, Michael      TMS                2010-01-02 09:30         2010-01-02 16:00           Eastern
16437              Arwood, Michael        TMS              2010-01-04 09:00             2010-01-04 09:30        Eastern
16437             Arwood, Michael         TMS               2010-01-04 09:30          2010-01-04 11:45            Eastern
16437             Arwood, Michael         TMS               2010-01-04 11:45          2010-01-04 13:45           Eastern
16437            Arwood, Michael          TMS               2010-01-04 14:30           2010-01-04 18:00 E       astern

Report Display

Person #         Person Name           PersonIdent         # of Days            Hours               PS
1234                Smith, David                 TMS                    1             10             Central

16592             Howard, Rhonda               TMS                     2                16.5      Central

13718               Cooper, Robyn               QMR                  2           14.25             Eastern

16437               Arwood, Michael             TMS                      2            14.75           Eastern

I was able to get the # of Days and Hour but it is not grouped, so the Report displays for each row. How can I group the output to display like above?

Thanks,

Post #1382576

Ray M

Posted Thursday, November 08, 2012 11:35 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 8:25 AM
Points: 1,467, Visits: 922

Not exactly sure where your issue is cause I cannot see what the issues is, but here's a guess.

In your report I think you need to create a row group.
(Right Click the Left Most Cell of your table on the Detail Row, and Select RowGroup --> Parent Group.

Group by the PersonID, and Check Add Group Header.
Then put all your values on that new row thats created, you can either hide the detail row, or delete it.

Post #1382659

Steve-0

Posted Friday, November 09, 2012 9:34 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, November 09, 2012 11:45 AM
Points: 5, Visits: 15

I'm having a similar issue. I want to create a group (custom) where I can hide 1TGRCMHC and 1MTGRENW rows and just have the group title with the totals for approved, disbursed and unfunded visable only, unless people wanted to see the source of the total.

Post Attachments

Capture.PNG (7 views, 6.96 KB)

Post #1383095

Ray M

Posted Friday, November 09, 2012 10:23 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 8:25 AM
Points: 1,467, Visits: 922

Your example does not look like there's a logical grouping available.

You can Hide the detail Row by right clicking the Row And Select Row Visibility --> Hide. and Then Check Display can be toggled by This report Item, and Select the textbox you want to control the hide and show. Which will allow user to see detail if required.

Post #1383130

Jysafe Lerroy

Posted Sunday, November 11, 2012 1:52 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 10, 2013 1:51 PM
Points: 15, Visits: 66

Hi

Thank you that was very helpfull,

Using Expression in SSRS, how can I select Min and MAX dates?

In other words, is it possible to do the below SQL queiry in SSRS expressoin?

CASE DATEDIFF(dd,MIN(CONVERT(VARCHAR(10),StartDT, 101)),MAX(CONVERT(VARCHAR(10),EndDT, 101))) WHEN 0 THEN 1
ELSE DATEDIFF(dd,MIN(CONVERT(VARCHAR(10),StartDT, 101)),MAX(CONVERT(VARCHAR(10),EndDT, 101))) AS [# of Days],

Thanks,

Post #1383465

« Prev Topic | Next Topic »

Permissions