/
How to create or customize reports ?

How to create or customize reports ?

Owned by Nicolas Barreau (Unlicensed)
Last updated: May 21, 2019 by Laurent Girard (Unlicensed)


This page describes how to build new reports in Reporting module for Axiodis and/or customize the existing reports.


Data warehouse content

Axiodis data warehouse propose by default following tables and corresponding data which can be used to create reports :

TableDescriptionType
IC_D_DRIVERDimension table about driversDimension
IC_D_LOGISTIC_AREADimension table about logistics areasDimension
IC_D_OPERATIONDimension table about transport operationsDimension
IC_D_PRODUCTDimension table about productsDimension
IC_D_PROVIDERDimension table about providersDimension
IC_D_ROUTEDimension table about routesDimension
IC_D_SEQUENCEDimension table about sequencesDimension
IC_D_SITEDimension table about sitesDimension
IC_D_TRAILERDimension table about trailersDimension
IC_D_TRUCKDimension table about trucksDimension
IC_M_OPERATIONMeasure table about transport operationsMeasure
IC_M_ROUTEMeasure table about routesMeasure
IC_M_SEQUENCEMeasure table about sequencesMeasure
IC_D_ROUTE_INVOLVED_PROVIDERS Dimension table to store all the involved providers fo the routesDimension
IC_D_ROUTE_VALORIZED_PROVIDERSDimension table to store all the valorized providers of the routesDimension


For further information and more details about Axiodis data warehouse content, contact Maplink.


Existing dataset and their parameters

DatasetDescriptionQueryParameters

DS_AXD_DRIVERS_ACTIVITY

Dataset about drivers activity

DS_AXD_ROUTESDataset to recover generic data about routes
SELECT dr.*, mr.*, 
mr.#SITES_COLLECTED+mr.#SITES_DELIVERED as #SITES, 
mr.#SITES_COLLECTED_PREDICTED+mr.#SITES_DELIVERED_PREDICTED as #SITES_PREDICTED,
((mr.#SITES_COLLECTED+mr.#SITES_DELIVERED) - (mr.#SITES_COLLECTED_PREDICTED+mr.#SITES_DELIVERED_PREDICTED)) as #NOMINAL_SITES,
mr.UV_COLLECTED+mr.UV_DELIVERED as UV, 
mr.UV_COLLECTED_PREDICTED+mr.UV_DELIVERED_PREDICTED as UV_PREDICTED,
((mr.UV_COLLECTED+mr.UV_DELIVERED) - (mr.UV_COLLECTED_PREDICTED+mr.UV_DELIVERED_PREDICTED)) as NOMINAL_UV, 
mr.UP_COLLECTED+mr.UP_DELIVERED as UP, 
mr.UP_COLLECTED_PREDICTED+mr.UP_DELIVERED_PREDICTED as UP_PREDICTED,
((mr.UP_COLLECTED+mr.UP_DELIVERED) - (mr.UP_COLLECTED_PREDICTED+mr.UP_DELIVERED_PREDICTED)) as NOMINAL_UP,
mr.UQ_COLLECTED+mr.UQ_DELIVERED as UQ, 
mr.UQ_COLLECTED_PREDICTED+mr.UQ_DELIVERED_PREDICTED as UQ_PREDICTED,
((mr.UQ_COLLECTED+mr.UQ_DELIVERED) - (mr.UQ_COLLECTED_PREDICTED+mr.UQ_DELIVERED_PREDICTED)) as NOMINAL_UQ,
mr.UM_COLLECTED+mr.UM_DELIVERED as UM, 
mr.UM_COLLECTED_PREDICTED+mr.UM_DELIVERED_PREDICTED as UM_PREDICTED,
((mr.UM_COLLECTED+mr.UM_DELIVERED) - (mr.UM_COLLECTED_PREDICTED+mr.UM_DELIVERED_PREDICTED)) as NOMINAL_UM
FROM IC_M_ROUTE mr
INNER JOIN IC_D_ROUTE dr ON mr.ID_ROUTE = dr.ID
WHERE dr.DOMAIN_CODE in ($P{logistic_area()})
AND dr.ROUTE_DATE >= CONVERT(DateTime, $P{start_date})
AND dr.ROUTE_DATE <= CONVERT(DateTime, $P{end_date})
order by dr.DOMAIN_CODE, dr.CODE

DS_AXD_ROUTES_VARDataset to recover generic data about routes
with AXD_ROUTES_VAR as (
SELECT dr.ID, dr.CODE, dr.EXTERNAL_ID, dr.ROUTE_DATE, dr.DOMAIN_CODE,
DATEPART(WW, dr.ROUTE_DATE) as ROUTE_WEEK, DATEPART(MM, dr.ROUTE_DATE) as ROUTE_MONTH, DATEPART(YY, dr.ROUTE_DATE) as ROUTE_YEAR, 
mr.TOTAL_DISTANCE_PREDICTED, mr.TOTAL_DISTANCE,
mr.TOTAL_TIME_PREDICTED, mr.TOTAL_TIME,
mr.TRANSPORTATION_COST_PREDICTED, mr.TRANSPORTATION_COST,
mr.#SITES_COLLECTED+mr.#SITES_DELIVERED as #SITES, 
mr.#SITES_COLLECTED_PREDICTED+mr.#SITES_DELIVERED_PREDICTED as #SITES_PREDICTED,
mr.UV_COLLECTED+mr.UV_DELIVERED as UV, 
mr.UV_COLLECTED_PREDICTED+mr.UV_DELIVERED_PREDICTED as UV_PREDICTED,
mr.UP_COLLECTED+mr.UP_DELIVERED as UP, 
mr.UP_COLLECTED_PREDICTED+mr.UP_DELIVERED_PREDICTED as UP_PREDICTED
FROM IC_M_ROUTE mr
INNER JOIN IC_D_ROUTE dr ON mr.ID_ROUTE = dr.ID
WHERE dr.DOMAIN_CODE in ($P{logistic_area()})
AND dr.ROUTE_DATE >= CONVERT(DateTime, $P{start_date})
AND dr.ROUTE_DATE <= CONVERT(DateTime, $P{end_date})
)
select AXD_ROUTES_VAR.*, cast(AXD_ROUTES_VAR.ROUTE_YEAR as nvarchar) as X_VALUE
from AXD_ROUTES_VAR
where 0 = $P{axis_type}
UNION ALL
select AXD_ROUTES_VAR.*, cast(AXD_ROUTES_VAR.ROUTE_MONTH as nvarchar) as X_VALUE
from AXD_ROUTES_VAR
where 1 = $P{axis_type}
UNION ALL
select AXD_ROUTES_VAR.*, cast(AXD_ROUTES_VAR.ROUTE_WEEK as nvarchar) as X_VALUE
from AXD_ROUTES_VAR
where 2 = $P{axis_type}
UNION ALL
select AXD_ROUTES_VAR.*, cast(AXD_ROUTES_VAR.ROUTE_DATE as nvarchar) as X_VALUE
from AXD_ROUTES_VAR
where 3 = $P{axis_type}

DS_AXD_TRUCKS_ACTIVITYDataset about trucks activity
SELECT dr.DRIVER_CODE, dd.LABEL, 
mr.TOTAL_TIME_PREDICTED, mr.TOTAL_TIME, mr.NOMINAL_DIFF_TOTAL_TIME, mr.RELATIVE_DIFF_TOTAL_TIME, 
mr.TOTAL_DISTANCE_PREDICTED, mr.TOTAL_DISTANCE, mr.NOMINAL_DIFF_TOTAL_DISTANCE, mr.RELATIVE_DIFF_TOTAL_DISTANCE 
FROM IC_M_ROUTE mr
INNER JOIN IC_D_ROUTE dr ON mr.ID_ROUTE = dr.ID
INNER JOIN IC_D_DRIVER dd ON dr.DRIVER_CODE = dd.ID
WHERE dr.DOMAIN_CODE in ($P{logistic_area()})
AND dr.ROUTE_DATE >= CONVERT(DateTime, $P{start_date})
AND dr.ROUTE_DATE <= CONVERT(DateTime, $P{end_date})
order by dr.DRIVER_CODE


How to create a report using existing dataset?

This section shows how to create a new report, using the existing datasets. 

  • First, you have to log into Axiodis with an authorized user (Authorization Reporting - Functionality by default - Creation within the authorization profile used).
  • Then launch the Reporting module 

 

  • Open Documents development

  • Create a new report (cockpit)

  • Add a widget and select a dataset

  • Drag & Drop the wished widget, and set the widget designer

  • Choose the fields in the list (measures and attributes from the dataset)

  • Save the report

  • Edit the report to add the definition of the parameters

  • Create the document analytical driver


  • Open and edit again the report, then add the parameters

  • Confirm and save the report.
  • You can now define parameters (Start date, End date, and logistic areas), and click on Execute


How to customize an existing report

All the existing reports (delivered by Maplink or developed by yourself) are customizable. You can edit them, add a widget, edit and/or delete the widget. You also can duplicate them.


How to create a new dataset

(warning) Be careful to define all the fields metadata, ATTRIBUTE or MEASURE, according to what you want to do with this new dataset.


How to update the datawarehouse database and MIA streams if necessary

In some cases, maybe you'll need to have more information into the current data warehouse. In that case, you'll need to follow the two steps:

  • Upgrade the datawarehouse SQL database, in order to create new tables and/or new attributes on existing tables
  • Then upgrade the MIA streams, or create new ones, in order to populate the data warehouse




www.maplink.global