7 Replies Latest reply on Mar 25, 2011 1:14 AM by TonyScalese

    FDM Report Script Syntax

    839966
      Hi,

      I'm trying to create a similar report script withing FDM but the report I need to create has to have a particular format. I created the following script but no data gets copied into the file. Does anyone have a syntax reference for this kind of script?

      This is what i have in Essbase which works:

      //ESS_LOCALE English_UnitedStates.Latin1@Binary
      //FORMAT
      { DECIMAL 5 }
      { NOINDENTGEN }
      { ROWREPEAT }
      { SUPPAGEHEADING }
      { SUPBRACKETS }
      { SUPCOMMAS }
      { SUPEMPTYROWS }
      { SUPFEED }
      { TABDELIMIT }
      { MISSINGTEXT "#Mi" }
      <SUPSHARE

      // REPORT LAYOUT
      <ROW ("Scenario","Version","Entity","ICP","Department","Region","Reporting", "Account")

      // PAGE HEADERS
      // ROW
      &CurrScenario
      &CurrVersion
      <LINK (<IDESCENDANTS("062") AND <LEV("Entity",0))
      <LINK (<LEV("ICP",0))
      "No Department"
      <LINK (<LEV("Region",0))
      "Input to Local"
      <LINK (<LEV("Account",0))
      //COLUMN
      &Yr1 &Yr2 &Yr3 &Yr4 &Yr5
      <COLUMN ("Period")
      <COLUMN ("Year")
      <LINK (<DESCENDANTS("YearTotal") AND <LEV("Period",0))
      !

      And this is what I've tried to create in FDM:

      'Declare local variables
      Dim objHW
      Dim strQuery
      Dim strFilePath
      Dim intMethod
      Dim strError
      Dim lngErrorNo

      '** Create the Essbase HypwWindow Object **
      Set objHW = CreateObject("upsES9XG4A.clsHypeWindowEB")

      'Initialize the object
      'Set the logging properties
      objHW.Connection.PstrLogUser = API.DataWindow.Connection.PstrUserID
      objHW.Connection.PstrOutboxPath = API.DataWindow.Connection.PstrDirOutbox
      'Set the Essbase Server properties
      objHW.Connection.PstrAppServer = "xxxx"
      'Application information
      objHW.Connection.PstrAppName = "xxx"
      objHW.Connection.PstrDatabase = "xxx"
      'User Account Information
      objHW.Connection.PstrUserName = "xxxx"
      objHW.Connection.PstrPassword = "xxxx"

      'Open the Connection
      If objHW.Connection.fConnect() Then
      'Set the file name
      strFilePath = "C:\EssReportScript.txt"
      intMethod = 2 '1=Report Script, 2=Report Script String

      Select Case intMethod
      Case 1
      'Set the report script file name
      '(Must Exist For this DB On Essbase Server)
      strQuery = "exp"

      Case 2
      'Sample Report Scripts (Based On Essbase BASIC Demo Application)
      strQuery = strQuery & "<COLUMN(Period,Year)" & vbCrLf
      strQuery = strQuery & "<LINK <DESC YearTotal and (<LEV (Period,0))" & vbCrLf
      strQuery = strQuery & "&Yr1 &Yr2 &Yr3 &Yr4 &Yr5" & vbCrLf
      strQuery = strQuery & "<ROW(Scenario,Version,Entity,ICP,Department,Region,Reporting,Account)" & vbCrLf
      strQuery = strQuery & "&CurrScenario" & vbCrLf
      strQuery = strQuery & "&CurrVersion" & vbCrLf
      strQuery = strQuery & "<LINK <DESC MAGUJ And (<LEV (Entity,0))" & vbCrLf
      strQuery = strQuery & "<LINK (<LEV (ICP,0))" & vbCrLf
      strQuery = strQuery & "<No Department" & vbCrLf
      strQuery = strQuery & "<LINK (<LEV (Region,0))" & vbCrLf
      strQuery = strQuery & "<Input To local"& vbCrLf
      strQuery = strQuery & "<LINK (<LEV(Account,0)" & vbCrLf
      strQuery = strQuery & "{" & vbCrLf
      strQuery = strQuery & "Decimal 5" & vbCrLf
      strQuery = strQuery & "Width 15" & vbCrLf
      strQuery = strQuery & "SUPBRACKET" & vbCrLf
      strQuery = strQuery & "SUPCOMMA" & vbCrLf
      strQuery = strQuery & "MISSINGTEXT "" """ & vbCrLf
      strQuery = strQuery & "UNDERSCORECHAR "" """ & vbCrLf
      strQuery = strQuery & "SUPPAGEHEADING" & vbCrLf
      strQuery = strQuery & "NOINDENTGEN" & vbCrLf
      strQuery = strQuery & "SUPFEED" & vbCrLf
      strQuery = strQuery & "ROWREPEAT" & vbCrLf
      strQuery = strQuery & "SUPEMPTYROWS" & vbCrLf
      strQuery = strQuery & "TABDELIMIT" & vbCrLf
      strQuery = strQuery & "}" & vbCrLf
      strQuery = strQuery & "!" & vbCrLf
      End Select

      'Execute the query string
      objHW.DataAccess.mListDataQueryFile CInt(intMethod), CStr(strQuery), CStr(strFilePath)
      Else
      'Connection failed return nothing
      strError = "HypeWindow function [fConnect] failed, make sure Essbase OLAP Service is running!"
      lngErrorNo = 2577
      'Log Action in transaction log
      API.DataWindow.DBTools.mLog 5000, API.DataWindow.Connection.PstrUserID, CDate(TStart), Now(), objHW.Connection.PstrUserName, "Err", "Connection Info: App=" & CStr(objHW.Connection.PstrAppName) & " App Server=" & CStr(objHW.Connection.PstrAppServer) & " Domain=" & CStr(objHW.Connection.PstrDomain) & " Auth=" & CStr(API.DataWindow.Connection.PlngAuthenticationType), CStr(strError)
      End If
        • 1. Re: FDM Report Script Syntax
          TonyScalese
          To my knowledge, report script execution by the FDM api is not supported. Are you trying to extract data for a data transfer process or just create a report?

          If it is just extract, have you considered a dataexport calc script? If it is jsut a report and you have to use a report script, you are going to have to shell out and run it via a MAXL command.
          • 2. Re: FDM Report Script Syntax
            839966
            I did try to use a dataexport script but since I am exporting all periods for 5 years dataexport does not give me the required format for an FDM multiload. Users are going to be loading this file therefore we want the transformations to be automated. Are you suggesting invoking a MAXL shell from within a FDM script? If so, how would I go about it?
            • 3. Re: FDM Report Script Syntax
              TonyScalese
              Use the multiloadaction event script to transform the file generated by the DATEXPORT calc to the required format.

              As you continue to use the forums, please consider taking the time to flag responses you find useful or correct.
              1 person found this helpful
              • 4. Re: FDM Report Script Syntax
                839966
                My problem with DataExport is that I only get months in the columns but I need to create an extract that has 5 years of data continously so I can load it with FDM. Is there a way to transpose the Year dimension in the DATAEXPORT or FDM?
                • 5. Re: FDM Report Script Syntax
                  TonyScalese
                  Dataexport will put the dense dimension with the least number of members in the columns. So in your app, that appears to be the time dimension. You could easily write a VBScript that will transpose the data into rows but take a closer look at the admin guide. FDM txt multiload files do not support more than 12 months of data in a single file.
                  1 person found this helpful
                  • 6. Re: FDM Report Script Syntax
                    839966
                    Thanks for the answer Tony. So if I understand well I have to create a connection to Essbase in order to run the calc script which will generate my file. Then I do my transformations and I should be good to load this file into HFM?

                    Is there somehwere I can get guidelines to setup this connection to Essbase?
                    • 7. Re: FDM Report Script Syntax
                      TonyScalese
                      There was a recent thread on creating a connection. Take a look through the forum and you'll get some ideas.