How to Call an Application Engine Process from App Studio

Alright folks in my previous post I gave you an introduction to Application Engine.

We also created out first process and called HelloWorld...

Awesome let's now have a look at calling this process in App Studio.

Let's create a new report and call it App Engine Sample.

This is how my report looks. I thought I will go green :D

I have made the parameter 1 and 2 cells editable so that user can enter the values. You can do this by right clicking on the cell > Format Cells >  Protection Tab > remove the tick on Cells check box

I have also added a button to calculate the values and once calculated it should show the result next to the Answer text box.

Note : I have created a report variable to assign the result from the Application process.

Let's define an action on button.

For the action lets select Application Engine Process..

Select the HelloWorld version. ( I am selecting version 2 as this is my latest version)

Set the rv_Result to Action.Result.Text

Now let's set the input parameters.

Set the 2 cells in your report for Input 1 and 2 . In my case its E4 and E5.

Ok done and dusted. Finally make sure you drag and drop the report variable on to the Answer text box so that it automatically updates when we click on Calculate.

Tadaaaaaaaaaaaaaaaaa

That's just the basics. You can look at the help in App Engine to have a look at the more complex use cases.

Hope this post helped you to get started with App Engine. I am working on more complex scenarios and will keep you posted with my findings soon.

Introduction to Application Engine for Infor BI

I was doing some research on Predictive Analytics recently and there are so many tools in the market which offers this functionality.

Then I thought about Infor BI. Well Infor doesn't have an analytics component inbuilt. At least that what i thought. And then I got to know about Application Engine Process Editor.

So what is Application Engine ??

This is Infor's definition

The Application Engine Process Editor is a tool that you need to define, verify, compile, test, and publish Application Engine processes. In principle, Application Engine processes are plain text files that contain the code written in the BI# programming language. The Application Engine Process Editor includes a text editor and supports the whole life cycle of a process.


Basically you can create custom programs using B# which is very similar to C# and these programs or processes can be called from Application Studio web services.

Let's see how we can do this later in the post.

Let's open up Application Engine and click on the log on button.

Connect to the repository where you will want to store the processes.

Let's go and create a new process...

When you click on the new option it opens up a new process editor shown above.

Copy the below code and paste it to the editor.

#define EngineVersion 4.0
#define RuntimeVersion 4.0

int HelloWorld(int a,int b)
@Description: "App Engine Addition";
@Category: "Place your process category here";
@Returns: "Returns an Integer";
@Parameter[a]: "Input Para 1";
@Parameter[b]: "Input Para 2";
{
    /* To use intellisense press Ctrl-Space and select a function from the list */
    /* To use autocompletion start typing the function. When the right function appears type the round opening bracket and select from a list of available signatures. */
    /* add your BI# code here */

    int number = a + b;
    WriteLocalFile("C:\\Files\\RnD\\Blog\\App Engine\\Test.txt", "a+b is : " + number);

    return number;
}

The first 2 lines defines the version of the Engine and the Run time. Use the default 2 lines which you get when you create a new process. The version I am using here might be different to what you have in your environment.

Next you have this line int HelloWorld(int a,int b)

This is the HelloWorld method which takes in 2 integer variables named a,b and outputs an integer variable.

@Description: "You need to describe the process here";
@Category: "Place your process category here";
@Returns: "Define the return type here";
@Parameter[a]: "Input Para 1";
@Parameter[b]: "Input Para 2";

If your method is using input parameters then it is mandatory that you define it using the @Parameter[] function used above. Depending on the number of input variables you have , you need to define @Parameter tags for each of the variables.

Next i add the 2 variables and assign it to a variable named number and I also write the value to a text file in my environment.

    int number = a + b;
    WriteLocalFile("C:\\Files\\Rnd\\Blog\\App Engine\\Test.txt", "a+b is : " + number);

    return number;

Finally I return the number variable to the program which called the process.

Now let's test our process............

Before you test it save the definition file so that you do not lose this code :).

Click on the Validate button to make sure the syntax is correct and once confirmed click on the Test Process button.

Enter values for a and b as shown below.....

Click on the run button ( Green button)

There you go, the process add up the 2 numbers and gives an result of 60. And it has also written the value to the Text file.

Let's publish this to the repository so that App studio can call this process.

Once published you can have a look at the processes by clicking on the load button.

Here I have 2 version of my HelloWorld Process. And depending on the details you have specified it will show up here.

In my next post I will explain how to call this process using Application Studio. Stay tuned geeks...

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

Using Custom MDX in App Studio

I was working on a fairly simple report for one of the clients I'm doing consulting few days back. At first it looked quite simple. But then suddenly client changed the requirement and I got caught to a Red light. Grrrrrr why do clients always have to change their requirements.

Well .... This is not something new to me , actually this happens quite frequently in the IT world :).

In this instance I had to use a Custom MDX to overcome this problem.

Report Layout

The Site combo drives the work centers in the report layout. The initial requirement was to show the selected Site and all of it's base elements which was a simple structure selection in the hyperblock.

Later they changed the requirement to show All base elements of the selected Site as well as the parent for each base element.

Note : Site in the combo box is in Level 3 where as the base elements can be in different levels in the hierarchy.

So to over come this problem I had to write a custom MDX. What is MDX ??

Multidimensional Expressions (MDX) is a query language for online analytical processing (OLAP) .

It's like using SQL in SQL Server.... But the syntax is different and could be difficult to understand. But with some research on the internet I was able to figure this out.

Below is my MDX

="Generate  (   Descendants (  StrToSet('{"&ReportVariables.rv_Site.Text&"}'), 10,LEAVES  ) ,

                                    UNION ({ [Work Center].CurrentMember.Parent}, Descendants  ( StrToSet('{"&ReportVariables.rv_Site.Text&"}') ,10,LEAVES ) )

                        ) "

Wooooah what the hell is this ???

Ok so let me break this down in to small pieces and explain what I have done here.


I have used the below MDX functions to achieve my goal.

1. Descendants
2. Generate
3. StrtoSet
4. UNION

Let me first explain what these functions do..

Descendants
Returns the set of descendants of a member at a specified level or distance, optionally including or excluding descendants in other levels.

Descendants (  StrToSet('{"&ReportVariables.rv_Site.Text&"}'), 10,LEAVES  )

The report varbale rv_Site contains the selection which the user did. I need to get the base elements of the particular site.  The key word here is "LEAVES" . This means the lowest level members of the hierarchy. The number 10 here means how many levels you need to go down on the hierarchy. Make sure you set a number greater than the lowest level in the hierarchy so that it picks the lowest level members.

StrToSet
Returns the set specified by a Multidimensional Expressions (MDX)– string.

StrToSet('{"&ReportVariables.rv_Site.Text&"}')

What this does is convert the Site value in to a readable MDX format


UNION

Returns a set that is generated by combining two sets.

UNION ({ [Work Center].CurrentMember.Parent}, Descendants  ( StrToSet('{"&ReportVariables.rv_Site.Text&"}') ,10,LEAVES ) )

Here I am combining  [Work Center].CurrentMember.Parent and Descendants  ( StrToSet('{"&ReportVariables.rv_Site.Text&"}') ,10,LEAVES )

What I am actually doing here is getting the parent of each base element and combining it with the base elements of rv_Site.

Generate

Generate function gives you the ability to loop through a set and peform a function on each of the items in the particular set.

So in our example I am selecting all the base elements of rv_site as the Set for the Generate function.

Then i loop through using the [Work Center].CurrentMember.Parent option to get the Parent of each base element.

I hope my explanation makes sense :D. Well if it did not at least I tried lol.

For a for information on MDX you can visit here.

I will post few more MDX related examples in the future. Cheers...

Hello Application Studio

Waaaaaaaazup people.

When I first started learning how to code in Java / C Sharp the first ever program i developed was called Hello World. It was a simple program which printed the text "Hello World".

Today I am going to show you how easy it is to create your first report in App studio and print the text " Hello App Studio" :)

Ok. First things first let's log on to the Best Practices Repository.

 Let's first briefly go through the different sections of App Studio.

Under Repository Explorer You have >

Report Catalog -  Contains all the reports you create

Database Structure - Contains the connection to the Datasource

Accessories - Contains variables and other custom objects

Click on the Report Catalog Tab > Locate the Reports Folder

Right Click on the Reports folder > New > Report

Let's call this Hello App Studio

Leave the defaults and press OK.'

When you create the report notice on the top you are in View Mode. 

As you should expect you see a blank report because we have not added any content to our report. Let's click on the Design option.

Oooh. Now doesn't that look familiar. Isn't that Excel ??

Well It's not exactly excel but App studio is a Excel like editor. And if you are a pro at Excel you are going to love this :)

Ok lets create set a heading for the report I am going to call this "My First App Studio Report" and a text Hello App Studio!!!

Ok Lets preview our report now.

Tadaaaaaaaaaaaaaaa

I know it doesn't look that pretty but we will learn how to do cool stuff and make it look good in the upcoming posts. Stay tuned.....