You are not logged in.

Mixing basic features with VBA

Dear visitor, welcome to Palo Community Forum. If this is your first visit here, please read the Help. It explains in detail how this page works. To use all features of this page, you should consider registering. Please use the registration form, to register here or read more information about the registration process. If you are already registered, please login here.

adx.jerome

Trainee

  • "adx.jerome" started this thread

Posts: 3

Date of registration: Nov 9th 2010

  • Send private message

1

Tuesday, November 9th 2010, 10:16am

Mixing basic features with VBA

Hi everybody,

I'm new at Palo and I'm trying to mix some basic features with some VBA in an Excel worksheet.

Let's say I have a cube with a "client" dimension, which contains a lot of information.

How could I, for instance:

- Populate a dropdown list with all the "client" elements?
- OnChange of that dropdown list, update my worksheet?

If we take the localhost/Demo database which comes as an example with the Palo package, the idea would be to have a dropdown list with all the Products, and have a determined overview of that products' profitability ("2009", "actuals", ...).

How would you do that?

Thanks in advance for your time.

J.

axi

Sage

Posts: 661

Date of registration: Mar 5th 2009

Location: Germany / Bargteheide

Occupation: BI

  • Send private message

2

Tuesday, November 9th 2010, 3:51pm

I would not use VBA for this (search for user name vesi if you want to go this way - he posted a VBA module to access most Palo functions).

Just create a new view with Palo functions (Addin menu) and then copy the selector formulae where I want them. Throw in some hardcoded references into the formula if neccessary.

Source code

 
1

=PALO.ENAME($A$1;"Years";"All Years";1;"")


I always color these Excel cells blue so the user should guess that on double clicking on them a popup shows.

adx.jerome

Trainee

  • "adx.jerome" started this thread

Posts: 3

Date of registration: Nov 9th 2010

  • Send private message

3

Wednesday, November 10th 2010, 1:37pm

Hi!

Thanks for your quick answer.
After some hours of manipulating Excel/Palo, I managed to achieve what I wanted.

However, I still have one problem. Do you know how I can retrieve an element's specific attribute through a macro? I seem to have something wrong, but cannot figure out what :-)

Source code

 
1

=PALO.DATAC("localhost/db";"#_My Attribute Cube";"Attribute I'd like to retrieve";"element")


The above formula works fine. However, when trying to translate that into a macro, for example through

Source code

 
1

ActiveCell.Value = Application.Run("PALO.DATAC", "localhost/db", "#_My Attribute Cube", "Attribute I'd like to retrieve", "element")


I get a !#NUM error in Excel when the macro is launched.

What's wrong with this?

Thanks again!

J

axi

Sage

Posts: 661

Date of registration: Mar 5th 2009

Location: Germany / Bargteheide

Occupation: BI

  • Send private message

4

Wednesday, November 10th 2010, 3:13pm

the quoting within VBA is tricky

sometimes you have to triple quote ...

more elegant and less error prone is putting it together via string concatenation:

just to show the principle - won't work:
formula = "PALO.whatever(" & servername & elementname ... & ")"


I try to avoid it like the plague ;-)

adx.jerome

Trainee

  • "adx.jerome" started this thread

Posts: 3

Date of registration: Nov 9th 2010

  • Send private message

5

Wednesday, November 10th 2010, 4:57pm

Thanks!!
Rate this thread