Hello,

I have a problem with percentage in cognos.

I created a simple query that returns a list of my work orders, and I need to calculate the percentage of complete/total and use the filter for "site" and "all sites", but if my filter is "all sites" , cognos adds all the percentages and returns a wrong percentage.

i used visualization By the Numbers to show the values.
my data iten used to calculate is:

[complete]/[total]

I tried to put the summary aggregation with the calculation value in the data item, but the result is the same.
Thank you for your attention.

Quote from: Sonishados on 27 Nov 2023 02:35:05 PMHello,

I have a problem with percentage in cognos.

I created a simple query that returns a list of my work orders, and I need to calculate the percentage of complete/total and use the filter for "site" and "all sites", but if my filter is "all sites" , cognos adds all the percentages and returns a wrong percentage.

i used visualization By the Numbers to show the values.
my data iten used to calculate is:

[complete]/[total]

I tried to put the summary aggregation with the calculation value in the data item, but the result is the same.
Thank you for your attention.

Hi,

I'm assuming that when you filter on a site, the report only returns one site row (with the correct percentage), but when you choose "All Sites" the percentage is the sum of the percentages calculated for each site?

If all you ever need is the overall percentage as a single figure, you could amend your calculation expression to be

total([complete] for report) / total([total] for report)

Does this work for you?

Cheers!

MF.

Quote from: MFGF on 27 Nov 2023 02:52:23 PMHi,

I'm assuming that when you filter on a site, the report only returns one site row (with the correct percentage), but when you choose "All Sites" the percentage is the sum of the percentages calculated for each site?

If all you ever need is the overall percentage as a single figure, you could amend your calculation expression to be

total([complete] for report) / total([total] for report)

Does this work for you?

Cheers!

MF.

Hi,

This expression return only the value of all sites, in this report when i filter the site i need it to return the site values and when selecting all sites return the value of all site.

In my expression ([complete]/ [total]) the filter works with select siteid in filter, but the value will be wrong if select all sites in filter, and your expression return unique value of all sites independent of the filter.

In this image I use discipline filter to filter values ��and returns the correct value.
You cannot view this attachment.

And here i select all values of discipline and the percentage not works.
You cannot view this attachment.

thanks for the feedback.

Quote from: Sonishados on 28 Nov 2023 12:14:07 PMHi,

This expression return only the value of all sites, in this report when i filter the site i need it to return the site values and when selecting all sites return the value of all site.

In my expression ([complete]/ [total]) the filter works with select siteid in filter, but the value will be wrong if select all sites in filter, and your expression return unique value of all sites independent of the filter.

In this image I use discipline filter to filter values ��and returns the correct value.
You cannot view this attachment.

And here i select all values of discipline and the percentage not works.
You cannot view this attachment.

thanks for the feedback.

Hi,

I don't quite follow what you are saying about the expression I suggested. It sounds like it's what you need, but I an probably misunderstanding?

If you filter the report for a single site, the expression I suggested would apply to the data only from that site - it's the only data available to it in the query, since your filter removes the other sites? If you don't filter the report for a single site (and therefore the data spans all sites), it should return the percentage across all of the sites?

Can you show an example of what happens if you use the expression I suggested? And/or can you explain in a little more detail what you are trying to do?

Cheers!

MF.

Quote from: Sonishados on 28 Nov 2023 12:14:07 PMHi,

This expression return only the value of all sites, in this report when i filter the site i need it to return the site values and when selecting all sites return the value of all site.

In my expression ([complete]/ [total]) the filter works with select siteid in filter, but the value will be wrong if select all sites in filter, and your expression return unique value of all sites independent of the filter.

In this image I use discipline filter to filter values ��and returns the correct value.
You cannot view this attachment.

And here i select all values of discipline and the percentage not works.
You cannot view this attachment.

thanks for the feedback.

Hi,

I just looked further at this - I was curious to see if I could replicate it.

It looks like you have a crosstab driving all of this. I created a similar crosstab, and added a calculation for a percentage as you did (eg [complete] / [total] ). In Properties, I set the Solve Order of the calculation to 2.

I then added the same items from the same query (including the calculation) into singletons in a table above the crosstab. Because I'm re-using the same calculation with a Solve Order of 2, it seems to work fine.

Maybe try changing the Solve Order, and see if this fixes things for you?

Cheers!

MF.

Quote from: MFGF on 28 Nov 2023 01:56:02 PMHi,

I don't quite follow what you are saying about the expression I suggested. It sounds like it's what you need, but I an probably misunderstanding?

If you filter the report for a single site, the expression I suggested would apply to the data only from that site - it's the only data available to it in the query, since your filter removes the other sites? If you don't filter the report for a single site (and therefore the data spans all sites), it should return the percentage across all of the sites?

Can you show an example of what happens if you use the expression I suggested? And/or can you explain in a little more detail what you are trying to do?

Cheers!

MF.

You are right in your question, if I filter by a single site it should return the percentage of that site, and if I filter by all sites it should return the percentage of all sites.

I can try to explain more what I want to do.
I need to create a active report to return the values of  total work orders, complete work orders and other values.
One of these values is the percentage of service orders that were fulfilled and I need to filter this percentage by site and also for all sites, but whenever I put all sites in the filter the return on the percentage is the sum of the percentages.

I created the expression, you told me.
You cannot view this attachment.

The filter was created like this, I put the siteid attribute in the value list and then in the properties under "No items in the value list" I put the value as "show" and set the name to "all sites".
You cannot view this attachment.

When filtering a value on Site, the expression returns the value that should be for all sites. (To make it easier, I included in the image the values ��next to what would be the division for the percentage [Complete](Atendidas) / [TOTAL](WONUM))
**In the image the values ��are 264 / 580 = 0.4551 (this would be the correct value for the CAM Site percentage)
You cannot view this attachment.

And when I filter for all sites the return is this. (The correct value would be what appeared in the previous image of 31.2371%)
You cannot view this attachment.

Thanks for your attention.

Quote from: Sonishados on 28 Nov 2023 03:50:14 PMYou are right in your question, if I filter by a single site it should return the percentage of that site, and if I filter by all sites it should return the percentage of all sites.

I can try to explain more what I want to do.
I need to create a active report to return the values of  total work orders, complete work orders and other values.
One of these values is the percentage of service orders that were fulfilled and I need to filter this percentage by site and also for all sites, but whenever I put all sites in the filter the return on the percentage is the sum of the percentages.

I created the expression, you told me.
You cannot view this attachment.

The filter was created like this, I put the siteid attribute in the value list and then in the properties under "No items in the value list" I put the value as "show" and set the name to "all sites".
You cannot view this attachment.

When filtering a value on Site, the expression returns the value that should be for all sites. (To make it easier, I included in the image the values ��next to what would be the division for the percentage [Complete](Atendidas) / [TOTAL](WONUM))
**In the image the values ��are 264 / 580 = 0.4551 (this would be the correct value for the CAM Site percentage)
You cannot view this attachment.

And when I filter for all sites the return is this. (The correct value would be what appeared in the previous image of 31.2371%)
You cannot view this attachment.

Thanks for your attention.

Hi,

I noticed you refer to this as an "active report". Is it just a regular report with prompts embedded on the page, or is it really an active report (created using one of the Active Reports templates)? That makes a huge difference.

If it's a regular report, you can use the report spec below as a guide to see how I did it:

<report xmlns="http://developer.cognos.com/schemas/report/16.2/"           expressionLocale="en-us" useStyleVersion="11.5"><!--RSU-SPC-0093 The report specification was upgraded from "http://developer.cognos.com/schemas/report/15.2/" to "http://developer.cognos.com/schemas/report/16.2/" at 2023-11-28. 20:21:30--><!--RSU-SPC-0093 The report specification was upgraded from "http://developer.cognos.com/schemas/report/14.0/" to "http://developer.cognos.com/schemas/report/15.0/" at 2018-9-28. 8:49:37--><!--RSU-SPC-0093 The report specification was upgraded from "http://developer.cognos.com/schemas/report/13.3/" to "http://developer.cognos.com/schemas/report/14.0/" at 2017-1-26. 15:28:50-->

<drillBehavior/>

<layouts>
<layout>
<reportPages>
<page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style>
<pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style>
<contents>

<table><style><defaultStyles><defaultStyle refStyle="tb"/></defaultStyles><CSS value="width:100%;border-spacing:0;height:100%"/></style><tableRows><tableRow><tableCells><tableCell><contents><table><style><defaultStyles><defaultStyle refStyle="tb"/></defaultStyles><CSS value="border-collapse:collapse;width:100%"/></style><tableRows><tableRow><tableCells><tableCell><contents><textItem><dataSource><staticValue>Revenue</staticValue></dataSource><style><CSS value="color:white"/></style></textItem></contents><style><CSS value="text-align:center;background-color:green;padding-top:10px;padding-bottom:0px"/></style></tableCell><tableCell><contents><textItem><dataSource><staticValue>Planned Revenue</staticValue></dataSource><style><CSS value="color:white"/></style></textItem></contents><style><CSS value="text-align:center;background-color:green;padding-top:10px"/></style></tableCell><tableCell><contents><textItem><dataSource><staticValue>Percent</staticValue></dataSource><style><CSS value="color:white"/></style></textItem></contents><style><CSS value="text-align:center;background-color:green;padding-top:10px"/></style></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><singleton name="Singleton1" refQuery="Query1">
<contents><textItem><dataSource><dataItemValue refDataItem="Revenue"/></dataSource><style><CSS value="font-size:14pt;font-weight:bold;color:white"/></style></textItem></contents>
</singleton></contents><style><CSS value="text-align:center;background-color:green;padding-bottom:10px"/></style></tableCell><tableCell><contents><singleton name="Singleton2" refQuery="Query1">
<contents><textItem><dataSource><dataItemValue refDataItem="Planned revenue"/></dataSource><style><CSS value="font-size:14pt;font-weight:bold;color:white"/></style></textItem></contents>
</singleton></contents><style><CSS value="text-align:center;background-color:green;padding-bottom:10px"/></style></tableCell><tableCell><contents><singleton name="Singleton3" refQuery="Query1">
<contents><textItem><dataSource><dataItemValue refDataItem="Percent"/></dataSource><style><dataFormat><percentFormat decimalSize="2"/></dataFormat><CSS value="font-size:14pt;font-weight:bold;color:white"/></style></textItem></contents>
</singleton></contents><style><CSS value="text-align:center;background-color:green;padding-bottom:10px"/></style></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><selectValue parameter="P_Prod" hideAdornments="true" required="false" autoSubmit="true" refQuery="Query2"><defaultSelections><defaultSimpleSelection>&apos;All Products&apos;</defaultSimpleSelection></defaultSelections><useItem refDataItem="Product line"/></selectValue></contents></tableCell><tableCell><contents><crosstab horizontalPagination="true" name="Crosstab1" refQuery="Query1">
<crosstabCorner>
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="xm"/>
</defaultStyles>
</style>
</crosstabCorner>


<noDataHandler>
<contents>
<block>
<contents>
<textItem>
<dataSource>
<staticValue>No Data Available</staticValue>
</dataSource>
</textItem>
</contents>
<style>
<CSS value="padding:16px;"/>
</style>
</block>
</contents>
</noDataHandler>
<style>
<CSS value="border-collapse:collapse"/>
<defaultStyles>
<defaultStyle refStyle="xt"/>
</defaultStyles>
</style>
<crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Product line"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Total(Product line)"><style><defaultStyles><defaultStyle refStyle="ol"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Total(Product line)"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="ov"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Revenue"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Planned revenue"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Percent"><style><defaultStyles><defaultStyle refStyle="cl"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Percent"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="cv"/></defaultStyles><dataFormat><percentFormat decimalSize="2"/></dataFormat></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns></crosstab></contents></tableCell><tableCell><contents/></tableCell></tableCells></tableRow></tableRows></table></contents><style><CSS value="height:100%;width:100%"/><defaultStyles><defaultStyle refStyle="ContentDataContainerBottomRowLeftTableCell"/></defaultStyles></style></tableCell></tableCells></tableRow></tableRows></table></contents>
</pageBody>

<pageFooter>
<contents>
<table>
<tableRows>
<tableRow>
<tableCells>
<tableCell>
<contents>
<date>
<style>
<dataFormat>
<dateFormat/>
</dataFormat>
</style>
</date>
</contents>
<style>
<CSS value="width:25%;text-align:left;vertical-align:top"/>
<defaultStyles><defaultStyle refStyle="FooterDateTableCell"/></defaultStyles></style>
</tableCell>
<tableCell>
<contents>
<pageNumber/>
</contents>
<style>
<CSS value="width:50%;text-align:center;vertical-align:top"/>
<defaultStyles><defaultStyle refStyle="FooterPageNumberTableCell"/></defaultStyles></style>
</tableCell>
<tableCell>
<contents>
<time>
<style>
<dataFormat>
<timeFormat/>
</dataFormat>
</style>
</time>
</contents>
<style>
<CSS value="width:25%;text-align:right;vertical-align:top"/>
<defaultStyles><defaultStyle refStyle="FooterTimeTableCell"/></defaultStyles></style>
</tableCell>
</tableCells>
</tableRow>
</tableRows>
<style>
<defaultStyles>
<defaultStyle refStyle="tb"/>
</defaultStyles>
<CSS value="width:100%;border-collapse:collapse;border-spacing:0px"/>
</style>
</table>
</contents>
<style>

<CSS value="padding:10px"/>
<defaultStyles><defaultStyle refStyle="FooterPageFooter"/></defaultStyles></style>
</pageFooter>
<pageHeader><contents><block>
<contents><textItem><dataSource><staticValue/></dataSource></textItem></contents>
<style><defaultStyles><defaultStyle refStyle="HeaderReportTitleTableCell"/></defaultStyles><CSS value="text-align:center"/></style></block></contents><style><defaultStyles><defaultStyle refStyle="ph"/></defaultStyles></style></pageHeader><XMLAttributes><XMLAttribute output="no" name="RS_legacyDrillDown" value="0"/></XMLAttributes></page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" output="no" value="true"/><XMLAttribute name="listSeparator" output="no" value=","/><XMLAttribute name="decimalSeparator" output="no" value="."/><XMLAttribute output="no" name="RS_modelModificationTime" value="2015-11-25T21:38:24.820Z"/></XMLAttributes><queries><query name="Query1"><source><model/></source><selection><dataItem aggregate="none" rollupAggregate="none" name="Product line"><expression>[Sales (query)].[Products].[Product line]</expression><XMLAttributes><XMLAttribute output="no" name="RS_dataType" value="3"/><XMLAttribute output="no" name="RS_dataUsage" value="0"/></XMLAttributes></dataItem><dataItem aggregate="total" name="Planned revenue"><expression>[Sales (query)].[Sales].[Planned revenue]</expression><XMLAttributes><XMLAttribute output="no" name="RS_dataType" value="2"/><XMLAttribute output="no" name="RS_dataUsage" value="2"/></XMLAttributes></dataItem><dataItem aggregate="total" name="Revenue"><expression>[Sales (query)].[Sales].[Revenue]</expression><XMLAttributes><XMLAttribute output="no" name="RS_dataType" value="2"/><XMLAttribute output="no" name="RS_dataUsage" value="2"/></XMLAttributes></dataItem><dataItem name="Percent" solveOrder="2"><expression>[Revenue] / [Planned revenue]</expression><XMLAttributes><XMLAttribute output="no" name="RS_dataType" value="2"/><XMLAttribute output="no" name="RS_dataUsage" value="2"/></XMLAttributes></dataItem><dataItemEdgeSummary solveOrder="1" refDataItem="Product line" aggregateMethod="total" name="Total(Product line)" label="Total"><XMLAttributes><XMLAttribute output="no" name="RS_dataType" value="3"/></XMLAttributes></dataItemEdgeSummary></selection><detailFilters><detailFilter use="optional"><filterExpression>[Sales (query)].[Products].[Product line] = ?P_Prod? or ?P_Prod? = &apos;All Products&apos;</filterExpression></detailFilter></detailFilters></query><query name="Query2">
<source>
<model/>
</source>
<selection><dataItem aggregate="none" rollupAggregate="none" name="Product line"><expression>[Sales (query)].[Products].[Product line]</expression><XMLAttributes><XMLAttribute output="no" name="RS_dataType" value="3"/><XMLAttribute output="no" name="RS_dataUsage" value="0"/></XMLAttributes></dataItem></selection>
</query></queries><classStyles><classStyle name="GuidedLayoutLeftPadding"><CSS value="padding-left:5px;border-top-width:1px;border-bottom-width:1px;border-left-width:1px;border-right-width:1px"/></classStyle><classStyle name="GuidedLayoutTopPadding"><CSS value="padding-top:5px;border-top-width:1px;border-bottom-width:1px;border-left-width:1px;border-right-width:1px"/></classStyle><classStyle name="GuidedLayoutRightPadding"><CSS value="padding-right:5px;border-top-width:1px;border-bottom-width:1px;border-left-width:1px;border-right-width:1px"/></classStyle><classStyle name="GuidedLayoutBottomPadding"><CSS value="padding-bottom:5px;border-top-width:1px;border-bottom-width:1px;border-left-width:1px;border-right-width:1px"/></classStyle><classStyle name="GuidedLayoutMargin"><CSS value="margin-bottom:10px"/></classStyle></classStyles><modelPath>/content/folder[@name=&apos;Samples&apos;]/folder[@name=&apos;Models&apos;]/package[@name=&apos;GO sales (query)&apos;]/model[@name=&apos;model&apos;]</modelPath><reportName>Percentage Test</reportName></report>

Cheers!

MF.

Hello,

Yes, I created it using the active report template.
My Cognos version is 11.0.13, I'm sorry for not saying this sooner.

Thank for your attention.

Quote from: Sonishados on 29 Nov 2023 02:11:29 PMHello,

Yes, I created it using the active report template.
My Cognos version is 11.0.13, I'm sorry for not saying this sooner.

Thank for your attention.

I think I understand what's going on now. You have an Active Report, and you are using Active Report Controls to filter the data in your Crosstab?

The way Active Reports work is that the data is queried from the data source and loaded into the report output at the time the output is generated. Whenever you use an Active Report Control to filter the data, it is not querying the database and re-running any calculations you may have - it is simply hiding or showing parts of the static data set within the report output. There are some rudimentary aggregation controls so that you can (for example) get totals to change within the output, but recalculating a percentage is beyond what they can do.

I think you are going to need to change your approach with this report. Instead of just having your crosstab which gets filtered, you are probably going to need to use a data deck (driven by the Site item), and master/detail your crosstab within this (based on Site). Each card of the deck would then show correct values for each of your sites. Your Active Report control (dropdown list) would then SELECT the data deck based on the Site variable it sets. You would also need to add a default card to the data deck (which appears when the Site active report variable is empty) to show all sites - this would need another version of the crosstab that remains unfiltered.

Cheers!

MF.