Top ten Suppliers report with 'OTHERS' as 11 th Rank

[ipmurali]

Hi all,

I need to create a report which shows total 11 rows.
The first 10 rows having top ten suppliers, and the 11th row as 'Others' having the sum of all other suppliers.

Hope it is clear what I am looking for.

Thanks and Regards,
ipm

[MFGF]

Try the following:

1. Add a new query calculation to the list to determine the rank - rank([your measure value] for report)
2. Add a second calculation called BestWorst with the expression if([Rank] <= 10) then ('Best 10') else ('Others')
3. Group on the BestWorst column.
4. Total your measure value using the Aggregate button.
5. Go to Condition Explorer and select the Variables folder.
6. Drag in a boolean variable and set its ecpression to [Query1].[BestWorst] = 'Best 10'
7. back on your report page, click on any of the list columns and use the Ancestor button to select 'List Columns Body Style'.
8. Go to the Style Variable property and select your boolean variable.
9. Hover over Condition Explorer and select the 'No' variable value.
10. Set the Box Type property to 'None'
11. Remove the variable value selection and cut the BestWorst column from the list.

Does this give you the result you are looking for?

MF.

[Nuffsaid]

Hi Guys,

I'm having the exact same issue as imurali but with a bit of a twist.

MF, that's a pretty slick solution for the list, but in my scenario I need to take it one step further.

I need to render the results (Top 10 + Others, or 11 "slices" if you will.. ) in a Pie Chart also. Any ideas?
For the life of me I can't make it happen. I've tried just about every method I can think of.

BTW I'm using a relational package. 8.3

Look forward to your reply...

Nuffsaid.

[MFGF]

Hi,

If the conditional hiding doesn't work for your chart (and I suspect it will not), plan B is to set up two queries - one filtered on just the top 10, and another filtered on everyone not in the top 10 (but just a summary), then UNION them together and use this to drive your pie chart.

Regards,

MF.

[Nuffsaid]

Hey Again,

Yea the union thing looks good on paper, but still can't get it to work.

Couple of issues I came across...

1) I can't get the "Other" query to filter based on [Rank]>10 (with the property set to after aggregation)
    Don't know what's up with that???????

2) Changing the actual product names to "Other" and getting a total so we can do a union.

I've tried cross joins to eliminate the top 10 from the "Other" query and this worked but I still have the naming / grouping of the "Others" issue. I've tried sub queries, I've tried bla bla bla.....
Not much hair left now........

Any other thoughts??

Nuffsaid.

[Nuffsaid]

Update,

Okay so Cognos is messin with my mind (again) .....

So it seems the [Rank]>10 filter is working, but if you drop the Rank data item on the report the top product will be ranked as # 1 NOT #11. How bizzarre is that??

Carry on......

Nuffsaid.

[rockytopmark]

Rank by definition will give 1 to the highest measure, so unless I am misunderstanding something, it is working as expected.

What I have done to make this work in that past, is create a query, #1 that does the Grouping and Ranking, which I am assuming you have done already.

Create a 2nd query and use the query #1 as a reference source for #2.  It will have 2 items when done.
Drag in the Measure from query #1
Add a new data item to query #2, and call it Pie Slice (or whatever you desire). 
Pie Slice's expression will use an if_then_else, such as:

If([query1].[rank item] < 11)
Then([query1].[Supplier Name])
Else('Others')

...to create your 11 unique values for which the Pie will render a slice and aggregate your measure.

There is no filtering desired or necessary, since you aren't desiring to eliminate any of your measureables, just group them differently.

[Nuffsaid]

EUREKA !!

Using sub queries so I could rename the products ranked less than 11 and then doing a union I was able to achieve the desired result set that could be rendered within the chart.

Thanks MF, your suggestion made me revisit some of things I had tried in the past and with a little perseverance, things worked out!!   ;D

Nuffsaid.

[Nuffsaid]

Hey Rocky,

Tried your method many many moons ago....

Although I could get it to work in the list using grouping, it will not render in a chart.

Nuffsaid.

[muraliparamu]

Thanks MF
It is working fine.

ipmurali
(I tried to change my email id in my ipmurali profile,
i got a validation link on new mail id,
But when try to validate it is giving error. So I could not use my ipmurali user name
who can help me to rectify this problem)

[MFGF]

EUREKA !!

Using sub queries so I could rename the products ranked less than 11 and then doing a union I was able to achieve the desired result set that could be rendered within the chart.

Thanks MF, your suggestion made me revisit some of things I had tried in the past and with a little perseverance, things worked out!!   ;D

Nuffsaid.

Thanks for the update - sorry not to have been back on before now to assist with the issues.  Kudos for persevering and finding the solution! :)

MF.

[MFGF]

Thanks MF
It is working fine.

ipmurali
(I tried to change my email id in my ipmurali profile,
i got a validation link on new mail id,
But when try to validate it is giving error. So I could not use my ipmurali user name
who can help me to rectify this problem)

Hi,

I had issues with my account a few weeks ago when I updated my email address - the validation email did not arrive, so I ended up locked out of my account.  One of the Cognoise Admins from BSP kindly sorted things out for me.  I'd suggest you send a private message to one of the site administrators (Dustpanman, for example) explaining what happened and asking him to re-activate your original account.

If I had the ability, I would happily do this for you, but unfortunately I am not a site administrator, so have no facility to manage accounts etc.

Good luck!

MF.

[ipmurali]

Thanx MF,
I already contacted site admin, he rectified the issue.

thanks again.

[ipmurali]

EUREKA !!

Using sub queries so I could rename the products ranked less than 11 and then doing a union I was able to achieve the desired result set that could be rendered within the chart.

Nuffsaid.

Nuffsaid,
Could you explain more detail (step by step), how you achieved... this in chart.

thanks in advance

[Nuffsaid]

Sorry for the delay,

I tried to apply my solution to the samples in 8.3 and although I could get the list to work the chart would not render. I received an Oracle error ORA-12704: character set mismatch

Anyway here's the report for 8.3 without the chart. Play around with it.

Nuffsaid

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/4.0/" expressionLocale="en-ca">
<modelPath>/content/package[@name='GO Sales (query)']/model[@name='2008-05-08T19:10:47.180Z']</modelPath>
<queries>
<query name="Top 10 Data">
<source>
<model/>
</source>
<selection><dataItem name="Retailer name" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Sales].[Retailer name]</expression></dataItem><dataItem name="Quantity" aggregate="total"><expression>[Sales (query)].[Sales].[Quantity]</expression></dataItem><dataItem name="Rank"><expression>rank([Quantity])</expression></dataItem></selection>
<detailFilters><detailFilter><filterExpression>[Rank]&lt;11</filterExpression></detailFilter></detailFilters></query>
<query name="Others Data">
<source>
<model/>
</source>
<selection><dataItem name="Retailer name" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Sales].[Retailer name]</expression></dataItem><dataItem name="Quantity" aggregate="total"><expression>[Sales (query)].[Sales].[Quantity]</expression></dataItem><dataItem name="Rank"><expression>rank([Quantity])</expression></dataItem></selection>
<detailFilters><detailFilter><filterExpression>[Rank]&gt;=11</filterExpression></detailFilter></detailFilters></query><query name="Top 10">
<source>

<queryRef refQuery="Top 10 Data"/></source>
<selection><dataItem name="Retailer name"><expression>[Top 10 Data].[Retailer name]</expression></dataItem><dataItem name="Quantity" aggregate="total"><expression>[Top 10 Data].[Quantity]</expression></dataItem></selection>
</query><query name="Others">
<source>

<queryRef refQuery="Others Data"/></source>
<selection><dataItem name="Retailer name"><expression>'others'</expression></dataItem><dataItem name="Quantity" aggregate="total"><expression>[Others Data].[Quantity]</expression></dataItem></selection>
</query><query name="Query1">
<source>

<queryOperation name="Union1" setOperation="UNION" duplicates="preserve">
<queryRefs><queryRef refQuery="Top 10"/><queryRef refQuery="Others"/></queryRefs>

<projectionList autoGenerated="true"><queryItem name="Retailer name"/><queryItem name="Quantity"/></projectionList></queryOperation></source>
<selection><dataItem name="Retailer name"><expression>[Union1].[Retailer name]</expression></dataItem><dataItem name="Quantity"><expression>[Union1].[Quantity]</expression></dataItem></selection>
</query></queries>
<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="border-collapse:collapse;width:100%"/></style><tableRows><tableRow><tableCells><tableCell><contents>
</contents></tableCell><tableCell><contents><list horizontalPagination="true" name="List41" refQuery="Query1">



<style>
<CSS value="border-collapse:collapse"/>
<defaultStyles>
<defaultStyle refStyle="ls"/>
</defaultStyles>
</style>
<listColumns><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Retailer name"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Retailer name"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Quantity"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Quantity"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns></list></contents></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><list horizontalPagination="true" name="List2" refQuery="Top 10">



<style>
<CSS value="border-collapse:collapse"/>
<defaultStyles>
<defaultStyle refStyle="ls"/>
</defaultStyles>
</style>
<listColumns><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Retailer name"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Retailer name"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Quantity"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Quantity"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns></list></contents></tableCell><tableCell><contents><list horizontalPagination="true" name="List3" refQuery="Others">



<style>
<CSS value="border-collapse:collapse"/>
<defaultStyles>
<defaultStyle refStyle="ls"/>
</defaultStyles>
</style>
<listColumns><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Quantity"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Quantity"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns></list></contents></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents/></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><list horizontalPagination="true" name="List11" refQuery="Top 10 Data">



<style>
<defaultStyles>
<defaultStyle refStyle="ls"/>
</defaultStyles>
<CSS value="border-collapse:collapse"/>
</style>
<listColumns><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Retailer name"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Retailer name"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Quantity"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Quantity"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Rank"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Rank"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns><sortList><sortItem refDataItem="Rank" sortOrder="ascending"/></sortList></list></contents></tableCell><tableCell><contents><list horizontalPagination="true" name="List1" refQuery="Others Data">



<style>
<CSS value="border-collapse:collapse"/>
<defaultStyles>
<defaultStyle refStyle="ls"/>
</defaultStyles>
</style>
<listColumns><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Retailer name"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Retailer name"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Quantity"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Quantity"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Rank"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Rank"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns></list></contents></tableCell></tableCells></tableRow></tableRows></table></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>
<classStyles>
<classStyle name="pd_1" label="Excellent">
<CSS value="background-color:#009933; color:#FFFFFF;"/>
</classStyle>
<classStyle name="pd_2" label="Very good">
<CSS value="background-color:#FFFFFF; color:#009933;"/>
</classStyle>
<classStyle name="pd_3" label="Average">
<CSS value="background-color:#FFFFFF; color:#CC9900;"/>
</classStyle>
<classStyle name="pd_4" label="Below average">
<CSS value="background-color:#FFFFFF; color:#990000;"/>
</classStyle>
<classStyle name="pd_5" label="Poor">
<CSS value="background-color:#990000; color:#FFFFFF;"/>
</classStyle>
</classStyles>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/></XMLAttributes></report>