How get my report design working?

[Former Member]

I have a report card report that i need to get working. I have the school name , address , and students name and report card information at top correct but the classes are not displaying correctly.

I will post the sql query here but if you need to see my report i can post a picture.

Sql query:

SELECT dbo.Course.number, dbo.Course.name, dbo.Calendar.endYear, dbo.Calendar.startDate, dbo.Calendar.endDate

FROM dbo.Calendar INNER JOIN

dbo.Course ON dbo.Calendar.calendarID = dbo.Course.calendarID

WHERE year(getdate()) BETWEEN year(dbo.Calendar.startDate) AND year(dbo.Calendar.endDate)

My objective is to get classes from a specific school year to show up for a specific student. The students courses and the calendar year are in different tables though. For instance, for john willy i want his 2010 -2011 classes to show up.

[Former Member · Accepted solution]

Try changing your query to

SELECT dbo.Course.number, dbo.Course.name, dbo.Calendar.endYear, dbo.Calendar.startDate, dbo.Calendar.endDate

FROM dbo.Calendar INNER JOIN

dbo.Course ON dbo.Calendar.calendarID = dbo.Course.calendarID

WHERE year(dbo.Calendar.startDate) <= year(getdate())

AND year(dbo.Calendar.endDate) >= year(getdate())

Ian

[Former Member · Accepted solution]

I will test this out when i can get crystal reports to connect correctly to my sql server. It was a good answer its just i cant tell wheter it will work until i put it into the report.

[Former Member · Accepted solution]

Its still displaying the wrong data when i preview it from the database. Half of the time anymore im getting a logon failed. Details: 08001: Microsoft ODBC SQL Server Driver DBNETLIB SSL Security error Database Vendor Code:18. What does this message mean? How do i get crystal reports to work correctly?

Edited by: jeffman2 on Jun 2, 2011 6:04 PM

Edited by: jeffman2 on Jun 2, 2011 6:05 PM

[former_member292966 · Accepted solution]

Hi,

Just to cover the bases, if you take the query from Crystal and run it in SQL Server, do you get back the proper records?

When you get wrong data, are you missing records or getting records outside the two years?

For the logon error, what version of SQL Server are you using?

Are you using ODBC to connect to the database?

What version of Crystal?

What operating system are you using?

If you are using ODBC and are on a 64 bit machine, make sure you have a 32 bit datasource configured with a unique name. Crystal is only 32 bit and I've seen issues if a 32 and 64 bit datasource existed with the same name.

Good luck,

Brian

[Former Member · Accepted solution]

i should mention i got this sql query to work fully in sql managment studio 2008 but it doesnt work when i go to preview it in crystal reports:

SELECT dbo.Calendar.startDate, dbo.Calendar.endDate, dbo.Course.name, dbo.Course.number, dbo.[Identity].lastName, dbo.[Identity].firstName, dbo.[Identity].middleName,

dbo.Person.studentNumber, dbo.School.name AS Expr1, dbo.School.address, dbo.School.city, dbo.School.state, dbo.School.zip, dbo.School.phone

FROM dbo.School INNER JOIN

dbo.Calendar INNER JOIN

dbo.Course ON dbo.Calendar.calendarID = dbo.Course.calendarID ON dbo.School.schoolID = dbo.Calendar.schoolID CROSS JOIN

dbo.Person INNER JOIN

dbo.[Identity] ON dbo.Person.personID = dbo.[Identity].personID AND dbo.Person.currentIdentityID = dbo.[Identity].identityID

WHERE dbo.School.name='GARRARD COUNTY HIGH SCHOOL' AND dbo.Person.studentNumber <> 'NULL'

ORDER BY dbo.[Identity].lastName

I will add the date to it but i will i change the query to add the date part above and how do i get the query to load more then 3000 records? Iam at a school and sometimes their are instances where i will need to load more then 3000 records.

on identity

Edited by: jeffman2 on Jun 3, 2011 7:09 PM

Edited by: jeffman2 on Jun 3, 2011 7:57 PM

Edited by: jeffman2 on Jun 3, 2011 8:25 PM

[former_member292966 · Accepted solution]

Hi,

First off it looks like the syntax in your FROM looks incorrect so I'm surprised it runs in SQL Server.

In the report, are you linking the tables together or are you using the SQL Command and typing your own SQL to report from?

If you are using the SQL Command I would recommend using the tables and linking them in Crystal.

Again, what version of Crystal are you using? This is important because different versions have their own quirks.

What OS are you using?

Thanks,

Brian

[Former Member · Accepted solution]

Iam using crystal reports 2011. Iam testing the sql in sql server management studio first where it automatically links the tables for fast peformance when i use the query wizard. I just tryed this sql code but apparently im using the wrong table or view. I sure wish a person from infinite campus had a sample rdl (sql report definition file) so i could compare it to what i have in my crystal report and link it correctly.

NOTE: this crystal report is for the high school

Here is the updated sql code so you can be of better service to me:

NOTE: the top 800 statement reduced my time to load but in all

SELECT TOP 800 [garrard].[dbo].[v_SchoolCurrent].[name]

, [garrard].[dbo].[v_SchoolCurrent].[address]

, [garrard].[dbo].[v_SchoolCurrent].[city]

, [garrard].[dbo].[v_SchoolCurrent].[state]

, [garrard].[dbo].[v_SchoolCurrent].[zip]

, [garrard].[dbo].[v_SchoolCurrent].[phone]

, [garrard].[dbo].[cube_transcript].[courseName]

, [garrard].[dbo].[cube_grades].[sectionNumber]

, [garrard].[dbo].[cube_grades].[teacherName]

, [garrard].[dbo].[cube_grades].[termName]

, [garrard].[dbo].[cube_transcript].[year]

, [garrard].[dbo].[cube_transcript].[score]

, [garrard].[dbo].[cube_transcript].[gradeLevel]

, [garrard].[dbo].[student].[studentNumber]

, [garrard].[dbo].[student].[lastName]

, [garrard].[dbo].[student].[firstName]

, [garrard].[dbo].[student].[middleName]

, [garrard].[dbo].[Calendar].[startDate]

, [garrard].[dbo].[Calendar].[endDate]

FROM

[garrard].[dbo].[cube_transcript] CROSS JOIN

[garrard].[dbo].[student] CROSS JOIN

[garrard].[dbo].[v_SchoolCurrent] CROSS JOIN

[garrard].[dbo].[cube_grades] CROSS JOIN

[garrard].[dbo].[Calendar]

WHERE [garrard].[dbo].[v_SchoolCurrent].[name]='GARRARD COUNTY HIGH SCHOOL' AND [garrard].[dbo].[cube_transcript].[gradeLevel] > 08

[former_member292966 · Accepted solution]

Hi,

I see two things that need to be worked out. The first is finding the proper table to work with. The second is to break down the query so you get the data you want.

CROSS JOINs create cartesians which will slow down your query because it's generating extra records if you do not include a proper where clause. I noticed the Calendar table isn't linked to a year so it. Things like this will cause you problems. If you can try to use INNER or OUTER joins.

From your first post you wanted to generate a report card listing all the courses for each student in the current school year. Break the query down into pieces. I made up the joins because you didn't link the tables so you will need to figure that out.

Start with the student like:

Select v_SchoolCurrent.name
, v_SchoolCurrent.address
, v_SchoolCurrent.city
, v_SchoolCurrent.state
, v_SchoolCurrent.zip
, v_SchoolCurrent.phone
, student.studentNumber
, student.lastName
, student.firstName
, student.middleName
From   
garrard.dbo.v_SchoolCurrent INNER JOIN garrard.dbo.student On 
	v_SchoolCurrent.UID = student.School_UID
WHERE v_SchoolCurrent.name='GARRARD COUNTY HIGH SCHOOL' 

This should return you a list of the students in your school.

Now try adding the calendar table to it:

Select v_SchoolCurrent.name
, v_SchoolCurrent.address
, v_SchoolCurrent.city
, v_SchoolCurrent.state
, v_SchoolCurrent.zip
, v_SchoolCurrent.phone
, student.studentNumber
, student.lastName
, student.firstName
, student.middleName
, Calendar.startDate
, Calendar.endDate
From   
(garrard.dbo.v_SchoolCurrent INNER JOIN garrard.dbo.student On 
	v_SchoolCurrent.UID = student.School_UID) INNER JOIN garrard.dbo.Calendar On 
		student.School_Year = Calendar.School_Year
Where 
WHERE v_SchoolCurrent.name='GARRARD COUNTY HIGH SCHOOL' And 
	Calendar.School_Year = 2011

Add the next table and check the results after each addition.

Good luck,

Brian

[Former Member]

i should mention the data is getting read from a infinite database for our school system. I need to read in students data for the report cards if you find a solution they have already made up for doing this on their website please post because its critical i get this working in a little time as possible.