Simple select query?

[PKFGimpy]

I'm a bit new to Crystal Reports, so forgive me if this is a simple question.

I have a table loaded into a .NET DataSet which I can access just fine. The table has not been linked to any other tables in Crystal Reports, nor has it been grouped.

I need to acccess each row in the table via a select query in order to display the text contents in my report header, and I can't figure out how to do this. I've been trying to accomplish this with a formula, but I am having trouble figuring out how to do this. I've tried using SQL queries, but the Formula Editor doesn't seem to understand SQL syntax.

For instance, it throws the error "The remaining text does not appear to be part of the forumla" if I run this query, "Select {CourseWork.CW_Name} FROM CourseWork
WHERE {CourseWork.CW_Field} = 1"

Am I taking the correct approach to this relatively simple task? Is there another way that I could accomplish the same thing?

Thanks.

 

[lbass]

Try inserting a subreport in the report header. In the subreport expert, select the name field to display in the detail section and then for the selection criteria, use:
{CourseWork.CW_Field} = 1. You might need to go to database->check "select distinct records".

This assumes that you need the body of the report for other purposes--otherwise you wouldn't need the subreport. You could just follow the steps above for the main report.

-LB

 

[PKFGimpy]

Cool, thanks for the idea. I've tried this and it will work, however, I will have to create 30 separate sub reports since I need to display each of the 30 records in the table. This is due to some odd structuring of this part of our database.

Are you sure there isn't an easier way of doing this?

And speaking of sub reports, is it possible to copy a subreport or move it between report sections?

 

[lbass]

I think you should give an overview of what you are trying to do. If you are trying to show all coursenames in the report header, then you should be able to simply insert a crosstab using {CourseWork.CW_Field} as your row field, and coursework as your summary field (use maximum).

-LB

 

[PKFGimpy]

Aah, cross tabs. I'll have to do some more research me thinks.

The report is a list of students and their marks. Above the list of students and their marks is a header that contains the title of the course work for each column that contains a mark.

Is this the type of task that is well suited for cross tabs? I don't know anything about them (yet).

Also, our database structure for our student marks is a little bit wonky... which might interfere with the abillity for crystal reports to handle this stuff.

 

[PKFGimpy]

Here's the database structure (in simplified form) to give you an idea of why I'm having issues with it in Crystal Reports.


StudentCourses - Stores the marks for the student in the given course. There are 30 columns here that represent up to 30 different marks.

Student Name
Course Identifier
Mark 1
Mark 2
Mark 3
Mark 4
Mark 5 ...
Mark 30


Course Work - Stores the description of the course work item as well as its weighting. There is a field called "ItemNumber" that identifies which student mark it is associated with. For instance "ItemNumber" = 1 would refer to "Mark 1" in the student courses table.

Course Work Label
Course Identifier
Item Number
Weighting


In order to match up these tables, the programmer needs to know beforehand that the value stored in "Item Number" matches up with Mark 1, Mark 2, Mark 3, etc. The database doesn't define this relationship, and it is making life difficult when trying to do things in Crystal Reports.

Crystal Reports doesn't seem to provide a simple means of making this connection. I also realize this might not be the best DB design, it's something that will have to consider changing.

 

[lbass]

Are you basically trying to get the column labels in page header? What version of CR are you using? If you are using v.9.0 or above, you could use "Add command" as your datasource (database->database expert->add command. Then you could write a union all query that would place all of the separate marks into one column. If you are comfortable with writing queries, you could use the command as your sole datasource, by also building in the other table and the linking. Or you could create a command that uses only the Student Courses table and then link the command to the other table in the conventional way. To do a union all, write a query like the following (exact syntax will be specific to your database):

SELECT
StudentCourses.`Student Name`, StudentCourses.`Course Identifier`, StudentCourses.`Mark1`, 'Mark1' as Mark
FROM
`StudentCourses` StudentCourses
UNION ALL
SELECT
StudentCourses.`Student Name`, StudentCourses.`Course Identifier`, StudentCourses.`Mark2`, 'Mark2' as Mark
FROM
`StudentCourses` StudentCourses
UNION ALL
SELECT
StudentCourses.`Student Name`, StudentCourses.`Course Identifier`, StudentCourses.`Mark3`, 'Mark3' as Mark
FROM
`StudentCourses` StudentCourses //etc. up to Mark30

Then you could link this command to the other table on Student Name, Course Identifier, and Mark1. {Command.Mark} which will return the text "Mark1","Mark2", etc., will allow you to identify the marks. You could then insert a crosstab in the report header, using {Command.Mark} as the column, CourseIdentifier and Student Name as rows, and {Command.Mark1} (which now holds all the marks) as the summary field.

-LB