FIRST CREATED ON 12 Aug 2025 I AUTHOR Emma Camacho |
Creating
an Excel 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 the 3 Top Stores Analysis Report.
Mail & Deploy Report
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.
ELEMENTS
NetSales
EvaluateDatasourceExpression( [ReportParameter].[Datasource],
"SELECT s.[Net Sales] FROM " &
[ReportParameter].[Model] & ".Measures As s " )
Units Sold
EvaluateDatasourceExpression( [ReportParameter].[Datasource], "SELECT s.[Units Sold] FROM " &
[ReportParameter].[Model] & ".Measures As s " )
Category Breakdown
Source: SQL Query
SQL Query Datasource: M&D Demo_Sales & Returns Sample v201912
SQL Query: "SELECT p.[Product], s.[Net Sales], s.[Units Sold] FROM " &
[ReportParameter].[Model] & ".Product As p " &
"INNER JOIN " & [ReportParameter].[Model] & ".Measures As s " &
"group by p.[Product] order by s.[Net Sales] desc "
Insertion Mode: Write To Worksheet
Row Insertion Mode: Overwrite
Store_rep
EvaluateSqlDatasourceExpression(
[ReportParameter].[Datasource], "SELECT store.[Store] FROM " &
[ReportParameter].[Model] & ".Store As store " &
"INNER JOIN " & [ReportParameter].[Model] & ".Measures As s " &
"group by store.[Store] order by s.[Net Sales] desc LIMIT 3 " , 2 )
Store
[RepeatedValue].[Store]
Net Sales_perStore
EvaluateDatasourceExpression( [ReportParameter].[Datasource], "SELECT s.[Net Sales] FROM " &
[ReportParameter].[Model] & ".Store As store " &
"INNER JOIN " & [ReportParameter].[Model] & ".Measures As s " &
"where store.Store = '" & [RepeatedValue].[Store] &"'" )
Units Sold_perStore
EvaluateDatasourceExpression( [ReportParameter].[Datasource], "SELECT s.[Units Sold] FROM " &
[ReportParameter].[Model] & ".Store As store " &
"INNER JOIN "&[ReportParameter].[Model]&".Measures As s " &
"where store.Store = '" & [RepeatedValue].[Store]&"'" )
CategoryBreakdown_perStore
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 = '" &[RepeatedValue].[Store]& "' group by p.[Product] order by s.[Net Sales] desc "
Insertion Mode: Fill MSO Chart