.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

Calculated measures, calculated cells, and custom rollups issue

Posted By:      Posted Date: August 29, 2010    Points: 0   Category :Sql Server
Hi,  I'm a little bit confused according to the purpose of Calculated measures, calculated cells, and custom rollups as they lead practically to the same goal, is there any differnce between those modes from a technical point of view. And for the last one, the custom roll up the question how to perform this, I need a walkthrough that lead me to undersand the way to set a custom roll up to a given dimesion, I visited microsoft site but the inforamtion that I have is poor, so is there any other source that exposes that as a step by step example? Thank youThe complexity resides in the simplicity

View Complete Post

More Related Resource Links

Weird Date vs Date/Time issue using a calculated column

I'm attempting to use the fab 40 attendance template. I don't need the time to show - Im able to hide that on the forms with jquery (endusersharepoint.com thank you!!) I WAS ATTEMPTING to create a calculated column called Start Date where the formula simply reads '=[Start Time]' When it's set to display 'Date Only' the date is off by a day. If I switch it to 'Date & Time' I get the correct date. Huh?

Created Calculated Measures Permenantly with MDX

I did some research on how to create a calculated measure in MDX, i found that by using MDX to create a Calculated Measure, the measure will not be persistent in the cube but only query-scoped or session-scoped. is it possible to create a persistent calculated measure using MDX? if not, where is a good point for me to start looking into xmla to create the calculated measure?

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

Help with Calculated Measures


Hi All,

I need to calculate adjusted Revenue based on Total Revenue - Tax - COGS.

But my Tax/COGS are based on Product Types, Partners, etc...


                 Tax       COGS

Partner 1     10%      5%

Partner 2      5%       10%

Partner 3      5%       0%


My questions are as follow:

1. Is this kind of calculation best done in MDX or TSQL?

I have about 280K rows per month, and 28 calculations per row (e.g. c= a x b is 1 calculation, then d = c * 0.1 is another), so about 8 Million calculations.  It's done using Excel 2010 currently.

2. How do I deal with zero or empty cell, will MDX speed up the performance?

3. How do I do this kind of calculation in MDX?  I don't know how to setup dimensions with Calculated Measures.

The books I have seen so far shows me easy calculations like [Measures].[Total Sales] - [Measures].[Discount] but each combination of Partner/Product

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

cell writeback question, normal and calculated measures



I have writeback enabled for one of my cubes and it's working fine. I'm updating regular measures that are summed.

However I have a calculated measure that uses these regular mesures in it's calculation. 

But when the regular measures are updated via writeback, the calculated measure stays the same. Writeback seems pretty useless if calculations that take those measures into account don't see the updated values. Am I missing something here? Do I need to alter the mdx of the calculated measure or something like that?


Thanks in advance.


Chart display issue with Moving Average Calculated Series


I have successfully produced a line graph containing 2 data series; a count of events by month and a moving average (5 month period) of said events.

My problem is trying to limit the chart to show ONLY a certain period of time.  In order to generate the Moving Average (calculated series) I had to make the query go further back in time to count the previous months' events.

For Example:  Chart should display Jan2010 through Oct2010, EventsPerMonth (series1), MovingAVG (series2). 

At first it displayed back to the begining of the data which was Sept2009 - Oct2010.  I figured out how to cut it off at Jan2010 using the Horizontal Axis Properties > Scalar features.

Final issue: I need to get the first data point (Jan2010) off of the Y-axis as a starting point.  Basically I need to shift it one interval to right, leaving a space, so that there is no line connected between the data line and the Y-axis. 

This is difficult to discribe, I apologize if this isn't super clear. 

Thank you!


Calculated measures limit?

Is there a limit to the number of calculated measures a cube can have?  Is there a performance hit after so many measures?

Dimension Security on some level and access to higher levels for Calculated/Scoped Measures



I'm trying to solve the following task:

I have a simple regular dimension e.g. calendar, there are some attributes: datekey (20100117, ...), month (201001, ...), year (2010).

There is a single user definded hierarchy called calendar:
datekey --> month --> year, the attribute relationships are configured accordingly.

There is one role that has only access to month 201001 (dimension data security - allowed memberset) - please be aware that I want to use "Visual Totals" or something simialr.

Now I have to calculate a measure: ([calendar].[calendar].[201001], [Measures].[sales]) / ([calendar].[calendar].[2010], [Measures].[sales])

How do I have to configure the security or calculate the measure that the sum of all months is used and by selecting the year only the Visual Total is shown?

Any hint is appreciated ;-)


Custom controls and ViewState management issue



I want to know that when we create custom controls, then is there State Management through ViewState done automatically or we have to handle it explicitely ?Since i have created Custom Controls that is behaving as if it is not maintaining its state on PostBack.

How to get character count in 'Multiple Text 'column using Calculated Column?


Hi All,

I have a multiple text box in a list

I want

  • A column which lists number of characters in multiple list column example 188, 199 etc
  • Multiple character column must not have more than 200 chars
Is this possible?

Appreciate all the help!


Custom Search issue with freetext


Hi All,

My Environment : MOSS 2007 (sp2) with SQL Server 2005

I have a custom search webpart which uses FullTextSqlQuery to fetch the results from Index. I have a some keywords and a free text boxt to build the search query. Poeple enter their query by selecting the keywords and enter some free text into the text box.

Issue : When any PDF document (Not tested with .Doc) is searched using custom webpart with the numbers like "2,0233.21" I do not get any results returned even though the results are returned using the SharePoint out of box full text search. But when I search the content like "2+0233+21" I get results both in Out of box and in custom search webpart.

Requirement: The Custom search should be able to find the document when numbers like "2,0233.21" are entered. It should work exactly like the out of box search.

Thanks in advance for your suggestions.



Calculated Columns in a Form Library receive error: Value does not fall within the expected range.


I have a forms library that posts the date the form was created. We would like to create a calculated column that allows us to look at these forms based on the month and year they were created. I had created a calculated column that extracts the month and replaces the number with a text string by using an if statement; "IF(MONTH([Proposal Date])=1,"January","") It goes on to evaluate each month and replace the number with the text string. This formula worked for quite some time, though now when I try to add any calculated columns or edit any existing calculated columns on this form library I receive the following error: Value does not fall within the expected range. Now my existing calculated columns (like the one above) do not work and I can not create new calcualted columns. Any insight would be awesome as this has been plaguing me for a couple of months now.

Trying to set Calendar Time Interval Using Calculated Column, but getting Error



So i was trying to use a calculated column that added 7 days to the start date of a calendar item. The calculated column worked fine because i checked with several tests. Then i tried using the Column as the End Time in the Time Interval Setting in the Calendar View. This broke Sharepoint. Not the normal graceful error im using to seeing from Sharepoint but the Server Error Page when a dump happens in a ASP page. I havent enabled the full error page because i assume its not like i can fix it, i dont have the source code.


Does anyone else experience this? If so is it a known problem and being worked on?




Calculated Date Fields

I'm trying to calculate whether a delivery is on time or late and by how many days. Is there a way to format the column so it show's the result in the number format as opposed to showing up as a date? Negative numbers don't export into excel well.

Font Expression in Calculated memebers?

I am using BIDS 2005 version 8.0.50727.42 The fontName and Fontsize properties of calculated memebers are not working ? Any one got the same issue?   Thanks, Alex  

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
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