ESPocketBiz – Reporting Cubes

Entersoft Business Suite^® | Entersoft CRM^® | Entersoft Mobile Suite^®

Τεχνικό εγχειρίδιο

Ταυτότητα

Έκδοση κειμένου 2.0

Τελ. ενημέρωση Ιούνιος 2021

Copyright

Δεν επιτρέπεται η αναδημοσίευση ή μετάδοση με οποιαδήποτε μορφή και για οποιοδήποτε λόγο μέρους ή όλου του παρόντος εγχειριδίου, χωρίς τη ρητή άδεια της Entersoft Α.Ε.

Όσον αφορά το περιεχόμενο του παρόντος…

Μπορεί να μεταβληθεί οποιαδήποτε στιγμή.
Εξυπηρετεί αποκλειστικά πληροφοριακούς σκοπούς.
Δεν παρέχεται εγγύηση για την ύπαρξη τυχόν λαθών ή για τη λανθασμένη χρήση ή για μη επιθυμητά αποτελέσματα από τη χρήση των προτεινόμενων στο παρόν διαδικασιών.

= General information

Analysis cubes, expand the reporting capabilities of PocketBiz and allowing dynamic data analysis. A cube is a multidimensional structure defined by its measures (facts) and dimensions. You can think of a measure as a data field, while a dimension can be represented by a column field or a row field.

The cube format is as follows:

The cube offers great flexibility to end users because they can easily change the dimensions shown on the horizontal and vertical axis and define filters to the data displayed. So, the elements of the cube are the dimensions and facts.

Dimensions

Dimensions are the characteristics of the data that we see, for example, sales dimension are the customer, date, vendor, etc.

Facts (measures)

Fact is the measured data, eg the value, the quantity, that data can be aggregated.

How to view a cube

To access a reporting cube, from the left PocketBiz menu, choose Reports / Reports. When the Reports tab opens, press double click on the preferred cube report.

A sample list of available reporting cubes. Cubes have the icon, left of their description.

Parameters (filters data)

Before we start the analysis for this report (cube) we have to check the parameter set that are required for this report.

The previous screenshot illustrates the set of parameters that the user have to fill in order to run successfully the report. All Bolded parameters are required.

Pressing the in the Country field, a list of available countries are shown and given for option. The same happens with every parameter has the magnifier icon.

We can check all the Countries we want and then by pressing “Select” button we will populate the next parameters by the same way.

With the button we can delete the specific row value of the parameter.

When we select to populate MSP or Customer field parameters, (due to the large number of records) a screen like the following pops up.

The difference is that the list of data is primarily empty. We have to filter the results by populating the available filters above the list and then press the “Search” button. Of course we can press the Search button without filtering.

Then, we can check the preferred records or all the records in the list by checking the box in the header line.

Notice, that the filtering is cumulative. This means that if we choose Country:GREECE, when we click in MSP, we will see only MSP’s for Greece. And if we choose MSP:Frigoglass, when we click in Customer, we will see only GREECE:FRIGOGLASS: customers.

Whats more, there are two (type of date) drop down list: From – To Created Date. In parameters From/To Date the user must set a date period within he/she wants to look the exported results.

In the optional parameter which name is Call type, the user have to define the call types that he\she wants to see in the exported results.

The Clear button, clears all the fields in the parameters form.

Parameters Templates

New template

For time saving, if we have to give the same parameters regularly, we could save them in a template by pressing the New template button

We have to give a name for this template and answering OK to the next question, if we want this template to be available to all users.

Choosing templates

We can choose to load a parameter template (specific saved values) from the drop down list named Templates.

After a while all fields will be populated with saved values as shown below:

Delete or Edit templates

By clicking the arrow, right of the “New template” button a drop down list with 3 option appears: Edit (Please don’t use – for administrative purposes),

Delete (delete the current template, its name appears left),

Update (saves the current changes to the current template)

OTRR cube Parameters

The OTRR (On Time Response Rates) Report, shows a calculated percent rate (OTRR fact)

firstly aggregated per Customer, Asset Categoty, Asset SubCategory and Call Type

The formula varies by Country.

Note: you can see the full actual Generic expression at the end of the manual.

These entities should be included in the criteria fields (parameters):

Country: Countries (Greece, Romania etc)

MSP: Maintenace Service Providers (Frigoglass, PEK Russia etc)

Customer: Customers (CCHBC ROM, Frigoglass Servia etc)

From Created Dated: Call Creation Date (from)

To Created Dated: Call Creation Date (to)

Region: Regions L1 (16384 etc) - optional

Call Type: Call types (121 Placement, Audit etc) - optional

Operations Excluded: Operations list excluded from the results

(01.03 wrong call etc)

Damages Excluded: Damages list excluded from the results (01.10 carbonate problem etc)

Reasons Excluded: Reasons list excluded from the results (Asset not present etc)

Finaly, we press the OK button and the following screen appears:

There is no data yet in the grid, cause the next step is the data layout creation.

Ribbon toolbar

In this section we will explain the four areas of the upper “ribbon toolbar”.

Options

Print: Opens a customizable print preview for printing, saving, emailing the data with current data layout.

Refresh: Refresh the data of the cube from the server. Please note, that Data are updated until the previous day.

Show editor: by clicking “Show Editor”, the PivotGrid Field List appears as the right screenshot:

For more information, see chapter Data Layout

Views

User can create and use multiple custom views. A view is a saved snapshot of specific display setting of the Data Layout.

Views: allows to choose a specific saved view

New field: allows to create a new field with optional naming:

After its creation, it appears in filter area.

By right click on it, we can open the Expression Editor.. to customize it, as described in the Expression Editor chapter.

After the window of PivotGrid Field List opens, we drag and drop this field into Data Area.

Customize: customize the cube for example the appearance of data (%)

Save view: save changes of current view

New view: save changes of data layout to a new custom named view

Delete view: deletes current view

Export

Exports data with current layout to various file formats like Excel, Web archive, PDF, HTML, CSV, Image, RTF, XLSX

Layout

Collapse all: Hides all detail rows, displays only a row with total

Expand all: Opposite of collapse all, displays all detail rows

Best Fit: provides the ability to automatically calculate and apply the optimal width required for a column to display its entire content by changing the columns width to biggest data value, without blanks.

Data Layout

As we can see the empty data grid has the name “Initial” in the Views field. Initial View means that it has no data layout set.

Above “drop” prompts in the Filter Area, there are the available fields for droping.. like MSP, Asset Category etc.

By the way, if we put the cursor over a field (eg Country) a filter funnel appears. With click on it, a drop down list shows the values we choosed prior in the Parameters (eg only Greece value choosen). So this is an “extra RUNTIME” filtering, maybe not so usefull. You can also invert filtering with an icon above.

Data Layout Configuration

To configure the Data Layout there are two ways:

the editor, by clicking the Show Editor button and manually with drag & drop the fields to data areas.

Show Editor

When user clicks the Show Editor button, a screen like Excel Pivot table as below appears:

In the upper window there are all available measurement fields we could drag and drop in the lower Layout section.

To drag a field to the area that you want, click and hold the field name in the field section, and then drag it to an area in the layout section.

Use the areas section of the PivotGrid Field List to rearrange fields the way you want by dragging them between the four areas.

You can also rearrange their order in the same area, by dragging them to the position you want. To delete a field from the PivotGrid, drag the field out of its areas section.

The layout section contains the Report Filter area, the Column Area, the Row Area, and the Data Area.

Filter Area: The report filter is used to apply a filter to the entire DataGrid. The filter was created already in Parameters (previous step)

Column Area: The Column Area works just like the Row Area. It lists the unique values of a field in the PivotGrid. The only difference, is that it lists the values across the top Row. Every field that exists in this area will displays its data as column.

Row Area: The Rows Area of the PivotGrid is where we typically start when building the outline of our report. When we drag a field into the Rows area, all the unique values in that field will be displayed in the first column. The PivotGrid removes all the duplicates in the field and only displays the unique values.

Data Area: The Data Area displays the data (values) that we want to summarize in our cube report. When we drag a field into the Data Area, the PivotGrid will automatically sum or count the data in that field. If the data in the field contains numbers, then the sum will be calculated. If the data contains text or blanks, then the count will be calculated.

The calculation type can be changed later to other functions like Average, Max, Min, etc. (click on the desired field and a list of available functions appears – with a tick selection in front of current function)

Defer Layout Updates (Performance tip): When you need to remove/add multiple elements in a report, you can choose not to update the grid after each change, but only after you have completed all changes. This can be done with checking this option before starting and then clicking on the Update button after you are done with your changes.

When this checkbox is checked, the “Update” button next to it becomes enabled, and now you can add, remove and move fields around without the Pivot updating itself. When you are done arranging fields, you press the Update button, and the grid does one and only one update.

The following screen using colored boxes, shows where each area of the PivotGrid is located on the report. This can be used as a guide to familiarize yourself with the different areas.

Next, there are two views demonstrate a difference in the configuration layout.

In the view:testview, the field “Asset Category” is in the Column Area instead of the view:OTRR Generic where it is in the Row Area.

Notice, that there is also a Total Row for every field in the Row Area and a Grand Total Row at the end.

A simple drag & drop by the user, moves the selected field to different areas inside the PivotGrid Field List or outside – in the main interface.

So we can just drag & drop a field without using the PFL into an empty area (with messages like “Drop data items here”, “Drop Row fields here”, “Drop Column fields here”, “Drop Data Items”) or near to an existing field.

We can click on the buttons to Expand / Collapse the specific data group, or the icon to Sort tha data ascending /descending on the specific column.

There is also an arrow button in the upper left corner . It collapse or expand the Ribbon toolbar.

Expression editor

With right-click on a calculated field and select «Expression Editor» or with click to “new field” button and choose «Expression Editor», we can write any expression we want for this field.

Lets suppose that we have two facts, Actual Amount and Budgeted and we want to create a percent column (what percentage of the budgeted amount has been reached).

We wrote the following:

In the upper area we build the expression by clicking on Functions, Operators, Fields, Constants from the lower areas, or just typing…

Field names must be in brackets [] .

Customize cube

To properly display the percentage in the groups, must be set in the properties of the specific field of the cube (customize button), the option "UseSummaryValues":

Also, after you enable the "UseSummaryValues" should write the fields using their "technical" name. This is shown at the top of the screen:

So you should write: Cube Element 8 / Cube Element 7.

Then you have to format the column as a percentage:

If you want to set a measurable as a percentage of the total, then you should use the option SummaryDisplayType at the properties of the measurable:

Alternatively, use the default option.

OTRR Generic field expression

Below as example, is the actual [OTRR Generic] field expression ((x1+x2..)/iif((x1+y1+x2+y2..)=0,1,( x1+y1..+x2+y2..)) where x=in time, y=delayed or

(ClosedInTime + Canceled InTimewithvisit / all calls created in the same period without the canceled withoutvisit) :

([In Time without Visit Operations Excluded]+[In Time with Visit Operations Excluded]+[In Time without Visit Damages Excluded]+[In Time with Visit Damages Excluded]+[In Time without Visit Reasons Excluded]+[In Time with Visit Reasons Excluded]+ [Cancelled In Time With Visit]+[In Time with Visit Regular Calls]) /Iif(([In Time without Visit Operations Excluded]+[Delayed without Visit Operations Excluded]+[In Time with Visit Operations Excluded]+[Delayed with Visit Operations Excluded]+[In Time without Visit Damages Excluded]+[Delayed without Visit Damages Excluded]+[In Time with Visit Damages Excluded] +[Delayed with Visit Damages Excluded]+[In Time without Visit Reasons Excluded]+[Delayed without Visit Reasons Excluded]+[In Time with Visit Reasons Excluded]+[Delayed with Visit Reasons Excluded] +[Cancelled In Time With Visit] +[Delayed without Visit Regular Calls] +[In Time with Visit Regular Calls] +[Delayed with Visit Regular Calls])=0,1, ([In Time without Visit Operations Excluded]+[Delayed without Visit Operations Excluded]+[In Time with Visit Operations Excluded]+[Delayed with Visit Operations Excluded]+[In Time without Visit Damages Excluded]+[Delayed without Visit Damages Excluded]+[In Time with Visit Damages Excluded] +[Delayed with Visit Damages Excluded]+[In Time without Visit Reasons Excluded]+[Delayed without Visit Reasons Excluded]+[In Time with Visit Reasons Excluded]+[Delayed with Visit Reasons Excluded] +[Cancelled In Time With Visit] +[Delayed without Visit Regular Calls] +[In Time with Visit Regular Calls] +[Delayed with Visit Regular Calls]) )

PDF Version

Ανώνυμος

Αναζήτηση

Pocketbiz Desktop Reporting Cubes General Manual

Ονοματοχώροι

Περισσότερα