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
3) I then created MDX to caluclate the differences between the Policy Count for the current period, and that of the previous period
3) I lastly created another MDX to create the difference as a percentage of the Policy Count
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
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