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.

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.....

Getting Started with Application Studio

Howdy Geeks.....

Ready to get started with the basics of App Studio ?? 

All right before we start App studio there are few things you need to check in your BI environment.

I have a check list which I normally check before I start development on App Studio.

1. App Studio / Web services / Dashboards / Rep Admin Installed and configured

2. A training or development Repository created with Report Catalog and Project

3. Users added to the repository

4. Web services configured to use Basic / Windows

5. Users added to the ASWS group

6. Web services / Dashboards URL's are accessible and working properly

7. Data source is available for report development ( This could be SSAS cubes or Infor OLAP Cubes)

Once you are ready open up App studio and Select the Repository , Project and Report Catalog.

Repository

The repository is a store for objects that the various Infor BI products have in common. A repository is stored in a relational database. The database can be SQL Server or Oracle and can store more than one repository. Essentially a repository contains users, groups, roles, projects and report catalogs. Users and groups can each be elements of several different roles.

Project

You use projects to save groups of reports on specific topics. Each project can have its own user permissions and administration rights. Projects and report catalogs are created and administered in the Repository. The Repository is administered through registrations. Each registration of a Repository object contains database connection information for the Repository, which includes database name, type, location and type of authentication. Each registration contains a Projects folder which can contain a number of report catalogs

Report Catalog

A report catalog is a component of the Repository which holds data about the structure of reports within a project. Typically, a report catalog contains a set of related reports. For example, the Best Practices Templates database supplied with Application Studio contains the Best Practices Templates project. This contains the Best Practices Templates report catalog which is a series of sample reports based on the Best Practices Templates database alias. The Report Catalog is also a pane in the Repository Explorer. It lists the report catalog, database aliases and reports you have access to.

In my next post I will guide you to create your first App studio report.

Infor BI Basics

Hello my dear geeks and freaks :)

This will be the first of the many Infor BI geeky posts you will see in the future.
In my first post I will teach you how to get started with Infor BI. 

Infor BI Architecture

The Installation is pretty straight forward.

You can select the components you require to install from the Installation screen shown above. To get the software you can download this from the Infor Xtreme web site. For more information visit https://www.inforxtreme.com

Application Studio

AKA App studio is the report / widget building tool in Infor BI.
It's like Cognos in IBM or SSRS in Microsoft or could be compared to Crystal reports.
You can create basic as well as very advance collateral using the tool.
I will be posting stuff on App studio basics and advance stuff soon. So stay tuned....

Office Plus

Office Plus is the excel integration tool which comes with Infor BI.
OP enables you to do analysis of data via Microsoft excel add on. 
This is similar to using SQL Analysis Services in Excel using Pivot tables. 
The concept is the same however OP has many different functionality and options compared to Pivot tables.  I will post some stuff on OP soon.

OLAP Server

Infor has it's own proprietary multi dimensional database. 
Infor OLAP (Online Analytical Processing) is an In memory database. 
You could create and maintain databases using the OLAP Administration tool. Typically an Administrator would work with this as things could get complicated.

Dashboards

Dashboards is simply an installation on IIS. This creates a web site called
Dashboards ( Well you can change this name at the installation if you like :D ).
You need IIS web server installed before proceeding with the Dashboards installation. You can select different authentication types such as
1. Basic - a basic user name and password to log on
2. Windows - uses windows authentication
3. IFS - Infor Federation services

This is where you would create your custom dashboards. The widgets for the dashboards will be created using App studio.

Self Service Dashboards

Self service is a very important feature in BI. Gartner defines what self service is here.
As the name suggest end users can create their own dashboard with minimal technical knowledge. However it is crucial that the users understand the data in the cubes. Creating a dashboard might sound easy with self service , however creating a dashboard which makes sense and gives insight might be tough to design.

We will talk about some of the best practices of dashboard building and tips and tricks to make your dashboards look good.