# Thread: Calculations in OLAP Cubes! (Excel XP)

1. ## Calculations in OLAP Cubes! (Excel XP)

Please move this to another board if it is out of context here!

From time to time I will connect to an OLAP Cube via Office Web Componenets (OWC) in Internet Explorer. The cube provides me with all the necessary info from its source. I can move/add/remove/filter....the data in the cube view just like Pivot Tables in Excel.
The view also allows the ability to generate new calculated fields for analysis. Using the OWC toolbar buttons, i can activate a wizard like feature that will help me create simple calculations like =[Source]![QTY]*[Source]![Price].

My Question!
I have attempted to use IF functions to set conditions on how to calculate certain fields, but I keep getting an error saying that the syntax is wrong!!! I tried, IF, and IIF, and rechecked the syntax based on Excel and Access, but still the error prevails.
I cannot find any further help from the OLAP help files or OWC. Can anyone assist me with an example of how to set up a IF function in an OLAP Cube.

Tx a stack!

2. ## Re: Calculations in OLAP Cubes! (Excel XP)

Hi Rudi,
I believe that OLAP cubes use MDX (multi-dimensional expression) queries, which use the IIF statement rather than plain IF. Can you give an example of the syntax that did not work?

3. ## Re: Calculations in OLAP Cubes! (Excel XP)

Hi Rory,

I will post that example to you tomorrow. I don't have access to the cube currently! Tx

4. ## Re: Calculations in OLAP Cubes! (Excel XP)

Hi Rory,

Here is the syntax I used, which threw out syntax errors!
=IFF([Measure].[Volumes]>=10000,[Measure].[Volumes]/2,[Measure].[Volumes])

Can you see the wrong thing I did?

As I was typing into the post, I suddenly saw my error!!!!!! <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22>
=IIF([Measure].[Volumes]>=10000,[Measure].[Volumes]/2,[Measure].[Volumes])

When i made the change, it worked!!! <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

Tx for your interest in my problem! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

5. ## Re: Calculations in OLAP Cubes! (Excel XP)

That was a cunningly hidden typo indeed.

6. ## Re: Calculations in OLAP Cubes! (Excel XP)

Ha ha....
Compiler errors, Syntax errors, you can give me anyday....but these Logical errors are a nightmare!!! (I often call them Dumbo user errors!!)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•