All about VBA and PALO

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

  • Maybe the problem is not only due to the TRUE/FALSE parameter:
    when using PALO.SETDATA, I found that even if variables passed are of the right type, the formula does not work unless I convert them again inside the formula, so, e.g.:

    dValue = Palo.SetData(sValue, 0, sParametr(1), sParametr(2), sParametr(3), sParametr(4), sParametr(5), sParametr(6), sParametr(7), sParametr(8), sParametr(9), sParametr(10), sParametr(11), sParametr(12), sParametr(13), sParametr(14), sParametr(15), sParametr(16), sParametr(17), sParametr(18), iDimensionCount - 2)

    becomes

    dValue = Palo.SetData(cstr(sValue), 0, cstr(sParametr(1)), cstr(sParametr(2))..., cint(iDimensionCount) - 2)

    I don't understand why it is necessary, but it solved similar problems that I was having with palo.formulas in vba. It's worth to try,

    hope this helps,

    regards,
    RQ

    The post was edited 3 times, last by realquo ().

  • Source Code

    1. dValue = Palo.SetData(CStr(sValue), 0, CStr(sParametr(1)), CStr(sParametr(2)), CStr(sParametr(3)), CStr(sParametr(4)), CStr(sParametr(5)), CStr(sParametr(6)), CStr(sParametr(7)), CStr(sParametr(8)), CStr(sParametr(9)), CStr(sParametr(10)), CStr(sParametr(11)), CStr(sParametr(12)), CStr(sParametr(13)), CStr(sParametr(14)), CStr(sParametr(15)), CStr(sParametr(16)), CStr(sParametr(17)), CStr(sParametr(18)), CInt(iDimensionCount) - 2)


    No results :(
    dValue is string, is it right?
  • It's a mysticism

    Source Code

    1. dValue = CDbl(Palo.SetData(CDbl(sValue), 0, CStr(sParametr(1)), CStr(sParametr(2)), CStr(sParametr(3)), CStr(sParametr(4)), CStr(sParametr(5)), CStr(sParametr(6)), CStr(sParametr(7)), CStr(sParametr(8)), CStr(sParametr(9)), CStr(sParametr(10)), CStr(sParametr(11)), CStr(sParametr(12)), CStr(sParametr(13)), CStr(sParametr(14)), CStr(sParametr(15)), CStr(sParametr(16)), CStr(sParametr(17)), CStr(sParametr(18)), CInt(iDimensionCount) - 2))


    CDbl(sValue) is Double but dValue is string %) How can that be?
  • It's quite the same of having to put Cstr(sString) when sString is already a string... Actually don't know, but I suppose the point is somewhere around here, try isolating the variable type problem by putting them directly into the formula.
    If you still need help, the best way is uploading your (sampled) db and your excel workbook with vba code, I'll give a look (and sure others will, too),
    regards,
    RQ
  • Thank you, Realquo

    but I have some problems, i can't open your uploaded file :(( i've given a task our IT-specialists, but it take great time (most likely there are no service packs). I'm not a programmer, so please can you explain how to crate a class in the VBA. I suppose cPalo is a txt-file in the first message of this topic?
  • Thanks all!!!
    Thank you, Realquo. I'm very grateful for your assistance and advice.

    I've read some manuals about classes in VBA. It works perfectly.

    I have some idea about improving functionality. It will be very convenient to use Excel's formulas with PALO.DATA together. It is necessary to calculate a simple function and write it in the database quite often. For example, we need to remove VAT from a value, so usually we use something like "=15.78/1.18". So it will be very good to realize macros that calculate a formula and write it in the DB.
  • Nice to know that it was useful :)
    About using macros to write formulas into Palo db, it's possibile for sure; anyway if you know in advance that the same calculation has to be performed each time, you could also use a cube rule that will calculate the net value "on the fly". In order to do this, you will need an additional measure, let's say "Net Value" and put a rule on your cube like (pseudo syntax):
    [Net Value] = [Gross Value]/1.18

    So you will be able to retain the original value in the "Gross Value" (assuming that you wrote the value into it) measure, and to read the net value using the new measure. Pls note that no value will be actually written in the "Net value" measure, since it's a calculation performed at run time.

    If you intend to seriously use Palo, I recommend you to buy a copy of the manual from Jedox.com, it's worth its 30€,

    have a nice day!
    RQ
  • internal loop data from cube via VBA

    Hi,
    because there's no easy function for the internal loop. This is my procedure "Step by Step":
    1) Add library "Jedox.Palo.XlAddin.tlb" into your VBA project (VBA Editor/Tools/Preference)
    2) Use function from Jedox_Palo_XlAddin/IPaloEngineCom/DataExportFromCubeEx2
    example:
    Public XlAddin As Jedox_Palo_XlAddin.IPaloEngineCom
    Public Function DataLoopFromCube
    Set XlAddin = New Jedox_Palo_XlAddin.ComInterface
    ...
    Declare variable for function DataExportFromCubeEx2
    ...
    DataLoopFromCube = XlAddin.DataExportFromCubeEx2(sServer, sCube, aDimMbr, dCompareValue1, eCompareOp1, eBoolOp, dCompareValue2, eCompareOp2, bIngoreEmptyCells, bBaseElementsOnly, sOutputFile, sValueSeparator, Append, bUseRules, True)
    this function creates csv file into your HDD with data
    3) Read data from file into VBA area

    Enjoy :)

    The post was edited 2 times, last by vesi ().

  • Hi,

    Vesi, thanks for the VBA tip for an internal loop, very useful.

    I am trying to re-write some of my MIS Alea/Infor PM transfer vba routines to work with Palo.

    In Alea you can use a "*" to tell the export routine that you want to use all elements in that dimension, is there an equivalent in Palo, I tried "(All Elements)" but this didnt work.

    This is the code to fill the ElementsArray, I have tested that works. I figured out that you have to use variant arrays rather than string arrays, and have experimented with different ways to embed arrays of multiple elements, within the ElementsArray.

    ******************************
    sServer = "LOCALHOST/demo"
    sCube = "Sales"

    'Range("rngProds") is a named range which contains 6 products
    Dim asCol1() As Variant
    ReDim asCol1(Range("rngprods").Cells.Count - 1)
    lCount = 0
    For Each Cell In Range("rngprods").Cells
    asCol1(lCount) = Cell.Value
    lCount = lCount + 1
    Next


    elementsArray = Array(asCol1, "United Kingdom", _
    "Jan", "2006", "Actual", Array("Units", "Turnover", "Cost of Sales"))
    ******************************

    Are the any other methods to fill this variable?

    Is there a way to embed a Subset into the ElementArray without having to loop through it and fill an array manually.

    Thanks all!!
    Simon
  • Hi Simon,
    1) Unfortunately Palo not accept a cumulated element in definition of array. You must define all basic elements below selected element.

    F.e.
    ReDim aDimMbr(1 To iDimensionCount) As Variant
    For iDimension = 1 To iDimensionCount
    sDimensionName = sDimensionsName(iDimension, 1)
    If (aElements(iDimension)) = "*" Then
    aDimMbr(iDimension) = Palo.eDimensionListElements(sServer, sDimensionName)
    Else
    'recursive function for add basic elements below selected element to array
    End If
    Next iDimension

    2) function DataExportFromCubeEx2 accept only array format ()()

    F.e.
    Dim aDimMbr(1 To 6) As Variant
    Dim aDimFrom1(1 To 1) As Variant
    Dim aDimFrom2(1 To 1) As Variant
    Dim aDimFrom3(1 To 1) As Variant
    Dim aDimFrom4(1 To 1) As Variant
    Dim aDimFrom5(1 To 1) As Variant
    Dim aDimFrom6(1 To 3) As Variant
    aDimFrom1(1) = "United Kingdom"
    aDimFrom2(1) = "Jan"
    ...
    aDimFrom6(1) = "Units"
    aDimFrom6(2) = "Turnover
    aDimFrom6(3) = "Cost of Sales"
    aDimMbr(1) = aDimFrom1
    aDimMbr(2) = aDimFrom2
    aDimMbr(3) = aDimFrom3
    aDimMbr(4) = aDimFrom4
    aDimMbr(5) = aDimFrom5
    aDimMbr(6) = aDimFrom6

    The post was edited 4 times, last by vesi ().