.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

"WHERE" in Cube calculation

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

Hi Everyone, I am trying to put a where clause in the Cube calculation but not getting it to work.

Here is the actual calculation which works:


([DimTime].[TimeHierarchy].CurrentMember .Lag (1),[Measures].[Actual Hour])* 1.25

Now, I want to add a where clause to this calculation which looks something like this:

WHERE ([Company].[Department].CHILDREN - [Company].[Department] .&[Research)


Can anybody please advise? Thanks a lot in advance for your help guys.

View Complete Post

More Related Resource Links

how to perform calculation and KPI in cube?


Hi ,

I have a sales cube in that i want to perform some calculation on sales column. 

How to do that?

how  to perform calculation and KPI in cube?



create calculation sas cube


how do write calcuation in ssas for caculated columns, here's syntax i have sql..

select (BIRTH_DT from dbo.HM_MEMBERSHIP

where (datediff(year,BIRTH_DT,GETDATE())) between  30  and 39

SSAS 2008 - Occupancy Calculation Cube?


Hi there,

Previously I posted my probelm into T-SQL forum section. Here is link http://social.msdn.microsoft.com/Forums/en/transactsql/thread/b02d37b4-d36e-42cc-b1f4-3c00abadfdd4

I have SQL 2005 table like

Room      AdmissionDate    DischargeDate
R1             2010-01-25           2010-01-28
R1             2010-01-28           2010-01-30
R1             2010-02-15           2010-02-17
R1             2001-02-20           NULL         
R2             2010-01-31           2010-01-31
R2             2010-02-10 

Cube Calculation get Members of second last Level of a parent-child hierarchie


Hi everyone,

I have a parent-child-hierarchie, where the different branches has different lengths; for example the fist one is:

Level1 - aLevel2 -aLevel3 -atotal

and the second:

Level1 - bLevel2-bLevel3 - bLevel4 - bLevel5 - btotal

Now I need the memebers of the second last Level, so in the examle: aLevel3 and bLevel5

How can I get them?

Thanks in advance for your help!


Is cube limit the number of calculation member?



I develop new application and user can create new calculation member. So some one can tell me "how many Calculation member a cube can contain?"


Much thanks.


How to calculate childs age,but when i enter childs age as '03/27/2007' it returns 3 years where as actual age is 2 yr 11 months

calculation, field and map traverse adjustment, and coordinate transformation

Free Pocket PC land surveying software -- COGO calculation, field and map traverse adjustment, and coordinate transformation -- for students and professionals.

Excel Services: Develop A Calculation Engine For Your Apps


The Excel Services architecture lets users design their own algorithms and share workbooks on a server.

Vishwas Lele and Pyush Kumar

MSDN Magazine August 2007

Absence and vacation days webparts calculation



does any1 know if there is a webpart that can calculate an employee's vacation days. and when the employee request some vacation days it show the days that are left.

or is there a tutorial to create such a webpart?


Getting Below error while accessing SQL server cube

Getting Below error while accessing SQL server cube   Error Generating Drop Down Values   Odbc driver returned an error (SQLExecDirectW).   Error Details Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. URL: http://SIMAPPWQS99/olap/msmdpump.dll. The http return status is 503. The service is unavailable. (HY000) SQL Issued: SELECT "- AppReceivedTimeYQMDD"."Year" FROM "Medicare E2E Tracking" ORDER BY 1

Clustering on cube slices

Hi, I would like to use the clustering algorithm "seperately" on each cube slice.  The problem description: Having many employes and their arrival-to-work time I created a cube with a fact table and 3 dimensions: Employee, Time, Office and I would like to run the clustering algorithm seperately for each employee to receive his arrival-to-work clusters and to find his exceptions e.g., for the employee John who has the following arrival time records: 08:05, 07:55, 8:10, 8:07, 10:30, 10:31, 10:28, 15:00 I want to receive two clusters, the first one will include  08:05, 07:55, 8:10, 8:07 The second one will include 10:30, 10:31, 10:28 (and the exception will be 15:00) Having many employees I would like to create one data mining model and to use the cube slice and dice options    Searching the web and the MSDN I didn't find whether and how it can be done - I will very appreciate your help   Thanks in advance, Lora

Restore - Cube Analysis Services 2005

Hi, I'm from Brazil. Sorry for my English. I made a backup for my cube that has more than 2 GB, but de backup is created with only 50 mb. The backup don't showed erro message. I restored this backup without erro, but when I tryed query the cube, i receive this message: The query could not be processed: o File system error: The following file is corrupted: Physical file: \\?\C:\SQL Server\MSSQL.2\OLAP\Data\teste.59.db\"Nome_Servidor" - "Nome_Database".190.cub\Fato Inadimplencia Safra Contrato.190.det\Fato Inadimplencia Safra Contrato.167.prt\172.fact.data. Logical file . Someone can help? I realy need make this backup.Fabrício França Lima | MCP, MCTS, MCITP | Visite meu site: http://fabriciodba.spaces.live.com/ | Dicas de artigos SQL: Siga-me no twitter @fabriciodba.

Cube Processing Error

Hi All when i process the cube some times i get the error  "OLE DB or ODBC  Error: Operation Cancelled; HY008. Internal Error: The Operation Terminated unsuccessfully  " https://connect.microsoft.com/SQLServer/feedback/details/286505/error-in-processing-cube?wa=wsignin1.0 In above link Microsoft they said to increase Query time out, i made it 0 even it repros I am using SQL Server 2008.   Thanks

Problem with calculation in combination with crossjoin

Hi fellows   I dont know how to rewrite my MDX in order to get the correct result.    The following MDX statement is reduced to a minimum in order to explain my problem: WITH    MEMBER [Article].[Article].[Article_CE]    AS AGGREGATE ([Article].[Article].[Article_1], [Customer].[Customer].[Unknown], [Location].[Location].[USA])              SELECT       {[Measures].[SalesNet]       ,[Measures].[Budget] } ON COLUMNS ,       NON EMPTY         [Customer].[Customer].[Customer].members         * {[Article].[Article].[Article].members          ,[Article].[Article].[Article_CE]} ON ROWS FROM SALES   If i dont display the customer hierarchie " [Customer].[Customer].[Customer].members " on rows, then every thing works fine! As Soon as I add the above customer hierarchie to my mdx statment, then every thin works fine except for my member " [Article].[Article].[Article_CE]". Instead of showing only one row for customer "Unknown" (see Member definition), a row appears for each customer and with the same SalesNet and Budget.   Any Idea how to rewrite my mdx ?   best regards Pellito

Instant Refreshment of Cube

HI All, Is that possible cube get refreshed as soon as data updated into the relation database?   Kindly Suggest Regards Amit

How to determine when the cube refresh has completed?

I'm trying run code after a cube refresh is completed, but I can't seem to figure out how to write code to determine when the cube refresh has been completed. If it were a simple calculation, I could use the code below to determine the "calculation state", but there doesn't seem to be "refresh state" that I'm aware of. Any help will be GREATLY appreciated!! Application.CalculationState = xlDone

OLE DB Error: Network-Related or Instance-Specific while Deploying SSAS 2008 Cube

Hi, I encountered the below listed error while attempting to deploy SSAS 2008 DB: Error 1 OLE DB error: OLE DB or ODBC error: Login timeout expired; HYT00; A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Named Pipes Provider: Could not open a connection to SQL Server [5]. ; 08001.  0 0  I am running a standalone MSSQL 2008 named instance on Windows Server 2008 R2. Here are the steps I have taken but yet the issue is not resolve: 1) Enable Remote connections on the server 2) Enable TCP\IP 3) SQL Browser is Running 4) Set Firewall Inbound Exceptions for SQL Browser, Default Port 1433, UDP Port 1434 5) Using Management Studio I can successfully connect to the MSSQL named instance from the app server 6) I can ping I MSSQL box from the app server 7) Read and implemented all steps in the MSDN post below: http://blogs.msdn.com/b/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx Thanks for your help.
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