Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Some MDX Calc measures delivers Grand Totals, others not, why

Status
Not open for further replies.

eo

MIS
Apr 3, 2003
809
SSAS 2005:

1) I have a Measure, which is a Distinct Count of a PolicyNumber. It is called PremPolCountCap.

2) I then used MDX to create a Parallel Period value
Code:
CREATE MEMBER CURRENTCUBE.[MEASURES].[KPIPrmCapParaPer]
 AS 
(ParallelPeriod(
                        [Date Transaction].[Date Transaction].[YearNo]
                        ,1
                        ,[Date Transaction].[Date Transaction].CURRENTMEMBER
                ),[Measures].[PremPolCountCap]),
FORMAT_STRING = "#,#", 
VISIBLE = 1;

3) I then created MDX to caluclate the differences between the Policy Count for the current period, and that of the previous period
Code:
CREATE MEMBER CURRENTCUBE.[MEASURES].[KPIPrmCapDiff]
 AS 

([Date Transaction].CurrentMember,[Measures].[PremPolCountCap])-
 
(ParallelPeriod(
                        [Date Transaction].[Date Transaction].[YearNo]
                        ,1
                        ,[Date Transaction].[Date Transaction].CURRENTMEMBER
                ),[Measures].[PremPolCountCap]),
FORMAT_STRING = "#,#", 
VISIBLE = 1;

3) I lastly created another MDX to create the difference as a percentage of the Policy Count
Code:
CREATE MEMBER CURRENTCUBE.[MEASURES].[KPIValuePerc]
 AS 
IIF(
    [Measures].[PremPolCountCap]=0,
    Null,
    [Measures].[KPIPrmCapDiff]/[Measures].[PremPolCountCap]
), 
FORMAT_STRING = "Percent", 
VISIBLE = 1;

The conventional measure (1) shows a Grand Total in the browser, but the calculated measure (2) does not, and then calculated measures (3) and (4) do??? This is very strange, especially as calculated measures (3) and (4) rely on calculated measure (2) for its result set???

So in the browser this all displays okay (except for the lack of Grant Total for calculated measure (2), but these calculated measures are destined for creation of a KPI.

I want to create a KPI that shows how the current Policy Count increased or decreased in terms of the position at the same time last year. So I used the conventional measure as the KPI Value, and thought to use the calculated measure (2) as the Goal Expression, but when viewing the KPI, it shows the KPI Value as expected, but nothing for the KPI Goal, and my suspicion is that it is connected to the same thing which is causing the lack of grant total.

Any ideas?

EO
Hertfordshire, England
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top