.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 calculated members in dimension

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :

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.

View Complete Post

More Related Resource Links

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


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?


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

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?


overriding "grand totals" for calculated members

hi all, is there a workaround for overriding "grand totals" for calculated members?  (I cannot use the workaround whereby I create the calculation in the DSV or view directly due to it being a pretty complex Descendant parent/child calculation).   thanks much for any thoughts, Cos

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

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  

MDX query performance is slow for complex logic implementation in Calculated Members.

We have implemented a calculated members which involves multiple calculated members  called in a nested e.q. Cal1 calls Cal2 and Cal2 calls  Cal3  and Cal3 calls Cal4 .  And when browing Cal1 through Excel it takes around 30 mins for getting result from a Partition having row count around 5 million. Follwing are the information on the scenario I am having: We are having partitioning in the cube. one partition contains around 4-6 million records Processers on Query Server: 4 quad core RAM on the Query Server: 64 GB Calculated members having simple calculation logics are giving results in no time. There is a Calculated members which calls 6 intermediate calculated in nested way and with small Data Set like 5-6 k rows it is running fine.   Any help will be much appreciated.   Regards, Sandeep

Use of Calculated members from one fact into the other fact MDX

Hi, I have a FactBookvalues which calculates the Book value of several aircraft. this book value has a startdate going fro example from 1980 up to inlcuding 2030. With this MDX statement im able to determine what the book value is for a specific month: SELECT {[Measures].[Asset Bookvalue balance]} ON 0,   [MSN].[Asset Label].allmembers on 1 from CMSX_DWH_OLAP where {StrToMember("[Book Value Start Date].[Year - Quarter - Month].[Month].&["  + Format(now(), "yyyy") + "]&[" +  Format(now(), "MM") + "]")}   this work great in SQL server management studio, it will list all aircraft with the bookvalue for that particulair month.What i want is to get this value for that specific date and use this value as a fact for the AssetFact. how is this possible? i tried to make a calculated member but this didnt work. Im a bit stuck. I dont want to get this bookvalue from the ETL process but from the the cube itself to see how you can reuse dataelements in other areas of ssas

Replace pre-calculated Measures at "All" Level depending upon the Dimension Hierarchy Level

Have an interesting Challenge; thought will reach out to you if you can help Currently I am working on SSAS 2008 OLAP Cube, whereby most aggregate level measures are pre-calculated using the C# Code as part of ETL and are stored in Data Warehouse as well as Measure Groups in Cube (MOLAP). These are mostly non-additive measures have to choose this approach for performance reasons, considering the complexity of the aggregations and data volumes.  Data  Volumes are Huge (about 300+ Million Rows/ per day),  98 % of measures are non additive / semi additive.  Cube is used primarily for advance analytics and will be eventually used for data mining like time series , what if analysis and scenario analysis  Excel is used as front end . Question is how can we replace the aggregate level data for various dimensions attributes (Totals and Grand Totals) from pre-calculated measures, those are also available in the Cube as measure groups? We are currently using Scope and Root statement which is not working as expected SCOPE ([D1].[H1].[A1], M1) Root (D1) = <Get Pre-calculated value for M1 from related Measure Group for D1].[H1].[A1],  > EndScope; SCOPE ([D1].[H1].[A2], M1) Root (D1) = <Get Pre-calculated value for M1 from related Measure Group for D1].[H1].[A2] > EndScope;   Thanks in Advance   Best Regards,   Dave

Sorting by Calculated Templatefield in Datagrid


I have tried several different approaches .


SOMEONE PLEASE sow me how to sort on template field with a calculated value.

Basically i have half the data i need in the DB and the other half comes from textbox1.text

I take the text in textbox1 and pass it along with a value from the DB (Orig) to a function that returns miles.

                    <asp:Label ID="Label1" runat="server" Text='<%# miles(Eval("Orig"), textbox1.text) %>'>

Whit the above method everything displays correctly but i cannot sort on that column .


I also tried using the miles() function in my select statement for my datasource but that was a nightmare and couldn't get that to work i either got "undeclared function "miles()" or when i fixed that i got "orig" not declared.

Sorry code is so jumbled , entire code is below:

<%@ Page Title="" Language="VB" MasterPageFile="~/Secure/MasterPage.master" %>

<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.Data" %>
<script runat="server">
    ' I

Creation of two calculated members


I have set of data for which I would like to create two user defined measure members which could be used when browsing the cube or when reporting using SSRS. I'm a MDX novice and really struggling with how to go about it (coming from a T-SQL background!) so will try to explain my problem based on the Adventure Works Cube. 

The first of the two measures is to get a result back which is the most recent non-empty record of a certain type prior to the currently browsed date.

So using this data from AdventureWorks as a good example:

(Filter: <Internet Sales Order Details.Sales Order Line> = 1)

(Filter: <Sales Reason.Sales Reason> = Manufacturer, Other)


Miscrosoft SQL Server Analysis Service does not show conditions and calculated members



I am using MS visual studio 2005 to modify a report. The reports has got some filters (conditions) and calculated fields but when I open the report, all it shows me blanks values in condition window screen (where it displays Dimension, hierarchy, Operator, Filter Expression, Parameter fields) and also it does not display any calculated fields in the calculated members widnow. 

I am using SP1. Do I need to re-install the software or am I misisng some tool to turn on.

Thanks in Advance


Calculated field Sorting Now Working , but have performance question


 Thanks to mitja.GTI.

My sorting is working ...

although i had to take the table that has my calculated column and make a view (so i could filter) and then bind the gridview to the view.

My question is as this.

1.) in order to get my calculated field i had to load my data into a table (as suggested)

2.)Add a column to the table and perform calculations

3.)make a dataview from the tables because i could not get the Gridview.datasource= ds.tables("loads").select("Deadhead <= " & sngRadius

4.)the largest of the 3 tables my query is based on contains about 7000 records. The way i have the logic & programming now everytime a new "truck location" is put in the textbox1 and "find loads/button1" is clicked. It calculates the deadhead miles of all 7000 records before filtering. I am filtering based on the deadhead miles.

Does anyone have any recomendation about how to speed this up a bit.

Also in order to get paging to work i had to rebind/filter the data again ... means looping through all 7000 records each time paging is called. This seems terribly ineffecient as i am already forced to use access instead of SQL.


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.


Using calculated members in rows


Hi guys

Is it possible to use Calculated Members in rows? I'm trying to alias my column name by using members on the rows axis. Take for example the following MDX query:
















Named Sets vs. Calculated Members and the Filter Field Conundrum (Year-to-Date measures by company c


I’m going to describe our SSAS solution a bit and what I have in it so far in order to ask the question regarding “year-to-date” sales. This is part example and part question, I suppose. "The Filter Field Conundrum" sounds like it should be a high-tech Encyclopedia Brown case or something. Can't seem to find a similar situation through searching, although perhaps I'm wrong and this has been done before.

We have a data warehouse that contains our transactional detail for the last nine or so years. Tables are somewhat analogous to the AdventureWorks solution, with a table for Sales, a table for Customers, Geographic information, etc. The data warehouse has a time table that contains many of the usual bits of information one would expect:

Full Date (mm/dd/yyyy)
Month Number
Month Name
Day of Month
Day of Week
Day of Week Name
Day of Year
Week of Year
and maybe a few other typical fields.

Then we also have:
Working Day (bit, with a 1 indicating that this was a scheduled working day according to our company calendar, and a 0 indicating that it was not)
Working Day of Month
Working Day of Year
(these fields indicate that a day is the nth scheduled working day, according to our company calendar, for a given month or year)

So, for example, 5/1

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