Current Year Data

I have an MDX DataSet that gets the data for 2007 (hard coded). I want to automate it to get the current year data so that I don't have to manually update the code in 2008.

The data set is as follows:

SELECT NON EMPTY {[Measures].[Fixtures]} ON COLUMNS,

NON EMPTY {( [Charterer].[Current Short Code].[Current Short Code].ALLMEMBERS )} ON ROWS

FROM ( SELECT ( { ([CP Date].[Year].&[2007]) } ) ON COLUMNS FROM [Voyage Analysis])

I got as far as being able to find the current year

WITH MEMBER [Measures].[ThisDay] AS Now()

MEMBER [Measures].[ThisYear] AS 'YEAR([ThisDay])'

SELECT {[ThisDay],[ThisYear]} ON COLUMNS

FROM [Voyage Analysis]

But I have no idea how to replace "&[2007]" with , "[ThisYear]".

As you can see I'm not very experienced with MDX, appreciate if anyone can help.

Thanks

Richard

You could build a string version of the year member, then use StrToMember():

SELECT NON EMPTY {[Measures].[Fixtures]} ON COLUMNS,

NON EMPTY {( [Charterer].[Current Short Code].[Current Short Code].ALLMEMBERS )} ON ROWS

FROM ( SELECT ( { StrToMember("[CP Date].[Year].&["

+ CStr(Year(Now())) + "]") } ) ON COLUMNS FROM [Voyage Analysis])

|||

Thanks. Perfect!

Richard

Current Month in ProClarity

I can′t get the current month,I have tried the following mdx without any result:

Code Snippet

Extract(Filter([Period].[Monthly].[Month].Members, [Period].[Monthly].CurrentMember.Properties("Current Period") ="-1"), [Period].[Monthly]).Item(0)

Code Snippet

Tail( Filter( [Date].[Date].Levels(3).Members, Not IsEmpty([Date].CurrentMember)), 1).item(1)

Here is an example that will give you the most current calendar month in Adventure Works where the month has "reseller sales":

Tail(Exists([Date].[Calendar].[Month].Members,,"Reseller Sales"),1)(0)

The general form of the calculation is:

Tail(Exists(<<date dimension>>.<<date hierarchy>>.<<date level>>.Members,, "<<measure group>>"),1)(0)

HTH,

Steve

sql

cumulative Percentage with set

hi,
I'm newbie in MDX,
could anyone please help on the following questions

I've following MDX

WITH
SET [topZ%]
AS 'TOPPERCENT(NONEMPTYCROSSJOIN({[product].[productcode].CHILDREN},{[Timeofday].[Dt].&[2006-09-01T00:00:00]}),70,([Measures].[sales]))'
//% contribution to the total
MEMBER [Measures].[%ofTot] AS '[Measures].[sales]/([Measures].[sales],[product].[productcode].[All])',FORMAT_STRING="PERCENT"
//Cumulative %
MEMBER [Measures].[Cum%] AS 'SUM({[topZ%].FIRSTSIBLING:[topZ%].CURRENTMEMBER},[Measures].[%ofTot])'
SELECT
{[Measures].[sales],[Measures].[%ofTot],[Measures].[Cum%] } on 0,
{[topZ%]} on 1
from testcube

I want the result as follows:

sales %ofTot Cum%
productA 2006-09-01 300 50% 50%
productB 2006-09-01 200 33% 83%
productC 2006-09-01 100 17% 100%

But there's problem on the [Cum%] column, its show #Error
Could someone please give some advice ? thanks a lot

Not sure how general a solution you need, but you can try something like:

MEMBER [Measures].[Cum%] AS

'SUM(Head([topZ%],

Rank(([product].[productcode].CurrentMember, [Timeofday].[Dt].CurrentMember),

[topZ%])), [Measures].[sales])

/([Measures].[sales],[product].[productcode].[All])',

FORMAT_STRING="PERCENT"

|||I see.
thanks deepak

cube design/MDX for Product Owns vs does not Own

I would really appreciate if you could give us your input for following design, I am having hard time to visualize the cube design.

I have a fact table and dimension tables as follows with rows.

CREATE TABLE [dbo].[Fact_ProductOwnership](
[OrgID] [int] NULL,
[SegmentID] [int] NULL,
[SubSegmentID] [int] NULL,
[ProductID] [int] NOT NULL
) ON [PRIMARY]

OrgID SegmentID SubSegmentID ProductID 1 1 1 2 1 1 1 3 1 1 1 6 2 2 2 2 2 2 2 3 2 2 2 6 3 1 1 1 3 1 1 3 3 1 1 5

Segment table rows

CREATE TABLE [dbo].[Segment](
[ID] [int] NULL,
[SegmentName] [varchar](20) NULL
) ON [PRIMARY]

ID SegmentName

1 Enterprize

2 Small Business

SubgSegment table and Rows

CREATE TABLE [dbo].[SubSegment](
[ID] [int] NULL,
[SubSegmentName] [varchar](20) NULL
) ON [PRIMARY]

ID SubSegmentName

1 Enterprize

2 Small Business

Product table and Rows

CREATE TABLE [dbo].[Products](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ProductDesc] [varchar](20) NULL
) ON [PRIMARY]

ID ProductDesc

1 Access_Yes

2 Access_No

3 Excel_Yes

4 Excel_No

5 SqlServer_Yes

6 SqlServer_No

What I would like achieve from above design is to get the list orgs for the following scenarios

1) Show the companies who owns Access_Yes & Excel_No

Above should display 2 orgs (1 and 2 qualify for this scenario)

2) Show the companies who owns SqlServer_yes & SqlServer_Ye

Above query should display 0 orgs(no org qualifies)

Could you please suggest me thtat is my above design wrong or is it too hard to ahieve from cube? Is there any alogorithm this can solve above problem ?

I would really appreciate if you could answer to above problem ?

You can design your cube with dimensions for Org and Product (and optionally Segment and SubSegment) and then create a row count measure. Then you would query for Orgs and use NONEMPTYCROSSJOIN to filter by products.

Something like:

SELECT NONEMPTYCROSSJOIN(NONEMPTYCROSSJOIN([Org].Members, {[Product].[Access_Yes]}, 1) {[Product].[Excel_No], 1) ON COLUMNS,

{} ON ROWS

FROM [MyCube]