| Author |
Message |
ggardei
Newbie
Joined: 03 Feb 2009
Location: United States
Online Status: Offline
Posts: 13
|
  
Topic: Two columns
Posted: 03 Feb 2009 at 7:23pm |
|
Greetings.
I need to display a set of records in two columns. The two columns are first semester and semesters. The report is getting the records from a table that contains data like so
Semester 1, English, 3.33
Semester 1, Math, 3.32
Semester 1, Art, 4.00
Semester 2, English, 2.34
Semester 2, Math, 3.35
Semester 2, Art, 4.00
How would I do this to get output like this
Semester 1 Semester 2
English 3.33 English 2.34
.... yadda yadda
George
|
|
---------------------------
George W. Gardei
IT CAMS Manager
God's Bible School and College
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 03 Feb 2009 at 8:36pm |
|
It would be so much easier to just group on theclass and display the semesters in rows but here is a possible solution if you can create views. I am assuming your table has a student I'd as a column. Create one view with just the student Id and classes grouping on it so you have a master list ofunduplicated students in each of their classes. Then create two more views, one with only semester 1 records and the other with only semester 2 records. Use all 3 views in the report in joining each of the semester views to the student list view on ID and class. This would give you data that you can use to display as you wanted.
|
| IP Logged |
|
ggardei
Newbie
Joined: 03 Feb 2009
Location: United States
Online Status: Offline
Posts: 13
|
Posted: 04 Feb 2009 at 8:03am |
|
Yes I agree grouped by classes and list class by rows would be easy. However, our Academy wishes to have each semester listed in a column so it is easier for parents of the student to comprehend the information, and use less paper to print each transcript
The report is also grouped by grade level
9
sem 1 sem 2
10
sem 1 sem 2
11
... yada yada
Edited by ggardei - 04 Feb 2009 at 8:03am
|
|
---------------------------
George W. Gardei
IT CAMS Manager
God's Bible School and College
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 04 Feb 2009 at 8:14am |
|
Can you use the VIEWS option i gave you?
|
| IP Logged |
|
ggardei
Newbie
Joined: 03 Feb 2009
Location: United States
Online Status: Offline
Posts: 13
|
Posted: 04 Feb 2009 at 8:26am |
|
Being fairly novice at Crystal reports, can you describe how one creates a view.
|
|
---------------------------
George W. Gardei
IT CAMS Manager
God's Bible School and College
|
| IP Logged |
|
rahulwalawalkar
Senior Member
Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
|
Posted: 04 Feb 2009 at 8:36am |
Hi,
Opps sorry for the post in between you can create a view based on the subquery below
you will need to have access to database and the tables where you can code the sql ,if using sql server 2000 then query analyzer if 2005 the Management studio
You can try creating subquery
select distinct sem1.id,sem1.marks'Semester1',sem2.marks 'Semester2'
from
(
Select
DISTINCT ID,sub,
CASE EXAM WHEN 'Semester 1' THEN Sub + ' ' + Convert(varchar(50),MARKS) END 'Marks1'
from sem
where exam = 'Semester 1 ') Sem1(id,sub,MARKS)
left JOIN
(SELECT distinct id,sub,
CASE EXAM WHEN 'Semester 2' THEN Sub + ' ' + Convert(varchar(50),MARKS) END 'Marks2' from sem
where exam = 'Semester 2 ') sem2(id,sub,MARKS)
ON sem1.ID = sem2.ID and sem1.sub=sem2.sub
Based on that you have student it and subject which are matching which you will have in the table
output
id Semester1 Semester2
1 Art 4 Art 4
1 English 3 English 2
1 Math 3 Math 3
Rahul
Edited by rahulwalawalkar - 04 Feb 2009 at 8:38am
|
| IP Logged |
|
ggardei
Newbie
Joined: 03 Feb 2009
Location: United States
Online Status: Offline
Posts: 13
|
Posted: 04 Feb 2009 at 8:43am |
|
Rahul,
Thank you for your quick response. This looks interesting. My report gets its data from a stored-procedure in the SQL; would it be possible run this SQL query in the report on this data?
If not; I may have to rework my stored procedure to provide the semester 1 and semester 2 data.
George
|
|
---------------------------
George W. Gardei
IT CAMS Manager
God's Bible School and College
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 04 Feb 2009 at 8:47am |
|
I was referencing creating views in your DB (SQL) not Crystal. Since you are using a Stored Proc you can retool it using Rahuls suggestions.
|
| IP Logged |
|
rahulwalawalkar
Senior Member
Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
|
Posted: 04 Feb 2009 at 8:50am |
Hi
Yes you can modify or create new stored procedure or View and copy paste the query modifying the columns accordingly.
I am not sure though if you can use this query in command object as Crystal struggles, when using subqueries but you never know
Cheers
Rahul
|
| IP Logged |
|
ggardei
Newbie
Joined: 03 Feb 2009
Location: United States
Online Status: Offline
Posts: 13
|
Posted: 04 Feb 2009 at 8:53am |
|
Thanks DBlank and Rahul for your help. I will rework my SQL
George.
|
|
---------------------------
George W. Gardei
IT CAMS Manager
God's Bible School and College
|
| IP Logged |
|
|
|
