I've to show a percentage on the report, however when no record return it doesn't show anything. How can I catch this and show 0% when denominator is 0.
Use an if-then-else construct in your calculation for the percentage.
MF.
or also you can try with Dataformat option with divide by zero or something like
Thx for replies. I tried both with no luck. The thing is there is nothing to check when there is no data in the list, not even the row count. ???
It sounds like you're saying when there is no denominator the result row doesn't even get rendered and you'd like it to be a zero.
If that's correct, I think you need to use subqueries (one for the numerator and one for the denominator). Then when you join them in your output query you need to coalesce every field so that it 'forces' there to ALWAYS be a numerator and a denominator. Of course, you'll coalesce each fact with a zero:
coalesce([numerator], 0)
The following works , but is a bit convulated:
1. Query1 is actual data-query
2. Query2 is dummy query fetching 1 row (make sure to fetch same number and type of dataitems
3. Add dataitem 'Type' to both queries. Constant value 2 for query 1, value 1 for query2
4. Create Query 3 as union of query 1 and query 2.
5. Add dataitem to query 3 : count(distinct[Type] for report) : counter
6. Associate query 3 with the report page
7. Use 2 blocks in the report, each with their own list.
8. Define string variable as : counter
9. set render variable based on counter on both blocks. Render first for value 1, second for value2
10. top list has query2 , bottom list has query1
If no real data is fetched, then just the dummy line is shown, if 1 or more real row is fetched, then only the real data is shown.
<report xmlns="http://developer.cognos.com/schemas/report/3.0/" expressionLocale="en"><!--RS:8.2-->
<modelPath>/content/package[@name='GO Sales and Retailers']/model[@name='model']</modelPath>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItem name="Type"><expression>2</expression></dataItem><dataItem name="Staff name" aggregate="none"><expression>[gosales_goretailers].[Sales reps].[Staff name]</expression></dataItem><dataItem name="Position" aggregate="none"><expression>[gosales_goretailers].[Sales reps].[Position]</expression></dataItem><dataItem name="Date hired" aggregate="none"><expression>[gosales_goretailers].[Sales reps].[Date hired]</expression></dataItem></selection>
<detailFilters><detailFilter><filterExpression>[gosales_goretailers].[Sales reps].[Staff name] = 'Abba'</filterExpression></detailFilter></detailFilters></query>
<query name="Query2">
<source>
<model/>
</source>
<selection><dataItem name="Type"><expression>1</expression></dataItem><dataItem name="Staff name" aggregate="none"><expression>'No Name'</expression></dataItem><dataItem name="Position" aggregate="none"><expression>'Zero'</expression></dataItem><dataItem name="Date hired" aggregate="none"><expression>maximum([gosales_goretailers].[Sales reps].[Date hired] for report)</expression></dataItem></selection>
</query><query name="Query3">
<source>
<queryOperation name="Union1" setOperation="UNION">
<queryRefs><queryRef refQuery="Query1"/><queryRef refQuery="Query2"/></queryRefs>
<projectionList autoGenerated="true"><queryItem name="Type"/><queryItem name="Staff name"/><queryItem name="Position"/><queryItem name="Date hired"/></projectionList>
</queryOperation>
</source>
<selection><dataItem name="Type"><expression>[Union1].[Type]</expression></dataItem><dataItem name="Staff name"><expression>[Union1].[Staff name]</expression></dataItem><dataItem name="Position"><expression>[Union1].[Position]</expression></dataItem><dataItem name="Date hired"><expression>[Union1].[Date hired]</expression></dataItem><dataItem name="Data Item1"><expression>maximum([Type] for report)</expression></dataItem></selection>
</query>
</queries>
<layouts>
<layout>
<reportPages>
<page class="pg" name="Page1" refQuery="Query3">
<pageBody class="pb">
<contents>
<block>
<contents><list class="ls" refQuery="Query2">
<style>
<CSS value="border-collapse:collapse"/>
</style>
<listColumns><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Staff name"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lc"><contents><textItem><dataSource><dataItemValue refDataItem="Staff name"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Position"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lc"><contents><textItem><dataSource><dataItemValue refDataItem="Position"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Date hired"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lc"><contents><textItem><dataSource><dataItemValue refDataItem="Date hired"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns></list></contents>
<conditionalRender refVariable="Counter"><renderFor refVariableValue="1"/></conditionalRender></block>
<block>
<contents><list class="ls" refQuery="Query1">
<style>
<CSS value="border-collapse:collapse"/>
</style>
<listColumns><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Staff name"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lc"><contents><textItem><dataSource><dataItemValue refDataItem="Staff name"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Position"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lc"><contents><textItem><dataSource><dataItemValue refDataItem="Position"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Date hired"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lc"><contents><textItem><dataSource><dataItemValue refDataItem="Date hired"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns></list></contents>
<conditionalRender refVariable="Counter"><renderFor refVariableValue="2"/></conditionalRender></block>
</contents>
</pageBody>
<pageHeader class="ph">
<contents>
</contents>
<style>
<CSS value="padding-bottom:10px"/>
</style>
</pageHeader>
<pageFooter class="pf">
<contents>
<table class="tb">
<tableRows>
<tableRow>
<tableCells>
<tableCell>
<contents>
<textItem>
<dataSource>
<reportExpression>AsOfDate()</reportExpression>
</dataSource>
</textItem>
</contents>
<style>
<CSS value="vertical-align:top;text-align:left;width:25%"/>
</style>
</tableCell>
<tableCell>
<contents>
<textItem>
<dataSource>
<staticValue>- </staticValue>
</dataSource>
</textItem>
<textItem>
<dataSource>
<reportExpression>PageNumber()</reportExpression>
</dataSource>
</textItem>
<textItem>
<dataSource>
<staticValue> -</staticValue>
</dataSource>
</textItem>
</contents>
<style>
<CSS value="vertical-align:top;text-align:center;width:50%"/>
</style>
</tableCell>
<tableCell>
<contents>
<textItem>
<dataSource>
<reportExpression>AsOfTime()</reportExpression>
</dataSource>
</textItem>
</contents>
<style>
<CSS value="vertical-align:top;text-align:right;width:25%"/>
</style>
</tableCell>
</tableCells>
</tableRow>
</tableRows>
<style>
<CSS value="border-collapse:collapse;width:100%"/>
</style>
</table>
</contents>
<style>
<CSS value="padding-top:10px"/>
</style>
</pageFooter>
<propertyList><propertyItem refDataItem="Data Item1"/></propertyList></page>
</reportPages>
</layout>
</layouts>
<reportVariables><reportVariable type="string" name="Counter">
<reportExpression>[Query3].[Data Item1]</reportExpression>
<variableValues><variableValue value="1"/><variableValue value="2"/></variableValues></reportVariable>
</reportVariables></report>
Try With Dataformating.......
only work around
i had this problem twice.
Hi,
Try using coalesce function.
coalesce([data item],0) --- if ur data item is null then it will return 0 and set the data format to percent.
HTH!
Regards,
Aarti