Using Scripting in App studio

I was talking to a very senior Infor BI guy some time back and we were sharing our knowledge on the different tools in the Infor BI Stack.

One of the things he asked me was about Scripting in Infor BI.So today I am sharing with you some of the use cases on Scripting which I have used in my client projects.

Actually there are so many things you can do using scripting in App studio.

It could be from changing a font of a text to hiding rows in the report to forcing a post back in the report. But most of the functionality can be done without using scripting. So today I will talk about couple of requirements which I had to use scripting to cater these requirements.

1.Changing the default text of elements in a dynamic Hyper block
Eg: You might want to change the text of a particular element to show a different name. Lets see how you can achieve this

I have created a VB function named SearchAndReplaceCellValue.

What it does is basically looks for a specific text in the spreadsheet and replaces it with a given text. In this example I am looking for a text "[GL Account].[BS_Cash and cash equivalents].[1]" and replacing this with the text "Cash".

Note : [GL Account].[BS_Cash and cash equivalents].[1] is the unique name of the element you want to replace.

There are some additional parameters I provide here.
sColumn - This is the column which will contain the hyperblock text
StartRow - This is the row which the function should start looking for the text
EndRow - This is the rown which the function should end the loop.
EndTag - This is the End of the report
SearchValue - Unique name of the element which you need to change
ReplaceValue - This is the text which will replace the existing value

This is how I call the function
Call SearchAndReplaceCellValue("H", 40, 500, "#END", "[GL Account].[BS_Cash and cash equivalents].[1]", "Cash")

This is the function defintion
Sub SearchAndReplaceCellValue(sColumn, StartRow, EndRow, EndTag, SearchValue, ReplaceValue)

for nRow= StartRow to EndRow
if (GetCellValue(sColumn, nRow)= EndTag) then exit sub

if (GetCellValue(sColumn, nRow)= SearchValue) then
Call SetCellValue(sColumn, nRow, ReplaceValue)
exit sub
end if

next


End Sub

2. Expanding and Collapsing a hyper block using a button click

I have a button which controls the expansion / Collapse functionality. And based on the click I update a variable named ExpandedState to 0 or 1.

Sub btnExpandCollapse_Click ()

if (btnExpandCollapse.Caption = "Collapse") then
Application.SetReportVariable "ExpandState","0"
Call SetDrillDownLevel()
else
Application.SetReportVariable "ExpandState","1"
Call SetDrillDownLevel()
End if


End Sub

Based on the ExpandedState variable value i set the drill down level. Here I have used 1 as the lowest level and 4 as the highest level. You will need to adjust the higher level based on the number of levels in your Hyperblocks.

Sub SetDrillDownLevel()

if (Application.GetReportVariable("ExpandState")="0") then
nLevel=1
else
nLevel=4
End if

Call ExpandHyperBlock("HB1", nLevel, false)
Call ExpandHyperBlock("HB2", nLevel, true)
End Sub

When Expanding/Collapsing multiple Hyperblocks the final call of the expand has the parameter True.. This is to ensure that the report is ONLY updated after all the Hyperblocks are expanded...
'if not the report will be updated at each and every Hyperblock expand/collapse

Sub ExpandHyperBlock(HBName, nLevel, bRecalc)
Dim myDWS
Dim Content
Dim HB

SET DWS=Spreadsheet.DefinitionWorksheet
Set HB=DWS.HyperBlocks.NamedItem(HBName)
HB.DrillDownLevelActive = True
HB.DrillDownLevelStart = nLevel
SET myDWS = nothing

if (bRecalc=True) then Spreadsheet.RecalcReport

End Sub