Searchable Catalog of BPC Script Logic Files

December 1, 2017

When you have created a BPC application that includes many script logic files it could be very useful to be able to easily search through every program in the environment for references to any specific dimension values or properties. This would be particularly useful when hierarchies change or when considering changing dimension property values. 

Fortunately it is relatively simple to create a searchable database containing every line of script logic code with reference to the model,program name,program line number. I have used Excel plus a bit of vb script to create this searchable code database, you could choose other more elegant solutions and add more bells and whistles but this does the job for me.

 The code can now be easily searched using standard functions like Ctrl F or using filters on columns.

How to create the script catalog

  • Download the metadata to a zip file using transaction UJBR

  • Unzip the files from the folder ADMINAPP

  • Open the VB editor in a new Excel file and paste the code from this blog page into it.

  • Create a button on Sheet 1 of the workbook and link it to the code

  • Run the code to create a catalog of all your script logic

     

 Now you have all the LGF and LGX files from your environment downloaded to a regular folder on your PC

 The next step is to use the Excel application to read every .LGF file in these directories and write the results into a single searchable page, together with the metadata , file name, model and  row id.

VB Code Download

The VB code required can be downloaded from the Downloads area or copy pasted from the section below

 

VB Code - copy and paste into Excel

 Sub read_lgf_files()

'---------------------------------------------------------

'  VB code to read through directories of text files and write the results 'into a single excel sheet Sheet1

'    Written by Peter Warren   BI Consultancy Solutions Ltd    Dec 2017

'---------------------------------------------------------

Const ForReading = 1

Dim fso, f, FldrC, fl, fc, fldr, nextfile, FD

RowID = 3

ColID = 4

ProgRow = 1

 

'Clear 100k rows from first 4 columns of Sheet1

Range(Cells(1, 1), Cells(100000, 4)).Clear

 

'Write the headers in row 1

Sheet1.Cells(1, 1) = "Model"

Sheet1.Cells(1, 2) = "Script File"

Sheet1.Cells(1, 3) = "Program Row ID"

Sheet1.Cells(1, 4) = "Code line "

Range(Cells(1, 1), Cells(1, 4)).Font.Italic = True

Range(Cells(1, 1), Cells(1, 4)).Font.Bold = True

'Set the status bar on so we can see the progress of the program execution

Application.DisplayStatusBar = True

'Create a File Dialog object so we can navigate to any directory from where we want to read files

Set FD = Application.FileDialog(msoFileDialogFolderPicker)

        ' Set the Help text description for the FolderBrowserDialog.

        FD.Title = _

            "Select the ADMINAPP directory or other top level directory for the script files"

        FD.Show

        'After selecting a folder store the selected item to a variable called FolderName and add a \ to the end

        FolderName = FD.SelectedItems(1) & "\"

  

   'Create fso as a FileSystemObject

   Set fso = CreateObject("Scripting.FileSystemObject")

   'object f is the selected folder from the file system object

   Set f = fso.GetFolder(FolderName)

 

' Create Folder Collection object FldrC

   Set FldrC = f.SubFolders

   'This loop with count up the total number of files in each sub folder of the main folder f and produce a total

   For Each FileCount In FldrC

   FcountTotal = FileCount.Files.Count + FcountTotal

   Next

'Now perform a double loop which will go through each folder in the collection and read each file within each folder

' for each Folder in Folder collection

   For Each fldr In FldrC   'loop through the folders

 

'create file collection object

   Set fc = fldr.Files

' for each file in file collection

 

   For Each fl In fc        'loop through the files

   ProgressCounter = ProgressCounter + 1

   ProgressPCT = ProgressCounter / FcountTotal

  Application.StatusBar = "Reading scripts in model..." & fldr.Name & " | Percent Complete = " & Format(ProgressPCT, "Percent")

 

'trap to skip reading the LGX files as they are not useful -

'could also just delete them from the directory instead

   If Right(fl.Name, 4) = ".LGF" Then

    Set nextfile = fso.GetFile(fldr.Path & "\" & fl.Name)

    Else

    GoTo SKIPFILE

    End If

'this is the bit that reads each file line by line

Set objTextFile = fso.OpenTextFile _

    (nextfile, ForReading)

ProgRow = 1

'The Do loop writes the result of each strNextLine to column 4 of sheet 1 and adds 1 to the row count

Do Until objTextFile.AtEndOfStream

    strNextLine = objTextFile.Readline

Sheet1.Cells(RowID, 1) = fldr.Name

Sheet1.Cells(RowID, 2) = fl.Name

Sheet1.Cells(RowID, 3) = ProgRow

Sheet1.Cells(RowID, 4) = strNextLine

RowID = RowID + 1

ProgRow = ProgRow + 1

Loop

SKIPFILE:

Next

Next

Application.StatusBar = "Complete...All scripts read"

End Sub

 

 

 

 

Please reload

Recent Posts

Please reload

Archive

Please reload

Tags

Please reload