Cross tab report error

[cognoshelp@ymail.com]

Please any cognos gurus help me out with this.

I need to develop a cross tab with date is a column and some other facts as rows.

eg:  12/16  12/17  12 /18   12/19  (Are four columns)

Row 1  please see the row calculations below.

Row 2

Row 3

Row 1 is calculated based on follwing condition select qty from abc (table) where (column) statcode=10

Row 2 is calculated based on follwing condition select qty from abc (table) where (column) statcode=20

Row 3 is calculated based on follwing condition select units from xyz (table) where (column) task_id=20

Similar I have few more calculations on rows.

I calculated them on query items in a single query but I am getting an error query reuse: the generated sql is not supported locally

Please help me by giving ideas of yours







Old post
---------
Please throw your ideas..

See the attachment to get clear idea, Information on column is date and rows are two different measures. I need to display this two fields and subtract one from other.

Any help would be thankful a ton!!

[wyconian]

Do you have DMR?  If you do try using a tuple command for the calcualtion something like tuple([date],[invn_adjmt_qty])-tuple([date],[units_pakd])

If you don't have DMR try swapping rows/columns so that you have date down the side and then you may be able to just subtract one column from the other

Good luck

[cognoshelp@ymail.com]

This report should be in cross tab. I have multiple measure such as receipts,shipments,present inventory etc..so to give you idea eg. add inventory value with receipt value and subtract the shipment value to get final warehouse inventory this is just example. I have multiple calculation as simliar to this.  Please help me

Do you have DMR?  If you do try using a tuple command for the calcualtion something like tuple([date],[invn_adjmt_qty])-tuple([date],[units_pakd])

If you don't have DMR try swapping rows/columns so that you have date down the side and then you may be able to just subtract one column from the other

Good luck

[cognoshelp@ymail.com]

Please any cognos gurus help me out with this.

I need to develop a cross tab with date is a column and some other facts as rows.

eg:  12/16  12/17  12 /18   12/19  (Are four columns)

Row 1  please see the row calculations below.

Row 2

Row 3

Row 1 is calculated based on follwing condition select qty from abc (table) where (column) statcode=10

Row 2 is calculated based on follwing condition select qty from abc (table) where (column) statcode=20

Row 3 is calculated based on follwing condition select units from xyz (table) where (column) task_id=20

Similar I have few more calculations on rows.

I calculated them on query items in a single query but I am getting an error query reuse: the generated sql is not supported locally

Please help me by giving ideas of yours

[Sunchaser]

Hi,

So, I'll try to give you an idea based on what I'm doing but I don't mean that it is "the real good solution":
-> I'm working with Oracle, so I try as soon as possible to work in native SQL (*) and in that case I will do something that I've already used for a crosstab:
"
select statment on table/view1
union all
select statment on table/view2
union all
select statment on table/view3
//etc ...
"
In one of my crosstabs, Ive 3 levels for the rows + the statment for the columns(dates, as yours), and 3 fields in the "Measure" area, created like that and it's working fine (so, only one query object in Cognos).

If you have full access to database, you should (may be) test something like that.
Good luck.
(hope it could help you in finding your solution).

(*): some will say that native SQL is never necessary, I'll disagree but won't argue and talk about it.

[cognoshelp@ymail.com]

Thanks for you reply.

But my problem is its similar to P&L(Profit and Loss) type of report where I need to add/subtact between this rows and the big issue over here is its getting data from Transational Database and I need to take care of performace also..

Please give me your ideas all cognos gurus here

Hi,

So, I'll try to give you an idea based on what I'm doing but I don't mean that it is "the real good solution":
-> I'm working with Oracle, so I try as soon as possible to work in native SQL (*) and in that case I will do something that I've already used for a crosstab:
"
select statment on table/view1
union all
select statment on table/view2
union all
select statment on table/view3
//etc ...
"
In one of my crosstabs, Ive 3 levels for the rows + the statment for the columns(dates, as yours), and 3 fields in the "Measure" area, created like that and it's working fine (so, only one query object in Cognos).

If you have full access to database, you should (may be) test something like that.
Good luck.
(hope it could help you in finding your solution).

(*): some will say that native SQL is never necessary, I'll disagree but won't argue and talk about it.

[blom0344]

(*): some will say that native SQL is never necessary, I'll disagree but won't argue and talk about it.

Cognos allows you to build Unions without having to resort to native SQL..
It takes some doing though , especialy if you have quite a number of subqueries to union.

Native SQL would be mandatory for a correlated subquery, you cannot build one in the query GUI AFAIK..