[solved] Report works only as 'View the report now'

[VisioX]

Hi there,

I have a nice issue here.

One of my reports is scheduled to run every 1st of the month to create a HTML, Excel and PDF output in the early morning.
Since this month (April) it is acting up. If i start the report and 'View the report now' and choose Excel, PDF or HTML it works great, but if i choose  the 'advanced options' and check 'Run in background' i always end up with ->

Code Select Expand

RQP-DEF-0177 An error occurred while performing operation 'sqlOpenResult' status='-28'. UDA-SQL-0114 The cursor supplied to the operation "APICursor::OpenResult" is inactive. UDA-SQL-0107 A general exception has occurred during the operation "{0}".

Any idea what could cause the problem??

Cognos BI 8.3 SP5
Xeon quad 2.3 GHz, 4GB ram running on MS Server 2003 R2 SP2

[VisioX]

Additional info:
The reports has 56 pages with a total of 200 query's

[VisioX]

I found the answer...

Cognos is not man enough to handle 200 queries.

[MFGF]

Wow!!!  That is one monster of a report!!!

[CognosPaul]

200 queries seems a bit excessive for a 56 page report. Are you using one query per report object? You might be able to cut down by sharing queries between objects.

The largest report I've created was only about 40 pages (with nested page sets). For that I only needed 15 or so queries. And that report was murderously slow to load and to work with; I can't even imagine 200 queries...

[VisioX]

I have the "standard" one query per page + 3 to 5 queries for Average / Total (Current Year, Last Year) + 3  to 5 Queries for total Average per page.
Cognos doesn't handle "Total Average" the way i would like to have it. It calculates total averages by report values, but we need it as a total from available data... that's how you end up with 200 queries.

Looks like i have to pre-create the reports in SQL and just fill the pages with data, without any calculation.

[CognosPaul]

Can you give me an example of Cognos not correctly totaling or averaging your values? I'm not sure I understand the difference between report values and available data. In theory a single query should be able to handle a detailed list plus sub and overall totals.

The only scenario that I can think of where you would need different queries is if you need different filters for the report object. And there are work-arounds for that too.

[VisioX]

Ok...

most report pages look like that... (Crosstab)

	2009										2010
Regions	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	CYTD Avg	PYTD Avg	Trend Avg
100	123	234	345	456	567	678	789	890	901	123	234	345	456	567	678	789
200	123	234	345	456	567	678	789	890	901	123	234	345	456	567	678	789
300	123	234	345	456	567	678	789	890	901	123	234	345	456	567	678	789
Company Average	123	234	345	456	567	678	789	890	901	123	234	345	456	567	678	789

CYTD Avg, PYTD Avg and Trend Avg are filtered values.
CYTD Avg = Current year to date (Jan - Mar 2010)
PYTD Avg = Prior year to date (Jan - Mar 2009)
Trend Avg = Average of the last 13 Months

Regular values are calculated, example "Customer-Pickups / Invoices".

If i take the regular Average function for "Company Average" and the last three columns, i get wrong data, because Cognos is calculating with the values "on the report", not with raw data like i use it in my value calculation.
Now i switch to calculated for "Company average" and the last three columns. The numbers are correct.
BUT!
My problem starts in this case in row "Company Average", columns CYDT, PYTD and Trend. Cognos doesn't show any values in these 3 cells.
Now i have to add singleton and 3 queries for these 3 cells. Which brings it up to 4 queries for this one page. Other pages have more columns, which means 6,7,8 queries per page. That's how i add them up to a total of 200 queries.

---

To make my problem more clear:
The values are calculated as "A/B" and shown as percent:
Example:
10 / 200 = 5.00%
5 / 500 = 1.00%
10 / 300 = 3.33%
Cognos average is giving me 3.11% ((5+1+3.33)/3), which is not what i need.
I need 25 / 1000 {(10+5+10)/(200+500+300)}, which is 2.50%

[CognosPaul]

This is difficult, but not insurmountable. Something like this would be better done inside a cube, but it can be done with relational. Try opening this report. It shows the averages for different time periods. The measures and averages are filtered inside the data items. While the overall report is filtered by the earliest date.

When I get another chance I'll make one that has calculated values in the report that are averaged correctly (that can probably be done by messing with the solve order and using the correct aggregate function).

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/6.0/" expressionLocale="he">
<modelPath>/content/folder[@name='Samples']/folder[@name='Models']/package[@name='GO Data Warehouse (query)']/model[@name='model']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItem name="Region" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Retailer site].[Region]</expression></dataItem><dataItem name="Year" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Time dimension].[Year]</expression></dataItem><dataItem name="Month" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Time dimension].[Month]</expression></dataItem><dataItem name="Measure"><expression>if([Sales (query)].[Time dimension].[Date]&gt;=2006-03-01 and [Sales (query)].[Time dimension].[Date]&lt;2007-04-01) then ([Sales (query)].[Sales fact].[Revenue]) else (null)</expression></dataItem><dataItem name="Month key" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Time dimension].[Month key]</expression></dataItem><dataItem name="CYTD Avg" solveOrder="1"><expression>average(
if([Sales (query)].[Time dimension].[Date]&gt;=2007-01-01 and [Sales (query)].[Time dimension].[Date]&lt;2007-04-01) then ([Sales (query)].[Sales fact].[Revenue]) else (null)
within detail [Year])</expression></dataItem><dataItem name="PYTD Avg" solveOrder="1"><expression>average(
if([Sales (query)].[Time dimension].[Date]&gt;=2006-01-01 and [Sales (query)].[Time dimension].[Date]&lt;2006-04-01) then ([Sales (query)].[Sales fact].[Revenue]) else (null)
within detail [Year])</expression></dataItem><dataItem name="Trend Avg" solveOrder="1"><expression>average(
if([Sales (query)].[Time dimension].[Date]&gt;=2006-03-01 and [Sales (query)].[Time dimension].[Date]&lt;2007-04-01) then ([Sales (query)].[Sales fact].[Revenue]) else (null)
within detail [Year])</expression></dataItem></selection>
<detailFilters><detailFilter><filterExpression>[Sales (query)].[Time dimension].[Date]&gt;=2006-1-1</filterExpression></detailFilter></detailFilters></query>
</queries>
<layouts>
<layout>
<reportPages>
<page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style>
<pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style>
<contents>
<crosstab refQuery="Query1" horizontalPagination="true" name="Crosstab1">
<crosstabCorner><style><defaultStyles><defaultStyle refStyle="xm"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Measure"/></dataSource></textItem></contents></crosstabCorner>


<style>
<defaultStyles>
<defaultStyle refStyle="xt"/>
</defaultStyles>
<CSS value="border-collapse:collapse"/>
</style>
<crosstabColumns><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Month" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><sortList><sortItem refDataItem="Month key"/></sortList></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Year" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><sortList><sortItem refDataItem="Year" sortOrder="ascending"/></sortList></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="CYTD Avg" edgeLocation="e5"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="PYTD Avg" edgeLocation="e4"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Trend Avg" edgeLocation="e7"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Region" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><defaultMeasure refDataItem="Measure"/><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabSuppress type="rowsAndColumns"/></crosstab>
</contents>
</pageBody>
<pageHeader>
<contents>
<block><style><defaultStyles><defaultStyle refStyle="ta"/></defaultStyles></style>
<contents>
<textItem><style><defaultStyles><defaultStyle refStyle="tt"/></defaultStyles></style>
<dataSource>
<staticValue/>
</dataSource>
</textItem>
</contents>
</block>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="ph"/>
</defaultStyles>
<CSS value="padding-bottom:10px"/>
</style>
</pageHeader>
<pageFooter>
<contents>
<table>
<tableRows>
<tableRow>
<tableCells>
<tableCell>
<contents>
<date>
<style>
<dataFormat>
<dateFormat/>
</dataFormat>
</style>
</date>
</contents>
<style>
<CSS value="vertical-align:top;text-align:left;width:25%"/>
</style>
</tableCell>
<tableCell>
<contents>
<pageNumber/>
</contents>
<style>
<CSS value="vertical-align:top;text-align:center;width:50%"/>
</style>
</tableCell>
<tableCell>
<contents>
<time>
<style>
<dataFormat>
<timeFormat/>
</dataFormat>
</style>
</time>
</contents>
<style>
<CSS value="vertical-align:top;text-align:right;width:25%"/>
</style>
</tableCell>
</tableCells>
</tableRow>
</tableRows>
<style>
<defaultStyles>
<defaultStyle refStyle="tb"/>
</defaultStyles>
<CSS value="border-collapse:collapse;width:100%"/>
</style>
</table>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="pf"/>
</defaultStyles>
<CSS value="padding-top:10px"/>
</style>
</pageFooter>
</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/></XMLAttributes></report>

[VisioX]

I tried the solve order and all that, didn't worked for me.

But I finally got it done!

I solved the issue with the help of SQL:
- Deleted all singletons and associated queries
- Created a new region "9999" and summarize the monthly values in there
- Got rid of the Average row at the end of each page
- Added a Conditional Style and a Report expression to make it look like a Average/Summary row.

Now I'm down to 94 queries (from 200) and is works again as it should!