having trouble generating my report

[rjaxn]

Hello all! I'm new to Cognos and reporting so I appreciate as much detail as possible. I have a simple 3 table SQL database with primary and foreign keys defined correctly:

order table= (order_id), firm_id, client_id, date_sent, order_variance
client table= (client_id), client_name
firm table= (firm_id), firm_name, firm_state, firm_county

I needed to create a crosstab report in Cognos 8 BI Report Studio that shows the following (from outermost to innermost):

rows: firm_state, firm_county, firm_name
columns: year, quarter
measures: order_variance

I created a view in SQL before importing the datasource into a COGNOS package. The view consists of the order_id, and columns for the year and quarter derived from the date_sent field.

I created the report as described above and received the error RQP-DEF-0103, so I went into Query Explorer and changed the propertis of my Query to Allow Cross Product. But now when I run the report it does not stop running. Can somebody please help me?

 

[blom0344]

Allow cross product has zero relevance regarding crosstab.
It refers to allowing cross-joins between query subjects.
To build a crosstab you need a normalized dataset and with the correct joins between you tables a database view will not be necessary.
Derived fields like year and month can be composed in the framework and published in the package.

The best test is to run the query as tabular SQL and look at the resultset.

Ties Blom

 

[rjaxn]

I thank you for your reply!

When you say "joins", are you referring to "relationships" among the tables that are defined in Framework Manager?

If so, those are set and are correct. Also, the database is normalized, as shown in my original post. I can't seem to figure out how to derive the year and quarter fields in the Framework Manager. Also, I need the quarter, not the month derived.

I don't quite understand what is meant by "run the query as tabular SQL"...is this done in Framework Manager or Report Studio?

Thanks in advance!

 

[blom0344]

Tabular SQL is meant for Report Studio. Actually, that is a slightly misleading term, but when you select a query in Report studio you can see the 'straigth' results either by right-clicking or choosing the correct menu button (sorry, no Cognos at hand, so I am a little vague)

joins = relationships (database term is join, FM calls these relationships)

As for derived query-items:

1. Open the query subject (assuming you have a model query subject on top of the database layer)
2. There is an add option in the GUI that allows you to create a new derived query items.
3. Give it the proper name.
4. Define year as : extract(year,[whatevernamespace].[whateverdate]
5. The same goes for month.

Caution! I am assuming that you model according to Cognos rules:

database layer
transformation layer (here you add derived query items)
presentation layer

If you are working on the database layer as such (bad choice anyway) thne you need to create an SQL object , which is little else than a sort of VIEW definition within the framework.

Ties Blom

 

[rjaxn]

Thanks again!

I appreciated all your help but I seem to be having no luck.

I figured out what you meant by viewing the tabular SQL data but when I try it times out with the following error RSV-BBP-0022 "The absolute affinity request 'asynchWait_Request' failed, the requested session does not exist.

This is the same error I get when I attempt to run the report.

 

[blom0344]

Look here:

http://businessintelligence.ittoolb...nity-request-asynchwait_requestfailed-1513575

Ties Blom

 

[CognosProfessional]

Did you run this query outside of Cognos products?

CP

 

[rjaxn]

Ties,
I found that KB article and similar ones like it...but I checked these things and the environment is fine.

Also, I created another package with just the 3 tables and removed the SQL view. I was able to generate a report with the records but I still can't figure out how to get the crosstab node of quarters and years. I couldn't figure out how to set up the derived quarter and year items in FM with the instructions you gave but thanks anyways.

CognosPro,
This report was run inside of Cognos 8.1.209.25 Report Studio.

 

[blom0344]

FM instructions:

1.Select a namespace
2.Rightclick and select create..
3.Now you have 3 options (model,database,procedure)
4.Choose model and assign a name
5.A new GUI opens and to the left you see the contents of the framework.
6.Navigate through the tree and select a table from the database layer.
7.Drag any columns you need to the right.
8.To create a derived query item click on the add
9. A new GUI opens. Here you can declare a name and an expression.

After this exercide you have a model query subject based on a database object.
In modelling you create these query subjects in the transformation layer and then assign relationships between them.

For a crosstab you can solely use drag and drop.

1.In Report Studio drag a crosstab from the toolbox.
2.The GUI shows 3 area's
3.Drag query items in either column,row or in the body.
4. Be careful how to drop them, using the edges to nest
5. In your case there is no need to concern yourselves with nodes, just correct dragging and dropping will do the trick.

Ties Blom

 

[rjaxn]

My report is working beautifully.

Thanks for all your help!