This page describes how to build new reports in Reporting module for Axiodis and / or customize existing report.
Datawarehouse content
Axiodis datawarehouse propose by default following tables and corresponding data which can be used to create reports :
| Table | Description | Type |
|---|
| IC_D_DRIVER | Dimension table about drivers | Dimension |
| IC_D_LOGISTIC_AREA | Dimension table about logistics areas | Dimension |
| IC_D_OPERATION | Dimension table about transport operations | Dimension |
| IC_D_PRODUCT | Dimension table about products | Dimension |
| IC_D_PROVIDER | Dimension table about providers | Dimension |
| IC_D_ROUTE | Dimension table about routes | Dimension |
| IC_D_SEQUENCE | Dimension table about sequences | Dimension |
| IC_D_SITE | Dimension table about sites | Dimension |
| IC_D_TRAILER | Dimension table about trailers | Dimension |
| IC_D_TRUCK | Dimension table about trucks | Dimension |
| IC_M_OPERATION | Measure table about transport operations | Measure |
| IC_M_ROUTE | Measure table about routes | Measure |
| IC_M_SEQUENCE | Measure table about sequences | Measure |
| IC_D_ROUTE_INVOLVED_PROVIDERS | Dimension table to store all the involved providers fo the routes | Dimension |
| IC_D_ROUTE_VALORIZED_PROVIDERS | Dimension table to store all the valorized providers of the routes | Dimension |
Existing dataset and their parameters
| Dataset | Description | Query | Parameters |
|---|
| Dataset about drivers activity |
| |
| DS_AXD_ROUTES | Dataset 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_VAR | Dataset 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_ACTIVITY | Dataset 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 SpagoBI 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)
- Edit the report to add the parameters definition
- 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 create a new dataset
How to update datawarehouse database and MIA streams if necessary
www.maplink.global
