1 2 Previous Next 18 Replies Latest reply: Aug 28, 2013 2:23 PM by dhiraj1981 RSS

    export all maps (mappings for all dimensions in EXCEL)

    Onono
      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
          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
            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
              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
                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
                  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
                    Have you tried google? Remember it's your friend :)
                    • 7. Re: export all maps (mappings for all dimensions in EXCEL)
                      Adam S
                      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
                        Look at the accelerators in workbench.
                        • 9. Re: export all maps (mappings for all dimensions in EXCEL)
                          beyerch2
                          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
                            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
                              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
                                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
                                  :)
                                  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
                                    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