Synchronizing 2 Crosstabs and 2 Charts

[kutrikb]

Hi ,

I have one Query: that shows regions and brands and measures

Region Crosstab   Region Chart

Brands Crosstab   Brands Chart


When I click on a Region to drill down it should impact the Crosstab below (it's the same query) and vice versa, that is when I drill down on a Brand it shouuld impact the upper crosstab.
They work horizontally, like the charts always get refrshed but not vertically.

Can someone tell me what the solution is?

thanks

B.

[nuknsltnt]

I have done exactly the same with List Reports.

Although my report was drilling through to itself at a lower level. Is that the case with what you are doing? Could provide some more info? Is the Drill through to another report or the same report.

"They work horizontally, like the charts always get refrshed but not vertically. "

Does that mean, when I drill on region the from say All Regions to North then the Brands displayed should automatically be only for the North Region?

And the charts have to display the information accordingly.

I am a bit busy, but if you can describe little more in detail then it would be helpful.

Cheers,
nk

[kutrikb]

I think you understand it well
Situation:

It's a table with 2 rows 2 columns

Upper row: Crosstab. There are measures on columns
and Regions on rows
The chart on the right of this shows the info accordingly

Second row:
There is a Crosstab with the same measures as above on columns
and Brands on rows.
Chart on the right , info accordingly.


When I drill down on a Region in the upper row , the row gets refreshed accordingly, that isyou see measures in the given region in the crosstab and in the chart.

It should refresh the bottom row, but it doesn't .
That is,in the bottom rows crosstab I should see the meausers belonging to the brands on rows in the context of the Region I drilled down in the upper crosstab.

and vice versa. If I drill down on a brand, the upper crosstab should show me regions - Measures in the context of the given brand(s).

Thank you very much for your help

I tried to create member setstructures and apply advanced drill behaviour, but then probably that's not the soltion

Thenks

bence

[nuknsltnt]

Bence,
Is the Package modeled as a OLAP source or as a Dimensionally modeled relational source?

Cheers,
nk

[kutrikb]

It's a Cube,

so OLAP.

Bence

[rockytopmark]

I'm not sure the effect you are looking for is designed into the product.

Don't forget, that even though XTab1 and XTab2 are built from the same query, the are probably still dealt with independently.

If you were to change your rows and columns to use MDX, you can probably have way more control over how the report responds to the drilling.

I did recreate this using the Great Outdoors Company Cube and I did see how the drill on the top XTab, affected the top Chart, but not the other XTab or Chart.

I beleive this is because the different dimensions being used, top vs bottom, regardless if they are in the same query.  Remember, Cognos 8 formulates the SQL request with only what is actually needed for the object (XTab, Chart, List)

[kutrikb]

It's clear so far, but how can I make a connection between them?
It's not a master-detail relationship. Because both can be master ADN detail as well.

I tried with member set structures, it didn't work. there was no effect,
I tried to drill through to itself, then I was able to drill down but not drill up.

I have no better ideas for now

Thanks

Bence

[MFGF]

Hi,

Try replacing the measure within your first Regions crosstab/chart with a query calculation defined as
aggregate([Measure] within set [Brands])
and replace the measure within your second Brands crosstab/chart with a query calculation defined as
aggregate([Measure] within set [Regions])

Next enable Member Sets within your query, and define a Member Set for Regions (change the name to RegionsMemberSet) and for Brands (change the name to BrandsMemberSet).

Finally go to the Drill Behaviour dialog, onto the Advanced tab, and set the Drill Up Behaviour and Drill Down Behaviour to 'Replace Expression' for Regions and Brands.

Does this give you what you need?

Regards,

MF.

[kutrikb]

It's working now perfectly
Thanks

Bence

[MFGF]

You're welcome!  ;D

[rockytopmark]

Not to steal the thread... but I am not getting the expected results with my test.  All values are the same across the Crosstab and Chart with the changes made as specified above.

After I created the member set... and renamed them similar to how you named them, am I expected to use these somewhere?  I did not see where these were used in the crosstab or reports and might be missing a step or two that was assumed.

I have not worked with member sets before...

Thanks in advance!!!

[MFGF]

Hi,

It's a detailed explanation, but here goes!  :)

The problem with the original report was that the context was not being preserved in one crosstab/chart when drilling down in the other crosstab/chart, because they contain different descriptive items.

The modification of the measure expressions forces one chart/crosstab to display its measure in context with the descriptive item set from the other chart/crosstab, but by default this is a static set in your expression and does not change when drilldown is performed in the other crosstab/chart.

The measure sets allow more control over what happens when drilling down in a particular set - these and the modifications you make in the advanced drill behaviour tab mean the original set item defined in the query becomes an expression used in the member set, and gets replaced with a new expression - children(whatever member was drilled down on) - once drilldown happens.  This means the measure value in the "other" crosstab/chart now also changes to be aggregate([Measure] within set children(whatever member was drilled down on) and thus preserves the context from the crosstab/chart you perform the drilldown in.

Any measure sets you add need to have a unique name within your report.  They will default to having the same name as the item you bring in from the query, so you must always remember to modify the name property to make it unique.  You do not need to use the member sets elsewhere by name in any expressions in this example - it's enough just to have them defined.

Here's an example I knocked together using the Great Outdoors Company package.  You can compare your report with what I've done here to see what's different - hopefully that will lead you to what's causing the problem in your report.

Best regards,

MF.

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/2.0/" expressionLocale="en-gb"><!--RS:8.1-->
<modelPath>/content/package[@name='Great Outdoors Company']/model[@name='model']</modelPath>
<layouts>
<layout>
<reportPages>
<page class="pg" name="Page1">
<pageBody class="pb">
<contents><table class="tb"><tableRows><tableRow><tableCells><tableCell><contents><crosstab class="xt" refQuery="Query1">



<style>
<CSS value="border-collapse:collapse"/>
</style>
<crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Product line" class="ml"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Total(Product line)" class="ol" solveOrder="1"><contents><textItem><dataSource><staticValue>Total</staticValue></dataSource></textItem></contents><factCell class="ov"/></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><defaultMeasure refDataItem="ProdRevenue"/><crosstabFactCell class="mv"><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents></crosstabFactCell><crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="ProdRevenue" class="ml"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows></crosstab>
</contents></tableCell><tableCell><contents><pieChart depth="0" holeSize="0" class="ch" avoidLabelCollision="true" refQuery="Query1">
<legend class="lg">
<legendPosition>
<relativePosition/>
</legendPosition>
<legendTitle class="lx"/>
</legend>
<axisTitle class="at"/>
<pieLabels class="al"/>
<style>
<CSS value="padding:5px;width:400px;height:200px"/>
</style>
<defaultChartMeasure refDataItem="ProdRevenue"/><pieSlices><chartNodes><chartNode><chartNodeMembers><chartNodeMember refDataItem="Product line"><chartContents><chartTextItem><dataSource><memberCaption/></dataSource></chartTextItem></chartContents></chartNodeMember></chartNodeMembers></chartNode></chartNodes></pieSlices></pieChart>
</contents></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><crosstab class="xt" refQuery="Query1">



<style>
<CSS value="border-collapse:collapse"/>
</style>
<crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Sales territory" class="ml"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Total(Sales territory)" class="ol" solveOrder="1"><contents><textItem><dataSource><staticValue>Total</staticValue></dataSource></textItem></contents><factCell class="ov"/></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><defaultMeasure refDataItem="TerritoryRevenue"/><crosstabFactCell class="mv"><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents></crosstabFactCell><crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="TerritoryRevenue" class="ml"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows></crosstab>
</contents></tableCell><tableCell><contents><pieChart depth="0" holeSize="0" class="ch" avoidLabelCollision="true" refQuery="Query1">
<legend class="lg">
<legendPosition>
<relativePosition/>
</legendPosition>
<legendTitle class="lx"/>
</legend>
<axisTitle class="at"/>
<pieLabels class="al"/>
<style>
<CSS value="padding:5px;width:400px;height:200px"/>
</style>
<defaultChartMeasure refDataItem="TerritoryRevenue"/><pieSlices><chartNodes><chartNode><chartNodeMembers><chartNodeMember refDataItem="Sales territory"><chartContents><chartTextItem><dataSource><memberCaption/></dataSource></chartTextItem></chartContents></chartNodeMember></chartNodeMembers></chartNode></chartNodes></pieSlices></pieChart>
</contents></tableCell></tableCells></tableRow></tableRows><style><CSS value="border-collapse:collapse;width:100%"/></style></table></contents>
</pageBody>
</page>
</reportPages>
</layout>
</layouts>
<queries><query name="Query1"><source><model/></source><selection><dataItem name="Product line" aggregate="none"><expression>[great_outdoors_company].[Products].[Products].[Product line]</expression></dataItem><dataItem name="Sales territory" aggregate="none"><expression>[great_outdoors_company].[Sales Territory].[Sales Territory].[Sales territory]</expression></dataItem><dataItem name="ProdRevenue"><expression>aggregate([great_outdoors_company].[Measures].[Revenue] within set [Sales territory])</expression></dataItem><dataItem name="TerritoryRevenue"><expression>aggregate([great_outdoors_company].[Measures].[Revenue] within set [Product line])</expression></dataItem><dataItem name="Total(Product line)"><expression>total(currentMeasure within set [Product line])</expression></dataItem><dataItem name="Total(Sales territory)"><expression>total(currentMeasure within set [Sales territory])</expression></dataItem></selection><memberSetStructure><memberSet name="Product line MS" refDataItem="Product line"/><memberSet name="Sales territory MS" refDataItem="Sales territory"/></memberSetStructure></query></queries><drillBehavior drillUpDown="true" modelBasedDrillThru="false"><drillQueryBehavior refQuery="Query1"><drillDataItemBehavior refDataItem="Product line"><drillUpBehavior><drillSimpleType type="replaceExpression"/></drillUpBehavior><drillDownBehavior><drillSimpleType type="replaceExpression"/></drillDownBehavior></drillDataItemBehavior><drillDataItemBehavior refDataItem="Sales territory"><drillUpBehavior><drillSimpleType type="replaceExpression"/></drillUpBehavior><drillDownBehavior><drillSimpleType type="replaceExpression"/></drillDownBehavior></drillDataItemBehavior></drillQueryBehavior></drillBehavior></report>

[rockytopmark]

Your instructions explained to change the Measure to use the "Aggregate([measure] with set([Dimension])" expression, but your example report has this on the Row Edge.

I see the difference and I will play with it.  I think this will open up some real good options, for dynamic reports.  Thanks!!!

I appreciate your sharing of this... I will put it to good use!!

[MFGF]

Hi,

It makes no difference whether the measure is on an edge or is the default measure, so don't worry about that being an issue.

Best regards,

MF.