locked
Groupping by server RRS feed

  • Question

  • Hello All,

    FYI - I'm not a report developer. I'm a DBA creating functional report to our production environment.

    Question:

    Is there a way to only display the server name once, groupping by the server name on the report with a dataset that looks like the below?

     

    Server Name Drive Letter Total  Drive Space (MB) Total Space Used (MB) Total Space Available (MB) Disk Percentage Available Drive Description
    ATLPTESTSERVERDB01 C 40,960 19,791 21,168 51.7 Local Fixed Disk -
    ATLPTESTSERVERDB01 D 99,014 23,566 75,448 76.2 Local Fixed Disk -  Data
    ATLPTESTSERVERDB01 M 998 33 965 96.7 Local Fixed Disk -  MSDTC
    ATLPTESTSERVERDB01 R 205,796 103,773 102,023 49.6 Local Fixed Disk -  DATA
    ATLPTESTSERVERDB02 C 40,960 19,238 21,722 53 Local Fixed Disk -
    ATLPTESTSERVERDB02 D 99,014 23,561 75,453 76.2 Local Fixed Disk -  Data
    ATLPTESTSERVERDB02 Q 497 32 465 93.6 Local Fixed Disk -  Quorum
    ATLPTESTSERVERDB02 S 102,896 15,654 87,242 84.8 Local Fixed Disk -  ATLPMYCTXDBCL_S
    ATLPTESTSERVERDB02 U 51,446 91 51,355 99.8 Local Fixed Disk -  ATLPMYCTXDBCL_U
    ATLPTESTSERVERDB02 W 51,446 91 51,355 99.8 Local Fixed Disk -  ATLPMYCTXDBCL_W
    ATLPTESTSERVERDB03 C 40,960 19,797 21,163 51.7 Local Fixed Disk -
    ATLPTESTSERVERDB03 D 99,014 23,837 75,177 75.9 Local Fixed Disk -  Data

     

    Ideally, I would like to have:

    Server Name Drive Letter Total  Drive Space (MB) Total Space Used (MB) Total Space Available (MB) Disk Percentage Available Drive Description
    ATLPTESTSERVERDB01 C 40,960 19,791 21,168 51.7 Local Fixed Disk -
    D 99,014 23,566 75,448 76.2 Local Fixed Disk -  Data
    M 998 33 965 96.7 Local Fixed Disk -  MSDTC
    R 205,796 103,773 102,023 49.6 Local Fixed Disk -  DATA
    ATLPTESTSERVERDB02 C 40,960 19,238 21,722 53 Local Fixed Disk -
    D 99,014 23,561 75,453 76.2 Local Fixed Disk -  Data
    Q 497 32 465 93.6 Local Fixed Disk -  Quorum
    S 102,896 15,654 87,242 84.8 Local Fixed Disk -  ATLPMYCTXDBCL_S
    U 51,446 91 51,355 99.8 Local Fixed Disk -  ATLPMYCTXDBCL_U
    W 51,446 91 51,355 99.8 Local Fixed Disk -  ATLPMYCTXDBCL_W
    ATLPTESTSERVERDB03 C 40,960 19,797 21,163 51.7 Local Fixed Disk -
    D 99,014 23,837 75,177 75.9 Local Fixed Disk -  Data

    --- Best Regards, Igor Santos Twitter: @sqlsantos Blog: sqlsantos.wordpress.com
    Monday, January 9, 2012 4:26 PM

Answers

  • Santos,

    As you are not core report developer I'll not ask you to perform some complex steps to achieve this. 

    Here are simple steps

    • Select Server Name Details filed as shown in below screen.
    • Set HideDuplicates property to your dataset name

    Let me know if you have any queries.


    Thanks,
    Sandip Shinde
    Please remember to mark the replies as answers if they help you and unmark them if they provide no help.
    • Proposed as answer by Ramakrishnan.lh Monday, January 9, 2012 5:44 PM
    • Marked as answer by Lola Wang Tuesday, January 10, 2012 3:49 AM
    Monday, January 9, 2012 4:38 PM
  • A quick and easy option would be to create a "stepped" appearance in table.

    To do that, create you drive letter group first, then as a parent group, set your Server Name group. Now you shoud have 1 row that contains both your Server and Drive groups.

    Next, right click on that row and click insert row >> "Inside Group - Above"

    You should be left with something like this:

    That will display the server name once and the all the drives underneath it.


    EDIT: Here is a more illustrated picture showing the groups:

     

    • Edited by Michael.H.Moore Monday, January 9, 2012 4:51 PM Clarification
    • Proposed as answer by Ramakrishnan.lh Monday, January 9, 2012 5:44 PM
    • Marked as answer by Lola Wang Tuesday, January 10, 2012 3:49 AM
    Monday, January 9, 2012 4:47 PM

All replies

  • Santos,

    As you are not core report developer I'll not ask you to perform some complex steps to achieve this. 

    Here are simple steps

    • Select Server Name Details filed as shown in below screen.
    • Set HideDuplicates property to your dataset name

    Let me know if you have any queries.


    Thanks,
    Sandip Shinde
    Please remember to mark the replies as answers if they help you and unmark them if they provide no help.
    • Proposed as answer by Ramakrishnan.lh Monday, January 9, 2012 5:44 PM
    • Marked as answer by Lola Wang Tuesday, January 10, 2012 3:49 AM
    Monday, January 9, 2012 4:38 PM
  • A quick and easy option would be to create a "stepped" appearance in table.

    To do that, create you drive letter group first, then as a parent group, set your Server Name group. Now you shoud have 1 row that contains both your Server and Drive groups.

    Next, right click on that row and click insert row >> "Inside Group - Above"

    You should be left with something like this:

    That will display the server name once and the all the drives underneath it.


    EDIT: Here is a more illustrated picture showing the groups:

     

    • Edited by Michael.H.Moore Monday, January 9, 2012 4:51 PM Clarification
    • Proposed as answer by Ramakrishnan.lh Monday, January 9, 2012 5:44 PM
    • Marked as answer by Lola Wang Tuesday, January 10, 2012 3:49 AM
    Monday, January 9, 2012 4:47 PM
  • Thanks Michael.

    Quick question - This will force my report to be sorted by the servername, correct?

    Do I have the option to sort by percentage used?


    --- Best Regards, Igor Santos Twitter: @sqlsantos Blog: sqlsantos.wordpress.com
    Tuesday, January 10, 2012 8:52 PM