Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Datawindow Report Slide Left

5 posts in DataWindow Last posting was on 2009-09-07 03:23:07.0Z
David Copper Posted on 2009-09-05 14:53:18.0Z
Sender: 5f0e.4aa27846.1804289383@sybase.com
From: David Copper
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Datawindow Report Slide Left
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4aa27b5e.5f68.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 5 Sep 2009 07:53:18 -0700
X-Trace: forums-1-dub 1252162398 10.22.241.41 (5 Sep 2009 07:53:18 -0700)
X-Original-Trace: 5 Sep 2009 07:53:18 -0700, 10.22.241.41
Lines: 31
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89904
Article PK: 419094

I am creating a datawindow report and want to show the marks
of students:

Student A
Subject A Subject B Subject D
95 75 65

Student B
Subject B Subject D Subject E
70 65 60

1) Different Students are taking different subjects
2) The width of each subject name are different

I try to use a SQL select all students and all subjects. If
the subject is not taken by a student, the width of that
subject will become zero using formula.

My problem is I have set the 'slide left' of each subject
names and marks but the result will become:

Student A
Englis Physical Chemistry
95 75 65

Student B
English Math Biology
70 65 60

The position of marks cannot below the subject name. How to
solve this problem? Thanks!


"Paul Horan[Sybase]" <phoran_remove Posted on 2009-09-05 17:09:30.0Z
From: "Paul Horan[Sybase]" <phoran_remove@remove_sybase.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4aa27b5e.5f68.1681692777@sybase.com>
Subject: Re: Datawindow Report Slide Left
Lines: 81
Organization: Sybase
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4aa29b4a$1@forums-1-dub>
Date: 5 Sep 2009 10:09:30 -0700
X-Trace: forums-1-dub 1252170570 10.22.241.152 (5 Sep 2009 10:09:30 -0700)
X-Original-Trace: 5 Sep 2009 10:09:30 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89905
Article PK: 419091

This is a typical example of a "pivot" query, and you can do it easily right
in the SQL - IF you know the values in the "subject" axis beforehand.

Assuming the following table structure:

CREATE table Student(
student_id integer not null default autoincrement,
student_name varchar(60) not null,
primary key (student_id) );

CREATE table Subject(
subject_id integer not null default autoincrement,
subject_name varchar(30) not null,
primary key (subject_id));

CREATE table Enrollment(
student_id integer not null,
subject_id integer not null,
grade_received integer not null,
primary key ( student_id, subject_id));

Now the SQL is very straightforward.

SELECT
student_name,
sum( grade_received * (if subject_id = 1 then 1 else 0 endif)) as
subj1_grade,
sum( grade_received * (if subject_id = 2 then 1 else 0 endif)) as
subj2_grade,
sum( grade_received * (if subject_id = 3 then 1 else 0 endif)) as
subj3_grade,
sum( grade_received * (if subject_id = 4 then 1 else 0 endif)) as
subj4_grade,
...
sum( grade_received * (if subject_id = 15 then 1 else 0 endif)) as
subj15_grade,
FROM
student
left outer join enrollment
on student.student_id = enrollment.student_id
ORDER BY
student_name ;

--
Paul Horan[Sybase]
http://blogs.sybase.com/phoran/

<David Copper> wrote in message news:4aa27b5e.5f68.1681692777@sybase.com...
>I am creating a datawindow report and want to show the marks
> of students:
>
> Student A
> Subject A Subject B Subject D
> 95 75 65
>
> Student B
> Subject B Subject D Subject E
> 70 65 60
>
> 1) Different Students are taking different subjects
> 2) The width of each subject name are different
>
> I try to use a SQL select all students and all subjects. If
> the subject is not taken by a student, the width of that
> subject will become zero using formula.
>
> My problem is I have set the 'slide left' of each subject
> names and marks but the result will become:
>
> Student A
> Englis Physical Chemistry
> 95 75 65
>
> Student B
> English Math Biology
> 70 65 60
>
> The position of marks cannot below the subject name. How to
> solve this problem? Thanks!


David Copper Posted on 2009-09-06 14:06:09.0Z
Sender: 5f0e.4aa27846.1804289383@sybase.com
From: David Copper
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Datawindow Report Slide Left
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4aa3c1d1.2efe.1681692777@sybase.com>
References: <4aa29b4a$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 6 Sep 2009 07:06:09 -0700
X-Trace: forums-1-dub 1252245969 10.22.241.41 (6 Sep 2009 07:06:09 -0700)
X-Original-Trace: 6 Sep 2009 07:06:09 -0700, 10.22.241.41
Lines: 100
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89906
Article PK: 419096

Hi Paul,

Thank you for your help. But I don't want to show the marks
of subjects which the student has not been taken.

The problem is I can't show the marks below the subject name
correctly using 'slide left'.

Student A
English Physical Chemistry
95 75 65

Student B
English Math Biology
70 65 60

David

> This is a typical example of a "pivot" query, and you can
> do it easily right in the SQL - IF you know the values in
> the "subject" axis beforehand.
>
> Assuming the following table structure:
>
> CREATE table Student(
> student_id integer not null default autoincrement
> , student_name varchar(60) not null,
> primary key (student_id) );
>
> CREATE table Subject(
> subject_id integer not null default autoincrement,
> subject_name varchar(30) not null,
> primary key (subject_id));
>
> CREATE table Enrollment(
> student_id integer not null,
> subject_id integer not null,
> grade_received integer not null,
> primary key ( student_id, subject_id));
>
> Now the SQL is very straightforward.
>
> SELECT
> student_name,
> sum( grade_received * (if subject_id = 1 then 1 else 0
> endif)) as subj1_grade,
> sum( grade_received * (if subject_id = 2 then 1 else 0
> endif)) as subj2_grade,
> sum( grade_received * (if subject_id = 3 then 1 else 0
> endif)) as subj3_grade,
> sum( grade_received * (if subject_id = 4 then 1 else 0
> endif)) as subj4_grade,
> ...
> sum( grade_received * (if subject_id = 15 then 1 else 0
> endif)) as subj15_grade,
> FROM
> student
> left outer join enrollment
> on student.student_id = enrollment.student_id
> ORDER BY
> student_name ;
>
> --
> Paul Horan[Sybase]
> http://blogs.sybase.com/phoran/
>
> <David Copper> wrote in message
> news:4aa27b5e.5f68.1681692777@sybase.com... >I am creating
> > a datawindow report and want to show the marks of
> students: >
> > Student A
> > Subject A Subject B Subject D
> > 95 75 65
> >
> > Student B
> > Subject B Subject D Subject E
> > 70 65 60
> >
> > 1) Different Students are taking different subjects
> > 2) The width of each subject name are different
> >
> > I try to use a SQL select all students and all subjects.
> > If the subject is not taken by a student, the width of
> > that subject will become zero using formula.
> >
> > My problem is I have set the 'slide left' of each
> > subject names and marks but the result will become:
> >
> > Student A
> > Englis Physical Chemistry
> > 95 75 65
> >
> > Student B
> > English Math Biology
> > 70 65 60
> >
> > The position of marks cannot below the subject name. How
> > to solve this problem? Thanks!
>
>


Chris Werner Posted on 2009-09-06 15:52:01.0Z
From: "Chris Werner" <cwAT{PleaseNoSpam}f-s.de>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4aa29b4a$1@forums-1-dub> <4aa3c1d1.2efe.1681692777@sybase.com>
Subject: Re: Datawindow Report Slide Left
Lines: 122
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4aa3daa1@forums-1-dub>
Date: 6 Sep 2009 08:52:01 -0700
X-Trace: forums-1-dub 1252252321 10.22.241.152 (6 Sep 2009 08:52:01 -0700)
X-Original-Trace: 6 Sep 2009 08:52:01 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89908
Article PK: 419095

Hi David,

you could try to implement the 'slide left' behaviour by
yourself using expressions for the X property of every
item. That X property would depend on X and width
of the item left beneath ist (for headline items) or on X
of the appropriate headline. The sequence of expression
evaluation might be a problem here so I'm not sure if you'll
get through using datawindow expressions only. Otherwise
you can implement that using PowerScript in an event called
after DataWindow retrieve.

Good luck

Chris Werner
f+s software gmbh


<David Copper> schrieb im Newsbeitrag
news:4aa3c1d1.2efe.1681692777@sybase.com...

> Hi Paul,
>
> Thank you for your help. But I don't want to show the marks
> of subjects which the student has not been taken.
>
> The problem is I can't show the marks below the subject name
> correctly using 'slide left'.
>
> Student A
> English Physical Chemistry
> 95 75 65
>
> Student B
> English Math Biology
> 70 65 60
>
> David
>
>> This is a typical example of a "pivot" query, and you can
>> do it easily right in the SQL - IF you know the values in
>> the "subject" axis beforehand.
>>
>> Assuming the following table structure:
>>
>> CREATE table Student(
>> student_id integer not null default autoincrement
>> , student_name varchar(60) not null,
>> primary key (student_id) );
>>
>> CREATE table Subject(
>> subject_id integer not null default autoincrement,
>> subject_name varchar(30) not null,
>> primary key (subject_id));
>>
>> CREATE table Enrollment(
>> student_id integer not null,
>> subject_id integer not null,
>> grade_received integer not null,
>> primary key ( student_id, subject_id));
>>
>> Now the SQL is very straightforward.
>>
>> SELECT
>> student_name,
>> sum( grade_received * (if subject_id = 1 then 1 else 0
>> endif)) as subj1_grade,
>> sum( grade_received * (if subject_id = 2 then 1 else 0
>> endif)) as subj2_grade,
>> sum( grade_received * (if subject_id = 3 then 1 else 0
>> endif)) as subj3_grade,
>> sum( grade_received * (if subject_id = 4 then 1 else 0
>> endif)) as subj4_grade,
>> ...
>> sum( grade_received * (if subject_id = 15 then 1 else 0
>> endif)) as subj15_grade,
>> FROM
>> student
>> left outer join enrollment
>> on student.student_id = enrollment.student_id
>> ORDER BY
>> student_name ;
>>
>> --
>> Paul Horan[Sybase]
>> http://blogs.sybase.com/phoran/
>>
>> <David Copper> wrote in message
>> news:4aa27b5e.5f68.1681692777@sybase.com... >I am creating
>> > a datawindow report and want to show the marks of
>> students: >
>> > Student A
>> > Subject A Subject B Subject D
>> > 95 75 65
>> >
>> > Student B
>> > Subject B Subject D Subject E
>> > 70 65 60
>> >
>> > 1) Different Students are taking different subjects
>> > 2) The width of each subject name are different
>> >
>> > I try to use a SQL select all students and all subjects.
>> > If the subject is not taken by a student, the width of
>> > that subject will become zero using formula.
>> >
>> > My problem is I have set the 'slide left' of each
>> > subject names and marks but the result will become:
>> >
>> > Student A
>> > Englis Physical Chemistry
>> > 95 75 65
>> >
>> > Student B
>> > English Math Biology
>> > 70 65 60
>> >
>> > The position of marks cannot below the subject name. How
>> > to solve this problem? Thanks!
>>
>>


"Paul Horan[Sybase]" <phoran_remove Posted on 2009-09-07 03:23:07.0Z
From: "Paul Horan[Sybase]" <phoran_remove@remove_sybase.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4aa29b4a$1@forums-1-dub> <4aa3c1d1.2efe.1681692777@sybase.com>
Subject: Re: Datawindow Report Slide Left
Lines: 35
Organization: Sybase
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4aa47c9b$1@forums-1-dub>
Date: 6 Sep 2009 20:23:07 -0700
X-Trace: forums-1-dub 1252293787 10.22.241.152 (6 Sep 2009 20:23:07 -0700)
X-Original-Trace: 6 Sep 2009 20:23:07 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89909
Article PK: 419097


<David Copper> wrote in message news:4aa3c1d1.2efe.1681692777@sybase.com...
> Hi Paul,
>
> Thank you for your help. But I don't want to show the marks
> of subjects which the student has not been taken.
>
> The problem is I can't show the marks below the subject name
> correctly using 'slide left'.

The solution I presented doesn't do that. You'd see an empty "cell" for a
subject that the student didn't take.

The real problem is that the overall design of your report is questionable
to begin with. You're showing different values in the same column. In row
1, the second value represents the score in Physical. In row 2, it's a Math
score.

>
> Student A
> English Physical Chemistry
> 95 75 65
>
> Student B
> English Math Biology
> 70 65 60

I think you have to go with nested reports to accomplish this design. The
"outer" query would just be a list of students, and the student name.
The nest would be based on the student_id, and would just be a query of that
student's subjects and scores.

-Paul-