Welcome :Guest
Congratulations!!!

Top 5 Contributors of the Month
hdjk11
Shivakumaran
dfefde5
hoangnganvy1993

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

### How to create a calculation in a list - Calculate the number of days between two dates

I have a calculated column in a list called Days Open. This column contains the number of days between today (Sysdate) and another column called the Effective Date. Therefore, if Sysdate is 11/16/2010 and the Effective Date is 10/14/2010, how do I calculate the number of days between the two dates?

I tried using this formula: =TEXT(Sysdate-[Effective Date],"h") and then I tried =INT(Sysdate-Effective Date) & TEXT(Sysdate-Effective Date, ":hh:mm")

Neither formula worked. I hope it is something real simple that I am missing here. Any help you can provide will be highly appreciated!

### Aggregation one level higher - MDX YTD calculation

Hello,

I have a MDX query like  as shown below.. ofcourse it has additional rows/columns ..i have simplified it here for example.

MEMBER [Measures].[CY_S] AS

Aggregate(YTD([Date].[Calendar Date].[Month Name].&[201009]),[Measures].[Sales])

MEMBER [Measures].[CY_B] AS

Aggregate(YTD([Date].[Calendar Date].[Month Name].&[201009]),[Measures].[Budget])

### CREATE CELL CALCULATION doubt

Hi experts,

I was writing the below MDX query

```WITH CELL Calculation [Final] FOR '([Measures].[Status Sum],[date].[Fiscal Year].allmembers,leaves([Customer]))'
AS Aggregate({NULL:[date].[Fiscal Year].currentmember},[Measures].[Status])
SELECT [Measures].[Status Sum] ON 0,
{[Date].[Fiscal Year].allmembers} ON 1

```

and it gives me the following result

```	Status Sum
All Periods   	89898
FY 2002	    4260
FY 2003	    10799
FY 2004	    87377
FY 2005	    89898
```

Now I want to recreate the same calculation in the cube using the CREATE CELL statement and this is what I came up with

```CREATE CELL Calculation CURRENTCUBE.[Status Sum] FOR '([Measures].[Status Sum],[date].[Fiscal Year].children,leaves([Customer]))'
AS Aggregate({NULL:[date].[Fiscal Year].currentmember},[Measures].[Status])
```

This gives me the correct result when I view the [Status Sum] measure against all the years. But when I look for a particular year like FY2005, the query scoped cell calculation (WITH cell calculation) gives me 89898,

### Rollup calculated measure at aggregate measure and only apply calculation at leaf

Hi

I have the following calculated measure: MeasureX = MeasureA * MeasureB.

I only want the calculation to apply at the leaf level, then the answer should rollup automatically to higher levels.

So instead of Sum(MeasureA) + sum(MeasureB). I need sum(MeasureX)

The solutions I've found for this issue, refer to the relational engine to solve the problem: etl/dsv/calc column.

But I need it to be solved in the cube.

Thanks,

Jason

MCITP BI Developer 2008 - MCTS SQL Server 2005

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

### AGE CALCULATION IN YEARS AND MONTHS

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

hi,

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?

thnx

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

### 'variable' is not declared. It may be inaccessible due to its protection level.

I have been trying to get a value from my sql database to post to the form I have.  I have thought i was pretty close but it keeps giving me the error "User_EmailTextBox is not declared.  It may be inaccessible due to its protection level".  I have been trying to get this to work for a week and I can't seem to figure it out.  Any help would be appreciated.  My code is below.web.config ---------------------------------------------------------------------------- <?xml version="1.0"?> <configuration> <connectionStrings> <add name="ECDRConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ECDR.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True" providerName="System.Data.SqlClient" /> </connectionStrings> <system.web> <compilation debug="false" targetFramework="4.0" /> </system.web> <!--Mail settings--> <system.net> <mailSettings> <smtp> <network host="YOUR HOST HERE"/> </smtp> </mailSettings> </system.net> <!--Mail settings--> </configuration> --------------

### Text Calculation - Removing all text after space

I have a list with workflows that was created for one of our clients that allows them to enter some information and the workflow takes care of sending emails to the necessary recipients as well as notifying the person the task is assigned to when it has reached a certain status or age. The "agent name" colum contains the agent's first and last name (ie John Smith).  I am wondering if there is a way that in the workflow, when an email is generated, if I am able to remove the last name using some sort of text calculation.  The reason I would like to try and do it this way if possible is that if I add the Columns Agent First Name and Agent Last Name, the current Agent Name colum that is being used to generate the agent's name in the email will now not be the correct column to use for this information and the 100s of entries already in the lists will not send out emails properly. Does anyone have a suggestion for this, or know some sort of text calculation formula that I can use to remove the last name from the lookup in the email?

### Percentile calculation performance

Hi, With the help of other members in this forum we created a solution to calculate percentile based on a dimension attribute (see Percentile Calculation ). However, performance is quite an issue - it takes about half a minute to calculate percentile on our cube (median calculation takes couple seconds). I do not think that our cube is an issue here because it also takes a while to compute it using Adwenture Works. How to improve the performance of query? Thanks

### Enhance SSAS so that a calculation returns multiple cells at once (Excel fill not a single cell but

I wonder if somebody can help me with the following question. I would like to create an enhancement to SSAS in a way so that one can pass in multiple values, run a calculation and in return retrieves not one but multiple cell results at the same time e.g. retSet = functionABC(inSET) where inSet is somthing like 5 4 3 6 7 3 4 4 AND retSet is 4 6 7 8 9 3 4 4 All my calculated members in MDX return a single value BUT I need to have a calculation that if called returns multiple results (a set) at once and in a way so that Excel will properly pick up on it and consequently fills multiple cells in the Pivot table. Is that possible at all and if so how? I would not mind to write a little CLR assembly to enhance the SSAS but don't know how to pass the results back. Any ideas are highly appreciated. Dirk

### Performancepoint Scorecard KPI Calculation problem

Hi, i want to calculate the profit in percent from a P/L statement Dimension. i use the following calculation: CREATE MEMBER CURRENTCUBE.[Measures].[CompanyEarnings] AS (     sum( setIncome, [Saldo] )+     sum( setCosts, [Saldo] )   ) / sum( setIncome, [Saldo] ) The Performance-Point Scorecard is Filtered by a Year-Month Hierarchy filter with the following Formular: ([Time].[Year - Month].[Month].&[2010-01-01T00:00:00]):<<UniqueName>> This Formular works fine for the Months, but the sum for the  Year is not calculating on only the selected months, its calculating on the hole year income and costs. If i use a filter on a set with the same months which i hoped was selected with the Scorecard-Filter, it worked fine. Here is the filter which worked, but is not dynamic: SET CURRENTCUBE.[setMonths] AS { [Time].[Month].&[2010-01-01T00:00:00], [Time].[Month].&[2010-02-01T00:00:00], [Time].[Month].&[2010-03-01T00:00:00], [Time].[Month].&[2010-04-01T00:00:00], [Time].[Month].&[2010-05-01T00:00:00], [Time].[Month].&[2010-06-01T00:00:00], [Time].[Month].&[2010-07-01T00:00:00] }; If i change the function that it use this filter all calculations seems to be fine: CREATE MEMBER CURRENTCUBE.[Measures].[CompanyEarnings] AS (     sum( setIncome, [Saldo] )+     sum( setCos

### getting minutes from calculation in gridview

Hi..I have to change the background colour of a calculated field (just the cell not the whole row) in my gridview called ElapsedTime, based on the number of minutes e.g > 30 mins background is red.I have already done something simliar for hours..I have succesfully written a sql statement that calculates elapsed timeCONVERT(Varchar, (DATEPART(dd, GETDATE() - DateAdded)-1) * 24 + DATEPART(hh,GETDATE() - DateAdded)) + 'h:' + CONVERT(Varchar, DATEPART(mi, GETDATE() - DateAdded)) + 'm' AS ElapsedTimeElapsed time looks like this e.g 11h:14mI then used this bit of code to get the hourProtected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs) Handles GridView1.RowDataBound        Dim et As String                ' For Each r As GridViewRow In GridView1.Rows        If e.Row.RowType = DataControlRowType.DataRow Then            et = e.Row.Cells(13).Text            et = et.Substring(0, et.IndexOf("h"))            Dim number As Integer = Int32.Parse(et)            If e.Ro
 Categories:
 ASP.Net Windows Application .NET Framework C# VB.Net ADO.Net Sql Server SharePoint Silverlight Others All