.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

Problem with a calculated measure that works across different time hierarchies.

Posted By:      Posted Date: October 04, 2010    Points: 0   Category :Sql Server

I was inspired to create a calculated measure which works with all available time hierarchies after reading this original post.


I created   a calculated measure called ‘Selected Hierarchy’ as follow which returns either ‘Admit’, ‘Received’ or ‘Discharge’ as the hierarchy is selected.


when NOT IsError(Extract ( Axis (0), [Admit Date].[Date Hierarchy]).Count ) then 'Admit'

View Complete Post

More Related Resource Links

Calculated measure that works across multiple hierarchies

Dear all,

I need to create a calculated measure for YTD that dynamically calculates based on the date hierarchy selected by the user (SSRS & Excel).

On the date dimension, I have 2 hierarchies "Year-Month-Day" and "Year-Week-Day", that both share the year and day attributes.

What's the best way to go about this?

Many thanks,


Calculated measure with axis problem


I'm using a Cube to display the % of Total Head Count measure fo [Measure].[Head_Count] with help of cubegrid. I show the record level by level each level form a MDX query and send to the ssas.

I'm using the calculated measure with axis(0,0) formula, when click the second level i get the wrong data. what is the problem ??


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?




Use Sharepoint Calendar Start Time field in calculated column

I'm trying to limit the calendar view to current week using the Sharepoint defined field Start Time. However, when I display the serial number behind the Start Time it looks like it is actually giving me the serial number for the Created Date field. I believe it is because the appointment is reoccurring but I'm hoping there is a way around it. I created a new testdate field and manually entered the date and set those appointments as all day events however they are not reocurring appointments. The testdate returns the correct serial number however the start time returns an incorrect serial number. SerialNumber1=DATE(YEAR([Start Time]),MONTH([Start Time]),DAY([Start Time])) SerialNumber2=DATE(YEAR([testdate]),MONTH([testdate]),DAY([testdate])) Start Time  SerialNumber1 Testdate  SerialNumber2 8/31/2010 40420 8/31/2010 40421 8/31/2010* 40405    9/1/2010* 40405    9/2/2010* 40405    9/7/2010 40427 9/7/2010 40428 9/13/2010* 40405    9/14/2010 40434 9/14/2010 40435 9/14/2010* 40405    9/15/2010* 40405    *These are reoccurring appointments scheduled as all day events. The others were entered as one time appointments scheduled as all day events.

run-time error's do not jump to problem line but to assembly with some xml error.

  Used too, when there was a runtime error the ide would break at the line that caused the problem. Now I'm thrown into some assembly code with the error System.Windows.Markup.XamlParseException was unhandled   Message='The invocation of the constructor on type 'Main.MainWindow' that matches the specified binding constraints threw an exception.' Line number '4' and line   Which is extremely uninformative and inefficient. I can look down the chain to find the error thrown but this still is not very informative. How the heck can I get it back to the way it used to be? This has something to do with wpf and the assembly code has System.Windows.Markup.XamlReader.RewrapException as the address. I spend anywhere from 10x to 10000x as long because of this. If the code is simple then I still have to read the error code and click a button which takes time.   Also, I'd rather it break on the line and quit the process as it used to do. I don't need JIT debugging.  

PeriodsToDate problem with calculated member

Hi I've come across an example where my calculated member will yield wrong results. I've been trying in the last few days to find a solution for this, but so far no luck. I have two measures: [Measures].[Marketing Spending (Actual)]: Aggregation: Sum [Measures].[New Subscriptions (Actual)]: Aggregation: Sum Then I created following calculated member in the schema: [Measures].[CPA (Actual)]=[Measures].[Marketing Spending (Actual)]/[Measures].[New Subscriptions (Actual)] If I run a query like this I get the correct results: SELECT [Measures].[CPA (Actual)] ON 0 from [Global B2C Weekly KPI Cube] WHERE [Date.Weekly Calendar].[2010].[1]:[Date.Weekly Calendar].[2010].[32] I can even add some other dimensions and all looks fine. But, when I create a calculated Member in the MDX query with PeriodsToDate and Aggregate, the results looks like the sum of the CPAs by week. WITH MEMBER [Measures].[x] AS Aggregate(PeriodsToDate([Date.Weekly Calendar].[2010],[Date.Weekly Calendar].[2010].[32]),[Measures].[CPA (Actual)]) SELECT [Measures].[x] ON 0 from [Global B2C Weekly KPI Cube] So my take is that as PeriodsToDate gives back all the members, the calculated measure is calculated for each member and then summed up.  In this case the measures should be summed first for the time period and then the calculation should be performed. I don't want to create extra memb

Measure Dependent Calculated Measure

Hi Was looking to create a calculated Measure Which needs to be dependent on another column of the FACT table. I need to have the Average of the particular Measure based on the fact that this particular 'set' has a common Related dimension's The example would give a better picture. I have a set of dimensions like STUDENT, TEACHER, COURSE etc as my dimensions and the grades, class standing based on marks for the subject, percentage of marks scored, Percentile, Marks Scored,StudentID and the related PK's etc are the measures on my fact table Here I want the Avg of the marks scored by the Students based on the grade say is  A or B or C  I was looking to create a calculated measure by using MDX which was a follows (NOT THE CORRECT SYNTAX JUST THE IDEA) SUM  (MARKS SCORED)/COUNT(DISTINCT StudentID) WHERE GRADE = A As this would be measured against a particular subject or a teacher or a Course. I was hoping if I could get help in putting this in a proper SYNATAX   Thanks in advance.  

Please help with converting calculated measure.

Hi, I have to dimensions: [Currency] with members CAD and USD and [Convet to] with members None, CAD, USD. The measures involved in calculation are [Amount Billed] and [Amount Received] The problem calculation is defined pretty simple: MEMBER CURRENTCUBE.[Measures].[AR] AS (abs([Measures].[Amount Billed] - [Measures].[Amount Received])>0.1,[Measures].[Amount Billed] - [Measures].[Amount Received] ,null); and worked perfect without currency conversion . I've read some info about currency conversion and designed this calculation to convert: scope (leaves([Time 2])); scope([Convert To].[Convert To].&[USD],[Currency].[Currency].&[CAD]); [Measures].[Amount Received] = ([Measures].[Amount Received],[Convert To].[Convert To].&[None])/validmeasure([Measures].[Cdrate]); [Measures].[Amount Billed] = ([Measures].[Amount Billed],[Convert To].[Convert To].&[None])/validmeasure([Measures].[Cdrate]); End Scope; scope([Convert To].[Convert To].&[CAD],[Currency].[Currency].&[USD]); [Measures].[Amount Received] = ([Measures].[Amount Received],[Convert To].[Convert To].&[None])*validmeasure([Measures].[Cdrate]); [Measures].[Amount Billed] = ([Measures].[Amount Billed],[Convert To].[Convert To].&[None])*validmeasure([Measures].[Cdrate]); End Scope; End Scope; That calculation provides correct results for both [Amount Billed] and [Amount

Optimize Calculated Measure containing COUNT EXISTING

Hi, My goal is to change the text color of all cells that contain aggregated values. Currently I achieve it like this: I COUNT the members of all attributes of all dimensions. To be multi-select-safe I am using the EXISTING keyword. If the members count of at least one dimension is not 1 than the background color of the cell is changed. CREATE MEMBER CURRENTCUBE .[Measures].[SingleCellSelected]   AS iif ((COUNT (Existing ([Dim1].[Attr1].[ Attr1].MEMBERS ))=1) AND (COUNT (Existing ([Dim1].[Attr2].[Attr2].MEMBERS ))=1) AND (COUNT (Existing ([Dim2].[Attr3].[Attr3].MEMBERS ))=1) AND (COUNT (Existing ([Dim2].[Attr4].[Attr4].MEMBERS ))=1) AND (COUNT (Existing ([Dim3].[Attr5].[Attr5].MEMBERS ))=1),1,0),   VISIBLE = 0  ;    SCOPE ([Measures].AllMembers ); FORE_COLOR (this ) = iif ([Measures].[SingleCellSelected]=1,0,16744448); END SCOPE ; This approach works but it performs badly with attributes with many members. Do you have any idea how to optimize this? Thank you!

MDX Query Measure with time period

Hi! How to set up the period in MDX? I need the measure what would be seted up by default 4, 29… days from today. For axample Amount from current day (02.09.2010) till 4 days from today is 06.09.2010. As Result I have to get a report: Buy form Vendor Amount today 02.09.2010 Amount +4days 02-06.09.2010 Amount +29days 02.09-30.09.2010 A 10000 250000 333333 B 150000 222222 555555 C 666666 444444 1222222 Sincerely, Milena

Problem when using calculated column as column group in a view

Hi,   I have a list with a calculated column named 'FullName', the formula is: "FirstName & " " &  LastName". I created a view group by 'FullName' column. The follow error appeared on the column:   <!-- #RENDER FAILED -->   Please help

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?

First time facing this problem in aspx page how to solve this..go to now

Hi . We have developed an asp.net application in Visual Studio 2008 with built in crystal report version =10.5.3700.0 , which is working fine in our local server. Our hosting server has crystal report file version =, Which is running on windows server 2008 (x64) with IIS 7. On load my crystal report aspx page am getting the following error. Error in File UNKNOWN.RPT: The request could not be submitted for background processing. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.  Exception Details: System.Runtime.InteropServices.COMException: Error in File UNKNOWN.RPT: The request could not be submitted for background processing. Source Error:  Line 32:         string user = ConfigurationManager.AppSettings["Userid"]; Line 33: Line 34:         crReportDocument.Load(Server.MapPath("CrystalReport.rpt")); Line 35: Line 36:         crConnectionInfo.ServerName = Server1; Source File: d:\inetpub\vhosts\xxxx.com\httpdocs\DT\Default.aspx.cs    Line: 34  Stack Trace:  [COMException (0x800002ad): Error in F

Calculated measure between two dates

Following fact table - Amount - StartDate - EndDate - SomeOtherDate   If the user selects a date in the time dimension I want to build the sum of Amount where the selected date is between StartDate and EndDate. Like this   SELECT SUM(Amount) WHERE <date> between StartDate and EndDate   Is it possible to create a calculated measure like this.   Thanks.

Date Time interval Problem

Hi I have a problem in which i have to check the time interval to the another which is already exist in Database table.For Exp.10:00 am  To 11:00 am i add an Event now if user insert the again this time interval  it's throw the error message.if user want's to edit this event For exp.10:15 am to 11:00 amhow do this.Thanks

Which version of Excel to install for a calculated measure?

I have a 64bit server running SSAS 2010 and I keep getting an ExcelMDX error when processing my cube. We do use an Excel function called FINV() in a calculated measure. I installed Excel 2007 on the SSAS 2010 server but this did not help (I also tried Excel 2010). I can start Excel on the server so I know it is installed. Does it matter whether I install Excel 32bit or 64bit on the server? How do I verify that Excel is installed correctly for SSAS 2010? Errors and Warnings from Response  MdxScript(Sales and Returns) (94, 8) The '[Excel].[FINV]' function does not exist.Kerry

How to measure WCF start up time ? ( WCF service .NET 3.5 hosted in IIS6 under win 2003 )

Hi ,   I have relative big and complex WCF (.net 3.5)service hosted in IIS 6 .  When IIS worker goes down , it takes a lot of time for service to wake up.  My question is how can I measure what exactly takes time ? Or its just service compilation time and nothing could be done here ? If I know what takes time I will try to optimize it .    Thanks. 
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