How Many Queries Should My Report Have?

[RobSil]

I'm new to Cognos and still learning, so forgive me if this question is a bit remedial. :)

How many queries should a report typically have if your reporting on the same data using different graphs/charts etc.?

To elaborate...
Suppose I have the following make believe Data table...

[ID][SalesPersonName][SalesCategory][QuantitySold][TotalSalePrice][DateSold]

I'm giving a report on monthly sales results.

So my report is roughly laid out with the following planned struture:

Page 1
  - Block
      * Intro text with summary data
      * E.g. Total Sales:
               Best Salesperson:
               Best Zone:
             etc. etc.
  - Block
      * Line chart of sales by date, 1 line for each sales person
  - Block
       * Table of all sales organized by Person, Date, etc.
  - Block
       * More Text introducing the upcomming pie graph     
  - Block
       * Pie Graph outlining overall salespersons performance in relation to sales overall.


Should this be 1 Query or multiple queries, 1 per section?  I'm abstracting a query to be conceptually a SQL query... so I could see it being efficient to only bring the data once.  But I am not sure how the charts interact with the queries and if that is the most effective strategy.  Also where in some cases we need the raw records, while in others we need summary data... not sure what is best approach.

Related to the above...
For summary data such as total sales per person, or for product.. should I create Data Items in my query and calculate that data or is there a mechanism by which I can summize it in-line in the report?  E.g. for my summary text section at the start of my report... how would I most effectively find the salesman with the most sales?

Thanks for reading, forgive me if this is a bit long of a question, but hopefully it will help me get an "a-ha" moment with Cognos RS :).

Thanks,
Robin

[MFGF]

Hi,

Basically, you would use as many queries as you need (there - how's that for a vague answer!  ;D )

In your case, I suspect you will need a query for each of the best performing summaries and totals at the top (most likely Singletons from the toolbox), a query for your line chart, a query for list, and a query for your pie chart.

For your Best Salesperson query, for example, you would need to bring in the Salesperson Name, the relevant measure, then add a calculation using the rank() function, and filter the query - [your calculation] = 1.

This would be for a normal relational package.  If you are using a dimensional package, however, you would be able to do this with a topCount() function.

Regards,

MF.

[RobSil]

Thanks for the prompt reply!

I'll keep that in mind... as per my other question concerning text re-peating I'll see if that helps that issue also.

When it comes to summarizing data... the objects seem to do it for you (e.g. a table summarizes based on what columns are in it).  Is that the best approach or should you be specific in your queries and just put every column into the table?

[MFGF]

When it comes to summarizing data... the objects seem to do it for you (e.g. a table summarizes based on what columns are in it).  Is that the best approach or should you be specific in your queries and just put every column into the table?

Yes - the studios are set up to automatically group and summarize by default, as most times that is the desired behaviour to answer the relevant business questions.  If this gives you what you need, don't over-complicate the reports by bringing in unneeded items.

You can also switch this behaviour off within a report if desired (occasionally this may be necessary).  To do this in Report Studio, go to Query Explorer, select your Query, and set the Auto Group and Summarize property to No.

Regards,

MF.