Order By Dimension Members In MDX

May 22, 2007

Hello!

I'm developing a report with Samsung products sales compared to other producers' sales.

I have the following dimensions:

Dim Period (date-time)
Dim Region (region where the products are sold)
Dim Product Group (notebooks, CRT,...)
Dim Producer (Samsung, LG, ..., Other producers)

Report should contain Samsung sales on the first row, other companies (LG, Dell,...) sales on subsequent rows and "Other producers" sales on the last row, i.e.:

Week 1 - SomeRegion - Notebooks - Samsung - 2350

Week 1 - SomeRegion - Notebooks - LG - 1100

Week 1 - SomeRegion - Notebooks - Dell - 3000

Week 1 - SomeRegion - Notebooks - Other Producers - 10000



How can I position Producer values in such way?

Please note, Dim Producers may be enalrged, so I can't just enumerate all Dim Producer members in the query.



I tried to use field ProducerType, which I set to 1 for Samsung, 2 for other companies (LG, Dell, ...) and 3 for "Other producers", after that I ordered by this field, however it didn't position producers as I expected.

Please see the query I used below:




Code Snippet

SELECT

NON EMPTY

{

[Measures].[Quantity]

} ON COLUMNS,

NON EMPTY

Order(

{

(

[Dim Period].[Period Week No].[Period Week No].ALLMEMBERS *

[Dim Region].[Region Name].[Region Name].ALLMEMBERS *

[Dim Product Group].[Product Group Name].[Product Group Name].ALLMEMBERS *

[Dim Producer].[Producer Name].[Producer Name].ALLMEMBERS

)

}, [Dim Producer].[Producer Type], ASC)

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM [ShelfShare]

WHERE ( [Dim Period].[Period Year].&[2007] )

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS



Any help is greatly appreciated!

View 3 Replies


ADVERTISEMENT

Show Only Non-empty Dimension Members?

Jul 14, 2005

greetings,

if I create a dimension from fact table, then it only has members which appeared in fact table. but if I create dimension from dimension table, then it shows all members, even those with no data. how can I show only those with existing data in member list?

thank you

View 1 Replies View Related

Split Olap Dimension Members

Apr 4, 2006

Argent
Need to split olap dimension members into three different dimension members
example
account dimension member = 11111200900
account_num.member = 11111
item_num.member = 200
sub_item = 900

Please help me
Thanks

View 9 Replies View Related

Create Or Drop Dimension Members

Mar 19, 2008

Hi All,

I am Srinivas working on SSAS 2005. I am not able to create or drop dimension members. I need to drop and create all members in one dimension before cube processing, as the columns are coming dynamically.

E.g. ALTER CUBE [Reporting] DROP DIMENSION MEMBER [Tbl Analysis Test].[Name]
Here cube is [Reporting] , dimension is [Tbl Analysis Test] and member is [Name].

Its giving the following error " Parsing the query ... Query (1, 46) The member '[Name]' was not found in the cube when the string, [Tbl Analysis Test].[Name], was parsed. Parsing complete"

Please look into this issue, advice me what do I do ASAP.

Thanks,
Srinivas

View 3 Replies View Related

Not Able Create Or Drop Dimension Members

Mar 19, 2008

Hi All,

I am Srinivas working on SSAS 2005. I am not able to create or drop dimension members. I need to drop and create all members in one dimension before cube processing, as the columns are coming dynamically.

E.g. ALTER CUBE [Reporting] DROP DIMENSION MEMBER [Tbl Analysis Test].[Name]
Here cube is [Reporting] , dimension is [Tbl Analysis Test] and member is [Name].

Its giving the following error " Parsing the query ... Query (1, 46) The member '[Name]' was not found in the cube when the string, [Tbl Analysis Test].[Name], was parsed. Parsing complete"

Please look into this issue, advice me what do I do ASAP.

Thanks,
Srinivas

View 1 Replies View Related

Analysis :: Querying Dimension Members In MDX?

Sep 17, 2015

The Member_Key we can get by the method suggested in the post, but when we get the Member_Key, Id as Measures/members then performance is very slow.

Is there any way i can get the Member_key in a set in rows section.

Like

With SET [UniqueName] AS [Geography].[Country].CURRENTMEMBER.UNIQUENAME
Select Measures.Value on Columns,
UniqueName on Rows
From Cube

I want the value as just like

Value
Employees, UniqueName

View 2 Replies View Related

Dimension Calculated Members Not Visible In Perspective

May 14, 2008



Hi,

I created some calculated members for a dimension. They are set to be visible and when i connect to the cube itself I can see them with both Excel and ProClarity as clients. I added them to a couple perspectives (by checking their boxes down at the bottom of the Calculations list) and deployed. There were no errors or issues. However from both Excel and ProClarity I do not see the calculated members of the dimension.

Any ideas?

View 9 Replies View Related

HOW TO MODIFY MDX TO RESTRICT MEMBERS IN TIME DIMENSION

Dec 29, 2006

There are some 55 members in the arrival year level of the time dimension (1995 - 2055).
I am trying to find a way to restrict the number of years returned by this mdx query to the current year - 5. Any help will be appreciated.

WITH MEMBER [Measures].[ParameterCaption]
AS '[TIME DIMENSION].[ARRIVAL YEAR].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[ParameterValue]
AS '[TIME DIMENSION].[ARRIVAL YEAR].CURRENTMEMBER.UNIQUENAME'
MEMBER [Measures].[ParameterLevel]
AS '[TIME DIMENSION].[ARRIVAL YEAR].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT
{
[Measures].[ParameterCaption],
[Measures].[ParameterValue],
[Measures].[ParameterLevel]
} ON COLUMNS,
[TIME DIMENSION].[ARRIVAL YEAR].allmembers ON ROWS
FROM [TOURISM CUBE]

Thanks

View 4 Replies View Related

Analysis :: Sorting Dimension Members From Fact

Dec 2, 2015

I've got a dimension built from a fact (whatever that's called?) ... it's a date interval field, i.e. 0-5 weeks, 6-10 weeks 11+ weeks. How do I sort these members in the respective order? Looks like this currently:

The problem lies in the fact that I don't have any secondary attributes to order it by, i.e. it's not a physical dimension where I can use a key for the 3 members. I was hoping I wouldn't need to create a separate dimension to get round this.

View 5 Replies View Related

Analysis :: Limit Date Dimension Members Dynamically?

Jul 7, 2015

We have a date dimension which spans till 2099 and there are future projection numbers (under measures). I want to limit the data for Future projections only to 5 years from today by default. Is there a way to do this with in the cube. I understand that this can be done using MDX but since we use excel to view the data from the cube it needs to be controlled with in the cube.

View 4 Replies View Related

Slowly Changing Dimension:inferred Members Update

Jul 12, 2006



Hi,

Anybody who knows how inferred members update function in slowly changing dimension? I came across this when I started using some of the functions of the Slowly changing dimension object.

Thanks!

cherrie

View 4 Replies View Related

Analysis :: Setup Multiple Default Members For A Dimension Attribute In SSAS?

Jun 2, 2015

I have a dimension report with an attribute reporttype which have different member let say A,B,C,D....etc.

I want to set more than one default members for dimension report.

I've read all about how dimensions can have only one default member, but I need to set more than 1.

View 2 Replies View Related

Simple MDX Query: Multiple Non-Contiguous Members From Same Dimension In Calculated Member

May 28, 2008

Hi

A very basic MDX question. Using the following from AdventureWorks as an example:




Code Snippet
WITH MEMBER [Measures].[Big Cities]
AS
(
[Geography].[City].&[New York]&[NY]
, [Measures].[Internet Order Count]
)
SELECT
[Measures].[Big Cities] ON COLUMNS
, [Customer].[Total Children].Children ON ROWS
FROM [Adventure Works]


How would I re-write the [Measures].[Big Cities] calculated member so it included both [Geography].[City].&[New York]&[NY] and [Geography].[City].&[Los Angeles]&[CA]? Assuming there is no logical connection between these members, and that they are non-contiguous. Also, in my real example, I cannot move [Measures].[Internet Order Count] to the WHERE clause, it must remain within the calculated member.

Thanks,

Julia.

View 9 Replies View Related

Reporting Services - Analysis Services - Displaying Dimension Members As Columns

Dec 3, 2006

I think I've seen a similar post on a blog or on the forums - but it seems like this should be possible -

I have an MDX query - that works fine in SQL Enterprise Manager, and has my dimension members on columns, and my measures on the rows. When I try the same query in Reporting Services, I get the error:

"The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension..
Parameter name: mdx (MDXQueryGenerator)"

Although it works when you pivot the view, I really need my data presented with the members on the columns and the measures on the rows. Another forum post mentioned using the SQL 9.0 driver, but I can't see this listed anywhere (the only one I see is the .NET framework Data Provider for Microsoft Analysis Services).

Here's what my query looks like -

SELECT
{ [Time].[Month].&[2006-09-01T00:00:00] ,
[Time].[Month].&[2006-10-01T00:00:00],
[Time].[Month].&[2006-11-01T00:00:00],
[Time].[Month].&[2006-12-01T00:00:00]
} on COLUMNS,
{
[Measures].[Unique Users],
[Measures].[UU Pct 1],
[Measures].[UU Pct 2],
} ON ROWS
FROM [Cube]



Any ideas?

Thanks,
Arjun

View 8 Replies View Related

Matrix SSAS Dimension/hierarchy Order Problem

Oct 4, 2006

I have a have a relatively simple SSAS cube that I'm trying to display in a matrix but the order of the data is not correct. The rows of the matrix are a time hierarchy and the query designer produces the correct MDX and when I run the MDX in the data tab, the order is correct. However in the report preview tab, the order is incorrect. Specifically, the time hierarchy looks like this Year-Month-FullDate. The report is ordering the Months like this 1,10,11,12,2,3,4,5,6,7,8,9. I ran the MDX query in Sql Studio and the order is correct, and I used Excel to consume the same cube and it produces the correct order.

The report is somehow treating the Month like character data instead of numeric, how do I fix this?

View 4 Replies View Related

Analysis :: Set Attributes In Sort Order For A Dimension In SSAS Cube

Jul 22, 2015

For Example: I have one dimension named as "Name", Under this I have "FirstName" and "LastName" Attributes are there.But when i drag "Name" dimension, By default "First Name" dragged. But i Want "Last Name" should drag.

View 6 Replies View Related

Analysis :: How To Set Attributes In Sort Order For A Dimension In SSAS Cube

Jul 22, 2015

I have a dimension like Districts, Under that 2 Attributes are there i.e,District ID and Districts. When i drag Dimension "Districts", in OLAP grid it come District ID first. But i want Districts to drag first. How can we sort Attributes(District ID and Districts) for a dimension.

View 6 Replies View Related

Analysis :: Add Dimension To Cube Dimension Without Any Relation In Dimension Usage

Oct 26, 2015

When i add a dimension to the cube dimension without any relation in my dimension usage to any measure group my units are going down.However when i remove the dimension from the cube am getting the correct values.

View 4 Replies View Related

Analysis :: Calculate Members At Certain Level In One Hierarchy As Sum Of Corresponding Members At A Certain Level

Jun 4, 2015

Problem setting is a geography dimension with multiple user defined hierarchies in SSAS 2008. 

Ex.: 
Hierarchy 1 (political territory): level 6 --> level 5 --> level 4 --> level 3 (state) --> level 2 --> level 1
Hierarchy 2 (sales territory): level 4 --> level 3 --> level 2 (sales region) --> level 1
...
Hierarchy 9

The relationship between state and sales region is n:1, i.e. one state belongs to exactly one sales region, and one sales region can consists of one or multiple states.  Unfortunatly I can't define this attribute relationsship in the dimension because it would lead to a diamond-shaped relationsship without a user-defined-hierarchy to back it up. So far that isn't much of a problem, user don't drill down from sales region to state. But now I want to define a calculated member that multiplies a measure from the main measure group with another measure from a weighting factor measure group at the state level and above. The granularity attribute of the geography dimension in the dimension usage tab of the weighting factor measuregroup is the state. 

So far what I've got is:

CREATE MEMBER Currentcube.Measures.[weighted measure state and above] AS NULL;
SCOPE (Measures.[weighted measure state and above],
Descendants(geography.[political territory].[all member],3,SELF_AND_BEFORE),
Descendants(geography.[salesterritory].[all member],2,SELF_AND_BEFORE),
... Descendants(geography.[hierarchy 9].[all member],1,SELF_AND_BEFORE)); this = sum(existing(geography.[political territory].state.members), measures.[main measure group measure] * measures.[weighting measure group measure]);END SCOPE;

This works from a functional point of view, but is rather slow when querying any other hierarchy than the political territory hierarchy, because SSAS first goes down from the state level to the key attribute of the geography dimension, and then aggregates from there to the sales region.In other words, I want SSAS to resolve the relationsship (which state belongs to which sales region) through the dimension, and not through the fact, and apply the calculation afterwards. Like some kind of currency conversion, but only from a certain level upwards.

View 5 Replies View Related

Filtering Out 1 Time Dimension Based On The Selection In Another Time Dimension

May 19, 2008

Hi!

Need some help building a query that does the following :

I have 2 Time Dimensions ; Time (Transdate) and ClosedDate (ClosedDate)

In my report/query, if [Time].CurrentMember = [Time].[YMD].[YMD].[2006].[200610].[20061031] I want to FILTER out all ClosedDate < [ClosedDate].[YMD].[YMD].[2006].[200610].[20061031]

Both Time Dimensions are Year -> Month -> Day and have the same Members.

I have every option available, using calculated Members and/or Measures to do this.

The report I'm creating is Aging of Receivables : Balance / 30 days / 60 days / etc.. But for the Aging, I need to filter like explained above.

Appreciate all help!

Regards,
Stian Bakke

View 3 Replies View Related

Using Members And Roles With ONE Db

Jan 8, 2007

i'm not sure to put this in data or security, so i'll put it in both and put on my flame suit.....I'd like to setup the security to use the one single DB that i've setup to use for my inventory, instead of the ASPNETDB.MDF that accompanies the normal setup.If i need to include more info, please ask. 

View 1 Replies View Related

Calculated Members

May 9, 2005

This is correct
store.currentmember.properties("Store_Manager")

I want to obtain information of the column Store_Manager, the table Store, this is dimension, but BUT IT SHOW TO ME A MESSAGE OF ERROR: #ERR

Do you can help me?

View 1 Replies View Related

Calculated Members

Jun 21, 2006

Hi...
I'm trying to make a calculated member but I want it at the last level only, with the others I want that shows the sum of the previus :confused: .... Somebody can help me????

View 1 Replies View Related

ARG! - Relationship Between Two Members

Dec 9, 2006

Hi all, What I'm trying to do and having a lot of trouble with is pulling how one user is related to another user from my database. I'll explain...

The table (relationships) looks like this:
ID
type: int

RELID1
type: int, is the user id that initiated the relationship request.

RELID2
type: int, is the user id of the second person in the relationship.

Story
type: varchar(255), quick blerb on how they are related.

Type
type: int, a number 1-20 based on the relationship they have

Status
type: int, 1 = confirmed by second person, 0 = not confirmed

What i'd like to have pulled is a list of the people that person (for example: 70) is related to. I am having the two following problems:
1. unqid of "70" could be in RELID1 or RELID2 as they could have initiated the request or been the second person.
2. I don't want to display them selves in their own relationship listing

Example Data:
ID
1
2
3
4

RELID1
25
15
70
12

RELID2
54
70
13
8

Story
Met on the east coast
Met at walmart
Met walking
Met outside

Type
14
11
3
8

Status
1
1
1
1

Example Output:
Again assuming the current user is "70", the sql should pull: 15, 13.. but i'd like to pull their names from another table called "Users" where "15" and "13" are the UnqID's in a column called "ID".
So:
15 = Bob Smith
13 = Jane DOe

What I've Tried:

Code:


SELECT RELID1, RELID2, FirstName, LastName
FROM Relationship
INNER JOIN ReMembers
ON Relationship.RELID1 = ReMembers.AccountID
WHERE RELID1 = 70 OR RELID2 = 70



This works, however, this only works for RELID1 and not RELID2 column. Moreover, this repeats the data for some reason.


If you have any more questions, or something isn't clear please let me know!

Thanks!

~ PhIve ~

View 12 Replies View Related

Calculated Members

Oct 20, 2005

I have a reporting services report that I'm creating from an Analysis Services cube.  I created two calculated memebers.  When I put either one of the calculated members in the report and try to render the query or generate the report, I get an error: Memory Error: Allocation Failure: Not enough storage is avaliable to process this command.   I have 21 GB of hard drive space. 

View 3 Replies View Related

TextBoxImpl Members

May 4, 2008

I am trying to use embedded code to determine if one of the textboxes on a report is visible. When I try to call the method that I have created I receive the following error:

The Value expression for the textbox €˜textbox1€™ contains an error: Public member 'visible' on type 'TextBoxImpl' not found.


What members are available to this type? I have not been able to locate any information on the web thus far. If someone can answer this question then this will also resolve an issue in another thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3274580&SiteID=1).

View 8 Replies View Related

What Kind Of DB Is Members ASPNETDB.MDF?

Aug 3, 2007

Hello,   I created web site using VWD 2005 Express, and I use 'Create New user account' control for signin up. I need to know what kind of database is ASPNETDB.MDF ' in order to set it on server? I created ASPNETDB.MDF (database?) when I first created user in  ASP.Net Web Site Administration Tool . What tipe is ASPNETDB.MDF database?  . I have my web site on server, and DB is not working, because it is not set on server.I have this databases available on my server:Microsoft Access                   Unlimited     Microsoft SQL Server 2000     1 x    MySQL                                 1 x    PostgreSQL                           1 xDSN/ODBC                           Unlimited SQL Database Users              Unlimited         Which one you recommend should I choose for members database (ASPNETDB.MDF)?Thank you,   Beezgetz       

View 11 Replies View Related

Can't Display Calculated Members

Mar 8, 2004

I have created a few calculated members under one dimension (meaning the parent dimension is not Measures, but other dimensions). It can be showed in the Analysis Manager, but cannot be displayed in MS Excel PivotTable (MS Office 2k, xp, even 2003). Is there any solution to display the calculated members (as with the dimension) in Excel PivotTable? vba code needed? service pack needed?

View 4 Replies View Related

Adding Members To DatabaseMailUserRole

Nov 15, 2007

I'm new at SQL. But I am trying to add a user to DatabaseMailUserRole. I found a sample script, but don't know how to edit it:

sp_addrolemember @rolename = 'DatabaseMailUserRole',
@membername = '<database user>'

I'm trying to add "Student" to the DatabaseMailUserRole. Please show me the SQL statement to do this. Thanks in advance.

View 1 Replies View Related

Group Login Members

Feb 20, 2008

Hi All,

'SACCAPRiskGroup' is my group login which has few users.
Now i want to know all users in this group. For this i am using
EXEC sp_helprolemember 'SACCAPRiskGroup'.

But no luck.

Can any one help me in this.

Thanks.
Malathi.

View 2 Replies View Related

Aggregated Members And Attributes

May 29, 2008

Hi,

the query below (from Adventure Works) displays the sales amount for three products and a custom member "aggregation" which is the aggregate of these three products, and it cross joins with the attribute "colour".




Code Snippet

with member [Product].[Product Categories].[Subcategory].&[31].[aggregation] as 'AGGREGATE({ [Product].[Product Categories].[Product].&[214], [Product].[Product Categories].[Product].&[215], [Product].[Product Categories].[Product].&[220] })'



SELECT { [Date].[Calendar].[All Periods] } ON COLUMNS ,



NON EMPTY { { { [Product].[Product Categories].[Product].&[214], [Product].[Product Categories].[Product].&[215], [Product].[Product Categories].[Product].&[220],[Product].[Product Categories].[Subcategory].&[31].[aggregation] } * { [Product].[Color].[All Products].CHILDREN } } } ON ROWS



FROM [Adventure Works]



WHERE ( [Measures].[Reseller Sales Amount] )


Can someone please explain me why I'm getting this result:








All Periods

Sport-100 Helmet, Red
Red
39328.1586

Sport-100 Helmet, Black
Black
12098.0788

Sport-100 Helmet, Blue
Blue
13331.5816

aggregation
Black
64757.819

aggregation
Blue
64757.819

aggregation
Red
64757.819 (note that 64757.819 is the total of the three products)


instead of something like this:









All Periods

Sport-100 Helmet, Red
Red
39328.1586

Sport-100 Helmet, Black
Black
12098.0788

Sport-100 Helmet, Blue
Blue
13331.5816

aggregation
Black
12098.0788

aggregation
Blue
13331.5816

aggregation
Red
39328.1586

and also if anyone knows of a possible way of getting the second type of result?

please note that if I create a custom member that aggregates members of any other level of the Product Category hierarchy, the problem doesn't exist (see code and results below)




Code Snippet
WITH MEMBER [Product].[Product Categories].[Category].&[4].[Aggregation] as
'AGGREGATE({ [Product].[Product Categories].[Subcategory].&[31],
[Product].[Product Categories].[Subcategory].&[32] })'
SELECT { [Date].[Calendar].DEFAULTMEMBER } ON COLUMNS ,
NON EMPTY { { { [Product].[Product Categories].[Subcategory].&[31],
[Product].[Product Categories].[Subcategory].&[32],
[Product].[Product Categories].[Category].&[4].[Aggregation]} * { [Product].[Color].[All Products].CHILDREN } } } ON ROWS
FROM [Adventure Works]
WHERE ( [Measures].[Reseller Sales Amount] )














All Periods

Helmets
Black
87915.3689

Helmets
Blue
91052.8681

Helmets
Red
79744.6953

Hydration Packs
Silver
65518.7485

aggregation
Black
87915.3689

aggregation
Blue
91052.8681

aggregation
Red
79744.6953

aggregation
Silver
65518.7485

View 1 Replies View Related

Inferred Members Not Updated

Dec 15, 2007

Hi

We have modelled snowflake schema where DegreeDimension(Childdimension) flakes to ProviderDimension(ParentDimension).
We are using SCD transformation with inferred member support enabled and radiobutton :use a boolean column to indicate wheather current record is an inferredmember.



DegreeDimension inserts some inferred records in to ProviderDimension with Providerid(BusinessKey) as 001 and inferredrowflag as 1. Now this particular record comes from ProviderDimension and instead of updating the inferred record, the SCD wizard in ProviderDimension is inserting the inferred member as a new record.
The business key inserted by degreeDimension is equally same as the business key coming from provider dimension.

Kindly suggest as to why SCD transformation is not updating inferred member but it is inserting as a .new row.



Thanks in advance
Mahesh

View 2 Replies View Related

Problems With Paralel Members

Mar 21, 2008

The Enrolled column displays data but the sma12 shows #error

WITH MEMBER [Measures].[SMA12] AS
Sum
(
ParallelPeriod([Dim Times].[Calandar].[Month], 11):[Dim Times].[Calandar], [Measures].[Enrolled]
)
/
Count(ParallelPeriod([Dim Times].[Calandar].[Month], 11):[Dim Times].[Calandar])
SELECT {[Measures].[Enrolled], [Measures].[SMA12]} ON 0
,[Dim Product Lines].[Product Line].Members ON 1
FROM [BusinessIntelligence]


What am I doing wrong?

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved