Utilizing Input Module budgets

The Business Analyze Input Module is a component which let you create you own data sheets in the database. These sheets can have numerous columns both standard columns and periodic columns. Check out the Administration Guide to learn how to create a data sheet in the input module.

Creating a data sheet is easy but using the data from this module in other sections of the platform can be kind of tricky if you do not understand completely how the data is stored in the database.
To be able to write a query to use this data you need to use information from the following four tables in the database:

Table name Description
ba_im_category Holds the information about your sheet
ba_im_column Definition of all columns in the module (not th userdata)
ba_im_data Holds the userdata for non-periodic columns
ba_im_perioddata Holds the userdata for periodic co

There is basically two tables that define your sheet and two tables that contains the data your users input into the database.

To be able to query the data you need to join ba_im_data and/or ba_im_perioddata for each column that exist in your sheet by the column "row_id" which identifies values of the same row. You also need to specify which "column_id" that you want to use data from for each joined expression.

Solution

But now there is a much simpler solution!
By downloading the below script and running it in your database it will install three procedures that creates a view and a table valued function for each periodic column in you IM database.

ba_im_createDataViews.zip

The views that will be created are named "dbo.ba_im_dataView_[COLUMN_ID]" and contains the following columns:

Column name Description
DataValue Contains the actual value in the periodic column
StartPeriod UNIXDATE formatted startperiod for the DataValue value
EndPeriod UNIXDATE formatted endperiod for the DataValue value
RowID Unique identifier of the IM row
RefDataValue_[n] One of these columns for each non-periodic column in your sheet

To be able to use this view in your Analyze PRO datatable, simply join it in your manual query and filter out the period and the reference values (RefDataValue_[n] columns).

Quite often when you are working with budgets that should be compared with for example sales data you run into the "empty periods problem".
When joining two tables with periodic data you have to decide in advance which table should be the master table and the one that will control amoungst other things the valid periods.

For example if we have a Sales VS Budget datatable:

  • If the Sales data is the master table, we wont get budget data for periods where there is no sale by just joining the two tables
  • If the Buget data is the master table, we wont get sales data for periods where there is no budgets registered

This is why the above script also creates one table-valued function named "dbo.ba_im_getTimedData_[COLUMN_ID]" which uses the above view.
What this function does is that it creates a "TimeTable" in runtime which is joined into the above view. It also provides beautified periodnames and includes the category column captions.
The function returns one row for each period, RefDataValue which means:

If the value column is monthly given the function will return one row pr month pr RefDataValue.

If there is more than one RefDataValue, it will create a cartesian product of the rows in all these columns and the time dimension.

The columns returned from the function are:

 

 name Description
Year Year of the DataValue value
Quarter Quarter of the DataValue value
Month Month of the DataValue value
StartU UNIXDATE formatted startperiod for the DataValue value
EndU UNIXDATE formatted endperiod for the DataValue value
StartDT DATETIME formatted startperiod for the DataValue value
EndDT DATETIME formatted endperiod for the DataValue value
DataValue Contains the actual value in the periodic column
RefDataValue_[n] Referenced value number n
RefDataCaption_[n] Referenced caption number n

When using the above function, use this function as the master table in your datatable and include other data by subselects or by joining them in

Example with table valued function:

Sheetname: SuperOffice budget pr. associate

This sheet has a budget column with ID = 90001.
Accordingly there vill be a table valued function named dbo.ba_im_getTimedData_90001 in your SuperOffice database.

This sheet has also one non-periodic column named Associate (column ID = 90000) which holds the associate_id of the row.

The columns of the ba_im_getTimedData_90001 are the following:

Column name Description
Year Year of the budget value
Quarter Quarter of the budget value
Month Month of the budget value
StartU UNIXDATE formatted start of budget value
EndU UNIXDATE formatted end of budget value
StartDT DATETIME formatted start of budget value
EndDT DATETIME formatted end of budget value
DataValue Sales budget for the associate
RefDataValue_90000 Associate ID for the current row
RefDataCaption_90000 Associate Name for the current row

In the query use BA Scripting Engine tags to map parameters to them

 

SELECT 

 td.RefDataValue_90000 as AssociateID,

 td.RefDataCaption_90000 as AssociateName,

 isnull(

  (

   SELECT sum(amount) 

   FROM crm7.sale 

   WHERE associate_id = td.RefDataValue_90000 

   AND status = 2 

   AND saledate between td.startDT and td.endDT

  )

 ,0) as SoldAmount, 

 isnull(dataValue,0) as Budget

FROM ba_im_getTimedData_90001(

 '=param("id":"afc72f61-3855-449e-a9f9-625496dc0712", "output":"startperiod", "alloutput":"=sv(date_all_startperiod);");',

 '=param("id":"afc72f61-3855-449e-a9f9-625496dc0712", "output":"endperiod", "alloutput":"=sv(date_all_endperiod);");'

 ) td

As you can see in the above example, the only table in the from statement is the IM table valued function ba_im_getTimedData_90001.

The sales amount is collected through a subselect which references period and associate_id from the IM timedData function

The IM timedData function needs start and endperiod as input to calculate the "timetable" it should return. The =param(); ScriptingEngine function provides this input.
Please read more about the =param(); function here.

Please post your feedback in the commentsection below

Happy coding!

 

Refer the Scripting Engine article for other functions