This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Aug 28, 2013 12:23 PM by dhiraj1981 RSS

export all maps (mappings for all dimensions in EXCEL)

Onono Newbie
Currently Being Moderated
Hi,

it shouldn't be an unknown issue - but unfortunately I can't find the way :-(
  • 1. Re: export all maps (mappings for all dimensions in EXCEL)
    SH Guru
    Currently Being Moderated
    There is no out of the box functionality that does this, it is something you would have to script yourself. However, if you look the Scripts\General folder there is a pre-built script there which exports the mappings for all dimensions for a given location in a delimited file format. You could then easily port those files into Excel.
  • 2. Re: export all maps (mappings for all dimensions in EXCEL)
    fiammdin Newbie
    Currently Being Moderated
    Hi,

    I have the same issue,
    I created the script for all needed locations, but the system is creating tro, tra and trn files.
    and they are stored in the Oubox export folder on the server.

    But how can i create a Excel file that looks like the File you can download from the mapping page wehen you click on Export to Excel, with the possibility to save it on the local machine and not only on the server?

    cheers

    **********************************************************************

    Sub ExportAllDimMapsForLocation()
    '------------------------------------------------------------------
    'UpStream WebLink DM Custom Script:
    '
    'Created By:      tshea
    'Date Created:      5/25/2004 22:10
    '
    'Purpose:               Export all dimension maps to delimited TextFiles
    '
    '------------------------------------------------------------------
              
    'Execute the export
    strFilePath = ""
    strFilePath = strFilePath & API.IntBlockMgr.InterfaceMgr.fExpTRx( API.POVMgr.PPOVLocation, "Account") & Chr(10) & Chr(13)
    strFilePath = strFilePath & API.IntBlockMgr.InterfaceMgr.fExpTRx( API.POVMgr.PPOVLocation, "Entity") & Chr(10) & Chr(13)
    strFilePath = strFilePath & API.IntBlockMgr.InterfaceMgr.fExpTRx( API.POVMgr.PPOVLocation, "ICP") & Chr(10) & Chr(13)
    strFilePath = strFilePath & API.IntBlockMgr.InterfaceMgr.fExpTRx( API.POVMgr.PPOVLocation, "UD1") & Chr(10) & Chr(13)
    strFilePath = strFilePath & API.IntBlockMgr.InterfaceMgr.fExpTRx( API.POVMgr.PPOVLocation, "UD2") & Chr(10) & Chr(13)
    strFilePath = strFilePath & API.IntBlockMgr.InterfaceMgr.fExpTRx( API.POVMgr.PPOVLocation, "UD3") & Chr(10) & Chr(13)
    strFilePath = strFilePath & API.IntBlockMgr.InterfaceMgr.fExpTRx( API.POVMgr.PPOVLocation, "UD4") & Chr(10) & Chr(13)
    strFilePath = strFilePath & API.IntBlockMgr.InterfaceMgr.fExpTRx( API.POVMgr.PPOVLocation, "UD5") & Chr(10) & Chr(13)
    strFilePath = strFilePath & API.IntBlockMgr.InterfaceMgr.fExpTRx( API.POVMgr.PPOVLocation, "UD6") & Chr(10) & Chr(13)
    strFilePath = strFilePath & API.IntBlockMgr.InterfaceMgr.fExpTRx( API.POVMgr.PPOVLocation, "UD7") & Chr(10) & Chr(13)
    strFilePath = strFilePath & API.IntBlockMgr.InterfaceMgr.fExpTRx( API.POVMgr.PPOVLocation, "UD8") & Chr(10) & Chr(13)

    'Display the file path
    If LCase(API.DataWindow.Connection.PstrClientType) = "workbench" Then
         MsgBox "Map Export files created: " & Chr(10) & Chr(13) & strFilePath
    Else
         'Let the user know we are done
         RES.PlngActionType = 2
         RES.PstrActionValue = "Map Export files created: " & Chr(10) & Chr(13) & strFilePath
    End If

    End Sub

    ************************************************************
  • 3. Re: export all maps (mappings for all dimensions in EXCEL)
    beyerch2 Expert
    Currently Being Moderated
    OK,

    I had some time to kill at work today and I wanted this functionality for myself so I went ahead and made it ........

    The first version I'm posting will create a CSV dump that will open into Excel (with items in separate columns) for me.

    In about another 30 minutes, I'll put another version of this code on here that will actually create an XLS file with separate worksheets for the dimensions. I made the CSV version first so that I could prove out all of the FDM specific stuff first ......

    This routine works two ways :

    #1 - For the current POV location, it will dump the map from tDataMap which should be the current map in the system.
    #2 - For the current POV location and POV Period, it will dump the map from vDataMap. This allows you to dump a map as it was during a prior period data load.

    Files will be dumped to your Outbox\ExcelFiles folder. The filename will depend on which option you have enabled for the subroutine, but it will include Location Name regardless.

    I have tested this in my 9.3.1 environment running SQL Server 2005. I make no guarantees, but it should work ........ If not, let me know what you run into and depending on my time I can either fix or advice you as to where to look......

    If the forum butchers the display format too much, give me an email address and I'll email you code. I will post this on my blog once I get that going at a later date, but still not ready for prime time.....

    Thanks

    Sub ExportAllCurrDimMapsForLocationtoCSV()
    '------------------------------------------------------------------
    'UpStream WebLink DM Custom Script: 
    '
    'Created By:         cbeyer
    'Date Created:       11-23-11
    '
    'Purpose:               Export all dimension maps to an Excel workbook       
    '               
    '------------------------------------------------------------------
    
    
    'Declare Constant
    'NOTE : This will control whether the function gets the current map in the system or whether it looks back for a specific Period
    '       FDM stores the Map for each period that was loaded... You may want to export a particular POV Period for audit purposes, etc.
    '       IF you enable this, be sure to set the POV Period before running.....
    Const boolgetPOVPeriodMap = False
    
    'Declare working variables
    Dim intPartitionKey
    Dim strOutputMessage
    Dim strSQL
    Dim strCategoryFreq
    Dim objPeriodKey
    Dim strOutputFileName
    Dim strOutputFilePath
    
    
    'Get the location (PartitionKey
    intPartitionKey = RES.PlngLocKey
    
    'Create SQL Query to get Current Map Data
    If boolgetPOVPeriodMap = False Then
         strSQL = "SELECT * FROM tDataMap where PartitionKey = " & intPartitionKey & " order by DimName ASC"
    Else
         strCategoryFreq = API.POVMgr.fCategoryFreq(API.POVMgr.PPOVCategory)
         Set objPeriodKey = API.POVMgr.fPeriodKey(API.POVMgr.PPOVPeriod, 0, strCategoryFreq)
         strSQL = "SELECT * from vDataMap where PartitionKey = " & intPartitionKey & " and PeriodKey = '" & objPeriodKey.dteDateKey & " 12:00:00 AM' order by DimName Asc"
    End If 
    
    'Create Recordset for all Exported Entities
    Set rsMap = DW.DataAccess.farsKeySet(strSQL)
    
    If rsMap.EOF And rsMap.BOF Then
         'No records
         If boolgetPOVPeriodMap = False Then
              strOutputMessage = "No Mapping data was found For " & API.POVMgr.PPOVLocation & ".  If this location Is using Parent Maps, you can only export mapping data at the parent location."     
         Else
              strOutputMessage = "No Mapping data was found For " & API.POVMgr.PPOVLocation & " for period " & API.POVMgr.PPOVPeriod & ".  If this location Is using Parent Maps, you can only export mapping data at the parent location."          
         End If 
    Else
         'Records Exist, process
         
         'Generate file name / path
         If boolgetPOVPeriodMap = False Then
              strOutputFileName = API.POVMgr.PPOVLocation & "_DimensionMaps.csv"
         Else
              strOutputFileName = API.POVMgr.PPOVLocation & "_" & objPeriodKey.strDateKey & "_DimensionMaps.csv"
         End If 
         
         strOutputFilePath = DW.Connection.PstrDirOutbox & "\ExcelFiles\"
         
         'Create file output
         Set objfilesys = CreateObject("Scripting.FileSystemObject") 
         Set objMapFile = objfilesys.CreateTextFile(strOutputFilePath & strOutputFileName, True)
         
         'Declare working variables
         Dim intRecordCount
         Dim intTotalRecordCount
         Dim strCurrDimName
         
         'Initialize variables
         intCurrRecordCount = 0
         intTotalRecordCount = 0
         strCurrDimName = ""
         
         'Write Start of File Headers
         If boolgetPOVPeriodMap = False Then
              objMapFile.Writeline (API.POVMgr.PPOVLocation & " - Map Conversion")
         Else
              objMapFile.Writeline (API.POVMgr.PPOVLocation & " - Map Conversion for " & rsMap.fields("PeriodKey"))
         End If 
    
         objMapFile.Writeline ("")
         objMapFile.Writeline ("Partition: " & API.POVMgr.PPOVLocation)
         objMapFile.Writeline ("User ID: " & DW.Connection.PstrUserID)
         
         With rsMap
              Do Until .eof
                   'Check to see if current DimName matches existing DimName.  If not, add headers
                   If rsMap.fields("DimName") <> strCurrDimName Then
                         'If the dimension name has changed to a different dimension name, show total information before starting headers
                         If strCurrDimName <> "" Then 
                              objMapFile.Writeline ("")
                              objMapFile.Writeline ("Total " & strCurrDimName & " records : " & intCurrRecordCount)
                                  intCurrRecordCount = 0
                          End If 
                          'Create default header at top of each new dimension group
                         objMapFile.Writeline ("")
                         objMapFile.Writeline ( rsMap.fields("DimName") & " Map ")
                            objMapFile.Writeline ("PartitionKey, DimName, Source FM Account, Description, Target FM Account, WhereClauseType, WhereClauseValue, -, Sequence, DataKey, VBScript")
                            strCurrDimName = rsMap.fields("DimName")
    
                   End If 
    
                     'Write Details
                   objMapFile.Writeline(intPartitionKey & "," & rsMap.fields("DimName").Value & "," & rsMap.fields("SrcKey").Value & "," & rsMap.fields("SrcDesc").Value & "," & rsMap.fields("TargKey").Value & "," & rsMap.fields("WhereClauseType").Value & "," & rsMap.fields("WhereClauseValue").Value & "," & rsMap.fields("ChangeSign").Value & "," & rsMap.fields("Sequence").Value & "," & rsMap.fields("DataKey").Value & "," & rsMap.fields("VBScript").Value)
    
                   'Increment Counters
                   intCurrRecordCount = intCurrRecordCount + 1
                   intTotalRecordCount = intTotalRecordCount + 1
                   'Move to the next record
                   .movenext
                   
              Loop
         End With
         
         'Write total records 
         objMapFile.Writeline ("Total " & strCurrDimName & " records : " & intCurrRecordCount)
         objMapFile.Writeline ("")
         objMapFile.Writeline ("Total records for all Dimensions : " & intTotalRecordCount)     
         
         'Close / release file objects
         objMapFile.close
    
    
         'Create output message          
         strOutputMessage = "Mapping data for " & API.POVMgr.PPOVLocation  & " complete, " & intTotalRecordCount & " rows exported."
         
    End If 
    
    'Close / release data objects
    rsMap.close
    
    
    'Display output
    If LCase(API.DataWindow.Connection.PstrClientType) = "workbench" Then
              MsgBox strOutputMessage        
    Else
         'Let the user know we are done
         RES.PlngActionType = 2
         RES.PstrActionValue = strOutputMessage
         
    End If
    
    End Sub
    Edited by: beyerch2 on Nov 23, 2011 2:28 PM
  • 4. Re: export all maps (mappings for all dimensions in EXCEL)
    beyerch2 Expert
    Currently Being Moderated
    As promised, this script creates a real live Excel file. Each worksheet is one of the maps...

    This has been tested for FDM 9.3.1, SQL Server 2005, and Excel 2003. I expect the code to work equally as well with 2007/2010 excel as it's pretty basic; however, no guarantees

    Any questions, feel free to ask.

    Thanks

    --------------
    Sub ExportAllCurrDimMapsForLocationtoXLS()
    '------------------------------------------------------------------
    'UpStream WebLink DM Custom Script: 
    '
    'Created By:         cbeyer
    'Date Created:       11-23-11
    '
    'Purpose:               Export all dimension maps to an Excel workbook       
    '               
    '------------------------------------------------------------------
    
    
    'Declare Constant
    'NOTE : This will control whether the function gets the current map in the system or whether it looks back for a specific Period
    '       FDM stores the Map for each period that was loaded... You may want to export a particular POV Period for audit purposes, etc.
    '       IF you enable this, be sure to set the POV Period before running.....
    Const boolgetPOVPeriodMap = False
    
    'Declare working variables
    Dim intPartitionKey
    Dim strOutputMessage
    Dim strSQL
    Dim strCategoryFreq
    Dim objPeriodKey
    Dim strOutputFileName
    Dim strOutputFilePath
    
    
    
    'Get the location (PartitionKey
    intPartitionKey = RES.PlngLocKey
    
    'Create SQL Query to get Current Map Data
    If boolgetPOVPeriodMap = False Then
         strSQL = "SELECT * FROM tDataMap where PartitionKey = " & intPartitionKey & " order by DimName ASC"
    Else
         strCategoryFreq = API.POVMgr.fCategoryFreq(API.POVMgr.PPOVCategory)
         Set objPeriodKey = API.POVMgr.fPeriodKey(API.POVMgr.PPOVPeriod, 0, strCategoryFreq)
         strSQL = "SELECT * from vDataMap where PartitionKey = " & intPartitionKey & " and PeriodKey = '" & objPeriodKey.dteDateKey & " 12:00:00 AM' order by DimName Asc"
    End If 
    
    'Create Recordset for all Exported Entities
    Set rsMap = DW.DataAccess.farsKeySet(strSQL)
    
    If rsMap.EOF And rsMap.BOF Then
         'No records
         If boolgetPOVPeriodMap = False Then
              strOutputMessage = "No Mapping data was found For " & API.POVMgr.PPOVLocation & ".  If this location Is using Parent Maps, you can only export mapping data at the parent location."     
         Else
              strOutputMessage = "No Mapping data was found For " & API.POVMgr.PPOVLocation & " for period " & API.POVMgr.PPOVPeriod & ".  If this location Is using Parent Maps, you can only export mapping data at the parent location."          
         End If 
    Else
         'Records Exist, process
         
         'Generate file name / path
         If boolgetPOVPeriodMap = False Then
              strOutputFileName = API.POVMgr.PPOVLocation & "_DimensionMaps.xls"
         Else
              strOutputFileName = API.POVMgr.PPOVLocation & "_" & objPeriodKey.strDateKey & "_DimensionMaps.xls"
         End If 
         
         strOutputFilePath = DW.Connection.PstrDirOutbox & "\ExcelFiles\"
    
         'Create Excel file reference     
         'Declare Excel working variables
         Dim oExcel
         Dim oBook
         Dim oSheet 'No puns here......
         Dim intCurrentSheetOrdinal
         Dim intCurrentRowOrdinal
         Dim intCurrentColOrdinal
    
         'Intialize Excel
         Set oExcel = CreateObject("Excel.Application")
         Set oBook = oExcel.Workbooks.Add
    
         
         'Declare working variables
         Dim strCurrDimName
         
         'Initialize variables
         strCurrDimName = ""
         intCurrentSheetOrdinal = 1
         intCurrentRowOrdinal = 1
         intCurrentColOrdinal = 1
         
         
         With rsMap
              Do Until .eof
                   'Check to see if current DimName matches existing DimName.  If not, add headers
                   If rsMap.fields("DimName") <> strCurrDimName Then
                         'If the dimension name has changed to a different dimension name, show total information before starting headers
    
                         'Create worksheet reference
                           Set oSheet = oBook.Worksheets(intCurrentSheetOrdinal)                     
                           
                          'Create default header at top of each new dimension group
                             If boolgetPOVPeriodMap = False Then
                                  oSheet.range("A1") = (API.POVMgr.PPOVLocation & " - Map Conversion")
                             Else
                                  oSheet.range("A1") = (API.POVMgr.PPOVLocation & " - Map Conversion for " & rsMap.fields("PeriodKey"))
                             End If 
    
                             oSheet.range("A3") = "Partition: " & API.POVMgr.PPOVLocation
                             oSheet.range("A4") = "User ID: " & DW.Connection.PstrUserID
                          
                             'NOTE: I could make an array of the field names and do a loop here; however, this is easier to read.....
                             '      probably not how I would do it from an efficiency standpoint, but since it's a limited number of fields
                             '      this will work.....
                                 oSheet.range("A5") = "PartitionKey"
                                 oSheet.range("B5") = "DimName"
                                 oSheet.range("C5") = "Source FM Account"
                                 oSheet.range("D5") = "Description"
                                 oSheet.range("E5") = "Target FM Account"
                                 oSheet.range("F5") = "WhereClauseType"
                                 oSheet.range("G5") = "WhereClauseValue"
                                 oSheet.range("H5") = "-"
                                 oSheet.range("I5") = "Sequence"
                                 oSheet.range("J5") = "DataKey"
                                 oSheet.range("K5") = "VBScript"
                             
                             'Update variables                   
                                strCurrDimName = rsMap.fields("DimName")
                                intCurrentRowOrdinal = 6
                                intCurrentSheetOrdinal = intCurrentSheetOrdinal + 1
                                
                                'Update worksheet name
                                oSheet.name = strCurrDimName
                                
    
                   End If 
    
                     'Write Details
                             'NOTE: I could make an array of the field names and do a loop here; however, this is easier to read.....
                             '      probably not how I would do it from an efficiency standpoint, but since it's a limited number of fields
                             '      this will work.....
                            oSheet.range("A" & intCurrentRowOrdinal) = intPartitionKey
                     oSheet.range("B" & intCurrentRowOrdinal) = rsMap.fields("DimName").Value
                     oSheet.range("C" & intCurrentRowOrdinal) = rsMap.fields("SrcKey").Value
                     oSheet.range("D" & intCurrentRowOrdinal) = rsMap.fields("SrcDesc").Value
                     oSheet.range("E" & intCurrentRowOrdinal) = rsMap.fields("TargKey").Value
                     oSheet.range("F" & intCurrentRowOrdinal) = rsMap.fields("WhereClauseType").Value
                     oSheet.range("G" & intCurrentRowOrdinal) = rsMap.fields("WhereClauseValue").Value
                     oSheet.range("H" & intCurrentRowOrdinal) = rsMap.fields("ChangeSign").Value
                     oSheet.range("I" & intCurrentRowOrdinal) = rsMap.fields("Sequence").Value
                     oSheet.range("J" & intCurrentRowOrdinal) = rsMap.fields("DataKey").Value
                     oSheet.range("K" & intCurrentRowOrdinal) = rsMap.fields("VBScript").Value
    
                   'Increment Counters
                   intCurrentRowOrdinal = intCurrentRowOrdinal + 1
                   'Move to the next record
                   .movenext
                   
              Loop
         End With
         
    
         'Close / release file objects
         
         oBook.SaveAs strOutputFilePath & strOutputFileName
         oExcel.Quit
         
    
         'Create output message          
         strOutputMessage = "Mapping data export for " & API.POVMgr.PPOVLocation  & " complete.  Extract file is : " & strOutputFilePath & strOutputFileName
         
    End If 
    
    'Close / release data objects
    rsMap.close
    
    
    'Display output
    If LCase(API.DataWindow.Connection.PstrClientType) = "workbench" Then
              MsgBox strOutputMessage        
    Else
         'Let the user know we are done
         RES.PlngActionType = 2
         RES.PstrActionValue = strOutputMessage
         
    End If
    
    End Sub
  • 5. Re: export all maps (mappings for all dimensions in EXCEL)
    Adam S Newbie
    Currently Being Moderated
    Thanks! Very helpful script. I did have to make minor edits, probably because the Oracle site didn't format very nicely, its working great for us now.

    Is there any way to have the file open after it saves, like other Export to Excel buttons in FDM? I don't want users searching the outbox if they can save the file to another location themselves.

    Thanks again.
  • 6. Re: export all maps (mappings for all dimensions in EXCEL)
    SH Guru
    Currently Being Moderated
    Have you tried google? Remember it's your friend :)
  • 7. Re: export all maps (mappings for all dimensions in EXCEL)
    Adam S Newbie
    Currently Being Moderated
    Thanks. I tried, but failed. I'm new to VBA, but pretty familiar with Excel VBA. I tried -

    oBook.Open strOutputFilePath & strOutputFileName

    Any help would be appreciated...
  • 8. Re: export all maps (mappings for all dimensions in EXCEL)
    tonyscalese Oracle ACE
    Currently Being Moderated
    Look at the accelerators in workbench.
  • 9. Re: export all maps (mappings for all dimensions in EXCEL)
    beyerch2 Expert
    Currently Being Moderated
    oBook.Open is not going to do what you want.

    oBook is a workbook object running on the FDM Server in this example. Even if the book were visible, it would not appear on the end user's machine, rather the server!

    What you need to do is redirect the user to a page that would push the file down to them so that they can open it locally.

    I assume this is the accelerator reference Tony is making (i.e. push a file down)
  • 10. Re: export all maps (mappings for all dimensions in EXCEL)
    fiammdin Newbie
    Currently Being Moderated
    Ciao,

    I solved id like that.

    ***********************************************************************************************************************************
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set file = fso.getFile(API.DataWindow.Connection.PstrDirOutbox & "\ExcelFiles\" & fileName)
    file.copy(webOutputFolder & fileName)

    RES.PlngActionType = 2
    RES.PstrActionValue = RES.PstrActionValue & vbCrLf & "<a href=""../CustomExportFiles/" & fileName & """ Type=""text/plain"">" & fileName & "</a>"
    ***********************************************************************************************************************************
    where the fileName is the File that has been created, and the CustomExportFiles is a virtuall Folder in the ISS.

    Like that you get a pop up where you can just click on it.
  • 11. Re: export all maps (mappings for all dimensions in EXCEL)
    JTF85 Journeyer
    Currently Being Moderated
    Does anyone have any hints on how to name the ranges for it to be re-uploaded using the Excel version of the code? I can't seem to figure out how to define a named range with ups so that it can be reloaded into FDM. I understand how to do it in Excel using the VBA toolset, but it's a bit different triggering it from outside Excel

    Thanks
    JTF
  • 12. Re: export all maps (mappings for all dimensions in EXCEL)
    beyerch2 Expert
    Currently Being Moderated
    That is probably the route I would go; however, that wouldn't get it to automatically open for him like he requested

    I THINK,but haven't tried this, that you could make it pop open like this .....

    RES.PstrActionValue = RES.PstrActionValue & vbCrLf & "<script language=""javascript"">window.open('_new','<a href=""http://servername/yourfolderhere/" & fileName & """');</script>"

    when that is passed to the browser, it should execute the javascript, pop open a new browser window, and attempt to access the file in the link.
  • 13. Re: export all maps (mappings for all dimensions in EXCEL)
    fiammdin Newbie
    Currently Being Moderated
    :)
    true, but a single click more on the page would be feasible... :)

    cheers
  • 14. Re: export all maps (mappings for all dimensions in EXCEL)
    beyerch2 Expert
    Currently Being Moderated
    Below is updated code with the following changes :

    - Adjusted SaveAs logic to prevent Excel prompts in the event the file already exists, etc. (i.e. DisplayAlerts TRUE / FALSE)
    - Added Range creation logic for each worksheet page. If I really wanted perfect code, could do this better, but it gets the job done.
    Sub ExportAllCurrDimMapsForLocationtoXLS()
    '------------------------------------------------------------------
    'UpStream WebLink DM Custom Script: 
    '
    'Created By:         cbeyer
    'Date Created:       11-23-11
    '
    'Purpose:               Export all dimension maps to an Excel workbook       
    '               
    '------------------------------------------------------------------
    
    
    'Declare Constant
    'NOTE : This will control whether the function gets the current map in the system or whether it looks back for a specific Period
    '       FDM stores the Map for each period that was loaded... You may want to export a particular POV Period for audit purposes, etc.
    '       IF you enable this, be sure to set the POV Period before running.....
    Const boolgetPOVPeriodMap = False
    
    'Declare working variables
    Dim intPartitionKey
    Dim strOutputMessage
    Dim strSQL
    Dim strCategoryFreq
    Dim objPeriodKey
    Dim strOutputFileName
    Dim strOutputFilePath
    
    
    
    'Get the location (PartitionKey
    intPartitionKey = RES.PlngLocKey
    
    'Create SQL Query to get Current Map Data
    If boolgetPOVPeriodMap = False Then
         strSQL = "SELECT * FROM tDataMap where PartitionKey = " & intPartitionKey & " order by DimName ASC"
    Else
         strCategoryFreq = API.POVMgr.fCategoryFreq(API.POVMgr.PPOVCategory)
         Set objPeriodKey = API.POVMgr.fPeriodKey(API.POVMgr.PPOVPeriod, 0, strCategoryFreq)
         strSQL = "SELECT * from vDataMap where PartitionKey = " & intPartitionKey & " and PeriodKey = '" & objPeriodKey.dteDateKey & " 12:00:00 AM' order by DimName Asc"
    End If 
    
    'Create Recordset for all Exported Entities
    Set rsMap = DW.DataAccess.farsKeySet(strSQL)
    
    If rsMap.EOF And rsMap.BOF Then
         'No records
         If boolgetPOVPeriodMap = False Then
              strOutputMessage = "No Mapping data was found For " & API.POVMgr.PPOVLocation & ".  If this location Is using Parent Maps, you can only export mapping data at the parent location."     
         Else
              strOutputMessage = "No Mapping data was found For " & API.POVMgr.PPOVLocation & " for period " & API.POVMgr.PPOVPeriod & ".  If this location Is using Parent Maps, you can only export mapping data at the parent location."          
         End If 
    Else
         'Records Exist, process
         
         'Generate file name / path
         If boolgetPOVPeriodMap = False Then
              strOutputFileName = API.POVMgr.PPOVLocation & "_DimensionMaps.xls"
         Else
              strOutputFileName = API.POVMgr.PPOVLocation & "_" & objPeriodKey.strDateKey & "_DimensionMaps.xls"
         End If 
         
         strOutputFilePath = DW.Connection.PstrDirOutbox & "\ExcelFiles\"
    
         'Create Excel file reference     
         'Declare Excel working variables
         Dim oExcel
         Dim oBook
         Dim oSheet 'No puns here......
         Dim oRange
         Dim intCurrentSheetOrdinal
         Dim intCurrentRowOrdinal
         Dim intCurrentColOrdinal
    
         'Intialize Excel
         Set oExcel = CreateObject("Excel.Application")
         Set oBook = oExcel.Workbooks.Add
    
         
         'Declare working variables
         Dim strCurrDimName
         
         'Initialize variables
         strCurrDimName = ""
         intCurrentSheetOrdinal = 1
         intCurrentRowOrdinal = 1
         intCurrentColOrdinal = 1
         
         
         With rsMap
              Do Until .eof
                   'Check to see if current DimName matches existing DimName.  If not, add headers
                   If rsMap.fields("DimName") <> strCurrDimName Then
                         'If the dimension name has changed to a different dimension name, show total information before starting headers
                         
                         'If the previous dimension was not "", then we are transitioning from one range to the next.  Lets create a named range on the just
                         'finished worksheet because we can or because you may want to use this for re-uploading
                         'NOTE : The range I'm creating is more for reference as to how to implement this and I don't know if I'm making the range in a fashion that
                         'FDM will pickup for importing.  
                         'NOTE : You probably want intCurrentRowOrdinal - 1 since it is 1 row past the last row of data at this point.  If you want to clean it up, 
                         'then you need to make sure RowOrdinal is not going to be less than the starting point and I didn't feel like adding the couple rows of 
                         'code to do the work properly as FDM will just ignore the blank row in all likelihood.
                         
                                                                      If strCurrDimName <> "" Then
                                Set oRange = oSheet.Range("A6:K" & intCurrentRowOrdinal)
                                oBook.Names.Add "ups"&strCurrDimName, oRange
                         End If 
    
    
                         'Create worksheet reference
                           Set oSheet = oBook.Worksheets(intCurrentSheetOrdinal)                     
                           
                          'Create default header at top of each new dimension group
                             If boolgetPOVPeriodMap = False Then
                                  oSheet.range("A1") = (API.POVMgr.PPOVLocation & " - Map Conversion")
                             Else
                                  oSheet.range("A1") = (API.POVMgr.PPOVLocation & " - Map Conversion for " & rsMap.fields("PeriodKey"))
                             End If 
    
                             oSheet.range("A3") = "Partition: " & API.POVMgr.PPOVLocation
                             oSheet.range("A4") = "User ID: " & DW.Connection.PstrUserID
                          
                             'NOTE: I could make an array of the field names and do a loop here; however, this is easier to read.....
                             '      probably not how I would do it from an efficiency standpoint, but since it's a limited number of fields
                             '      this will work.....
                                 oSheet.range("A5") = "PartitionKey"
                                 oSheet.range("B5") = "DimName"
                                 oSheet.range("C5") = "Source FM Account"
                                 oSheet.range("D5") = "Description"
                                 oSheet.range("E5") = "Target FM Account"
                                 oSheet.range("F5") = "WhereClauseType"
                                 oSheet.range("G5") = "WhereClauseValue"
                                 oSheet.range("H5") = "-"
                                 oSheet.range("I5") = "Sequence"
                                 oSheet.range("J5") = "DataKey"
                                 oSheet.range("K5") = "VBScript"
                             
                             'Update variables                   
                                strCurrDimName = rsMap.fields("DimName")
                                intCurrentRowOrdinal = 6
                                intCurrentSheetOrdinal = intCurrentSheetOrdinal + 1
                                
                                'Update worksheet name
                                oSheet.name = strCurrDimName
                                
    
                   End If 
    
                     'Write Details
                            oSheet.range("A" & intCurrentRowOrdinal) = intPartitionKey
                     oSheet.range("B" & intCurrentRowOrdinal) = rsMap.fields("DimName").Value
                     oSheet.range("C" & intCurrentRowOrdinal) = rsMap.fields("SrcKey").Value
                     oSheet.range("D" & intCurrentRowOrdinal) = rsMap.fields("SrcDesc").Value
                     oSheet.range("E" & intCurrentRowOrdinal) = rsMap.fields("TargKey").Value
                     oSheet.range("F" & intCurrentRowOrdinal) = rsMap.fields("WhereClauseType").Value
                     oSheet.range("G" & intCurrentRowOrdinal) = rsMap.fields("WhereClauseValue").Value
                     oSheet.range("H" & intCurrentRowOrdinal) = rsMap.fields("ChangeSign").Value
                     oSheet.range("I" & intCurrentRowOrdinal) = rsMap.fields("Sequence").Value
                     oSheet.range("J" & intCurrentRowOrdinal) = rsMap.fields("DataKey").Value
                     oSheet.range("K" & intCurrentRowOrdinal) = rsMap.fields("VBScript").Value
    
                   'Increment Counters
                   intCurrentRowOrdinal = intCurrentRowOrdinal + 1
                   'Move to the next record
                   .movenext
                   
              Loop
         End With
    
         'Final Sheet Named Range addition
         'Since the loop will end and we will not execute the above logic to create the range for the previous sheet
         'the easiest (laziest) solution is to just handle the last sheet after the loop.
         'We're basically doing the same stuff we did above, just down here.
          If strCurrDimName <> "" Then
              Set oRange = oSheet.Range("A6:K" & intCurrentRowOrdinal)
               oBook.Names.Add "ups"&strCurrDimName, oRange
          End If      
    
         'Close / release file objects
         
         'Added some logic here to ensure you don't get caught up on the file replace prompt.
         oExcel.Application.DisplayAlerts = False
         oBook.SaveAs strOutputFilePath & strOutputFileName
         oExcel.Application.DisplayAlerts = True
         oExcel.Quit
         
    
         'Create output message          
         strOutputMessage = "Mapping data export for " & API.POVMgr.PPOVLocation  & " complete.  Extract file is : " & strOutputFilePath & strOutputFileName
         
    End If 
    
    'Close / release data objects
    rsMap.close
    
    
    'Display output
    If LCase(API.DataWindow.Connection.PstrClientType) = "workbench" Then
              MsgBox strOutputMessage        
    Else
         'Let the user know we are done
         RES.PlngActionType = 2
         RES.PstrActionValue = strOutputMessage
         
    End If
    
    End Sub
    Edited by: beyerch2 on Dec 14, 2011 9:43 AM
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points