Create a Report to Compare Volumes
Here’s how you can create a report that shows the comparison of planned volumes and prior year volumes for each category. The report contains only total values for the entire period and monthly data isn’t. You can select multiple accounts and categories, and define the time range of the report.
Required Editions
Available in: Lightning Experience Available in: Enterprise and Unlimited Editions where Consumer Goods Cloud is enabled |
Example
{
"datasources": [
{
"name": "AccountMonthlyMeasures"
}
],
"attributes": {
"productdimension": [
{
"name": "category"
}
],
"timedimension": [
{
"name": "Total"
}
],
"kpidimension": [
{
"name": "main",
"type": "filter",
"measures": [
{
"name": "PYShipment",
"label": "PY Volume"
},
{
"name": "PlanTotalVolumeResult",
"label": "Plan Volume"
},
{
"name": "PlanvsLYVolumeResult",
"label": "Plan Vs PY"
}
]
}
]
},
"filters": [
{
"label": "Period",
"type": "periodmonth",
"name": "periodmonth"
},
{
"type": "multiselect",
"name": "accountsfids",
"label": "Accounts",
"source": "PlanAccounts"
},
{
"type": "multiselect",
"label": "Categories",
"level": "category",
"name": "productsfids",
"source": "Products"
}
],
"uimapping": {
"FlatList": {
"rowdims": [
"productdimension",
"kpidimension"
],
"coldims": [
"timedimension"
],
"layout": {
"colstyles": {
"product": {
"width": "200px"
},
"time": {
"width": "200px"
},
"kpi": {
"width": "200px"
}
},
"rowstyles": {}
}
}
}
}This configuration generates a flatList report that does the following:
- Retrieve data from promotion and account writebacks using AccountMonthlyMeasures datasource.
- Read data at the product level identified by name: category. The productdimension metadata has the name that determines the product level: productdimension = 'category'.
- Aggregate monthly values to total and provide a single value for each KPI/product combination using timedimension = 'Total'.
- Fetch two KPIs that are defined in kpidimension, Planend Volume and PY volume, and calculate the percentage Plan Vs PY.
- Has these filters:
- periodmonth to let you define the time scope of the report by selection of year, start month, and total number of months
- accountsfids as multiple selections to let you select accounts
- productsfids as multiple selections to let you select products of certain level defined in level attribute
- Generate a flatList as configured in UIMapping. The flatList has rowdim and coldim. The rowdim is defined as product and KPI. So the first column is product, and the second column is KPI. All data will be grouped by product, and KPI combination, which means for each category three KPI rows are shown. The coldim is defined as time that has a single data column for Total values.
Example You can modify the report to show category, sub category, and brand totals for each KPI
without changing the filters.
Here are the changes you must make:
- Define product levels (category, sub category, and brand) in product dimension to retrieved data for the lowest product level (brand) and then added to calculate the values for the higher product levels.
- Define a grouping section for flatList.
{
"datasources": [
{
"name": "AccountMonthlyMeasures"
}
],
"attributes": {
"productdimension": [
{
"name": "category"
},
{
"name": "subcategory"
},
{
"name": "brand"
}
],
"timedimension": [
{
"name": "Total"
}
],
"kpidimension": [
{
"name": "main",
"type": "filter",
"measures": [
{
"name": "PYShipment",
"label": "PY Volume"
},
{
"name": "PlanTotalVolumeResult",
"label": "Plan Volume"
},
{
"name": "PlanvsLYVolumeResult",
"label": "Plan Vs PY"
}
]
}
]
},
"filters": [
{
"label": "Period",
"type": "periodmonth",
"name": "periodmonth"
},
{
"type": "multiselect",
"name": "accountsfids",
"label": "Accounts",
"source": "PlanAccounts"
},
{
"type": "multiselect",
"label": "Categories",
"level": "category",
"name": "productsfids",
"source": "Products"
}
],
"uimapping": {
"FlatList": {
"rowdims": [
"productdimension",
"kpidimension"
],
"coldims": [
"timedimension"
],
"grouping": {
"rows": [
[
"subcategory"
],
[
"category"
]
],
"cols": []
},
"layout": {
"colstyles": {
"product": {
"width": "200px"
},
"time": {
"width": "200px"
},
"kpi": {
"width": "200px"
}
},
"rowstyles": {}
}
}
}
}Example You can further modify the report to see quarterly and halfyearly comparison for each value.
These are the configuration changes you must make:
- Introduce additional time dimensions in the order of time hierarchy. For example, Total, HalfYear, and Quarter.
- Define the grouping for the time dimension.
{
"datasources": [
{
"name": "AccountMonthlyMeasures"
}
],
"attributes": {
"productdimension": [
{
"name": "category"
},
{
"name": "subcategory"
},
{
"name": "brand"
}
],
"timedimension": [
{
"name": "Total"
},
{
"name": "HalfYear"
},
{
"name": "Quarter"
}
],
"kpidimension": [
{
"name": "main",
"type": "filter",
"measures": [
{
"name": "PYShipment",
"label": "PY Volume"
},
{
"name": "PlanTotalVolumeResult",
"label": "Plan Volume"
},
{
"name": "PlanvsLYVolumeResult",
"label": "Plan Vs PY"
}
]
}
]
},
"filters": [
{
"label": "Period",
"type": "periodmonth",
"name": "periodmonth"
},
{
"type": "multiselect",
"name": "accountsfids",
"label": "Accounts",
"source": "PlanAccounts"
},
{
"type": "multiselect",
"label": "Categories",
"level": "category",
"name": "productsfids",
"source": "Products"
}
],
"uimapping": {
"FlatList": {
"rowdims": [
"productdimension",
"kpidimension"
],
"coldims": [
"timedimension"
],
"grouping": {
"rows": [
[
"subcategory",
"Total"
],
[
"category",
"Total"
]
],
"cols": [
[
"Total"
],
[
"HalfYear"
],
[
"Quarter"
]
]
},
"layout": {
"colstyles": {
"product": {
"width": "200px"
},
"time": {
"width": "200px"
},
"kpi": {
"width": "200px"
}
},
"rowstyles": {}
}
}
}
}Did this article solve your issue?
Let us know so we can improve!
