Ignoring attribute in report heading for other attributes below

[dmcmunn]

I need to have an attribute's description shown in the heading of a report, whose ID is used in ApplyComparison() filters for each of the metrics contained on the report below.

Unfortunately, I also need another attribute on each row of the report which I do *NOT* want filtered by the attribute I need in the report heading.

Is the solution for this situation one of XSL and a document in v7.2.2 ?

Layout appears as this...

Org
Time Period
Group

Prov Qty1 Qty2
xxxx 1 1234

Fact table contains attributes/measures:
Org, Time Period, Prov, Qty1 Qualifier, Qty2 Qualifier

A 1:M relationship exists between:
Group and Qty1 Qualifier
and
Group and Qty2 Qualifier

I can get all the correct Prov and Qty values on the report until I add Group to the report.

Any thoughts would be appreciated.

TIA

 

[Z3]

Trying to help, would need more details. It'd help if you post the SQL.
That being said, you can always create a view, which will have the group on the report - which may help.

 

[entaroadun]

Hmm, as I understand it you want a report that has a separate page for each Group attribute element. On that page, you want Qty 1 and Qty 2 only for that Group attribute element, but you want to display every Prov, even if there are no records in the fact table for that Prov. For those rows, you want zero in Qty 1 and Qty 2.

Is that it?

 

[dmcmunn]

entaroadun,
Almost...

I am using a pre-pass report filter to select the list of Prov's for the report. For a Prov to be included on the final report, it must have had a positive value for either of the two metrics: Qty1 or Qty2 for the Org selected during Time Period selected.

Qty1 is qualified via an ApplyComparison( "( #0 in (select qualifier from GroupQualifierRelateTable where GroupID = #1) )", Qty1Qualifier@ID, Group@ID) same relative ApplyComparison() for Qty2 metric.

The wacky thing about it is the Prov's must *not* be filtered by the Group selected. I was able to convince the customer to accept the report, but to turn on the "view report filter" option in v7.2.3 when exporting or printing to capture which Group had been selected via the prompt. It ain't pretty, but it was the best I could do on short notice, as a lowly MicroStrategy hack.

 

[entaroadun]

Ah... You simply need an extra version each of the Qty 1 and Qty 2 metrics. In this extra version, set the dimensionality to ignore the Group filter. We'll call these Qty 1 A and Qty 2 A.

Place them in the report filter in a "[Qty 1 A] > 0 Or [Qty 2 A] > 0" filter expression. This way, when you run the report, the filter will be applied without consideration for group, every Prov in that time period and org will return, and the Qty 1 and Qty 2 for that specific Group will display (with 0 in the Provs that have no values in that Group).

Give it a shot.

 

[dmcmunn]

entaroadun,

I'll give it a shot after I get this to production.

Thanks,
dmcmunn