This template is designed to work as a reusable starting point for Sales Pipeline reporting across Busopp, Order and Invoice, using consistent datamart views and a parameter-driven connection pattern.
The template can open and run “out of the box” if default values are provided (or if you enter the parameters when prompted).
What you get in the template
The model uses the standard Sales Pipeline view pattern:
core_details(anchor / header)meta_codes(context and slicers)meta_dates(lifecycle timing)item_values_lines/item_values(numeric values)- optional:
meta_values(aggregated values derived from item_values)
It also follows the long-thin pattern (META_TYPE + ATTRIBUTE) and creates combined fields like CODE_ATTRIBUTE to support shaping/pivoting.
First-time setup steps
- Open the PBIT
- When prompted, enter parameter values (or accept the defaults, if provided by the template logic)
- Click Apply
- Refresh the model
- Confirm the report shows data
If you see an authentication error, it’s almost always the token / permissions, not the report.
Parameters and suggested default values
These are the parameters visible in your model. The “Suggested default” column is what makes the template run straight away for a typical demo/dev setup.
Datamart connection parameters
| Parameter | What it controls | Suggested default value | Notes |
|---|---|---|---|
DataMart_Host | SQL host name | bmt-dwh-uks-syn-dev-ondemand.sql.azuresynapse.net | Environment-specific (dev/test/prod) |
DataMart_Endpoint | Database / endpoint | dwh_silver | Likely stable per environment |
DataMart_Token | Access token passed into get.myView | <your report token> | Use a placeholder (e.g. DEMO_SALES REPORT) |
Domain / view selection parameters
| Parameter | What it controls | Suggested default value | Notes |
|---|---|---|---|
param_DataMart | Which domain you are loading | busopp | Pick one domain as the “starter” |
param_ValueType | Version of values view (used as @version for item_values) | beta | Align with your published view versions |
Optional “view mart” parameters (used for dynamic selection)
| Parameter | What it controls | Suggested default value | Notes |
|---|---|---|---|
param_Date | The Date value displayed on the chart x-axis | PROPOSAL_ESTIMATED_SIGN | Works well for Busopp examples |
param_DisplayValue | The chart y-axis | TOTAL_VALUE_BASE | Safe and intuitive starter metric |
param_Slicer1 | The first filter by value (category) | PROGRAMME_DELIVERY | Remember these are the META_TYPE + ATTRIBUTE pattern |
param_Slicer2 | The second filter by value (category) | LINK_KEY_CUSTOMER_ID | Remember these are the META_TYPE + ATTRIBUTE pattern |
If you want the template to “just work” for Orders by default, you’d swap those to an order-appropriate date/value attribute later.
Switching domains (Busopp → Order → Invoice)
To switch the template from one domain to another:
- Change
param_DataMarttoorderorinvoice - Refresh
- Update (optional) defaults:
param_Dateparam_DisplayValueparam_ValueType(only if you use different versions)
Because the queries are modular, you shouldn’t need to rewrite transformations — just change the settings.
Common problems and fixes
- Prompted for parameters every time
That’s normal for PBIT. Use the “fallback defaults” pattern in Power Query if you want it to run without prompts. - Refresh fails / permission denied
Usually token or data access. Confirm the token is valid and the user has rights. - Numbers look duplicated
Check grain: you may be using long-thin meta tables directly without pivoting or without anchoring joins fromcore_details. - Currency looks wrong
Ensure you’re not aggregating across_BASEand_CURRattributes.