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

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

Long list of calculated members

Posted By:      Posted Date: September 07, 2010    Points: 0   Category :Sql Server
·         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  

View Complete Post

More Related Resource Links

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

Calculated date field displays differently in List View and Data View web parts

In SharePoint GUI I created a simple custom list with a calculated Date/Time field (Due).  The calculation is =[Created]+7.  I'm trying to display this data on a page using a DVWP.  But first, for testing purposes, I also created a second calculated Date/Time (TestCreate) field in my custom list that is simply =[Created]. In the List View from within SharePoint, the dates show as expected (for example): Created = 8/12/2010 3:45 PM Due = 8/19/2010 3:45 PM TestCreate = 8/12/2010 3:45 PM In SharePoint Designer, I added a Data View web part and the above three data elements (each configured to display mm/dd/yyyy hh:mm:ss tt).  I see the time displayed for the example list item as: Created = 8/12/2010 3:45:30 PM Due = 8/19/2010 10:45:00 AM TestCreate = 8/12/2010 10:45:00 AM I've tested a couple scenarios and the calculated fields always display five hours in the past (along with dropping the seconds).  It seems like the DVWP is returning the date in a different time zone (with no seconds).  The lcid is set right (1033).  I didn't see any time zone settings for the DVWP specifically.  I'm basically stuck.  Could someone help me figure this one out. Thanks.

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

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

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)


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

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


ListData.svc fails if list has calculated column with Yes/No (boolean) return type


I have a list with a calculated column whose return type is Yes/No.  If I try to browse to the list or a list item using REST via ListData.svc, I get the error "An error occurred while processing this request".  Can't seem to find anything in 14\LOGS or the System or Application event logs.  I've tried multiple calculation formulas such as:



Both display "Yes" when viewing through the SharePoint UI and are sortable and filterable.  If I change the calculated column return type to anything else (single line of text, currency, date/time, etc.) and update the formula as necessary, ListData.svc displays the list item XML as expected.  If I replace the calculated column with a regular column whose type is Yes/No, ListData.svc displays the list item XML as expected.

Is this a known bug?  Is there a workaround (other than manually casting to bool in my code)?

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:
















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?


wpf Datagrid bound to a Simple List of Long values - not so simple


Somewhere I've lost the plot.  I have a complicated senario, and solved most of it, but still I have one issue that illudes me.  Here goes

User Control has custom Dependancy Properties so that it can bind to it's host window.  One property Route is a string.  The string is actually a comma delimited list of long (BigInt stored values in SQL 2008r2 - pulled through a DataSet, then parsed into a string) ex.  "9,6,12"  Real simple so far.  The user control binds a CollectionViewSource to this string, and uses a custom converter to turn the string into a list(of long)... (or ObservableCollection(of Long), or BindingList(of Long))  The key is the value must be a long, when pulled.  Next enters the DataGrid...

I have a datagrid with 1 column.  Its a DataGridComboBoxColumn.  Its item source is bound to an actual dataset.datatable based CollectionViewSource called RoutingLocations.  A simple list of ID,Abbrev,LocationID.  I bind the DisplayValue to Abbrev.  So I get a nice lookup of the RoutingLocations in the comboBox Column.  Then my list(of Long) gets attached to the DataGrid as it's ItemSource.  End Result  I get 3 rows properly displaying the selected Abbrev for the supplied ID.

Now the problem.  I can not get this datagrid to update the source (it nev

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

can set contain calculated members?


member [Material].[Material Type].[HL_LG] as ([Material].[Material Type].&[HL]+[Material].[Material Type].&[LG])
set [Material Type] as {[Material].[Material Type].&[O],
[Material].[Material Type].[HL_LG],[Material].[Material Type].&[W]}
on columns,
 non empty
[Material Type]
} on rows
 SELECT ([Date].[Calendar].[Date].[2010-09-01]:[Date].[Calendar].[Date].[2010-09-30]) on columns
,[Material].[Material Type].[All].[HL_LG] on rows
 FROM [InSiteCube]
[Location].[Location Active].&[-1]

It says A set has been encountered that cannot contain calculated members.
if i remove the 5th row from the bottom (,[Material].[Material Type].[All].[HL_LG] on rows) it works fine.

Any suggestion is appreciated. Please help.


Thanks, Saikhnaa

SharePoint calculated field based on other list values


Hello Everybody!

I have a repeating list that seems like that

List 1

Title    Number


A        10

B        11

A        11

B        9


I need to create a list with a field that contains each different titles from list 1 (A + B) and another column with the sum of the numbers associated with A or B in list 1.

List 2

Title     Number


A         21

B         20


My problem is that I don't know which method using to do that.

Calculated field can only use list field (not external list), infopath can get this but I don't know how to do it.

I wanted to know if someone has an issue other than developing. Because my client doesn't want to pay for a development.

Thank you very much!!!!


InfoPath Calculated Field doesn't Post to SharePoint List when form is submitted


I have a nice InfoPath form that includes a calculated total field.  That works great.   The form is published to a SharePoint list and when users of the form Submit the form to the list the form information neatly populates to the list.   Well, all of the fields populate to the list except for the calculated total field, that one is just blank.  Naturally, I've noodled around to figure out why and therefore I'm here now asking for guidance.   What should I be focusing on to resolve this issue?


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