SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Please Help Me Pull this off

    hello Gentlemen,

    i am currently at a cross road, i have to pull off a report for a university, with all students and their registration number in a row and all the courses they have registered in a session as columns. Now, the interesting thing is that as the rows increase horizontally downwards with the list of students and their registration numbers in a class, the columns increase vertically simultaneously with the courses that was registered, the course units and marks obtained.

    please see schema of tables with explanation below:

    institution_Courses is where all university courses are held
    institution_programme is where all the programmes the university offers are stored
    institution_PersonalData is where all the student profiles are held
    Institution_Academicprofile is where all the academic profiles of the students are held
    Institution_Programme_Courses is where all the courses a programme offeres are held i.e computer science offers csc 101, csc 112, etc...
    Institution_Programme_Course_Registration is where all student course registrations are stored across different sessions



    CREATE TABLE [dbo].[Institution_Courses](
    [CourseId] [int] IDENTITY(1,1) NOT NULL,
    [CourseCode] [varchar](20) NULL,
    [courseName] [varchar](150) NULL,
    [Activated] [bit] NULL,
    CONSTRAINT [PK_Institution_Courses] PRIMARY KEY CLUSTERED
    (
    [CourseId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO


    CREATE TABLE [dbo].[Institution_Programme](
    [ProgrammeId] [int] IDENTITY(1,1) NOT NULL,
    [ProgrammeTypeId] [int] NOT NULL,
    [DepartmentId] [int] NOT NULL,
    [CertificateId] [int] NULL,
    [ProgrammeName] [varchar](500) NULL,
    [StartLevel] [varchar](50) NULL,
    [EndLevel] [varchar](50) NULL,
    [Duration] [int] NULL,
    [UnitsRequired] [int] NULL,
    [Activated] [bit] NULL,
    [categoryid] [int] NULL,
    CONSTRAINT [PK_Institution_Programme] PRIMARY KEY CLUSTERED
    (
    [ProgrammeId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    CREATE TABLE [dbo].[Institution_PersonalData](
    [PersonalId] [int] IDENTITY(1,1) NOT NULL,
    [SessionId] [int] NOT NULL,
    [Surname] [varchar](50) NULL,
    [FirstName] [varchar](50) NULL,
    [MiddleName] [nvarchar](50) NULL,
    [DateofBirth] [datetime] NULL,
    [PlaceofBirth] [varchar](50) NULL,
    [Sex] [char](1) NULL,
    [Religion] [int] NULL,
    [MaritalStatus] [int] NULL,
    [PhoneNumber] [varchar](50) NULL,
    [EmailAddress] [varchar](50) NULL,
    [NationalityId] [int] NULL,
    [StateofOrigin] [int] NULL,
    [LGA] [int] NULL,
    [HomeTown] [varchar](50) NULL,
    [RefCode] [uniqueidentifier] NOT NULL,
    [DateFilled] [datetime] NULL,
    [PictureURL] [varchar](100) NULL,
    [StatusId] [int] NOT NULL,
    [contact_address] [varchar](max) NULL,
    [ModeOfEntry] [varchar](50) NULL,
    CONSTRAINT [PK_Institution_PersonalData] PRIMARY KEY CLUSTERED
    (
    [PersonalId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO


    CREATE TABLE [dbo].[Institution_AcademicProfile](
    [AcademicDetailsId] [int] IDENTITY(1,1) NOT NULL,
    [PersonalId] [int] NOT NULL,
    [MatricNo] [varchar](50) NULL,
    [ProgrammeId] [int] NULL,
    [CurrentLevel] [int] NULL,
    [CurrentSession] [int] NULL,
    [EntryRegNo] [varchar](50) NULL,
    [AcademicStatusId] [int] NULL,
    [categoryid] [int] NULL,
    [NDGPA] [varchar](10) NULL,
    [HNDGPA] [varchar](10) NULL,
    PRIMARY KEY CLUSTERED
    (
    [AcademicDetailsId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    CREATE TABLE [dbo].[Institution_Programme_Courses](
    [ProgrammeCourseId] [int] IDENTITY(1,1) NOT NULL,
    [ProgrammeId] [int] NOT NULL,
    [CourseId] [int] NOT NULL,
    [CourseTypeId] [int] NOT NULL,
    [CourseUnit] [int] NOT NULL,
    [SemesterId] [int] NOT NULL,
    [LevelId] [int] NOT NULL,
    [Activated] [bit] NULL,
    [pass_mark] [numeric](18, 2) NULL,
    CONSTRAINT [PK_Institution_Programme_Courses] PRIMARY KEY CLUSTERED
    (
    [ProgrammeCourseId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    CREATE TABLE [dbo].[Institution_Programme_Course_Registration](
    [CourseRegId] [int] IDENTITY(1,1) NOT NULL,
    [PersonalId] [int] NOT NULL,
    [ProgrammeId] [int] NOT NULL,
    [LevelId] [int] NOT NULL,
    [SemesterId] [int] NOT NULL,
    [CourseId] [int] NOT NULL,
    [SessionId] [int] NOT NULL,
    [DateReg] [datetime] NULL,
    [Approved] [bit] NULL,
    [DateApproved] [datetime] NULL,
    [TestScore] [decimal](18, 2) NULL,
    [ExamScore] [decimal](18, 2) NULL,
    [Total] [decimal](18, 2) NULL,
    [DateRecorded] [datetime] NULL,
    [CourseUnit] [int] NULL,
    [CourseType] [varchar](20) NULL,
    CONSTRAINT [PK_Institution_Programme_Course_Registration] PRIMARY KEY CLUSTERED
    (
    [CourseRegId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]


    Please help as i am at a cross road.

    also attached is a sample of the report to be produced

    Thanks Gurus.
    Attached Files Attached Files

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    What database are you using?
    And what is the problem you're having?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    that's microsoft sql server, guido (biggest hints are the square brackets and the IDENTITY property)

    what he wants is a PIVOT report query written for him, i.e. a crosstab layout

    sounds like a homework assignment to me, no way i'm doing it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hello Guys, thanks for your comments. i beg to disagree it is a home work or assignment, else i would have shown the teacher to what extent i have gone and then the teacher can tell me where exactly where i got it wrong, so i can get better at it next time, this is my principle about learning. unfortunately, this is not so and i cannot show the client query that does not churn out his report, have tried using the cross tab query as indicated by r937 but not just getting it. i would appreciate any help that can be rendered as time is runing out on me. yes its MS SQL Server 2008R2.

    thanks

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Can't help you with MS SQL server, sorry.
    Quote Originally Posted by nellyihu View Post
    i would have shown the teacher to what extent i have gone and then the teacher can tell me where exactly where i got it wrong, so i can get better at it next time, this is my principle about learning.
    Yes, that's what works best here too
    i cannot show the client query that does not churn out his report
    Why not?

  6. #6
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok thanks Buddy, will keep on searching till i get a clue.
    Why not?
    because the company have been paid to deliver. i had pulled it off using classic asp but if you print, does not display header and footers that is why i resorted to sql reporting services however i am new to cross tabs therefore do not know how to construct the query. would appreciate a hint to let me carry on. thanks all the same buddy. cheers

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    two minutes on google and i found this --

    http://www.mssqltips.com/sqlserverti...in-sql-server/
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks r937, delivered the report using the classic asp style, but got a little curious, i used the Microsoft SQL Server Reporting Services to attempt a much neater and flexible report than using html and nested tables but for one reason the place i needed to display CW,EX T, GP which is classwork,exam work, total and Grade Point as attached in document in the original post, for one reason did not just show the values. Which brings me to my question does the tablix control in MS SQL Reporting Service display only aggregate functions as data in the tablix data field or do i have an option to display the data in the tablix cell without aggregate data? pretty new to business inteligence, ans would appreciate all the help.

    Regards,

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by nellyihu View Post
    ...does the tablix control in MS SQL Reporting Service display only aggregate functions as data in the tablix data field or
    i have never used Reporting Service so i can't help, sorry
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh... ok thx


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •