Welcome to my messy world, Jenifer. :-)
Multiple queries under a single query object can mean a couple of things, and you can't tell which without inspecting both. The first (and worst) reason is because Cognos is sending 2 separate queries to the database, and "stitching" those results together. The ability to do this is one of the great strengths of Cognos, but there can be major performance issues if Cognos cannot "push down" all of the work to the database engine.
More common, though, is that Cognos is sending two similar queries to the DB to fulfill everything you've designed into the report.
To distinguish between the two, what you're looking for is whether Cognos is generating two separate datasets, and performing a join between them on the Cognos server, or it's just generating two parallel but non-identical versions of the base query, and using both to meet your report's requirements.
(In truth, I
hate when Cognos shows me multiple queries like this, because I have to figure out the why of it. But usually the reason is benign, and no cause for worry.)
Hope that helps you.
Regards,
------------------------------
Michael Walter
Business Analyst
TX HHSC
Austin
------------------------------
Original Message:
Sent: Fri November 05, 2021 11:13 AM
From: Jenifer Broughton
Subject: Pull report fields out of sql
Hi Michael,
This is a great place for me to start. I do have one other quick question. What if I go into the generate SQL and see the following?
Thank you!
------------------------------
Jenifer Broughton
Original Message:
Sent: Fri November 05, 2021 04:23 AM
From: Michael Walter
Subject: Pull report fields out of sql
Not sure exactly what you're looking for, Jenifer, but this would be my approach to what I think you are asking.
I use the Tools->Show SQL/MDX command to generate the SQL, then Select All, copy, and paste it into Word. I've built some macros to handle the next steps, but the essential one is to strip out all of the double quotes (") Cognos inserts into the SQL. I take that result and paste in into Format SQL Statements Online - sqlformat.org. This process generates human-readable SQL.
The report fields will be the SELECT list from the outermost SELECT statement. (I say outermost, because in complex reports, Cognos may well generate nested subqueries. But the report can't see the subquery(s)' columns; only those at the top level.)
I hope that helps you. If it doesn't, please add a little more detail as to what you're trying to accomplish, and I'll try to respond..
Regards,
------------------------------
Michael Walter
Business Analyst
TX HHSC
Austin
Original Message:
Sent: Thu November 04, 2021 02:32 PM
From: Jenifer Broughton
Subject: Pull report fields out of sql
Hello,
We are working on converting some reports, what is the easiest way to pull out only report fields from the sql?
We don't have audit ability on it.
Thanks,
Jenifer
------------------------------
Jenifer Broughton
------------------------------
#CognosAnalyticswithWatson