.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Sorting the list of attributes (not members) of a dimension

Posted By:      Posted Date: September 20, 2010    Points: 0   Category :Sql Server

Hi there,

Any solution with SSAS 2008 R2 for that problem?


"It looks very simple, but I cannot find how to order the list of attributes in a dimension. Let's say I have a dimension Item with two attributes:

- Code
- Description

and I want the Description to appear first. By default it's using the alphabetical ordering but can I change that ?"

It works fine with measures. In BIDS, CubeDesigner, there is are up und down buttons in order to move measures up or down. In the attribute pane, these buttons are inactive.

any help?


View Complete Post

More Related Resource Links

Sorting calculated members in dimension


Hello. I've created a calculated member in dimension member.

My dimension member have three values: Item A, Item B and Item D. My calculated member is Item C (Item C = Item A + Item B). When I browsing the cube this calculated member is shown at the bottom of the list.

Is possible to specify a sort order in MDX for a calculated member? I use a SSAS 2008R2.

Thanks in advance.

SSAS - How to get other members from dimension that has Parent Child hierarchy?

I have a Sales Territory dimension that has employee and parent employee attribute on which parent child hierarchy is defined and it gives below hierarchy while browsing - - Mark Rolls --- Lumin Jacs ----- Larry Gomes ------- Messica Owens ------- Tom Ted ----------- Jackson Lopez ----- Matthew Ron --- Fred jacob - Jason Ron --- Jecy Pedro   But beside this parent-child hierarchy I have other attributes like employee address, email and telephone. My facts related sales transaction is tagged to lowest level. For example here facts are available only for Jackson Lopez (being Field Executive). When use below query I get complete sales reporting hierarchy result with some measures like sales amount, sales volume. But while accesing other attributes like address or email, it's repeating address/email/telephone of jackson Lopes everywhere to whom fact record is linked, Actually I want address/email/telephone of each sales employee from that dimension within hierarchy. How do I get it? The query I used is: Here Parent Terr ID is parent child hierachy. SELECT ( Descendants( { [Dim SalesRegion].[Parent Terr ID].[Employee Level 01].&[538018] /* here Mark Rolls is 538018 */ }, 0,AFTER), NONEMPTY([Dim SalesRegion].[Emp Address].[Emp Address].Members), NONEMPTY([Dim SalesRegion].[Emp Email].[Emp Email].Members) ) ON ROWS , { ([Dim Date].[The Year].[The Year].[CY-2010], [Dim

add another dimension to list

How can I add another dimension as string to this: list<patientInfo> strlist

Long list of calculated members

·         I have a long list of calculated members. How can I make this long list user friendly like categorizing them by functionality? Can I create measure group for calculated measures?   Alex  

sorting a list by properties of its objects

I have a list that contains several instances of a custom class and I want to sort it by properties of the custom class it containseg  heres the classpublic class Theclass { public long ID { get; set; } public DateTime Date { get; set; } public int Sequence { get; set; } }List<TheClass> lst = new List<TheClass>();lst.add(instance1 of 'TheClass')lst.add(instance2 of 'TheClass')etc, etcso I could end up with a lst object containing many instances of 'TheClass'how could I subseqently sort this list by sequence within IDor by Date within ID

MDX query to filter on specific dimension attributes

Ok, trying to figure out how to filter on specific dimension attributes. Are there any suggestions? I have a measure that needs to be filtered by a dimension that has numerous attributes (20 attributes in total) but I only want to show that measure against 10 of the attributes and once I get that result I need filter that result set against my time dimension (which this part I have figure out)   This is what I have so far: With member [calculated_measure_name] as ( [measures].[count_of_people], { [dimelgiblestatus].[elgible].[elgible1], [dimelgiblestatus].[elgible].[elgible2], [dimelgiblestatus].[elgible].[elgible3], [dimelgiblestatus].[elgible].[elgible4], [dimelgiblestatus].[elgible].[elgible5], [dimelgiblestatus].[elgible].[elgible6], }   )   Select [calculated_measure_name] on columns, [date].[fsicalyear].&[2008], [date].[fsicalyear].&[2009], [date].[fsicalyear].&[2010], [date].[fsicalyear].&[2011] on rows from mycube   When I run it, it gets no errors but in the result set for the numbers I get "#error"; do I need to basically create a subcube in order to get the result like I would like? If so how would I start doing that?   ThanksNetwork Analyst

SSAS 2K5 - Name of the dimension included in attributes

Hi I have a fact table with 2 field using the same dimension table. So, in SSAS 2005, The dimension is added (Lets say dimXYZ). So the dimYXY is used for 2 field in my fact table. When I go in my "dimension usage" tab ... i'm adding the 2 dimension that are bind to 1 physical dimension wich is dimXYZ. So I rename (in the dimension usage tab) the dimension freshly added. ("Dimension AB" and "Dimension CD"). But when I browse the cube, the attributes are displayed like Dimension AB.NameOfAttributes and Dimension CD.NameOfAttributes) Is there a way to hide the name of the dimension in the attributes ? Thanks * Sorry for the bad english ... I speak french ;-)--------------------- Richard Martin

Duplicate key attributes with SCD2 dimension

I have a dimension table populated according to the SCD2 type, and have the following data (example data)   Surrogate Key Business Key EmployeeName Location SCD_StartTime SCD_EndTime 1 100 John Smith Boston 2001-12-31 NULL 2 101 Jane Doe Boston 2001-12-31 2004-09-30 3 101 Jane Doe New York 2004-09-30 NULL   In other words, in September 2004, Jane Doe moved from Boston to New York. In my dimension, I have created the following hierarchy: Location -> Employee (business key) -> Surrogate Key What I would like to achieve is the following structure when I browse the dimension on this hierarchy: - Location ---- Boston ------- John Smith ------- Jane Doe ---- New York ------- Jane Doe Instead of this, I get the error that a duplicate attribute key has been found on the field "Employee". I understand the error because in the hierarchy, Jane Doe falls under Boston and New York, and SSAS is not able to put them under one of them. But since Jane has existed in both locations, I would like to have her appear under both locations. How can I accomplish this? Thanks! -

Advanced Sorting on a List



I just created a list and my requirement is to sort by last name. However, when I go to the Sort & Group section in SPD, I can't see any advanced sorting options and can only sort by the fields themselves. How would I apply a sort expression to a standard list?


Switch selected members of two attributes with identically named members



I have two attributes (A and B) which have the exact same set of members.
I have a measure giving me a value for the relationship (A,B).

In a calculated measure, I would like to automatically calculate the value for (B,A) (which is not the same as (A,B)).

I was thinking of doing the following in the calculated measure:
1. Get the selected members of attribute A.
2. Perform SetToStr on the set of selected members.
3. Replace the name of attribute A by the name of attribute B.
4. Perform StrToMember.
5. Do steps 1-4 for attribute B.
6. Retrieve the value.

Unfortunately I have no idea how to perform a string replacement with MDX.

Am I missing something?


Please Help with Defining Calculated Measure based on Dimension Members



The issue looks pretty simple yet I got stuck. I want to define the measure [NR Var] as [NR]-[F NR] for years/months/dates before 2009 and [NR]-[FI NR] for 2009 and on. I am using [Year]-[Month]-[Date] hierarchies in the cube. I have defined scope:

Scope ([Measures].[NR Var], {
descendants([Date].[Year - Month - Date].[Year].&[2004], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2005], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2006], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2007], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2008], 2, self_before_after)});
this=[NR]-[F NR];
End Scope;

but it does not work for some reason. If I get rid of DESCENDANTS function, it works but applies the scope only to the YEAR level. Another problem with using SCOPE is that it affects [NR Var] only yet I have other calcs derivative of [NR Var] which I want the scope to affect as well.

So I ideally I would like to have something like:



case when {
descendants([Date].[Year - Month - Date].[Year].&[2004], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2005], 2, self_befor

Calculated members in dimension are not filterable in reports


Hi all,

I have created a time calculation dimension (based on David ) with calculated members. When I work in Management Studio, I can select the dimension called [Date Calculations] and use it in the query. The only thing is, I don't need everytime all the available calculations, so I want to restrict them by selecting a few.

This is what I have done in the query below, but when I want to run this query I get an error:

TITLE: Microsoft SQL Server 2008 Analysis Services

A set has been encountered that cannot contain calculated members.




 NON EMPTY { [Measures].[Stock Value], 
       [Measures].[Nr of products] } ON COLUMNS, 
 NON EMPTY { ([Date].[Year - Month - Date].[Day].ALLMEMBERS * 
        [Date Calculations].[Date Calculations].[Date Calculations].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

Select other dimension members automatically



I'm trying to figure out if something is possible. Assume a simple fact table of sales $ and 2 dimensions, customers and products.

The requirement is, when a customer is selected in the customer dimension and a product is selected in the product dimension, any other customers that have sales for that product would also be selected. So if I select John Smith and product 1234, anyone else that has purchased 1234 will also be selected in the customer dimension. Is this possible? I don't know a ton about mdx, I thought it was only for queries, but are there say an event I can capture when the user clicks on a customer and run some mdx to select other customers that have also purchased that product? Or some other way if possible.

Thanks in advance.


Excel 2010 - Calculated Members on Dimension



Was wondering if there was any workaround in Excel 2010 to make these appear. My understanding is that the behaviour is unchanged from 2007.

Does anyone know of any plans to support calculated members on dimensions in Excel?


ConvertToUnknown for Dimension Attributes



I have a dimension table:



Now, in AS Vendor dimension, I drag and drop "AgentName" attribute from tblAgent. This works fine. However, what AS does now, is that it basicly tries to inner join these two tables. So if there is no Agent for Vendor, it doesn't load that Vendor at all. How can I force it to conver such to unknown?

Modelling snowflake type schema as referenced dimension and unknown members


Hi All

I have a snowflake schema that has the following tables:

1. FactTransaction  

2. DimSecurity

3. DimSecurityGrouping 

DimSecurity.id joins directly to FactTransaction.securityid. The DimSecurityGrouping table is used to model security groupings for reporting purposes. Multiple groupings can be defined for the same security. The layout of the table us such GroupName, Level1Name, Level2Name, SecurityNumber. DimSecurityGrouping.securitynumber join to DimSecurity.securitynumber (natural keys). Not every entry in DimSecurity has a defined grouping.

In BIDS I have setup a referenced relationship between DimSecurityGrouping and DimSecurity. When I process the cube I get errors saying "the attribute key cannot be found..." for records in DimSecurity without matching records in DimSecurityGrouping.

How can I ignore just these errors for just this scenario and not all errors on the cube when processing, or is there a better alternative?


Persisting DropDown List attributes accross page Postbacks


Can anyone advice how can DropDownList attributes be persisted accross page Postbacks?


DropdownList1.AutoPostBack = True

On Postback the attribute is no longer available (ie. attributes.count=0).
I tried persisting using the code below, but was unsuccessful:

Before PostBack: ViewState.Add("DDL","DropdownList1")
On PostBack: DropdownList1 = ViewState("DDL")

This generated the error: DropDownlist is not marked as serializable.

I than tried:

Before PostBack: Session.Add("DDL","DropdownList1")
On PostBack: DropdownList1 = Session("DDL")

This did not err but attributes were still not available.

Ideas Anyone?








ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend