How to drilthrough form lowest level?

[bvk.cognoise]

Hi Mate,

I need small help in drill through.

i am using cube as souce,my requirement is i have a parent report which is developed using cube it has four levels
for eg: region>country>state>branch

i have another taget report which has only one level
for eg:branch

no my requirement is in my target report when drill down to lowest level(branch) if click on particular branch it should give output only for that branch in jy target report.but i dont wan to enable this drill through in all levels.only i want to drillthrough to another report when i click on branch.

so could you please help me how to achieve this.

Thanks
bvk

[bvk.cognoise]

Hi Mates,

Any suggesions please.

Thanks
bvk

[MFGF]

Hi,

I was involved in implementing this in a report a while ago - the requirement was that clicking on the hyperlinks displayed on levels 1, 2 and 3 would implement drill-down, and clicking on the hyperlinks displayed on level 4 would implement drill-through.

When adding the drill-through definition, the default behaviour after adding drill-through to a hierarchy is drill-through, which forces the user to 'right click/drill-down' at each level and then just click to drill-through, which is clumsy and prone to error as the user could drill-through at other than the lowest level. The implementation was to calculate the level, and use this to drive a conditional block with the same hierarchy field, but at level 4 the field displayed has a drill-through definition. User behaviour is to click on an underlined member and the report drills down until level4 and then drills-through to the report.  The expression to work out the level was

      ordinal(level(item([FOURTH_HIERARCHY_LEVEL],0)))

Regards,

MF.

[bvk.cognoise]

Hi MFGF,

Thank you very much ...... :)

Thanks
bvk

[leahmarie]

Sorry to comment on an old already done thread - but MFGF - is there anyway you could elaborate on how you accomplished this?  I have had so many requests to do this and this is the first time I have researched and found anyone who has been the least bit successful.

Thanks so much.

[MFGF]

With pleasure!

My example has Product info in the rows of my crosstab and is based on the sample great_outdoors_sales_en powercube

1. Create a crosstab. Populate the rows with the Product Line level, the columns with the Year level and the measures with Revenue.
2. Add a new calculated item called CurrLevel to the query with the expression ordinal(level(item([Product Line level from the query],0)))   - this returns the level you are currently seeing from the Products hierarchy ie 0 for the All Products level, 1 for the Product Line level, 2 for the Product Type level, 3 for the Product level.
3. Unlock the report structure.
4. Drag a conditional block inside the existing crosstab row heading.
5. In the Block Variable property of the conditional block, define a new boolean variable with the expression [Query1].[CurrLevel] = 3
6. Make sure the Current Block property of the conditional block is set to "No", then drag the Product Line item (in the row header) inside the conditional block.
7. From the menubar, select Data > Drill behavior and enable "Allow drill-up and drill-down"
8. Set the Current Block property of the conditional block to "Yes"
9. From the Data Items tab, drag the Product line item into the conditional block
10. Click on the Product line item within the conditional block (make sure the properties say "Text item") and define the drill-through definition as desired.
11. Re-lock the structure of the report
12. Select the crosstab row heading (Crosstab Node Member) and go to the Properties property - add CurrLevel as a property.
13. Save. Run. Test. Enjoy. :)

If you need it, the report spec is below.

Cheers!

MF.

<report xmlns="http://developer.cognos.com/schemas/report/11.0/" useStyleVersion="10" expressionLocale="en-us">
            <modelPath>/content/folder[@name=&apos;Samples&apos;]/folder[@name=&apos;PowerPlay&apos;]/package[@name=&apos;great_outdoors_sales_en&apos;]/model[@name=&apos;2010-06-18T15:38:44.109Z&apos;]</modelPath>
            
            <queries>
               <query name="Query1">
                  <source>
                     <model/>
                  </source>
                  <selection><dataItemLevelSet name="Year"><dmLevel><LUN>[great_outdoors_sales_en].[Years].[Years].[Year]</LUN><itemCaption>Year</itemCaption></dmLevel><dmDimension><DUN>[great_outdoors_sales_en].[Years]</DUN><itemCaption>Years</itemCaption></dmDimension><dmHierarchy><HUN>[great_outdoors_sales_en].[Years].[Years]</HUN><itemCaption>Years</itemCaption></dmHierarchy></dataItemLevelSet><dataItemLevelSet name="Product line"><dmLevel><LUN>[great_outdoors_sales_en].[Products].[Products].[Product line]</LUN><itemCaption>Product line</itemCaption></dmLevel><dmDimension><DUN>[great_outdoors_sales_en].[Products]</DUN><itemCaption>Products</itemCaption></dmDimension><dmHierarchy><HUN>[great_outdoors_sales_en].[Products].[Products]</HUN><itemCaption>Products</itemCaption></dmHierarchy></dataItemLevelSet><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><dataItem name="CurrLevel"><expression>ordinal(level(item([Product line],0)))</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>
                              <crosstab refQuery="Query1" horizontalPagination="true" name="Crosstab1">
                                 <crosstabCorner><style><defaultStyles><defaultStyle refStyle="xm"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Revenue"/></dataSource></textItem></contents></crosstabCorner>
                                 
                                 
                                 <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>
                                    <defaultStyles>
                                       <defaultStyle refStyle="xt"/>
                                    </defaultStyles>
                                    <CSS value="border-collapse:collapse"/>
                                 </style>
                              <crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Year" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Product line" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><conditionalBlocks>
         <conditionalBlockDefault>
            <contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents>
         </conditionalBlockDefault>
      <conditionalBlockCases refVariable="Level3"><conditionalBlock refVariableValue="1"><contents><textItem><dataSource><memberCaption/></dataSource><reportDrills><reportDrill name="Drill-Through Definition1"><drillLabel><dataSource><staticValue/></dataSource></drillLabel><drillTarget><reportPath path="/content/folder[@name=&apos;Samples&apos;]/folder[@name=&apos;Models&apos;]/package[@name=&apos;GO Sales (query)&apos;]/report[@name=&apos;ProdNoDrillTarget&apos;]"><XMLAttributes><XMLAttribute output="no" name="ReportName" value="ProdNoDrillTarget"/><XMLAttribute output="no" name="class" value="report"/></XMLAttributes></reportPath><drillLinks><drillLink><drillTargetContext><parameterContext parameter="p"/></drillTargetContext><drillSourceContext><dataItemContext refDataItem="Product line"/></drillSourceContext></drillLink></drillLinks></drillTarget></reportDrill></reportDrills><style><defaultStyles><defaultStyle refStyle="hy"/></defaultStyles></style></textItem></contents></conditionalBlock></conditionalBlockCases></conditionalBlocks></contents><propertyList><propertyItem refDataItem="CurrLevel"/></propertyList></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><defaultMeasure refDataItem="Revenue"/><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell></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 output="no" name="RS_CreateExtendedDataItems" value="true"/><XMLAttribute output="no" name="listSeparator" value=","/><XMLAttribute output="no" name="RS_modelModificationTime" value="2010-06-18T19:38:44.127Z"/></XMLAttributes><reportVariables><reportVariable type="boolean" name="Level3">
         <reportExpression>[Query1].[CurrLevel] = 3</reportExpression>
         <variableValues>
            <variableValue value="1"/>
         </variableValues>
      </reportVariable></reportVariables><reportName>Drill down through</reportName><drillBehavior drillUpDown="true"/></report>

[leahmarie]

This is absolutely wonderful.  I'm sorry for the long time on my response.  I went on vacation and then forgot to check back for a couple of weeks.  I truly appreciate the time you took out!

[cognostechie]

Good work MFGF !

[Dennis1587]

Thanks a lot!!!!