Quote from: Sonishados on Yesterday at 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.

Quote from: MFGF on Yesterday at 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: dougp on 27 Nov 2023 10:12:29 AMEasy enough.

Your target report will have a query that gets all of the customers for the various filters, including product line because it's related to what you clicked.  Then you'll join that (on customer) query to another query that does NOT filter by product line in order to get all of the transactions for those customers.

Thank you!  I should have thought of this solution...much appreciated!

Quote from: cognostechie on 24 Nov 2023 10:52:33 PMYou have changed the scenario completely. That being said, everything is possible but if you have already decided that you want to give up then I am not going to make any efforts to give you the solution. 

I tried to simplify my example as best I can, and in doing so I used a bad example of transaction ID, though that really doesn't change the context of my question.  I intuited, apparently incorrectly, that you were saying it couldn't be done (your first paragraph says that the summary report doesn't have transaction ID within its granularity).  Apologies for the apparent offense I have caused you...

Quote from: Sonishados on Yesterday at 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: Sonishados on Yesterday at 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.

I am looking for a way to block the name suggestions when using "Send Report by Email" as it leaks user names from across all projects. (We don't actually store the email addresses, so this cannot be used to send emails across project boundaries.) 

I would be happy just requiring the sender to always supply the complete email address (e.g. "who@where.com") rather than having the To field prompt with the full name (e.g. "Doctor Who" when I type "Do") when I might not even have known Doctor Who existed, and when Doctor Who is not in my project.

Is there a config setting for this?

Using Cognos 11.1

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 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.

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.