Getting Hierarchy in Join

[mel4363]

Hi Everyone,

I am reporting of a SQL Database, using Report Studio Version 10.2.1.

I have two queries in my report joined using vehicle registration numbers. My question now is is it possible to get a hierarchy into the join ? I want this for two reasons, one is to use it for drill-down/up and the other is for drill-through at any level in the hierarchy to a detail report.

Any help is much appreciated

Thanks

Mel

[Francis aka khayman]

you can only create hierarchy in Transformer Cube, Framework Manager model, (TM1, dynamic cubes as well but i'm not familiar very familiar of those). you cannot create hierarchy in a report

[mel4363]

you can only create hierarchy in Transformer Cube, Framework Manager model, (TM1, dynamic cubes as well but i'm not familiar very familiar of those). you cannot create hierarchy in a report

I do have the hierarchy. It was created in Framework manager (Sorry for not having clarified this).. The challenge is when I add it into the report (After joining the two queries) it turns it into a data Item. Disabling me to use it in the way I want.

[Francis aka khayman]

ok. you might want to take a step back and examine why you need two queries in your report. since you are using a dimensional model maybe you can accomplish what you are trying to do by using dimensional functions?

anyway if you really need to "ADD HIERARCHY" in the joined query in your report, you can try this (no guarantees if this will work tough)
i assume you have a third query, the output of the two queries you join already.
1. select the that query in the query explorer
2. in Query Properties, set Override Dimension Info to yes, another tab beside "Projected Data Items" (bottom) will appear called Dimension Info
3. you need to re-define the hierarchy you want including your measures

now you have your hierarchy back hopefully it works like a normal hierarchy. good luck

[mel4363]

ok. you might want to take a step back and examine why you need two queries in your report. since you are using a dimensional model maybe you can accomplish what you are trying to do by using dimensional functions?

anyway if you really need to "ADD HIERARCHY" in the joined query in your report, you can try this (no guarantees if this will work tough)
i assume you have a third query, the output of the two queries you join already.
1. select the that query in the query explorer
2. in Query Properties, set Override Dimension Info to yes, another tab beside "Projected Data Items" (bottom) will appear called Dimension Info
3. you need to re-define the hierarchy you want including your measures

now you have your hierarchy back hopefully it works like a normal hierarchy. good luck

I will try just that and get back to you

Thank you so much

[mel4363]

ok. you might want to take a step back and examine why you need two queries in your report. since you are using a dimensional model maybe you can accomplish what you are trying to do by using dimensional functions?

anyway if you really need to "ADD HIERARCHY" in the joined query in your report, you can try this (no guarantees if this will work tough)
i assume you have a third query, the output of the two queries you join already.
1. select the that query in the query explorer
2. in Query Properties, set Override Dimension Info to yes, another tab beside "Projected Data Items" (bottom) will appear called Dimension Info
3. you need to re-define the hierarchy you want including your measures

now you have your hierarchy back hopefully it works like a normal hierarchy. good luck

I tried with no success .. This suggestion works in that it has created what looks like a hierarchy (But it lists all items) but not enough for me to achieve my goal (Drill-down/up) .. I also tried using dimensional functions but seem not to be allowed in a join as well .. I get an error each time I use them (e.g. descendants([item],level) ..

[Francis aka khayman]

i see. then why don't you take a step back and analyze why you need two queries in your report and join them? can't you achieve the same using dimensional functions?

[bdbits]

It is possible I am overlooking something, but if you already have a hierarchy defined in a dimensional model, it seems like you are making this more difficult than you need.

If you have the Cognos samples available to you, try this:

• Open a new report using the package "Sales and Marketing (conformed).
• In the package, navigate to the Products hierarchy and drop "Products" onto your report.
• Choose menu "Data" and item "Drill Behavior...".
• Check "Allow drill-up and drill-down" and click "OK".
• Run the report.

You should see a clickable item called "Products". When you click it, you will navigate down the "Products" hierarchy as defined in the package. Once down, you can right-click to drill back up a level.

Here's the report spec created as described above. The core thing is using a level from the hierarchy in the report, and enabling drill up/down. Is there something about your report or package that is different so that this approach does not work?

Code Select Expand


<report xmlns="http://developer.cognos.com/schemas/report/11.0/" useStyleVersion="10" expressionLocale="en-us">
<modelPath>/content/folder[@name='Samples']/package[@name='Sales and Marketing (conformed)']/model[@name='model']</modelPath>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItemLevelSet name="Products"><dmLevel><LUN>[Sales and Marketing (conformed)].[Products].[Products].[Products]</LUN><itemCaption>Products</itemCaption></dmLevel><dmDimension><DUN>[Sales and Marketing (conformed)].[Products]</DUN><itemCaption>Products</itemCaption></dmDimension><dmHierarchy><HUN>[Sales and Marketing (conformed)].[Products].[Products]</HUN><itemCaption>Products</itemCaption></dmHierarchy></dataItemLevelSet></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/></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>
<crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Products" edgeLocation="e1"><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"/><XMLAttribute name="RS_modelModificationTime" value="2007-10-15T14:54:50.860Z" output="no"/></XMLAttributes><drillBehavior drillUpDown="true" modelBasedDrillThru="true"/></report>


[mel4363]

Hi bdbits,

No that part I am very clear about (Defining dril-up/down in report) - Wanted to do this in the query that I have joined queries to produce.

Think I l go back to the drawing board as Khayman suggests. I l do the joins in the database

Thank you so much for your suggestions

Mel