How to incorporate Drill up and Down in CROSS TAB Report based on user selectio

[rama83]

Hi,

I am using Cognos8.2 version with DMR (Hierarchies created by FM using Regular & Measure Dimension)

Requirement:

I need to create a crosstab report with one measure.Row and column are displayed by according to user selection.

EX:In rows we need to display  the following data items: Year, Quarter, Month, Week and Day according to user selection, similarly in Columns(Country,region,state,city,zipcode and gender) based on user selection(hierarchies level shown here;Year->Quarter->month->week->day,country->region->state->city->zipcode)

For the requirement I created two parameters with static choice with use and display values

And I created two data items one for row selection another for column selection

Row selection Data item:

Case when (? parameter1?=’year’) then (year)
         When (? parameter1?=’qtr’) then (Quarter)
         When (? parameter1?=’month’) then (month)
         â€¦â€¦
          ….
Similarly Column selection.

Once user select country/region/state/city/zip code in prompt page column selection we need to display related data in report.

Ex: if user select Country and Year in prompt page following data displaying

                             India                                   Unknown
              Amount Booked                      Amount Booked         
2008          100000                                          2005
2009         200000                                           1500


Here Year having 2008,2009 data and Country having India,Unknown.

Here I am able to display as per selection in report but I am not able to see drill down to display further levels.(Here I am enable the Drill up and drill down option in Data Menu)

Ex: if I click on India I am not getting report with Region wise data...similarly for other levels also.....


Please suggest me on above issue.

Regards,
Rama

[rama83]

Guys,
Please any one give me solution its very urgent...................

[uttam.mistry]

i think this is because you are using case statement, assuming you just want to select the level from user...
that can be done by use of macro... (i have also developed the similar report!!)

see the below example based on cognos sample database:

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/4.0/" expressionLocale="en-gb">
<modelPath>/content/package[@name='Great Outdoor Sales (cube)']/model[@name='2007-10-18T20:18:37.771Z']</modelPath>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItemMeasure name="Revenue"><dmMember><MUN>[great_outdoor_sales_en].[Measures].[Revenue]</MUN><itemCaption>Revenue</itemCaption></dmMember><dmDimension><DUN>[great_outdoor_sales_en].[Measures]</DUN><itemCaption>Measures</itemCaption></dmDimension></dataItemMeasure><dataItem name="YearPr"><expression>#'[great_outdoor_sales_en].[Years].[Years].[' + prompt('Parameter1','token','Year') + ']'#</expression></dataItem><dataItem name="RegionPr"><expression>#'[great_outdoor_sales_en].[Sales region].[Sales region].[' + prompt('Parameter2','token','Sales region') + ']'#

</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 horizontalPagination="true" name="Crosstab2" refQuery="Query1">
<crosstabCorner>
<contents><textItem><dataSource><dataItemLabel refDataItem="Revenue"/></dataSource></textItem></contents>
<style>
<defaultStyles>
<defaultStyle refStyle="xm"/>
</defaultStyles>
</style>
</crosstabCorner>


<style>
<CSS value="border-collapse:collapse"/>
<defaultStyles>
<defaultStyle refStyle="xt"/>
</defaultStyles>
</style>
<crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="YearPr" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="RegionPr" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><defaultMeasure refDataItem="Revenue"/></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>
<promptPages><page name="Prompt Page1">
<pageHeader>
<contents>
<block>
<contents>
<textItem>
<dataSource>
<staticValue/>
</dataSource>
<style>
<defaultStyles>
<defaultStyle refStyle="tt"/>
</defaultStyles>
</style>
</textItem>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="ta"/>
</defaultStyles>
</style>
</block>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="hp"/>
</defaultStyles>
</style>
</pageHeader>
<pageBody>
<contents><selectValue parameter="Parameter1"><selectOptions><selectOption useValue="Year"><displayValue>Year</displayValue></selectOption><selectOption useValue="Quarter"><displayValue>Quarter</displayValue></selectOption><selectOption useValue="Month"><displayValue>Month</displayValue></selectOption></selectOptions></selectValue><selectValue parameter="Parameter2"><selectOptions><selectOption useValue="Sales region"><displayValue>Sales region</displayValue></selectOption><selectOption useValue="Country"><displayValue>Country</displayValue></selectOption><selectOption useValue="Branch"><displayValue>Branch</displayValue></selectOption></selectOptions></selectValue></contents>
<style>
<defaultStyles>
<defaultStyle refStyle="py"/>
</defaultStyles>
</style>
</pageBody>
<pageFooter>
<contents>
<promptButton type="cancel">
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="bp"/>
</defaultStyles>
</style>
</promptButton>
<promptButton type="back">
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="bp"/>
</defaultStyles>
</style>
</promptButton>
<promptButton type="next">
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="bp"/>
</defaultStyles>
</style>
</promptButton>
<promptButton type="finish">
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="bp"/>
</defaultStyles>
</style>
</promptButton>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="fp"/>
</defaultStyles>
</style>
</pageFooter>
<style>
<defaultStyles>
<defaultStyle refStyle="pp"/>
</defaultStyles>
</style>
</page></promptPages></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><drillBehavior drillUpDown="true" modelBasedDrillThru="false"/></report>
 

[uttam.mistry]

just to let you know that if you are not able to find the the data item mapping i.e. [Model].[..].[Dimension].[Level]... drag that level to Detail filter section, it will show you the model path for that level...

what i m doing here is dynamically creating the reference for data item.
using
#'[great_outdoor_sales_en].[Years].[Years].[' + prompt('Parameter1','token','Year') + ']'#

so if user selects Year it will query
[great_outdoor_sales_en].[Years].[Years].[Year]
when Month  it will query
[great_outdoor_sales_en].[Years].[Years].[Month]

so make sure you pass correct level name from prompt



hope this helps

[rama83]

Thanks  lot yar,its very useful for me.
Above mentioned thing working only when query items are coming for same hierarchies.
If the query items are coming from different hierarchies like Product,Retailer Site,Sales Staff,order.........
how we can write a query.
User Requirements:We need to use two prompts one for row selection another column selection for crosstab report.
In the Column selection prompt query items are coming different hierarchies.

For Ex:-I have a parameter:Column_selection         
In this parameter user can select query items those are coming from different Hierarchies.

use value                                           Display Value
Product Line(Product Hierarchies)                          Product Line
Product Type(Product Hierarchies)                         Product Type
Sales Region(Sales Staff Hierarchies)                      Sales Region                         
Country(Sales Staff Hierarchies)                             Country

How we can show the hierarchies in CrossTab for above requirement.

Please any one suggest me.
Thanks in Advance.





                 

[uttam.mistry]

its simple... you need to understand what we are doing here...

in that case, from prompts, instead of returning (i.e. use value) level name, return full path for level

so create prompt like

display value = Product Line
use value [package].[Product].[Product].[Product Line]

display value = Country
use value [package].[Region].[Sales Region].[Country]


change macro as
#prompt('Parameter1','token','[package].[Product].[Product].[Product Line]')#

[rama83]

Thanks Lot Yar,I got the solutions................