Calculations in Mdx Query

How to show the vlaues of increase in salary for few departments along with other departments.

Ex

we have A,B,C,D departments having total salary of 1000 each

A 1000

B 1000

c 1000

d 1000

Total 4000

If there was an increment of 2% for A and C deptment

Then I want to see the results as

Old_sal NewSal

A 1000 1020

B 1000 1000

C 1000 1020

D 1000 1000

Total 4000 4040

Please help to write the mdx query to populate the above results.

I am using Pivot table as client

Thanks In advance

The simplest technique is using a calculated member which detects the current department member and increases the Old_Sal by a percentage to give the NewSal value. The drawback to this technique is "hardcoding" the detection of the department in the calculated member making the calculation somewhat inflexible. However, without knowing a lot more detail about your model this is the best suggestion I have. Here's the MDX for the calculated member [NewSal]

WITH MEMBER Measures.[NewSal] AS
'
IIF([Department].[Department].CurrentMember.Name = "A" OR [Department].[Department].CurrentMember.Name = "C",
Measures.[Old_Sal] * 1.2,
Measures.[Old_Sal])
'

SELECT {Measures.[Old_Sal], Measures.[NewSal]} ON COLUMNS
,{[Department].[Department].[<your level>].Members} ON ROWS
FROM [your cube]

Hope this helps.

PGoldy

|||

Hello Paul. I have thought about the same problem. Will this work in a MDX-script?

Create Member CurrentCube.[Measures].[NewSal] AS "NA";
SCOPE ([Department].Members) =
( CASE
When [Department].CurrentMember IS [Department].[ DepA]
OR [Department].CurrentMember IS [Department].[ DepB]
THEN [Salary]*1.2
ELSE [Salary]
END);

This is with the assumption that you have correct attribute relations defined.

Regards

Thomas Ivarsson

|||

This should instead be formulated as:

Create Member CurrentCube.[Measures].[NewSal] AS [Measures].[Salary];
SCOPE ({[Department].[DepA],[Department].[DepB]}, [Measures].[NewSal]);
This = 1.2 * [Measures].[Salary];
END SCOPE;

|||

Thanks for the responses to every body.

Using these I can change the values but as I am using pivot table in the aspx page. The total are not calculated. They show the same old total.

How to get the new total also..

Thanks

|||

Hi Anil:

Using the CreateMember... as Michael, and Thomas suggested (Thanks Michael and Thomas) is a change to the cube definition which resides on the server. When the new calculated member resides on the server you can reference is from your pivot table and see [Measures].[NewSal] as an additional measure.

Hope this helps - PaulG

|||

Thank you Micheal, Thomas and paul for giving me valuble responses

I understood what you are saying.

I am still having problems with executing these.

I am new to analysis services and pivot table concepts.

I am using asp.net 2003 and sql server 2000

I wish to do the above logic at a click of a button.

Could you guys pleas help in to provide steps How to proceed for this scenario.

in the below code

cmd.execute is giving error saying syntax error.

Please Help!!!

Dim m_XML As String

Dim strOLAPConn As String = ConfigurationSettings.AppSettings("OLAPConnectionString")

Dim objPT As PivotTableClass = New PivotTableClass

Dim objPTView As PivotView

Dim fldFCElement, fldCenter, fldProcess, fldHierarchy As PivotField

Dim fSetFCElement, fsetOrgTitle, fSetProcess, fsetOrgAttr, fsetCenter, fsetHierarchy As PivotFieldSet

Try

Dim cnn As New ADODB.Connection

Dim cmd As New ADODB.Command

Dim rs As New ADOMD.Cellset

cnn.ConnectionString = strOLAPConn

cnn.Open()

cmd.ActiveConnection = cnn

Cmd.CommandText = "Create Member Demo.[Measures].[NewSal] AS '[Measures].[BY];SCOPE ({[Center].[NASA Headquarters]}, [Measures].[NewSal]);This = 1.2 * [Measures].[BY];END SCOPE;'"

cmd.Execute()

objPT.ConnectionString = cnn.ConnectionString

objPT.Toolbar.Enabled = True

objPT.DataMember = "Demo"

objPT.AutoFit = True

objPTView = objPT.ActiveView

fSetProcess = objPTView.FieldSets("Process")

objPT.ActiveView.ColumnAxis.InsertFieldSet(fSetProcess)

...

...

.

|||

Ahhh... Yes. The command you are trying to send will not work. First of all, what I suggested was syntax for Analysis Services 2005. Apparently, you are connecting to an Analysis Services 2000 instance, so that is one reason why it does not work... Even if you were connecting to AS2005, it still would not work, since SCOPE statements need to be defined on the server.

Now, if what you need to do is create the calculated member with DDL, your CommandText should be something like:

CREATE MEMBER Demo.[Measures].[NewSal] AS 'IIF([Department].[Department].CurrentMember IS [Department].[Department]. OR [Department].[Department].CurrentMember IS [Department].[Department]., Measures.[Old_Sal] * 1.2, Measures.[Old_Sal])'

This will, of course, not return any data, why you could use the ExecuteNonQuery() method on your Command object for clarity - at least you can do this with ADOMD.NET, not quite sure about ADOMD.