Suppress Repeating Data in SQL Report

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • pmarisole
    #1

    Suppress Repeating Data in SQL Report

    I am trying to write a report that displays a hierarchy such as:
    Director, Manager, Employee
    I want to suppress the repeating Director, Manager for the employees.

    This is my code:
    sSQL = "SELECT FY99Info.MDName , FY99Info.MgrNam e, FY99Info.firstn ame,
    FY99Info.lastna me, FY99Info.grade
    FROM FY99Info
    WHERE left(FY99Info.o rgcode,5) = '" & left(session("M Org"),5) & "' and
    FY99Info.mgmtle vel < 4
    ORDER by FY99Info.MDName , FY99Info.orgcod e, FY99Info.mgmtle vel desc "

    This is the SQL table layout (FY99Info).
    Emp# Org Director Manager Employee Mgmtlevel
    1 10336088 Brian Smith Michael Smart Bill Mitchell 0
    2 10336088 Brian Smith Michael Smart Heidi Rainey 0
    3 10336088 Brian Smith Michael Smart Ponita Asnor 2
    4 10336088 Brian Smith Michael Smart Marcus Jones 0
    5 10607655 Rick Doe Glenn Thomas Helen Kelley 0
    6 10607655 Rick Doe Glenn Thomas Gaston Knight 2
    7 10607655 Rick Doe Helen Black Prentis Parker 0
    8 10607655 Rick Doe Helen Black Billie Spike 0
    9 10739900 Jason Smart Tim Snow Joe Monty 3
    10 10739900 Jason Smart Tim Snow Randi Bull 3
    11 10739900 Jason Smart Tim Snow Misty Wonton 2


    This is how I want it to display on the report
    MD Manager Employee
    Brian Smith Michael Smart Bill Mitchell
    Heidi Rainey
    Ponita Asnor
    Marcus Jones
    Rick Doe Glenn Thomas Helen Kelley
    Gaston Knight
    Helen Black Prentis Parker
    Billie Spike
    Jason Smart Tim Snow Joe Monty
    Randi Bull
    Misty Wonton

    Tags: manageremployee + wpf
    • Plamen Ratchev
      #2
      Re: Suppress Repeating Data in SQL Report

      This is best solved in your reporting tool. Most tools have suppress
      duplicates option (I know for sure Crystal Reports has it). You just need to
      check it for the selected report columns.

      Regards,

      Plamen Ratchev
      Home - SQLStudio.com
      http://www.SQLStudio.com



      "pmarisole" <jbarnes5@midso uth.rr.comwrote in message
      news:1168461987 .524531.260550@ k58g2000hse.goo glegroups.com.. .
      >I am trying to write a report that displays a hierarchy such as:
      Director, Manager, Employee
      I want to suppress the repeating Director, Manager for the employees.
      >
      This is my code:
      sSQL = "SELECT FY99Info.MDName , FY99Info.MgrNam e, FY99Info.firstn ame,
      FY99Info.lastna me, FY99Info.grade
      FROM FY99Info
      WHERE left(FY99Info.o rgcode,5) = '" & left(session("M Org"),5) & "' and
      FY99Info.mgmtle vel < 4
      ORDER by FY99Info.MDName , FY99Info.orgcod e, FY99Info.mgmtle vel desc "
      >
      This is the SQL table layout (FY99Info).
      Emp# Org Director Manager Employee Mgmtlevel
      1 10336088 Brian Smith Michael Smart Bill Mitchell 0
      2 10336088 Brian Smith Michael Smart Heidi Rainey 0
      3 10336088 Brian Smith Michael Smart Ponita Asnor 2
      4 10336088 Brian Smith Michael Smart Marcus Jones 0
      5 10607655 Rick Doe Glenn Thomas Helen Kelley 0
      6 10607655 Rick Doe Glenn Thomas Gaston Knight 2
      7 10607655 Rick Doe Helen Black Prentis Parker 0
      8 10607655 Rick Doe Helen Black Billie Spike 0
      9 10739900 Jason Smart Tim Snow Joe Monty 3
      10 10739900 Jason Smart Tim Snow Randi Bull 3
      11 10739900 Jason Smart Tim Snow Misty Wonton 2
      >
      >
      This is how I want it to display on the report
      MD Manager Employee
      Brian Smith Michael Smart Bill Mitchell
      Heidi Rainey
      Ponita Asnor
      Marcus Jones
      Rick Doe Glenn Thomas Helen Kelley
      Gaston Knight
      Helen Black Prentis Parker
      Billie Spike
      Jason Smart Tim Snow Joe Monty
      Randi Bull
      Misty Wonton
      >

        Comment

        • Erland Sommarskog
          #3
          Re: Suppress Repeating Data in SQL Report

          pmarisole (jbarnes5@midso uth.rr.com) writes:
          I am trying to write a report that displays a hierarchy such as:
          Director, Manager, Employee
          I want to suppress the repeating Director, Manager for the employees.
          Do you use Reporting Services? In such case, you may get better
          assitance in microsoft.publi c.sqlserver.rep ortingsvcs.

          Or do you want your query to produce this result:
          This is how I want it to display on the report
          >
          MD Manager Employee
          Brian Smith Michael Smart Bill Mitchell
          Heidi Rainey
          Ponita Asnor
          Marcus Jones
          Rick Doe Glenn Thomas Helen Kelley
          Gaston Knight
          Helen Black Prentis Parker
          Billie Spike
          Jason Smart Tim Snow Joe Monty
          Randi Bull
          Misty Wonton
          Please don't even think of it. It's not impossible, but it would
          be complicated. Do this in your report tool, as this is standard
          stuff for whichever tool you are using.


          --
          Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

          Books Online for SQL Server 2005 at
          http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

          Books Online for SQL Server 2000 at
          http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

            Comment

            Previous template Next
            Working...