INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close