Mega / GIga

Hi Friends,
How can i transform the following query to return size in
Gigabytes ?
str(sum(convert(dec(15),v.size))* 8192 / 1048576,10,2)+'MB'
thx
Johnny
Divide by 1024?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Johnny" <johnny_silvestre@.yahoo.de> wrote in message
news:942601c4339b$9a47f110$a401280a@.phx.gbl...
> Hi Friends,
> How can i transform the following query to return size in
> Gigabytes ?
> str(sum(convert(dec(15),v.size))* 8192 / 1048576,10,2)+'MB'
> thx
> Johnny
|||divide by 1024

Mega / GIga

Hi Friends,
How can i transform the following query to return size in
Gigabytes ?
str(sum(convert(dec(15),v.size))* 8192 / 1048576,10,2)+'MB'
thx
JohnnyDivide by 1024?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Johnny" <johnny_silvestre@.yahoo.de> wrote in message
news:942601c4339b$9a47f110$a401280a@.phx.gbl...
> Hi Friends,
> How can i transform the following query to return size in
> Gigabytes ?
> str(sum(convert(dec(15),v.size))* 8192 / 1048576,10,2)+'MB'
> thx
> Johnny|||divide by 1024

Mega / GIga

Hi Friends,
How can i transform the following query to return size in
Gigabytes ?
str(sum(convert(dec(15),v.size))* 8192 / 1048576,10,2)+'MB'
thx
JohnnyDivide by 1024?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Johnny" <johnny_silvestre@.yahoo.de> wrote in message
news:942601c4339b$9a47f110$a401280a@.phx.gbl...
> Hi Friends,
> How can i transform the following query to return size in
> Gigabytes ?
> str(sum(convert(dec(15),v.size))* 8192 / 1048576,10,2)+'MB'
> thx
> Johnny|||divide by 1024

MDX to fetch last 300 non empty dates?

Can someone enlighten me on how to return the last 300 dates worth of data from an MDX query?

For instance, this returns me all the dates in 2007...

SELECT ([Measures].[Total]) ON 0,
NON EMPTY [Calendar].[Date].CHILDREN ON 1
FROM [DB]
WHERE ([Calendar].[Year].[2007])

But let's say I want to return the last 300 non-empty dates and their "Total" value? (The Calendar dimension has the standard Year, Month, Date hierarchy...)

Thanks in advance.

How about something like this:

SELECT ([Measures].[Total]) ON 0,

Tail(

Filter(

([Calendar].[Date].CHILDREN) As S

Not IsEmpty(S.Current))

,300)

ON 1

FROM [DB]

This should return the last 300 non empty dates.

Van Dieu

|||Thanks Van -- very helpful. For some reason the "Not IsEmpty(...)" didn't work, but no worries, this workaround did...

TAIL(FILTER([Calendar].[Date].CHILDREN, ABS([Measures].[P&L Total]) > 1), 300)

MDX to Determine Week Ending Date

Hello,

I'm trying to write MDX that will return the last child for the week having a full 7 days of data. I'm close, but can't figure out how to get the date to return. The sample below returns the correct week, but whenever I use "lastchild", etc. I get that lastchild expects a member and a tuple is being used.

WithMember measures.X asCount(([Date Dimension].[Time - Weeks].CurrentMember.children,[Measures].[Total Dollars]) , EXCLUDEEMPTY )

Select

Tail(Filter({[Date Dimension].[Time - Weeks].[Week].&[2007]&[18]:[Date Dimension].[Time - Weeks].[Week].&[2007]&[26]}, measures.X.Value = 7 )) on 0,

nullon 1

From

[MyCube]

This is to be used in a report header, so ultimately I'd like it to return a string like "Data as of Week Ending 6/16/2007"

Any help is appreciated.

So, this doesn't answer the question, but could you approach the overall problem another way to get what you're looking for? For example, you could have an attribute in your date dimension that recorded the day of the week number from 1 to 7. If you assume that if there exists data for the 7th day, the week is completed. If this gives you what you're looking for, you could determine the last day 7 associated with fact data with a query like this:

Code Snippet

select

{} on 0,

TAIL(

HIERARCHIZE(

EXISTS(

[Delivery Date].[Date].Members,

[Delivery Date].[Day of Week].&[7],

'Internet Sales'

)

),

1

) on 1

from [Adventure Works]

|||

I was actually able to get around the issue by using .item(0). See the working code below:

WithMember measures.X asCount(([Date Dimension].[Time - Weeks].CurrentMember.children,[Measures].[Total Dollars]) , EXCLUDEEMPTY )

Select

nullon 0,

Tail(Tail(Filter({[Date Dimension].[Time - Weeks].[Week].&[2007]&[1]:[Date Dimension].[Time - Weeks].[Week].&[2007]&[53]}, measures.X.Value = 7 )).Item(0).Children) on 1

From

[MyCube]

This code returns the date of the last day of the last full week of data.

MDX to always return the same number of columns

I have a query which has a WHERE clause in my MDX statement which when applied can restrict the set members (and therefore columns) that are returned. I need the number of columns returned to be the same every time. Is there a simple way to always return all members of the set regardless of the WHERE clause?This seems to be to do with the set up of my cube. I don't understand why. I have a customer flag attribute on my customer dimension. If I use this in my WHERE clause NON EMPTY columns aren't returned. I also have an order flag dimension where order flag is the only attribute. If this is used in the WHERE then they are. The only difference that I can see is that order flag is the key column of the dimension. I don't know whether that's the reason for this behaviour?

MDX Question

All,

I have a simple MDX query to return results based on user i/p in search screen. User can type in trade code and the results should give all the source of it. The dimension TRade contains couple of million members that are not unique. (Only source + trade is unique)

The below works when there is a single member corresponsing to input (xxxx). If there are multiple members, it returns nothing.

I can't use filter function here as the trade dimension is large and it takes time to filter. I can't search using member key as the key is made of Trade code + source where source is not avaiable during search,

any solution?

SELECT Measures.[Value] on 0,

NON EMPTY ([Source].[Source].Members) on 1

from MyCube

WHere [Trade].[Trade Code].[xxxx]

Cheers,

Arun

dear Arun,

Create a view with all distinct values that you pretend to aggredate. And in the datasourceview of AS, import this view and use it as a dimension.

regards!!

|||

Hi Pedro,

I dont really want to do that. The dimension design is right. I have another hierarchy in the Trade dimension with levels as Source & Trade Code. this is exactly what I need.

But my problem is filtering using the member name when it appears more than once in the dimension.

Cheers,

Arun

|||

Arun,

Do you have something like this:

Source Trade 1 T11 1 T12 2 T21 3 T31 3 T32 3 T33

correct?!

|||

Arun,

As PedroCGD has mentioned, make a Distinct Trade Value for Filter Criteria, and donot think about Single trade as you example

SELECT Measures.[Value] on 0,

NON EMPTY ([Source].[Source].Members) on 1

from MyCube

WHere [Trade].[Trade Code].[xxxx]

But think about Ranges.

Make a selection criteria for Start Range and End Range. I hope you will understand.

Bhudev

|||

Saw the rply only now.

I had manageed to solve the problem myself. Below is the query.

Source System dimension: (this dimension will have <20 members)

Key - name

1 - ss1

2 - ss2

3 - ss3

4 - ss4

trade dimension : (this can have millions of members) - uniquekey : SourceID + tradecode

SourceID TradeCode Key 1 TC11 Trade.TradeCode.&[1]&[TC11] 1 TC12 Trade.TradeCode.&[1]&[TC12] 2 TC11 Trade.TradeCode.&[2]&[TC11] 2 TC22 Trade.TradeCode.&[2]&[TC22] 3 TC31 Trade.TradeCode.&[3]&[TC31]

Code Snippet

SELECT [Measures].[Value] ON 0,

NON EMPTY (

STRTOSET(" {" +

GENERATE ( [Source System].[Source].[Source]. Members,

"[Trade].[Trade Code].&[" + CSTR([Source System].[Source].CurrentMember.Member_Key ) + "]&[xxxxxxx]" ,

"," )

+ "}")

)

ON 1

FROM [MyCube]

MDX query with filter clause

Hi,

I have the following MDX query which is supposed to return the number of records with Actual time > TargetTime

for each customer, location and editor. Actual Time and Target Time are measures on the Fact table defined in minutes and represented by integer values.

The hierarchy I have created is

Customer Name

Location Name

Editor Name

The MDX query I have written is:

Count(filter(Descendants([DimCustomer].[Customer_Hierarchy].CurrentMember,[DimCustomer].[Customer_Hierarchy].[EditorName]), [Measures].[ActualTime]>[Measures].[TargetTime] ))

But this query gives me the number of customers rather than the number of records with ActualTime > TargetTime

for each customer, location, editor.

Any help would be appreciated.

The best approach here in my opinion would be to create calculated column in DSV returns 1 when ActualTime > TargetTime and 0 otherwise, and then define a new measure on top of this column with Aggregation Type as SUM.|||

I have implemeneted a dsv for the above with a measure named turnaroundtime to get the count of records with ActualTime > TargetTime

I also need to get the count of documents for each customer -> location -> editor where actual time > target time and edit type = Voice

Documents is the fact table. For each document in the fact table, the edit type field specifies if its 'voice' or 'other'

I was trying to get the filter clause to work for this.

|||

Can you create another dimension for Edit Type? If you did this, then you'd be able to filter a query using the new dimension, selecting the member [Voice].

HTH,

Dave Fackler

|||

I created another dimension for edit type but not sure how to write the calculated member.

I need the count of documents where Edit type = Voice and ActualTime > TargetTime

documents is the fatc table.

How do I write the query to get the number of documents for each customer -> location->editor.

I am new to MDX and not able to figure it out

|||

Assuming you want this as an additional calculated measure (and not just doing the filtering within a given query), you could add a calculated member named something like [Measures].[VoiceTurnAroundTime] with a calculation of "([Measures].[TurnAroundTime], [Edit Type].[Voice]")...

HTH,

Dave Fackler

|||I tried that but that displays an error.|||Can you provide the exact calculation as you have it typed in and the error being displayed?

Dave F.|||It worked...thanks a lot.

MDX query question

Here is the content of my source table.

IND_ID CALL_ID

1 1

1 2

2 3

2 4

3 3

3 4

4 7

I would like to create a measures which return the number of IND_ID with 2 element in the group for exemple

Call #2 times

2

Call #1 times

1

could u help me please !

Sorry, but I'm kinda confused about what you are trying to return. Could you describe the logic in a little more detail or possilby even write a SQL statement to show us what it is you are trying to achieve?

Thanks,
Bryan

MDX query for SQL equivalent function IN

I need help in writing MDX query which will return the count of Sales for differnt SalesPerson. Something equivalent to SQL query

Select Sales_Count from Sales where SalesPersonId IN (6200, 4367, 87650, 2222, 18, 334, 9090).

There are hundred thousands of SalesPersons, so is there any way i can run the MDX Query for a batch of SalesPersonId s. One more problem is the SalesPersonId s are not consecutive..Please can anyone help me solve this problem.

How are your user's selecting which sales persons to return?

B.

|||

Just place your MDX strings for each ID you want to be returned in the ROWS() part of the MDX.

SELECT { [Measures].[SomeCount] } ON COLUMNS,

{ [Heir].[ID1], [Heir].[ID2], [Heir].[ID3], [Heir].[ID4] } ON ROWS

FROM [Cube]

Will return:

[Heir].[ID1] 1259

[Heir].[ID2] 51454

[Heir].[ID3] 65514

[Heir].[ID4] 124

|||

That is one of many ways to handle this problem. It just depends on what exactly you are wanting to have returned and how you intend to assemble your list of Sales Reps.

B.

|||

True, I just supplied the exact syntax for the SQL statement provided.|||If you have a (front-end) Sql client that doesn't expose value hierachies to the user, and hence rely on users typing in the literals. You may consider use the class of functions that convert strings to member/tuple/set; so it will not result into an invalid member error when your client translates sql to mdx.

select city, profit from sales where city in ('Redwood City', 'Fremont') group by city;
==>
select [Measures].[Profit] on columns,
StrToSet("{[Customers].[Redwood City], [Customers].[Fremont]}") on rows from sales

but if the user typed RedwoodCity instead or Redwood City is not a valid member, the query wouldn't fail, and would still return results for Fremont.

That may be why Brian was asking how the IN list is constructed.

MDX query for SQL equivalent function IN

I need help in writing MDX query which will return the count of Sales for differnt SalesPerson. Something equivalent to SQL query

Select Sales_Count from Sales where SalesPersonId IN (6200, 4367, 87650, 2222, 18, 334, 9090).

There are hundred thousands of SalesPersons, so is there any way i can run the MDX Query for a batch of SalesPersonId s. One more problem is the SalesPersonId s are not consecutive..Please can anyone help me solve this problem.

How are your user's selecting which sales persons to return?

B.

|||

Just place your MDX strings for each ID you want to be returned in the ROWS() part of the MDX.

SELECT { [Measures].[SomeCount] } ON COLUMNS,

{ [Heir].[ID1], [Heir].[ID2], [Heir].[ID3], [Heir].[ID4] } ON ROWS

FROM [Cube]

Will return:

[Heir].[ID1] 1259

[Heir].[ID2] 51454

[Heir].[ID3] 65514

[Heir].[ID4] 124

|||

That is one of many ways to handle this problem. It just depends on what exactly you are wanting to have returned and how you intend to assemble your list of Sales Reps.

B.

|||True, I just supplied the exact syntax for the SQL statement provided.|||If you have a (front-end) Sql client that doesn't expose value hierachies to the user, and hence rely on users typing in the literals. You may consider use the class of functions that convert strings to member/tuple/set; so it will not result into an invalid member error when your client translates sql to mdx.

select city, profit from sales where city in ('Redwood City', 'Fremont') group by city;
==>
select [Measures].[Profit] on columns,
StrToSet("{[Customers].[Redwood City], [Customers].[Fremont]}") on rows from sales

but if the user typed RedwoodCity instead or Redwood City is not a valid member, the query wouldn't fail, and would still return results for Fremont.

That may be why Brian was asking how the IN list is constructed.

MDX query count of new customers with a new flag who purchased in this period and following peri

Hi all,

I would like some help with an MDX query that will give the following information - return the count of new customers for the current year that have a new customer flag set and then the count of those returning customers in the following years that have made purchases. Here is an example of the data I'm taking about -

CustomerID NewCustomer Year

1 1 2000

1 0 2001

2 1 2001

2 0 2002

1 0 2002

etc.

So, for the above I'm looking for something similar to the following -

Year 2000 New Customers = 1

Year 2001 New Customers = 1

Year 2001 Repeat Customers = 1

Year 2002 Repeat Customers = 2

Does this make sense and is it possible?

Thanks!

Brian

Please see this thread for the answer: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1225081&SiteID=1

MDX OR Operator

Hi, On my reports, I have paramters which work as AND. So, it'll return me data where LOB1= @.LOB1 AND LOB2 = @.LOB2 and so on. I want to change this operator to OR so it should return me data where LOB1= @.LOB1 OR LOB2 = @.LOB2 and so on.

LOB1,2,3 & 4 are part of the same hierarchy and LOB1 is parent of LOB2 and so on.

SELECT NON EMPTY { [Measures].[Billing Charge AM] } ON COLUMNS, NON EMPTY { ([Cost Center].[Hierarchy].[Cost Center].ALLMEMBERS * [Dim Rel Server App].[Application NM].[Application NM].ALLMEMBERS *

[Dim Server].[Server NM].[Server NM].ALLMEMBERS * [Date].[Date SK].[Date SK].ALLMEMBERS * [Product].[Products].[Product Tier 3].ALLMEMBERS * [Date].[MMM-YYYY].[MMM-YYYY].ALLMEMBERS * [Dim Server].[DR].[DR].ALLMEMBERS * [LOB].[LOB].[LOB Level 4].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [TRAC - DC] WHERE ((STRTOSET (@.LOB1)),(STRTOSET (@.LOB2)),(STRTOSET (@.LOB3)),(STRTOSET (@.LOB4)),(STRTOSET (@.Region)),(STRTOSET (@.Market)),(STRTOSET (@.DistributedProducts)),(STRTOSET (@.ReportingGroup)),(STRTOSET (@.Application)),(STRTOSET (@.Server),strtoset(@.CostCenter))) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

What you are after is also known as an Asymmetric Set. I have written a stored proc that makes it easier to generate these sets which is part of the Analysis Services Stored Procedure project on codeplex www.codeplex.com/asstoredprocedures.

If you check ou the wiki page it explains a bit more about what these sets look like:

http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=AsymmetricSet&referringTitle=Home

It uses Martial Status and Gender as an example, but you should be able to do the same thing with LOB1,2,3,4 if they are all different attributes. There are other samples in the MDX examples that you can download from codeplex.

If you can load the ASSP assembly on your server, I think you should be able to change your query to look like the following:

SELECT NON EMPTY { [Measures].[Billing Charge AM] } ON COLUMNS, NON EMPTY { ([Cost Center].[Hierarchy].[Cost Center].ALLMEMBERS * [Dim Rel Server App].[Application NM].[Application NM].ALLMEMBERS *

[Dim Server].[Server NM].[Server NM].ALLMEMBERS * [Date].[Date SK].[Date SK].ALLMEMBERS * [Product].[Products].[Product Tier 3].ALLMEMBERS * [Date].[MMM-YYYY].[MMM-YYYY].ALLMEMBERS * [Dim Server].[DR].[DR].ALLMEMBERS * [LOB].[LOB].[LOB Level 4].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [TRAC - DC] WHERE

ASSP.AsymmetricSet(((STRTOMEMBER (@.LOB1)),(STRTOMEMBER (@.LOB2)),(STRTOMEMBER (@.LOB3)),(STRTOMEMBER (@.LOB4)))

,(STRTOSET (@.Region)),(STRTOSET (@.Market)),(STRTOSET (@.DistributedProducts)),(STRTOSET (@.ReportingGroup)),(STRTOSET (@.Application)),(STRTOSET (@.Server),strtoset(@.CostCenter))) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

|||

Hi Gosbell,

Thanks for the solution. Now I face one more issue.

If I want to pass multiple values strtomember () function will throw error.

If I use strtoset () function, it throws the following error.

No Appropriate Overload functuion was found in the Stored Procedure.The parameters are incorrect.

I am unable to use AsymmetricSet(tuple) because it strttotuple() also throws error if I pass multiple values.

Can you please let me know how to use

ASSP.AsymmetricSet(((STRTOMEMBER (@.LOB1)),(STRTOMEMBER (@.LOB2)),(STRTOMEMBER (@.LOB3)),(STRTOMEMBER (@.LOB4)))

as

ASSP.AsymmetricSet(((STRTOSET (@.LOB1)),(STRTOSET (@.LOB2)),(STRTOSET (@.LOB3)),(STRTOSET(@.LOB4)))

Please let us know the solution ASAP.This would be of great help.

Thanks

Dinesh.

|||I have just checked in a change to the ASSP source that will allow the AsymmetricSet function to work with sets as parameters, but you would have to download the latest change set from the source code tab and compile it yourself in order to get this change at the moment. It will be part of the next release, but I'm not sure at the moment exactly when we will do the next release.

MDX OR Operator

Hi, On my reports, I have paramters which work as AND. So, it'll return me data where LOB1= @.LOB1 AND LOB2 = @.LOB2 and so on. I want to change this operator to OR so it should return me data where LOB1= @.LOB1 OR LOB2 = @.LOB2 and so on.

LOB1,2,3 & 4 are part of the same hierarchy and LOB1 is parent of LOB2 and so on.

SELECT NON EMPTY { [Measures].[Billing Charge AM] } ON COLUMNS, NON EMPTY { ([Cost Center].[Hierarchy].[Cost Center].ALLMEMBERS * [Dim Rel Server App].[Application NM].[Application NM].ALLMEMBERS *

[Dim Server].[Server NM].[Server NM].ALLMEMBERS * [Date].[Date SK].[Date SK].ALLMEMBERS * [Product].[Products].[Product Tier 3].ALLMEMBERS * [Date].[MMM-YYYY].[MMM-YYYY].ALLMEMBERS * [Dim Server].[DR].[DR].ALLMEMBERS * [LOB].[LOB].[LOB Level 4].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [TRAC - DC] WHERE ((STRTOSET (@.LOB1)),(STRTOSET (@.LOB2)),(STRTOSET (@.LOB3)),(STRTOSET (@.LOB4)),(STRTOSET (@.Region)),(STRTOSET (@.Market)),(STRTOSET (@.DistributedProducts)),(STRTOSET (@.ReportingGroup)),(STRTOSET (@.Application)),(STRTOSET (@.Server),strtoset(@.CostCenter))) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

What you are after is also known as an Asymmetric Set. I have written a stored proc that makes it easier to generate these sets which is part of the Analysis Services Stored Procedure project on codeplex www.codeplex.com/asstoredprocedures.

If you check ou the wiki page it explains a bit more about what these sets look like:

http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=AsymmetricSet&referringTitle=Home

It uses Martial Status and Gender as an example, but you should be able to do the same thing with LOB1,2,3,4 if they are all different attributes. There are other samples in the MDX examples that you can download from codeplex.

If you can load the ASSP assembly on your server, I think you should be able to change your query to look like the following:

SELECT NON EMPTY { [Measures].[Billing Charge AM] } ON COLUMNS, NON EMPTY { ([Cost Center].[Hierarchy].[Cost Center].ALLMEMBERS * [Dim Rel Server App].[Application NM].[Application NM].ALLMEMBERS *

[Dim Server].[Server NM].[Server NM].ALLMEMBERS * [Date].[Date SK].[Date SK].ALLMEMBERS * [Product].[Products].[Product Tier 3].ALLMEMBERS * [Date].[MMM-YYYY].[MMM-YYYY].ALLMEMBERS * [Dim Server].[DR].[DR].ALLMEMBERS * [LOB].[LOB].[LOB Level 4].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [TRAC - DC] WHERE

ASSP.AsymmetricSet(((STRTOMEMBER (@.LOB1)),(STRTOMEMBER (@.LOB2)),(STRTOMEMBER (@.LOB3)),(STRTOMEMBER (@.LOB4)))

,(STRTOSET (@.Region)),(STRTOSET (@.Market)),(STRTOSET (@.DistributedProducts)),(STRTOSET (@.ReportingGroup)),(STRTOSET (@.Application)),(STRTOSET (@.Server),strtoset(@.CostCenter))) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

|||

Hi Gosbell,

Thanks for the solution. Now I face one more issue.

If I want to pass multiple values strtomember () function will throw error.

If I use strtoset () function, it throws the following error.

No Appropriate Overload functuion was found in the Stored Procedure.The parameters are incorrect.

I am unable to use AsymmetricSet(tuple) because it strttotuple() also throws error if I pass multiple values.

Can you please let me know how to use

ASSP.AsymmetricSet(((STRTOMEMBER (@.LOB1)),(STRTOMEMBER (@.LOB2)),(STRTOMEMBER (@.LOB3)),(STRTOMEMBER (@.LOB4)))

as

ASSP.AsymmetricSet(((STRTOSET (@.LOB1)),(STRTOSET (@.LOB2)),(STRTOSET (@.LOB3)),(STRTOSET(@.LOB4)))

Please let us know the solution ASAP.This would be of great help.

Thanks

Dinesh.

|||I have just checked in a change to the ASSP source that will allow the AsymmetricSet function to work with sets as parameters, but you would have to download the latest change set from the source code tab and compile it yourself in order to get this change at the moment. It will be part of the next release, but I'm not sure at the moment exactly when we will do the next release.

MDX Help

Hello,

I have the following MDX Complication.

Goal - Return on the members UP TO the current day of the week. ie Today is Wednesday. I want to return all members Monday-Wednesday.

I currently have a Time.Test.[Current Day of Week] to find the current Day of Week tuple.

Tail(Filter([Time].test.[day In week].Members,NOT IsEmpty([Time].test.[day In week].CurrentMember)),1).Item(0)

Below is my [Previous Week] tuple. Is there a way to limit the member set to only include members up to the current day of the week?

Tail(Filter([Time].[Week].Members,NOT IsEmpty(Time.Week.CurrentMember)),1).Item(0).Lag(1)

Any help is much appreciated!

-Lawrence

If I understand your hierarchies correctly, you should be able to do something like the following to get a set of members from the start of the week to the last nonempty day of the week.

Code Snippet

PeriodsToDate(

[Time].[Week]

, Tail(Filter([Time].test.[day In week].Members,NOT IsEmpty([Time].test.[day In week].CurrentMember)),1).Item(0)

)

I am also wondering if the calc might not be a bit faster if you replaced the filter test with the NonEmpty function

Code Snippet

PeriodsToDate( [Time].[Week], Tail(NonEmpty([Time].test.[day In week].Members),1).Item(0))

It's not clear if you wanted to do the same sort of thing to the previous week calc, but you should be able to use similar logic

|||

Hello,

I am not sure why the below MDX always return Sunday (7) instead of the current "Day In Week"?

The hierarchy has three levels [Year], [Day In Week] & [Day]. [Day In Week] is not the lowest level, so wondering if that's causing it

[Day In Week] has members (1,2,3,4,5,6,7) and Day follows

TAIL([Time].[Test].[Day In Week].Members, 1).Item(0)

TAIL(NONEMPTY([Time].[Test].[Day In Week].Members, [Measures].[Checking Account]), 1).Item(0)

Goal - return the current Day In Week

ie [Current Day] =

TAIL( NONEMPTY([Time].[Time].[Day].Members, [Checking Account]), 4).Item(0)

ie [Current Day Of Week] =

Your help is much appreciated,

-Lawrence

|||

If your [Day In Week] attribute only has 7 members then if you have any Sunday with data then the Tail(NonEmpty()) expression will always return [Sunday].

Assuming that your definition of "current date" is the last date with data, then you will probably need to use a date attribute (one that has a member for each date) and find the last nonempty member in that attribute and then use the EXISTING operator to filter that member against the [Day In Week] attribute.

eg

EXISTING Time.[Test].[Day In Week].Members, {[Time].[date].[Date].[<Current Date>]}

|||

My [Current Date] returns the last Day member that has data.

[Current Date] = TAIL( NONEMPTY([Time].[Time].[Day].Members, [Checking Account]), 1).Item(0)

You are saying to use this member to find the [Day In Week] member.

I tried the code below, and it returned the correct [Day In Week] member.

select EXISTING (Time.[Test].[Day In Week].Members, {TAIL( NONEMPTY([Time].[Time].[Day].Members, [Checking Account]), 1).Item(0)} ) on columns from gmd

So I used the following

[Current Day of Week] = EXISTING (Time.[Test].[Day In Week].Members, {TAIL( NONEMPTY([Time].[Time].[Day].Members, [Checking Account]), 1).Item(0)} )

However, I am getting an error (#Error) when I test [Current Day of Week]

select [time].[test].[current day of week] on columns from gmd

select ([Checking Account], [time].[test].[current day of week]) on columns from gmd

Thanks for your help again,

-Lawrence

|||

Sorry, my mistake. I got Existing and Exists() round the wrong way. Try using Exists().

PS. You should be able to hover over the #Error or double click on it in SSMS to see a more detailed error message. I'm guessing it would have been saying that the Existing operator requires a single set as input.