MDX Question - Use Except() & LAG() within NON EMPTY ON ROWS
-
Hello, I am new to MDX but am trying to modify existing code to use the Except function within the NON EMPTY ON ROWS which already includes the LAG function. Is this possible?
Here is the example of my code that will not work because I am getting the error that says "Two sets specified in the function have different dimensionality":
WITH
MEMBER
[Measures].[Production Hours Percent] as
IIF((IsEmpty([Measures].[Scheduled Hours])OR [Measures].[Scheduled Hours]=0),0,([Measures].[Production Hours]/[Measures].[Scheduled Hours])* 100)
MEMBER
[Measures].[Dialer Log Time Percent] as
IIF((IsEmpty([Measures].[Production Hours])OR [Measures].[Production Hours]=0),0,([Measures].[System Log Hours]/[Measures].[Production Hours])* 100)
MEMBER
[Measures].[Workload Completion Percent] as
IIF((IsEmpty([Measures].[Accounts Downloaded])OR [Measures].[Accounts Downloaded]=0),0,([Measures].[Accounts Worked]/[Measures].[Accounts Downloaded])* 100)
SELECT NON EMPTY
{
NonEmpty([Measures].[Production Hours Percent])
, NonEmpty([Measures].[Dialer Log Time Percent])
, NonEmpty([Measures].[Accounts Downloaded])
, NonEmpty([Measures].[Workload Completion Percent])
} ON COLUMNS,
NON EMPTY {
STRTOSET(
"{
[Dim Month Year Monthly Deck].[Beginning Of Month Year].&["+@ReportDate+"].LAG(12)
:
[Dim Month Year Monthly Deck].[Beginning Of Month Year].&["+@ReportDate+"]
}
")
,
Except
(
[Dim Tier].[Description].[Description].Members,
{
[Dim Tier].[Description].[Description].[WORKOUT]})} ON ROWS
FROM [MonthlyDeck]
Does anyone know how I can modify the above code to get it to work? I need to be able to exclude a certain Tier from DimTier, but also be able to only report on the past 12 months' worth of data?
Thank you very much for any assistance you can offer!
-
You possibly want to look at adding the Except to the where clause (ie after the 'FROM <cube_name>'). In using the Except where you are, you're trying to say 'give me these dates, except for these Tiers', and obviously Tiers aren't Dates
Steve.
-
Thank you very much, Steve! That did work for me!
Now I am trying to modify a separate YTD MDX query, but am having trouble being able to only return the Year as ROWS.
WITH
MEMBER
[Measures].[Promises Taken Per Hour] as
IIF (IsEmpty([Measures].[Production Hours]),0,[Measures].[Promises Taken]/[Measures].[Production Hours])
MEMBER [Measures].[Promise Taken Rate] AS
IIF(IsEmpty([Measures].[Right Party Contacts]) , 0, ([Measures].[Promises Taken]/[Measures].[Right Party Contacts])* 100)
MEMBER [Measures].[Promises Kept Per Hour] AS
IIF(IsEmpty([Measures].[Production Hours]) , 0,[Measures].[Promises Kept]/[Measures].[Production Hours])
MEMBER [Measures].[Promise Kept Rate] AS
IIF(IsEmpty([Measures].[Promises Taken]), 0, ([Measures].[Promises Kept]/[Measures].[Promises Taken])* 100)
SELECT NON EMPTY
{
NonEmpty([Measures].[Promises Taken Per Hour])
, NonEmpty([Measures].[Promise Taken Rate])
, NonEmpty([Measures].[Promises Kept Per Hour])
, NonEmpty([Measures].[Promise Kept Rate])
} ON COLUMNS,
NON EMPTY
{
STRTOSET(
"{
[Dim Month Year Monthly Deck].[Beginning Of Month Year].&["+@ReportDate+"].LAG(12)
:
[Dim Month Year Monthly Deck].[Beginning Of Month Year].&["+@ReportDate+"]
}")} ON ROWS
FROM [MonthlyDeck]
WHERE Except
(
[Dim Tier].[Description].[Description].Members,
{
[Dim Tier].[Description].[Description].[WORKOUT]})
The above gives me my results per the Beginning Of Month Year so I have a row for each month in the year. However, I'd like to be able to sum up the results on a yearly basis. I tried the YEAR() function, but that didn't seem to work. I think I need to add in something along the lines of
[Dim Month Year Monthly Deck].[Calendar Year].[Calendar Year]
but that doesn't seem to be working like I thought it would.
I have this query below, but that doesn't return any results, nor an error:
WITH
MEMBER
[Measures].[Promises Taken Per Hour] as
IIF (IsEmpty([Measures].[Production Hours]),0,[Measures].[Promises Taken]/[Measures].[Production Hours])
MEMBER [Measures].[Promise Taken Rate] AS
IIF(IsEmpty([Measures].[Right Party Contacts]) , 0, ([Measures].[Promises Taken]/[Measures].[Right Party Contacts])* 100)
MEMBER [Measures].[Promises Kept Per Hour] AS
IIF(IsEmpty([Measures].[Production Hours]) , 0,[Measures].[Promises Kept]/[Measures].[Production Hours])
MEMBER [Measures].[Promise Kept Rate] AS
IIF(IsEmpty([Measures].[Promises Taken]), 0, ([Measures].[Promises Kept]/[Measures].[Promises Taken])* 100)
SELECT NON EMPTY
{
NonEmpty([Measures].[Promises Taken Per Hour])
, NonEmpty([Measures].[Promise Taken Rate])
, NonEmpty([Measures].[Promises Kept Per Hour])
, NonEmpty([Measures].[Promise Kept Rate])
} ON COLUMNS,
NON EMPTY
{
STRTOSET(
"{
[Dim Month Year Monthly Deck].[Calendar Year].&[Year("+@ReportDate+")].LAG(3)
:
[Dim Month Year Monthly Deck].[Calendar Year].&[Year("+@ReportDate+")]
}")} ON ROWS
FROM [MonthlyDeck]
WHERE Except
(
[Dim Tier].[Description].[Description].Members,
{
[Dim Tier].[Description].[Description].[WORKOUT]})
Does anyone have any other suggestions? Any help is greatly appreciated! Thank you!
-
[Dim Month Year Monthly Deck].[Calendar Year].[Calendar Year]
When you say this doesn't work as you'd thought it would/should, what does it return exactly?
A simple test you could try, to see if it gets you closer to what you want, would be to return the members of the Calendar Year, so something like
[Dim Month Year Monthly Deck].[Calendar Year].MEMBERS
To get your year function to work, you'll need to ensure that what Year() returns and how your member names are set up, are the same. For example, let's assume that Year() returns an integer, like 2010 from a date value like '2010-01-05', but your members are actually named [Fiscal Year 2010] and [Fiscal Year 2009] , then trying to make [2010] == [Fiscal Year 2010] is obviously never going to work. It may be as simple as adding in a prefix to the member name string.
HTH,
Steve.
-
Steve,
I was able to modify the query to look like this:
WITH
MEMBER
[Measures].[Promises Taken Per Hour2] as
IIF (IsEmpty([Measures].[Production Hours]),0,[Measures].[Promises Taken]/[Measures].[Production Hours])
MEMBER [Measures].[Promise Taken Rate] AS
IIF(IsEmpty([Measures].[Right Party Contacts]) , 0, ([Measures].[Promises Taken]/[Measures].[Right Party Contacts])* 100)
MEMBER [Measures].[Promises Kept Per Hour] AS
IIF(IsEmpty([Measures].[Production Hours]) , 0,[Measures].[Promises Kept]/[Measures].[Production Hours])
MEMBER [Measures].[Promise Kept Rate] AS
IIF(IsEmpty([Measures].[Promises Taken]), 0, ([Measures].[Promises Kept]/[Measures].[Promises Taken])* 100)
SELECT NON EMPTY
{
NonEmpty([Measures].[Promises Taken Per Hour])
, NonEmpty([Measures].[Promise Taken Rate])
, NonEmpty([Measures].[Promises Kept Per Hour])
, NonEmpty([Measures].[Promise Kept Rate])
} ON COLUMNS,
NON EMPTY
{
[Dim Month Year Monthly Deck].[Calendar Year].[Calendar Year]
} ON ROWS
FROM [MonthlyDeck]
WHERE Except
(
[Dim Tier].[Description].[Description].Members,
{
[Dim Tier].[Description].[Description].[WORKOUT]})
and it runs and gives me records for ALL of the years that I have in the database. Now, I'm trying to work in the LAG() to give me just 3 years' worth of info, but am stumbling on the fact that I now have the [Dim Month Year Monthly Deck].[Calendar Year].[Calendar Year] in the ON ROWS section and I had to remove the LAG function that was working with the ReportDate parameter. I'm hoping I can combine them together somehow but haven't been able to get that syntax to work.
Do you have any recommendations? Thanks! - I really appreciate all of your help!
-
What do your member names look like for the years? Is it like [Dim Month Year Monthly Deck].[Calendar Year].[2010] ? Or is it like [Dim Month Year Monthly Deck].[Calendar Year].[FY2010] ? or ???
Being able to return all of the members proves out that your query works, in general
so now it's a case of determining exactly -- what your year member names look like
- and what your parameter value looks like.
If your parameter isn't really a date, then it could be that the Year function is returning null/nothing, which will make the Range and LAG function fail.
Steve.
-
The members of CalendarYear are 2010, 2009, 2008, etc.
The parameter for ReportDate is '2009-12-01T00:00:00' - this is how I've gotten it to work with our cube.
Thank you!
-
I think the Year() function is one provided by the VBA lib rather than being a native MDX function, so the format of that datestring may be the cause of your issue.
You could try an inline conversion to a date in a string format that Year() likes, alternatively, you could simply use some generic string functions to extract the year yourself. This could look like -
NON EMPTY
{
STRTOSET(
"{
[Dim Month Year Monthly Deck].[Calendar Year].&[" + @ReportDate.Left(4) + "].LAG(3)
:
[Dim Month Year Monthly Deck].[Calendar Year].&[" + @ReportDate.Left(4) + "]
}")} ON ROWS
If the <string>.Left() method doesn't work (which is likely if you're stuck with VB.net, as I think it's a C# method), you should be able to use something like substring. Basically any string function that lets you grab the portion you are after and passes the compiler
The substring approach would look likeNON EMPTY
{
STRTOSET(
"{
[Dim Month Year Monthly Deck].[Calendar Year].&[" + @ReportDate.Substring(0,4) + "].LAG(3)
:
[Dim Month Year Monthly Deck].[Calendar Year].&[" + @ReportDate.Substring(0,4) + "]
}")} ON ROWS
HTH,
Steve.
-
Steve,
I tried getting the Substring() and Left() to work, but wasn't able to. In my research, I found the Subset() and was able to get that to work with hardcoded values - not using my @ReportDate yet.
Here's what I have:
WITH
MEMBER
[Measures].[Promises Taken Per Hour] as
IIF (IsEmpty([Measures].[Production Hours]),0,[Measures].[Promises Taken]/[Measures].[Production Hours])
MEMBER [Measures].[Promise Taken Rate] AS
IIF(IsEmpty([Measures].[Right Party Contacts]) , 0, ([Measures].[Promises Taken]/[Measures].[Right Party Contacts])* 100)
MEMBER [Measures].[Promises Kept Per Hour] AS
IIF(IsEmpty([Measures].[Production Hours]) , 0,[Measures].[Promises Kept]/[Measures].[Production Hours])
MEMBER [Measures].[Promise Kept Rate] AS
IIF(IsEmpty([Measures].[Promises Taken]), 0, ([Measures].[Promises Kept]/[Measures].[Promises Taken])* 100)
SELECT NON EMPTY
{
NonEmpty([Measures].[Promises Taken Per Hour])
, NonEmpty([Measures].[Promise Taken Rate])
, NonEmpty([Measures].[Promises Kept Per Hour])
, NonEmpty([Measures].[Promise Kept Rate])
} ON COLUMNS,
NON EMPTY
{
SUBSET([Dim Month Year Monthly Deck].[Calendar Year].[Calendar Year], 7,2)
} ON ROWS
FROM [MonthlyDeck]
WHERE Except
(
[Dim Tier].[Description].[Description].Members,
{
[Dim Tier].[Description].[Description].[WORKOUT]})
Bu using Subset(), I can start at the year I want (which is 2008 in my case), and get that year and the next year to return for me. I obviously don't want this hardcoded in here, and will work at trying to get the @ReportDate to work. But I wanted to pass along this information to see if you agree that this is an option.
Thank you again for all of your help!
-
Subset would work, but personally I'd use the range. The issue is not so much 'how' to select the actual years you're looking for, but more that you need to convert/use the parameter value to create a valid member name from the Year attribute dimension. ie the primary peice of work is to get a function that modifies the @ReportDate value, to get like '2010', '2009'.
Steve.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply