FIRST CREATED ON 11 Aug 2025 I AUTHOR Emma Camacho |
Creating
an Word report using a PowerBI as a data source in Mail & Deploy
allows you to leverage powerful data analysis capabilities. Here's an
example to help you get started:
Use case
Design a Store Analysis Report for a specific Store.
Mail & Deploy Report
The output Report looks like this:
How to get there?
PowerBI Server Database datasource
How to configure a PowerBI Datasource in version 3.6 and higher?
Datasource definition
Report Template
The template is attached to this article.
PARAMETERS
Datasource
"M&D Demo_Sales & Returns Sample v201912"
This is the name of the Datasource used in the M&D Datasource definition and it is used to ease the SQL queries writing.
Model
"[M&D Demo_Sales & Returns Sample v201912].[Model]"
In order to ease the SQL queries writing, this parameter can be defined.
Store
"Contoso"
This is the parameter that defines the Store to be filtered to.
ELEMENTS
NetSales
format( EvaluateDatasourceExpression( [ReportParameter].[Datasource], "SELECT s.[Net Sales] FROM " &
[ReportParameter].[Model]& ".Store As store " &
"INNER JOIN "&[ReportParameter].[Model]&".Measures As s " &
"where store.Store = '" & [ReportParameter].[Store] &"'" ), "$ ##,##0" )
Units Sold
format( EvaluateDatasourceExpression( [ReportParameter].[Datasource], "SELECT s.[Units Sold] FROM " &
[ReportParameter].[Model]&".Store As store " &
"INNER JOIN "&[ReportParameter].[Model]&".Measures As s " &
"where store.Store = '" & [ReportParameter].[Store] &"'" ), "##,##0")
Category Breakdown
Source: SQL Query
SQL Query Datasource: M&D Demo_Sales & Returns Sample v201912
SQL Query: "SELECT p.[Product], s.[Net Sales] FROM " &
[ReportParameter].[Model]& ".Product As p " &
"INNER JOIN "&[ReportParameter].[Model]&".Store As store " &
"INNER JOIN "&[ReportParameter].[Model]&".Measures As s " &
"where store.Store = '" & [ReportParameter].[Store] & "' group by p.[Product] order by s.[Net Sales] "
Insertion Mode: Fill MSO Chart
Returns
format( EvaluateDatasourceExpression( [ReportParameter].[Datasource], "SELECT s.[Returns] FROM " &
[ReportParameter].[Model]&".Store As store " &
"INNER JOIN "&[ReportParameter].[Model]&".Measures As s " &
"where store.Store = '" & [ReportParameter].[Store] &"'" ), "$ ##,##0" )
Units Returned
format( EvaluateDatasourceExpression( [ReportParameter].[Datasource], "SELECT s.[Units Returned] FROM " &
[ReportParameter].[Model]&".Store As store " &
"INNER JOIN "&[ReportParameter].[Model]&".Measures As s " &
"where store.Store = '" & [ReportParameter].[Store] &"'" ), "##,##0")
Return Breakdown
Source: SQL Query
SQL Query Datasource: M&D Demo_Sales & Returns Sample v201912
SQL Query: "SELECT p.[Product], s.[Returns] FROM " &
[ReportParameter].[Model]&".Product As p " &
"INNER JOIN "&[ReportParameter].[Model]&".Store As store " &
"INNER JOIN "&[ReportParameter].[Model]&".Measures As s " &
"where store.Store = '" & [ReportParameter].[Store] & "' group by p.[Product] order by s.[Returns] "
Insertion Mode: Fill MSO Chart
Today
Date.Now.Tostring("dd.MM.yyyy")