Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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!
    Regards,
    Rudi

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
    Regards,
    Rudi

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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>
    Regards,
    Rudi

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculations in OLAP Cubes! (Excel XP)

    That was a cunningly hidden typo indeed.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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!!)
    Regards,
    Rudi

Posting Permissions

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