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.

Computation problem in Crosstab

3 posts in PB Infomaker Report Writer Last posting was on 1998-06-19 00:15:59.0Z
C.K.Ho Posted on 1998-06-18 10:58:08.0Z
Newsgroups: sybase.public.infomaker.general
From: "C.K.Ho" <chenkhee@Bigfoot.com>
Subject: Computation problem in Crosstab
Date: Thu, 18 Jun 1998 18:58:08 +0800
Lines: 49
Organization: DECOM SYSTEM SDN BHD
X-Newsreader: Microsoft Outlook Express 4.72.2106.4
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.2106.4
Message-ID: <348_b2x6e6pm9GA.202@forums.powersoft.com>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.infomaker.general:7050
Article PK: 243080

Hi, I would like to ask you a question regarding the crosstab. I’m currently
using IM5, I’m facing difficulty of computing the value under a same column.
It is quite confusing if I keep on explain, let me put it in this way.

DATA
assetno date downtime status
CRANE02 17/06/1998 1 OSM
CRANE02 17/06/1998 2 IDL
CRANE02 17/06/1998 1 BRK
CRANE02 17/06/1998 1.5 MTC
CRANE02 17/06/1998 2 IDL


CROSSTAB DEFINITION
Row : assetno
Column : date, status
Value : downtime

DESIGN PAINTER
@date
@col
assetno downtime



PREVIEW PAINTER
17/06/1998
OSM IDL BRK MTC
CRANE02 1.0 4.0 1.0 1.5



In the design painter only one column for status where consist of ‘OSM’,
‘IDL’, ‘BRK’, MTC’ for each day, and when in preview painter it will shown
as above .

Now, my question is how am I going to minus the downtime between the ‘IDL’
and the ‘MTC’ where there are expand from one column object. Or, is there
any other way to overcome this problem in SQL query before bringing up to
design painter.

I'm quite poor in explaination, hopefully you can understand.

Thanks

Regards, C.K.Ho


DawnBrownEyes[TeamPS] Posted on 1998-06-19 00:15:59.0Z
Newsgroups: sybase.public.infomaker.general
From: "DawnBrownEyes[TeamPS]" <browneyd@kochind.com>
Subject: Re: Computation problem in Crosstab
Organization: TeamPS
X-Newsreader: Microsoft Internet News 4.70.1161
Date: Thu, 18 Jun 1998 20:15:59 -0400
Lines: 64
Message-ID: <348_01bd9b1f$e9d31c20$3d18d192@kii-201520>
References: <348_b2x6e6pm9GA.202@forums.powersoft.com>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.infomaker.general:7046
Article PK: 243076

I am embarassed. There is a very easy way to do this. Dah...

It will work for static datawindow. Which means that you know that you
will always have 5 columns:
OSM IDL BRK MTC IDL-MTC

after you create the crosstab, right click on the datawindowobject and
select: from the menu: crosstab

there is a checkbox in the lower left: Rebuild columns at runtime. CHECK
IT.

The datawindow will retrieve what is in the database and build the
datawindow accordingly.

NOTE: IT WILL ALWAYS STAY THIS WAY (unless you rebuild it)

Now you can insert a computed field between the 2 columns. And code:
downtime_1 - downtime_3
this will delete the 2nd column from the 4th column. You can add a header
...

Sorry, about the hard answer, I was doing unions today and I guess they
were on my mind.

HTH
--
Dawn T Brown Eyes (TeamPS)

C.K.Ho <chenkhee@Bigfoot.com> wrote in article
<b2x6e6pm9GA.202@forums.powersoft.com>...
> Hi, I would like to ask you a question regarding the crosstab. I’m
currently
> using IM5, I’m facing difficulty of computing the value under a same
column.
> It is quite confusing if I keep on explain, let me put it in this way.
>
> DATA
> assetno date downtime status
> CRANE02 17/06/1998 1 OSM
> CRANE02 17/06/1998 2 IDL
> CRANE02 17/06/1998 1 BRK
> CRANE02 17/06/1998 1.5 MTC
> CRANE02 17/06/1998 2 IDL
>
>
> CROSSTAB DEFINITION
> Row : assetno
> Column : date, status
> Value : downtime
>
> DESIGN PAINTER
> @date
> @col
> assetno downtime
>
>
>
> PREVIEW PAINTER
> 17/06/1998
> OSM IDL BRK MTC
> CRANE02 1.0 4.0 1.0 1.5
>
>
>
> In the design painter only one column for status where consist of ‘OSM’,
> ‘IDL’, ‘BRK’, MTC’ for each day, and when in preview painter it will
shown
> as above .
>
> Now, my question is how am I going to minus the downtime between the
‘IDL’
> and the ‘MTC’ where there are expand from one column object. Or, is there
> any other way to overcome this problem in SQL query before bringing up to
> design painter.
>
> I'm quite poor in explaination, hopefully you can understand.
>
> Thanks
>
> Regards, C.K.Ho
>
>
>
>


DawnBrownEyes[TeamPS] Posted on 1998-06-18 23:46:47.0Z
Newsgroups: sybase.public.infomaker.general
From: "DawnBrownEyes[TeamPS]" <browneyd@kochind.com>
Subject: Re: Computation problem in Crosstab
Organization: TeamPS
X-Newsreader: Microsoft Internet News 4.70.1161
Date: Thu, 18 Jun 1998 19:46:47 -0400
Lines: 91
Message-ID: <348_01bd9ae2$8e1e9fe0$3d18d192@kii-201520>
References: <348_b2x6e6pm9GA.202@forums.powersoft.com>
Path: forums-1-dub!forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.infomaker.general:7047
Article PK: 243077

That is a tough question!

You could possibly do it with the datawindow. But you would have to use PB
can't do it with IM.

So you will have to do it using sql.

The problem is that you may have 2 idl for the same date and 1 mtc (like
the example) so you can't just say sum(i.downtime) - sum(m.downtime) ..
where i is the table where status = idl and m. is the table where status =
mtc.

You could do it in a stored procedure.

The following SQL lets you do in in the datawindow painter:
Go to the dw painter & create a TABULAR dw, sql select. In the sql select
painter, convert to syntax and paste the following code in substituting
your field names. This code sums all the status codes volumes. Since it
is a tabular dw you can now do whatever you want with the fields. In the
painter window create a computed column: IDL - MTC. Create a group by
date, then asset, and total the detail fields. Bring the detail band up so
the detail row can not be seen. Now you should have the 5 totals you need.


I know it is pretty cumbersome but what you want to do is pretty
complicated.

SELECT
ldate,
assetno,
OSM=sum(mtest.downtime),
IDL=0,
BRK=0,
MTC=0
FROM mtest
where status = 'OSM'
group by ldate, assetno
UNION ALL
SELECT
ldate,
assetno,
OSM=0,
IDL=sum(mtest.downtime),
BRK=0,
MTC=0
FROM mtest
where status = 'IDL'
group by ldate, assetno
UNION ALL
SELECT
ldate,
assetno,
OSM=0,
IDL=0,
BRK=sum(mtest.downtime),
MTC=0
FROM mtest
where status = 'BRK'
group by ldate, assetno
UNION ALL
SELECT
ldate,
assetno,
OSM=0,
IDL=0,
BRK=0,
MTC=sum(mtest.downtime)
FROM mtest
where status = 'MTC'
group by ldate, assetno

HTH
--
Dawn T Brown Eyes (TeamPS)

C.K.Ho <chenkhee@Bigfoot.com> wrote in article
<b2x6e6pm9GA.202@forums.powersoft.com>...
> Hi, I would like to ask you a question regarding the crosstab. I’m
currently
> using IM5, I’m facing difficulty of computing the value under a same
column.
> It is quite confusing if I keep on explain, let me put it in this way.
>
> DATA
> assetno date downtime status
> CRANE02 17/06/1998 1 OSM
> CRANE02 17/06/1998 2 IDL
> CRANE02 17/06/1998 1 BRK
> CRANE02 17/06/1998 1.5 MTC
> CRANE02 17/06/1998 2 IDL
>
>
> CROSSTAB DEFINITION
> Row : assetno
> Column : date, status
> Value : downtime
>
> DESIGN PAINTER
> @date
> @col
> assetno downtime
>
>
>
> PREVIEW PAINTER
> 17/06/1998
> OSM IDL BRK MTC
> CRANE02 1.0 4.0 1.0 1.5
>
>
>
> In the design painter only one column for status where consist of ‘OSM’,
> ‘IDL’, ‘BRK’, MTC’ for each day, and when in preview painter it will
shown
> as above .
>
> Now, my question is how am I going to minus the downtime between the
‘IDL’
> and the ‘MTC’ where there are expand from one column object. Or, is there
> any other way to overcome this problem in SQL query before bringing up to
> design painter.
>
> I'm quite poor in explaination, hopefully you can understand.
>
> Thanks
>
> Regards, C.K.Ho
>
>
>
>