Can you copy a query?

[nickirea]

I would like to have a page with 4 column charts on it - the first chart displays "all data", and the other 3 display a subset of the data, in the same format.

There is a field called "category" which can have a value of 1, 2 or 3.

The "master" chart should display all items - one column per month. The "child" charts should be for category 1 items, category 2 items, and category 3 items.

In effect, I would like to base the second chart on the query for the first chart, but with a filter. How do you do this in Report Studio?


Many thanks
Nicki

 

[almeids]

Add 3 child queries, and use a Tabular Reference to your original query instead of a Tabular Model and add your category filters at the query level (you can also put the tabular refences down a level within a tabular model and filter or add calculations in the tabular model).
If you haven't built queries from scratch before: Tabular References can be found in the insertable objects Toolbox, drag one into the query's Children pane, set its only property to point to your original query (note that what it actually points to is the tabular model of the original query), drag its data items in from the Query Items pane, and create your filter. Easiest thing to do would be to build one query entirely, then copy it and update the filter to create the other two.
This will hit the database once and then reuse the results for the additional 3 charts.
good luck
Steve

 

[blom0344]

Steve,

Do you mean an SQL object when you say 'Tabular reference'?
There is no such thing AFAIK in the toolbox (by that name)

Ties Blom

 

[almeids]

er... maybe?
I'm suddenly terribly ashamed that I may be the only kid on the block still running ReportNet.
I'm afraid I don't know the Cognos 8 architecture/ terminology, though "SQL Object" sounds more like an equivalent concept to ReportNet's Tabular SQL than a Tabular Reference. What I'm talking about is essentially a pointer to a query's result set that lets you reuse it in another query.
Nicki, which platform are you on?

 

[blom0344]

Well, you're hardly the only one. We're running both ReportNet AND C8, which is mighty confusing most of the time..

Ties Blom

 

[nickirea]

Thanks for your replies - I am on Cognos 8, with Report Studio 8.2.

 

[nickirea]

Ok, have discovered that in the Query Explorer, you can click on Queries, and then copy and paste query objects. I have then set the relevant filter on the "copied" queries.

Then in the page explorer, I put copies of my chart object in different cells, and set the source query to the different queries e.g. qSourceDataCat1 or qSourceDataCat2.

This doesn't sound as efficient as the suggested approach, as it will touch the database 4 times (once for parent chart, once for each of 3 child charts).

Any further ideas welcomed.

Nicki

 

[almeids]

In the Cognos 8 RS user manual, Appendix F, Report Studio Objects, there's something called a Query Reference: "A reference to another query defined in the same query set."

That's literally all it has to say about it but it sounds promising, might be worth looking into for those of you fortunate enough to have a C8 installation (sigh)

 

[blom0344]

Nicki,

Depending on your RDBMS and query definition, hitting the database 4 times may be less taxing than you think. The second (and subsequent) execution of the query may use cached results on the server and perform much better than the first execution. So, while hitting the database 4 times may seem inefficient, it could be not as bad as you might think..

Ties Blom