BI

From JRapid

Jump to: navigation, search

JRapid includes a complete module for Business Intelligence Reporting that runs directly on the transactional database that supports each JRapid application. This feature is extremely useful when working with management user levels that require summarized reports of the information captured through the system.

BI functionality can be quickly included in every application as simply as clicking in the required options through JRapid Designer or by adding the corresponding attribute to an entity definition.

It enables end users to create highly customized business views without the need to rely on specialized report authors or programmers.

Crosstabs

A crosstab groups rows by one property, columns by another, and uses an aggregate function (count, sum etc) of a third property for the table data. The grouping properties are called dimensions. The table data is called a metric. As Businessintelligence.com defines it:

Metrics: When we use the term metric we are referring to a direct numerical measure that represents a piece of business data in the relationship of one or more dimensions.

To make it easier to grasp, here's how a crosstab generated for an Opportunity entity of a CRM system looks like. In this example, an opportunity has an amount property that is being summed for the measure. The rows dimension is grouping by the customer's sales representative and the columns dimension is doing the same by month and year of the closing date property.


Crosstab example


Crosstabs generated by JRapid offer a set outstanding features and close integration to the web application business model.

Dynamic crosstab configuration
End users of a JRapid web application can dynamically configure a crosstab selecting the dimensions to use.
Filtering
Filters can be applied by end users to restrict data corresponding to certain criteria.
Different measures
Measure used in a crosstab can by switched by the end user.
Drill down
By clicking on a metric result end users can obtain a detailed view of how that value is composed and even open any of the related records.


The crosstabs generated for an entity can be linked to the application work-flow by including them as part of a panel or as an extra tab in listings.

The information presented by a crosstab can be easily converted into a configurable graph.

Another way of enabling users to access the crosstabs and graphs is by creating a Dashboard. A combination of a template and a view can be added to any JRapid app to quickly get this feature working. This allows end users to create tabular and graphical views of their key metrics on one screen.

A Crosstab Example

To see how this works I'll walk you through the process of creating a crosstab. We first create a new JRapid project. We'll be using the CRM template offered as example at the splash window to quickly generate a working application with sample data.

NOTE: make sure your project version is at least v1.0nb78 to guarantee the features shown
here work properly. Read how to upgrade your project version here.


This template includes the following key entities:

  • Customer: holds basic information for each customer.
  • SalesRepresentative: model for each of the sales representatives in the company using the CRM.
  • Opportunity: sale opportunity that targets a certain customer, has a status and an amount of money.

Each Customer has a designated sales representative.


File:bi3.jpg


It's very easy to create a crosstab. To do this through the visual aid of the JRapid Designer, we click on the entity in the Entity Diagram.


File:bi4.jpg


We switch to the Advanced tab and select the Crosstab option. We need to select a default row and column dimension as shown in the next image.


File:bi5.jpg


This generates the following AML application definition. Note that all that is needed is to include the crosstab attribute for the entity and the default dimension attribute for the properties.

<entity crosstab="crosstab" defaultlisting="withFilters" label="Opportunity" menu="Operations" name="Opportunity">
   ...
   <property dimension="column" display="secondary" label="Closing Date" name="closingDate" type="date" widget="jdatepicker"/>
   <property dimension="row" default="'NEW'" display="secondary" enumset="Stages" label="State" name="state" required="required" type="enum"/>
   ...
</entity>

If we click on the Opportunity entity in the Entity Tree we get the entity's form in the preview pane. We need to switch to the crosstab preview by clicking on the Crosstab link. It's better if we open the crosstab on a new browser tab by holding down CTRL key and clicking on this link (CMD key if on a Mac).


File:bi6.jpg


The crosstab with its default dimensions looks like this. To change the dimensions we need to click on the Configure button at the top of the crosstab.


File:bi7.jpg

We click on the Dimensions link to bring up the rows and columns configuration. To remove a selected value we click on it. We do the same on the values we wish to add. In this example, I'll change the crosstab to show opportunities by Sales Representative and closing month and year.

File:bi9.jpg

We now have our crosstab looking like this.


File:bi10.jpg


The measure is counting the number of properties. It would be nicer to show the sum of the amounts for each entity. To do this we need to go back to the configuration, select the measures link and in there choose the sum_amount option.


File:bi11.jpg


And now our crosstab has been dynamically configured just as an end user would be able to do.


File:bi12.jpg


If we click on a value a list opens showing which opportunity records are being summed to get that metric. We can also click on one of this lines to open the opportunity form to see all the properties and even edit them.


File:bi13.jpg


Last, I'll show you how we can get a bar graph out of a crosstab. We first need to remove the month dimension so that our information makes more sense and produces a clear chart. We do this by clicking on the remove button of the corresponding dimension.


File:bi16.jpg


We then use the Charts select box to choose the Bar chart type.


File:bi14.jpg


And we get the chart with no effort at all.


See also

Retrieved from "http://wiki.jrapid.com/w/BI"
Personal tools