Filter is dropped in the middle of a crosstab

[RyanDonohue]

I have had my entire team look at the following issue and none of us can figure it out. I certainly hope you can.

I have a report with 4 crosstabs; each one gives a little more detail than the level before it. They all refer to the same query

1) Aggregate(Division) (1 Column)

2) Division (As many Columns as there are Divisions)

3) Division
Region (As many Columns as there are Regions)

4) Division
Region
Branch (As many Columns as there are Branches)

I have 3 optional cascading multiselect prompts. One for Division, Region, and Branch. Logically, if a user picks only 1 Branch, each table should show the same values. This is where I run into the issue.

Tables 1 and 4 will be identical. Table 2 will show the correct value for the first line of the crosstab (in most cases revenue). Every subsequent line will be the total of the Division ignoring the remaining filters on the query. The same is true for Table 3, but it gives me the region totals.

I think the most confusing part is that the filter is applied to these tables for the first line. Why would it be applied to one, but not all?

Is there another approach that I could use to provide the correct rollup totals based on the filters? Any suggestion would be welcome at this point.

 

[blom0344]

This is really too difficult to comment on without seeing the report, but filters may fail when the query underneath is a 'stitch' query. cognos in that case generates 2 seperates SQL statements and stitches them back using coalesce / full outer join
tactic. The filter designed is then not fully exerted against the whole set , but only against 1 of 2

As nearly always my advice is to check the SQL generated to see if anything out of the ordinary is taken place..

Ties Blom

 

[RyanDonohue]

Thank you Blom. Your explanation for the scenario makes a lot of sense and I feel this may be indeed what is happening. Unfortunately, I am unable to view the SQL due to validation errors
caused by my token (this occurs in several reports, but they all work as expected).

Assuming this is the issue, how would I resolve the stitching so that my report would filter on every record?

Thanks again for responses to all of my inquiries.

 

[blom0344]

'Stitching' is the Cognos to deal with multifact queries. When modelled right these are the desired way to draw measures from 2 facts and still get proper results. However, they may also occur when the report is based on 'incompatible' items from the model. Cognos will always attempt to compile a SQL to return results even if it then generates 'wrong' results.

If you cannot validate the SQL - even with a working report - than something is wrong

Just a suggestion.. What happens when you apply the filter after auto-aggregation?

Ties Blom

 

[RyanDonohue]

I've tried after auto-aggregation, but it gives me the same results.

I've even tried aggregate([Revenue] within set [Branch]) thinking this would force the crosstab to evaluate at the branch level, but this too did not change my results.

 

[blom0344]

Any chance of running a trace database side? We often use traces to get a better picture what goes on beneath the surface. We SQL server this is dead easy, with other databases one is often at the mercy of an DBA..

Ties Blom

 

[RyanDonohue]

I'm at the mercy of DBAs backlogged with months worth of work.

If I was able to fix the SQL so that it could compile (replace all of my tokens with a fixed member), would I be able to do something to fix it (assume it is stitching)?

Let me set up this mediafire hosting and I will load my report.

 

[RyanDonohue]

I have attached my report. Maybe you could have a look at it. Thanks.

 

[blom0344]

Sorry, I did not know this was based on a dimensional model, this is basically MDX instead of SQL. Not really my field, forget my story about stitching which applies to a relational one..

Ties Blom

 

[RyanDonohue]

Hey Ties,

I wanted to thank you for your help with this. I was able to figure it out finally!

Basically what was happening, was that I had a calculated measure that was making decisions in one hierarchy (Income Statement) while my crosstab rows and filter we being defined in another (Company Hierarchy). When I removed this calculated measure, it worked fine. I was able to figure out another way to get the numbers I wanted without using the calculated measure.

I am not sure why it ever retrieved the first number correctly, but that is mystery I don't need an answer to.

-Ryan