COGNOiSe.com - The IBM Cognos Community

Hi All,
I am facing problem with TOPCOUNT function in AR. I am using dimentional model.
My requirement is

If I select a particular [District] then it will shows its top 20 [Coworkes] for its [Sales]
So for that I am using a District DATA DECK which is going to filter a LIST having master detail relationship with a CHART. The CHART will shows the TOP 20 Coworkes's Sales.

I am using the function TopCount( [Coworker],20,[Sales]), I am getting top 20 Coworkers but not group by district.

So if I select a district in the DECK, It wont show the top 20 Coworker to that particular District.

Please help me .
 

Hi,

By the sounds of it you simply need to create a master-detail between the data deck and the list based on District?

MF.

Hi MF,

I already create the relationship but its not showing the top 20 to that particular District.

It shows like : 1st it ll filter out the top 20 Coworker ( present in All District), then the masterdetail relation applies..

Hmmm. I just put together a simple example - a data deck driven by my Region level, a list on the data deck with a calculated item topCount([My Products level],20,[My Revenue measure]) and I added the Region level as an extra item to the query of the list. I linked the list to the data deck via a master-detail based on the Region level item, then added a data radio button group based on my Region level and set up a connection between this and my data deck.

it works as I expect - every card shows the top 20 products for that region.

You must have done something wrong somewhere? Check your master-detail is actually there.

Here is my report spec:

<report xmlns="http://developer.cognos.com/schemas/report/11.0/" useStyleVersion="10" expressionLocale="en-us" application="true">
            <modelPath>/content/package[@name=&apos;GOS_en_Parameter_Map&apos;]/model[@name=&apos;model&apos;]</modelPath>
            <drillBehavior/>
            <layouts>
               <layout>
                  <defaultDataFormats>
                     <currencyFormat sign="-"/>
                  </defaultDataFormats>
                  <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><appDataRadioButtonGroup name="Data Radio Button Group1" refQuery="Query3">
      <appRadioButtonDefinition><appRadioButtonLabel refDataItem="Sales region"/></appRadioButtonDefinition><appOnSelectSetVariableValues><appSetVariableValueToDataItemValue refDataItem="Sales region" refAppVariable="vSales region"/></appOnSelectSetVariableValues><appContainerSelect><appCondition><appConditionDataItemComparison refDataItem="Sales region" refAppVariable="vSales region" operator="in"/></appCondition></appContainerSelect></appDataRadioButtonGroup></contents></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><appDataDeck width="100%" name="Data Deck1" refQuery="Query1">
         <appDataCard>
            <contents><list horizontalPagination="true" name="List1" refQuery="Query2">
         
         
         
         <noDataHandler>
            <contents>
               <block>
                  <contents>
                     <textItem>
                        <dataSource>
                           <staticValue>No Data Available</staticValue>
                        </dataSource>
                        <style>
                           <CSS value="padding:10px 18px;"/>
                        </style>
                     </textItem>
                  </contents>
               </block>
            </contents>
         </noDataHandler>
         <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="Top 20 Products"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Top 20 Products"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Revenue"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Revenue"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns><masterDetailLinks><masterDetailLink><masterContext><dataItemContext refDataItem="Sales region"/></masterContext><detailContext><dataItemContext refDataItem="Sales region"/></detailContext></masterDetailLink></masterDetailLinks></list></contents>
         </appDataCard>
      <appCardDefinition><appCardValues><appCardValue refDataItem="Sales region"/></appCardValues></appCardDefinition><appContainerSelect><appCondition><appConditionDataItemComparison refDataItem="Sales region" refAppVariable="vSales region" operator="in"/></appCondition></appContainerSelect></appDataDeck></contents></tableCell></tableCells></tableRow></tableRows></table></contents>
                        </pageBody>
                     </page>
                  </reportPages>
               </layout>
            </layouts>
         <XMLAttributes><XMLAttribute output="no" name="RS_CreateExtendedDataItems" value="true"/><XMLAttribute output="no" name="listSeparator" value=","/><XMLAttribute output="no" name="RS_modelModificationTime" value="2014-04-23T13:22:51.913Z"/></XMLAttributes><queries><query name="Query1"><source><model/></source><selection><dataItemLevelSet name="Sales region"><dmLevel><LUN>[great_outdoors_sales_en].[Sales regions].[Sales regions].[Sales region]</LUN><itemCaption>Sales region</itemCaption></dmLevel><dmDimension><DUN>[great_outdoors_sales_en].[Sales regions]</DUN><itemCaption>Sales regions</itemCaption></dmDimension><dmHierarchy><HUN>[great_outdoors_sales_en].[Sales regions].[Sales regions]</HUN><itemCaption>Sales regions</itemCaption></dmHierarchy></dataItemLevelSet></selection></query><query name="Query2"><source><model/></source><selection><dataItem name="Top 20 Products"><expression>topCount ([great_outdoors_sales_en].[Products].[Products].[Product],20,[Revenue])</expression></dataItem><dataItemMeasure name="Revenue"><dmMember><MUN>[great_outdoors_sales_en].[Measures].[Revenue]</MUN><itemCaption>Revenue</itemCaption></dmMember><dmDimension><DUN>[great_outdoors_sales_en].[Measures]</DUN><itemCaption>Measures</itemCaption></dmDimension><XMLAttributes><XMLAttribute output="no" name="RS_dataType" value="9"/></XMLAttributes></dataItemMeasure><dataItemLevelSet name="Sales region"><dmLevel><LUN>[great_outdoors_sales_en].[Sales regions].[Sales regions].[Sales region]</LUN><itemCaption>Sales region</itemCaption></dmLevel><dmDimension><DUN>[great_outdoors_sales_en].[Sales regions]</DUN><itemCaption>Sales regions</itemCaption></dmDimension><dmHierarchy><HUN>[great_outdoors_sales_en].[Sales regions].[Sales regions]</HUN><itemCaption>Sales regions</itemCaption></dmHierarchy></dataItemLevelSet></selection></query><query name="Query3"><source><model/></source><selection><dataItemLevelSet name="Sales region"><dmLevel><LUN>[great_outdoors_sales_en].[Sales regions].[Sales regions].[Sales region]</LUN><itemCaption>Sales region</itemCaption></dmLevel><dmDimension><DUN>[great_outdoors_sales_en].[Sales regions]</DUN><itemCaption>Sales regions</itemCaption></dmDimension><dmHierarchy><HUN>[great_outdoors_sales_en].[Sales regions].[Sales regions]</HUN><itemCaption>Sales regions</itemCaption></dmHierarchy></dataItemLevelSet></selection></query></queries><appProperties><appVariables><appVariable name="vSales region"/></appVariables></appProperties></report>

MF.

Hi MF,

Thanks for your reply, But my requirement is little bit different.

I have a prompt Drop-Down List having 4 values -- Country, State, Districts, Regions

Below that I have a DECK with these 4 data Items in radio button Select. then depending upon the selected value my Chart will shows the Top 10 Co-Workers sales.