Conditional highlighting in DMR Report

[ydeliwala]

I am struggling with conditional highlighting on a DMR report and would appreciate all the help.

I am on 8.4 version and have the report xml below the post from the GO Sales (analysis) package.

Here is the problem summary:

I have a simple crosstab with Years from Time dimension in the columns and Planned Revenue as measure. On the rows I am not pulling all the levels but pulling individual members. I have Cities Boston and Los Angeles and the country United States from the Sales Staff Dimension. There is no nesting between country and city.

I want to highlight the measure cell for a city when its planned revenue for a given year is less than 40% of the country revenue.

For Eg. Boston's planned revenue for 2004 is $25,000 and United States' planned revenue for 2004 is $100,000, Boston's measure cell (intersection tuple of Boston,2004) should be highlighted since $25,000 is less that 40% of $100,000 (ie $40,000)

Thanks!
Yo!

<report xmlns="http://developer.cognos.com/schemas/report/6.0/" expressionLocale="en">
   <modelPath>/content/folder[@name='Samples']/folder[@name='Models']/package[@name='GO Sales (analysis)']/model[@name='model']</modelPath>
   <drillBehavior modelBasedDrillThru="true"/>
   <queries>
      <query name="Query1">
         <source>
            <model/>
         </source>
         <selection><dataItemLevelSet name="Year"><dmDimension><DUN>[Sales (analysis)].[Time dimension]</DUN><itemCaption>Time dimension</itemCaption></dmDimension><dmHierarchy><HUN>[Sales (analysis)].[Time dimension].[Time dimension]</HUN><itemCaption>Time dimension</itemCaption></dmHierarchy><dmLevel><LUN>[Sales (analysis)].[Time dimension].[Time dimension].[Year]</LUN><itemCaption>Year</itemCaption></dmLevel></dataItemLevelSet><dataItemMeasure name="Planned revenue"><dmMember><MUN>[Sales (analysis)].[Sales].[Planned revenue]</MUN><itemCaption>Planned revenue</itemCaption></dmMember><dmDimension><DUN>[Sales (analysis)].[Sales]</DUN><itemCaption>Sales</itemCaption></dmDimension></dataItemMeasure><dataItemMember name="Los Angeles"><dmMember><MUN>[Sales (analysis)].[Sales staff].[Sales staff].[City]-&gt;[all].[710].[1003].[21]</MUN><itemCaption>Los Angeles</itemCaption></dmMember><dmDimension><DUN>[Sales (analysis)].[Sales staff]</DUN><itemCaption>Sales staff</itemCaption></dmDimension><dmHierarchy><HUN>[Sales (analysis)].[Sales staff].[Sales staff]</HUN><itemCaption>Sales staff</itemCaption></dmHierarchy></dataItemMember><dataItemMember name="Boston"><dmMember><MUN>[Sales (analysis)].[Sales staff].[Sales staff].[City]-&gt;[all].[710].[1003].[19]</MUN><itemCaption>Boston</itemCaption></dmMember><dmDimension><DUN>[Sales (analysis)].[Sales staff]</DUN><itemCaption>Sales staff</itemCaption></dmDimension><dmHierarchy><HUN>[Sales (analysis)].[Sales staff].[Sales staff]</HUN><itemCaption>Sales staff</itemCaption></dmHierarchy></dataItemMember><dataItemMember name="United States"><dmMember><MUN>[Sales (analysis)].[Sales staff].[Sales staff].[Country]-&gt;[all].[710].[1003]</MUN><itemCaption>United States</itemCaption></dmMember><dmDimension><DUN>[Sales (analysis)].[Sales staff]</DUN><itemCaption>Sales staff</itemCaption></dmDimension><dmHierarchy><HUN>[Sales (analysis)].[Sales staff].[Sales staff]</HUN><itemCaption>Sales staff</itemCaption></dmHierarchy></dataItemMember></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>
                     <crosstab refQuery="Query1" horizontalPagination="true" name="Crosstab1">
                        <style>
                           <defaultStyles>
                              <defaultStyle refStyle="xt"/>
                           </defaultStyles>
                           <CSS value="border-collapse:collapse"/>
                        </style>
                        <crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Year" edgeLocation="e5"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabCorner><contents><textItem><dataSource><dataItemLabel refDataItem="Planned revenue"/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="xm"/></defaultStyles></style></crosstabCorner><defaultMeasure refDataItem="Planned revenue"/><crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Boston" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Los Angeles" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="United States" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows></crosstab>
                  </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>
   <XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/></XMLAttributes></report>

[MFGF]

Hi,

I don't have time to try this right now, but you could see how far you get with the following approach:

1. Add a new query item  - Boston Revenue - tuple(currentMember([Your time hierarchy]),[Your Boston Member],[Your revenue measure])
2. Add a second one as above but for LA
3. Add a third item as above but for United States
4. Define a boolean variable with the expression [Query1].[Boston Revenue] < [Query1].[United States Revenue] * 0.4
5. Select your Boston row heading, then right-click and 'select member fact cells'
6. Apply the variable in the Style Variable property and select the Yes value and format as desired.
7. Repeat steps 4 to 6 for LA
8. Select the Year column heading, and define Boston Revenue, LA Revenue and United States revenue as properties (use the Properties property).

Hope I haven't forgotten any critical steps - this is all off the top of my head!

MF.

[ydeliwala]

Thanks MF!

I had to change the calc data item a little bit since I had the Year level from the Time dimension and not the hierarchy itself. I modified the item as  tuple(currentMember(hierarchy([Year])),[Boston],[Planned revenue]).

My only concern is if I have more than 50 rows in the report I will have to create that many data items and variables to achieve the result. Do you think if there is any other quicker way to achieve the result like creating member set for the cities?

For e.g. cities Boston, LA, Chicago, Denver etc they all compare their planned revenue to that of country USA. Is there a way to create a member set for all the cities and then create only one calculated data item pulling intersection tuple from the city member set, year & measure and compare it with that of Country tuple?

Yo!