I am very new to Cognos. I Have a chart report that runs off of a tabular sql. The tabular sql does counts off of source tables and I want to graph the counts. I have the counts at the top (1) and the months at the bottom (2). The report validates fine but when I go to run it I get a DPR-ERR-2082 error (exception was encountered while building the cube).Ã, If I just run tabular data I get "an error occured while performing operation 'sqlOpenResult' status='-28'. with the same DPR-ERR-2082 error.
I wonder if the problem might related to similar issue with crosstab reports.
Try this
1. Open the CQEConfig.xml file, located in the install_location\configuration directory, in your favorite text editor.
2. Change the value of crosstabDecimalPrecision from 7 to 2.
3. Restart the Cognos ReportNet service.
Or another issue:
The maximum size for a measure in a crosstab and chart is 19 digits. You can use the maximum of 19 integer with 0 decimal precision.
The default decimal precision is 7, so 100000000000 works but 1000000000000 would generate the error.
Darek... thanks for your help. We tried those things but no luck. I'm trying to open a case with Cognos support if they can ever get my company account straightened out. I'm thinking that the tabular sql might be messing things up. In the sql I'm counting instances of data to create the numbers for the graph. It works fine in Oracle Reports but not in Cognos.
Could you provide the working Oracle SQL?
select YM, Month, sum(Received) Received, sum(Created) Created, sum(OpenBacklog) Open_Backlog, sum(Closed) Closed, 50 Backlog_Goal_Max
from(
select to_char(imported_on, 'YYYYMM') YM, to_char(imported_on, 'Month') Month, count(to_char(imported_on)) Received, 0 Created, 0 OpenBacklog, 0 Closed
from document
where to_char(imported_on, 'YYYY/MM') >= :start_date and to_char(imported_on, 'YYYY/MM') <= :end_date
group by to_char(imported_on, 'YYYYMM'), to_char(imported_on, 'Month')
UNION
select to_char(created_on, 'YYYYMM') YM, to_char(created_on, 'Month') Month, 0 Received, count(to_char(created_on)) Created, 0 OpenBacklog, 0 Closed
from response
where to_char(created_on, 'YYYY/MM') >= :start_date and to_char(created_on, 'YYYY/MM') <= :end_date
group by to_char(created_on, 'YYYYMM'), to_char(created_on, 'Month')
UNION
select to_char(created_on, 'YYYYMM') YM, to_char(created_on, 'Month') Month, 0 Received, 0 Created, count(to_char(created_on)) OpenBacklog, 0 Closed
from response
where completed_on is null and to_char(created_on, 'YYYY/MM') >= :start_date and to_char(created_on, 'YYYY/MM') <= :end_date
group by to_char(created_on, 'YYYYMM'), to_char(created_on, 'Month')
UNION
select to_char(created_on, 'YYYYMM') YM, to_char(created_on, 'Month') Month, 0 Received, 0 Created, 0 OpenBacklog, count(to_char(created_on)) Closed
from response
where completed_on is not null and to_char(created_on, 'YYYY/MM') >= :start_date and to_char(created_on, 'YYYY/MM') <= :end_date
group by to_char(created_on, 'YYYYMM'), to_char(created_on, 'Month')
)
group by YM, Month
order by YM
So, do you have a total of three tabular sets for your query? Do any of them work alone?
The chart works fine in our Oracle development server, actually there are three, I only sent you one of the three sql's. When we run in our Production Oracle Reports environment the report won't work. That's why I'm trying Cognos. We are moving from Oracle Reports to Cognos anyway.
I apologize for not being to specific. If I was the one bringing it into ReportNet, the statement you've posted would be divided into three tabular sets:
1. First UNION between firs and second SELECT
2. Second UNION between third and fourth SELECT
3. Third UNION combining two previous results
These would make a single Query object in Report Studio.
and I'd definitely replace thow :start_date and :end_date with ?start_date? and ?end_date?
Why don't you paste here the clipboard copy of your report so we can all look at it?
<report xml:lang="en-us" xmlns="http://developer.cognos.com/schemas/report/1/"><!--RS:1.1-->
<modelConnection name="/content/package[@name='GIDEP']/model[@name='2005-08-11T20:58:03.796Z']"/>
<querySet xml:lang="en-us">
<BIQuery name="Query1">
<cube><factList><item refItem="BACKLOG_GOAL_MAX" aggregate="none" isHeader="true"/><item refItem="RECEIVED" aggregate="none" isHeader="true"/><item refItem="CREATED" aggregate="none" isHeader="true"/><item refItem="OPEN_BACKLOG" aggregate="none" isHeader="true"/><item refItem="CLOSED" aggregate="none" isHeader="true"/></factList><dimension name="YM"><level name="YM"><item key="true" refItem="YM" aggregate="none"/></level></dimension><dimension name="MONTH"><level name="MONTH"><item key="true" refItem="MONTH" aggregate="none" isHeader="true"/></level></dimension></cube>
<tabularSQL name="Tabular SQL1" idConnection="(server not shown)"><SQL>select YM, Month, sum(Received) Received, sum(Created) Created, sum(OpenBacklog) Open_Backlog, sum(Closed) Closed, 50 Backlog_Goal_Max
from(
select to_char(imported_on, 'YYYYMM') YM, to_char(imported_on, 'Month') Month, count(to_char(imported_on)) Received, 0 Created, 0 OpenBacklog, 0 Closed
from document
where to_char(imported_on, 'YYYY/MM') >= :start_date and to_char(imported_on, 'YYYY/MM') <= :end_date
group by to_char(imported_on, 'YYYYMM'), to_char(imported_on, 'Month')
UNION
select to_char(created_on, 'YYYYMM') YM, to_char(created_on, 'Month') Month, 0 Received, count(to_char(created_on)) Created, 0 OpenBacklog, 0 Closed
from response
where to_char(created_on, 'YYYY/MM') >= :start_date and to_char(created_on, 'YYYY/MM') <= :end_date
group by to_char(created_on, 'YYYYMM'), to_char(created_on, 'Month')
UNION
select to_char(created_on, 'YYYYMM') YM, to_char(created_on, 'Month') Month, 0 Received, 0 Created, count(to_char(created_on)) OpenBacklog, 0 Closed
from response
where completed_on is null and to_char(created_on, 'YYYY/MM') >= :start_date and to_char(created_on, 'YYYY/MM') <= :end_date
group by to_char(created_on, 'YYYYMM'), to_char(created_on, 'Month')
UNION
select to_char(created_on, 'YYYYMM') YM, to_char(created_on, 'Month') Month, 0 Received, 0 Created, 0 OpenBacklog, count(to_char(created_on)) Closed
from response
where completed_on is not null and to_char(created_on, 'YYYY/MM') >= :start_date and to_char(created_on, 'YYYY/MM') <= :end_date
group by to_char(created_on, 'YYYYMM'), to_char(created_on, 'Month')
)
group by YM, Month
order by YM</SQL><dataItem name="YM"/><dataItem name="MONTH"/><dataItem name="RECEIVED"/><dataItem name="CREATED"/><dataItem name="OPEN_BACKLOG"/><dataItem name="CLOSED"/><dataItem name="BACKLOG_GOAL_MAX"/></tabularSQL></BIQuery>
<BIQuery name="Query1_chart"><cubeReference refQuery="Query1"/><summary><dataCells><item refItem="BACKLOG_GOAL_MAX"/><item refItem="RECEIVED"/><item refItem="CREATED"/><item refItem="OPEN_BACKLOG"/><item refItem="CLOSED"/></dataCells><columnEdge><level refLevel="MONTH"><item refItem="MONTH"/></level></columnEdge><rowEdge><cellMembers/></rowEdge></summary></BIQuery></querySet>
<layoutList>
<layout>
<pageSet>
<page name="Page1">
<pageBody>
<chart refQuery="Query1_chart">
<chartBody/>
<title render="false">
<text/>
</title>
<subtitle render="false">
<text/>
</subtitle>
<footer render="false">
<text/>
</footer>
<legend>
<itemTruncationText>
<text/>
</itemTruncationText>
</legend>
<axis axisPosition="X">
<labels/>
</axis>
<categoryItemTruncationText>
<text/>
</categoryItemTruncationText>
<axis axisPosition="Y1">
<labels/>
</axis>
<combinationChart depth="0" standardType="standard"><line><chartMember refMember="BACKLOG_GOAL_MAX"/></line><column showValues="none"><chartMember refMember="RECEIVED"/></column><column showValues="none"><chartMember refMember="CREATED"/></column><column showValues="none"><chartMember refMember="OPEN_BACKLOG"/></column><column showValues="none"><chartMember refMember="CLOSED"/></column></combinationChart>
<chartMeasure><chartText><queryItemRef refItem="BACKLOG_GOAL_MAX"/></chartText><style><numberFormat decimalSize="0"/></style><member refMember="BACKLOG_GOAL_MAX"/></chartMeasure><axis axisPosition="Y2"><labels/></axis><chartLevel refLevel="MONTH"><queryItemRef refItem="MONTH"/><chartText><queryItemRef content="label" refItem="MONTH"/></chartText></chartLevel><chartMeasure><chartText><queryItemRef refItem="RECEIVED"/></chartText><style><numberFormat decimalSize="0"/></style><member refMember="RECEIVED"/></chartMeasure><chartMeasure><chartText><queryItemRef refItem="CREATED"/></chartText><style><numberFormat decimalSize="0"/></style><member refMember="CREATED"/></chartMeasure><chartMeasure><chartText><queryItemRef refItem="OPEN_BACKLOG"/></chartText><style><numberFormat decimalSize="0"/></style><member refMember="OPEN_BACKLOG"/></chartMeasure><chartMeasure><chartText><queryItemRef refItem="CLOSED"/></chartText><style><numberFormat decimalSize="0"/></style><member refMember="CLOSED"/></chartMeasure></chart>
</pageBody>
<pageHeader>
<block class="reportTitle">
<textItem class="reportTitleText">
<text/>
</textItem>
</block>
<style>
<CSS value="padding-bottom:10px"/>
</style>
</pageHeader>
<pageFooter>
<table>
<tableRow>
<tableCell>
<textItem>
<expression>AsOfDate()</expression>
</textItem>
<style>
<CSS value="vertical-align:top;text-align:left;width:25%"/>
</style>
</tableCell>
<tableCell>
<textItem>
<text>- </text>
</textItem>
<textItem>
<expression>PageNumber()</expression>
</textItem>
<textItem>
<text> -</text>
</textItem>
<style>
<CSS value="vertical-align:top;text-align:center;width:50%"/>
</style>
</tableCell>
<tableCell>
<textItem>
<expression>AsOfTime()</expression>
</textItem>
<style>
<CSS value="vertical-align:top;text-align:right;width:25%"/>
</style>
</tableCell>
</tableRow>
<style>
<CSS value="border-collapse:collapse;width:100%"/>
</style>
</table>
<style>
<CSS value="padding-top:10px"/>
</style>
</pageFooter>
</page></pageSet></layout>
</layoutList>
</report>
Now I can see better. Couple of things I've noticed from start:
1. To much grouping I think. You don't have to group by YYYYMM and Month. If you group by YYYYMM and get min or max or first or last of Month, the result will be the same
2. Try to use Framework Manager Query Subjects and Query Items. Don't just force SQL down to CRN.
3. Brake those four union queries into three separate tabular sets just like I've explained in my previous suggestion
4. If I am not mistaken, in "proper SQL", if a field is not part of grouping, it needs to have aggregate function specified. That means instead of "0 Created", use "sum(0) Created". Yet again, if this will be FM driven, you won't have to worry. (actually this would be just autosummarized calculated data item with contstant value of zero and name of Created).
5. Instead of count(to_char(imported_on)), do count(imported_on) or count(1). Result should be the same.
Try these and let me know if it fixed the problem.
Thanks Darek,
I will take this up with my team lead. They have another person doing the Framework Manager task and she just went on vacation. We are still in development and running off a local box so I might be able to get this all done without too much trouble. I'm a reguritated mainframe puke so I'm still experiencing SQL and the context of it's use so I overdo things at times. I'll post a reply when I've had a chance to try these things.
Jim
I ended up doing this chart task in NetCharts, thanks for your help.
How do I close one of these topics?