Problem with a Matrix Report

  • I'm trying to create a matrix report that shows grade level on the left side and geography code across the top. The report is to show the number of students in a grade level with a particular geography code. The sql run and the data looks good:

    ---------------------------------------------------------

    select

    ss.graden as Grade,

    s.geocode as Geo,

    s.suniq

    from

    stustat as ss inner join

    studemo as s on ss.suniq = s.suniq inner join

    track as t on ss.trkuniq = t.trkuniq

    where

    t.schoolc = @School

    and ss.edate <= @ddate

    and (ss.xdate >= @ddate or ss.xdate is null)

    group by

    ss.graden,

    s.geocode,

    s.suniq

    order by

    ss.graden desc,

    s.geocode desc

    ---------------------------------------------------------

    The problem I'm having is that there are (sometimes) duplicate geography codes across the top of the report and they indicate different counts under them for the same geography code.

  • Opus,

    Your Statement:

    select

    ss.graden as Grade,

    s.geocode as Geo,

    s.suniq

    from

    stustat as ss inner join

    studemo as s on ss.suniq = s.suniq inner join

    track as t on ss.trkuniq = t.trkuniq

    where

    t.schoolc = @School

    and ss.edate <= @ddate

    and (ss.xdate >= @ddate or ss.xdate is null)

    group by

    ss.graden,

    s.geocode,

    s.suniq

    order by

    ss.graden desc,

    s.geocode desc

    Explanation:

    For your statement, you are grouping by grade, geocode, and then student id. To count number of students per geocode per grade, you will have to modify your statements to the following:

    New Statement:[/u]

    select

    ss.graden as Grade,

    s.geocode as Geo,

    count(s.geoCode) as NumStudents

    from

    stustat as ss inner join

    studemo as s on ss.suniq = s.suniq inner join

    track as t on ss.trkuniq = t.trkuniq

    where

    t.schoolc = @School

    and ss.edate <= @ddate

    and (ss.xdate >= @ddate or ss.xdate is null)

    group by

    ss.graden,

    s.geocode

    order by

    ss.graden desc,

    s.geocode desc

    Note:[/u]

    Please do a syntax check prior to running the "New Statement" above, I do not have the tables so I have not executed the statement. There may be potential syntax errors. Also, the "New Statement" above will count number of student per geocode per grade; the statement will not handle "count distinct students."

    I hope this sample will work for you.

    Regards,

    Wameng Vang

    MCTS

  • I'm still getting two columns with the same geography code. Grades 10 and 12 are under the first geocode "P" column and 11 grade under the second geocode "P" column. I did a "select distinct geocode" from the table and there is on one code with "P".

  • Opus,

    For now, judging from our postings, your query will return for each grade, a geocode, and a student count. Therefore, geocode can exist more than once, because you are grouping by grade first, and then geocode.

    However, if you want me to clarify this further. Please provide DDL scripts for table structure and provide some sample input data for me to test out.

    You can check for me on my IM, if you need a quicker response.

    Regards,

    Wameng Vang

    MCTS

  • mengus,

    Sent you a PM.

  • Opus,

    I have replied to your PM. What I meant by IM, was my MSN contact information.

    Please keep in mind to post information in the original topic posting, not PMs. 😉 You can copy the content of the private messages into the original topic posting so that others are aware that you've either have further questions or that you have found the solution you are looking for. This helps us manage our time searching topics to answer.

    Regards,

    Wameng Vang

    MCTS

  • DDL

    Attached has show the problem with the columns.

    This is going to be long ...

    CREATE TABLE [dbo].[studemo](

    [suniq] [int] NOT NULL,

    [ident] [varchar](10) NOT NULL,

    [stuuniq] [int] NULL,

    [firstname] [varchar](16) NOT NULL,

    [middlename] [varchar](16) NULL,

    [lastname] [varchar](20) NOT NULL,

    [namesfx] [varchar](4) NULL,

    [nickname] [varchar](16) NULL,

    [genderc] [varchar](1) NOT NULL,

    [ethnicc] [varchar](1) NOT NULL,

    [birthdate] [smalldatetime] NOT NULL,

    [birthplace] [varchar](32) NULL,

    [regdate] [smalldatetime] NULL,

    [ssn] [char](9) NULL,

    [gradyear] [decimal](4, 0) NULL,

    [homelangc] [varchar](2) NULL,

    [primlangc] [varchar](2) NULL,

    [homeaddr1] [varchar](32) NULL,

    [homeaddr2] [varchar](32) NULL,

    [homecity] [varchar](24) NULL,

    [homestate] [varchar](4) NULL,

    [homezip] [char](9) NULL,

    [mailaddr1] [varchar](32) NULL,

    [mailaddr2] [varchar](32) NULL,

    [mailcity] [varchar](24) NULL,

    [mailstate] [varchar](4) NULL,

    [mailzip] [char](9) NULL,

    [emailaddr] [varchar](32) NULL,

    [varchar](64) NULL,

    [phnnumber] [char](10) NULL,

    [phntypec] [varchar](1) NULL,

    [phnunlist] [decimal](1, 0) NULL,

    [phnmsg] [decimal](1, 0) NULL,

    [bverbasc] [varchar](2) NULL,

    [bverdocnum] [varchar](32) NULL,

    [citizenc] [varchar](2) NULL,

    [countryc] [varchar](2) NULL,

    [dUsEntry] [smalldatetime] NULL,

    [geocode] [varchar](10) NULL,

    [resschoolc] [varchar](5) NULL,

    [resdistc] [varchar](10) NULL,

    [chcschoolc] [varchar](5) NULL,

    [gradreqc] [varchar](10) NULL,

    [careeruniq] [int] NULL,

    [hlduniq] [int] NULL,

    [buspuniq] [int] NULL,

    [busduniq] [int] NULL,

    [maritalc] [varchar](1) NULL,

    [migrantnum] [varchar](16) NULL,

    [legalbind] [varchar](254) NULL,

    [passwd] [varchar](11) NULL,

    [retainflag] [decimal](1, 0) NULL,

    [norank] [decimal](1, 0) NULL,

    [inforelc] [varchar](2) NULL,

    [counsfuniq] [int] NULL,

    [memberc] [varchar](10) NULL,

    [chgby] [varchar](5) NULL,

    [chgdt] [datetime] NULL,

    [stunotes] [text] NULL,

    [graddate] [smalldatetime] NULL,

    [compstatc] [varchar](2) NULL,

    [outofstate] [tinyint] NOT NULL CONSTRAINT [DF_studemo_outofstate] DEFAULT ((0)),

    [SSID] [int] NULL,

    [SSIDChangeFlag] [int] NOT NULL CONSTRAINT [SSIDChangeFlag_default] DEFAULT ((0)),

    ------------------------------------------

    CREATE TABLE [dbo].[stustat](

    [stuuniq] [int] NOT NULL,

    [ident] [varchar](10) NOT NULL,

    [suniq] [int] NOT NULL,

    [edate] [smalldatetime] NULL,

    [stustatc] [varchar](1) NULL,

    [graden] [decimal](2, 0) NULL,

    [trkuniq] [int] NULL,

    [engprofc] [varchar](2) NULL,

    [servicec] [varchar](2) NULL,

    [funiq] [int] NULL,

    [snsortby] [varchar](24) NULL,

    [xdate] [smalldatetime] NULL,

    [entryc] [varchar](3) NULL,

    [exitc] [varchar](3) NULL,

    [chgby] [varchar](5) NULL,

    [chgdt] [datetime] NULL,

    [snreserve1] [decimal](1, 0) NULL,

    [snreserve2] [decimal](1, 0) NULL,

    ---------------------------------------------

    CREATE TABLE [dbo].[track](

    [trkuniq] [int] NOT NULL,

    [trackc] [varchar](3) NOT NULL,

    [descript] [varchar](32) NOT NULL,

    [schoolc] [varchar](5) NOT NULL,

    [schyear] [decimal](4, 0) NOT NULL,

    [ppd] [decimal](2, 0) NOT NULL,

    [dpc] [decimal](2, 0) NOT NULL,

    [tpy] [decimal](2, 0) NOT NULL,

    [mpy] [decimal](2, 0) NULL,

    [perqtr] [decimal](2, 0) NULL,

    [perhalf] [decimal](2, 0) NULL,

    [perfull] [decimal](2, 0) NULL,

    [countabs] [decimal](1, 0) NULL,

    [posatt] [decimal](1, 0) NULL,

    [mpfte] [decimal](3, 0) NULL,

    [usesat] [decimal](1, 0) NULL,

    [belluniq] [int] NULL,

    [skipdays] [decimal](1, 0) NULL,

    [credyr] [decimal](6, 4) NULL,

    [sched] [decimal](1, 0) NULL,

  • FYI,

    This is the query for which the original author is now using for his reports. (Reporting Services against a SQL Server 2000 database, thus we cannot use the flexibility of pivot tables from SQL Server 2005. Therefore, I apologize for the code below. ;))

    select

    distinct

    helper.Grade

    , helper.Geo

    , ISNULL(counter.NumStudents,0) as NumStudents

    from

    (

    select

    Grade

    , Geo

    from

    (

    select

    distinct geoCode as Geo

    from studemo

    ) as h

    ,

    (

    select

    distinct graden as Grade

    from stustat

    ) as h2

    ) as helper

    left outer join

    (

    select

    ss.graden as Grade

    , s.geocode as Geo

    , count(s.geoCode) as NumStudents

    from

    stustat as ss

    inner join studemo as s

    on ss.suniq = s.suniq

    inner join track as t

    on ss.trkuniq = t.trkuniq

    where

    t.schoolc = @School

    and

    ss.edate <= @ddate

    and

    (ss.xdate >= @ddate or ss.xdate is null)

    group by

    ss.graden

    , s.geocode

    ) as counter

    on helper.Geo = Counter.Geo

    and

    helper.Grade = Counter.Grade

    order by

    helper.Grade desc

    , helper.Geo desc

    Regards,

    Wameng Vang

    MCTS

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply