Paginated Reports: Development Techniques

Introduction

Paginated reports (RDL format) are the go-to solution for operational reporting scenarios requiring pixel-perfect layouts, multi-page documents, printable formats, and precise control over data presentation. Unlike Power BI's interactive reports optimized for exploratory analytics, paginated reports excel at invoices, regulatory documents, detailed transaction listings, financial statements, and any scenario where exact layout control is paramount.

This comprehensive guide covers advanced paginated report development from RDL fundamentals through complex layout techniques, parameter strategies, dataset optimization, subscription automation, embedding in applications, performance tuning, and enterprise governance. Whether migrating from SQL Server Reporting Services (SSRS) or building new paginated reports in Power BI Premium, you'll learn proven techniques for production-grade implementations.

Prerequisites

Power BI Report Builder (free download from Microsoft)
Power BI Premium workspace or Report Server (for publishing)
SQL Server or Azure SQL Database (for datasets)
Understanding of SQL queries and T-SQL
Familiarity with basic report design concepts
(Optional) Visual Studio with SSDT for advanced scenarios

When to Use Paginated Reports

Paginated vs Interactive Reports

Scenario	Paginated Reports	Interactive Reports
Multi-page printable documents	✅ Designed for printing	❌ Optimized for screen
Pixel-perfect layouts	✅ Exact positioning	❌ Responsive design
Detailed transaction listings	✅ Tables with 1000s of rows	⚠️ Performance issues
Invoices, statements, forms	✅ Industry standard	❌ Not designed for this
Regulatory/compliance documents	✅ Precise formatting	❌ Layout may shift
Export to PDF/Excel	✅ Primary use case	⚠️ Limited fidelity
Interactive filters and drill	❌ Limited interactivity	✅ Designed for exploration
Real-time dashboards	❌ Scheduled generation	✅ Live refresh
Mobile optimization	❌ Fixed layout	✅ Responsive
Email subscriptions	✅ Native support	⚠️ Via Power Automate

Use Paginated Reports for:

Operational reporting (invoices, purchase orders, packing slips)
Regulatory and compliance documents (financial statements, audit reports)
Detailed transaction listings (all orders for a customer)
Multi-page documents with headers/footers
Precise control over page breaks and layout
Batch printing scenarios
Email distribution of formatted documents

Core RDL Components

Report Definition Language (RDL) Structure

<!-- Simplified RDL structure -->
<Report>
  <DataSources>
    <DataSource Name="SQLServer">
      <ConnectionString>...</ConnectionString>
    </DataSource>
  </DataSources>
  
  <DataSets>
    <DataSet Name="SalesOrders">
      <Query>
        <CommandText>SELECT * FROM Orders WHERE OrderDate >= @StartDate</CommandText>
        <QueryParameters>
          <QueryParameter Name="@StartDate">
            <Value>=Parameters!StartDate.Value</Value>
          </QueryParameter>
        </QueryParameters>
      </Query>
    </DataSet>
  </DataSets>
  
  <ReportParameters>
    <ReportParameter Name="StartDate">
      <DataType>DateTime</DataType>
      <DefaultValue>
        <Values>
          <Value>=DateAdd("m", -1, Today())</Value>
        </Values>
      </DefaultValue>
    </ReportParameter>
  </ReportParameters>
  
  <Body>
    <ReportItems>
      <Tablix Name="OrderTable">
        <!-- Table definition -->
      </Tablix>
    </ReportItems>
  </Body>
  
  <PageHeader>
    <ReportItems>
      <Textbox Name="CompanyLogo">
        <Value>Company Name</Value>
      </Textbox>
    </ReportItems>
  </PageHeader>
  
  <PageFooter>
    <ReportItems>
      <Textbox Name="PageNumber">
        <Value>=Globals!PageNumber &amp; " of " &amp; Globals!TotalPages</Value>
      </Textbox>
    </ReportItems>
  </PageFooter>
</Report>

Essential Report Items

Report Item Hierarchy:

Report
├── Page Header (appears on every page)
├── Body (main content area)
│   ├── Textbox (labels, titles, expressions)
│   ├── Tablix (tables, matrices, lists)
│   │   ├── Row Groups (grouping data)
│   │   ├── Column Groups (cross-tab scenarios)
│   │   └── Detail Rows (individual records)
│   ├── Chart (visualizations)
│   ├── Subreport (nested reports)
│   ├── Rectangle (container for layout)
│   ├── Image (logos, photos)
│   └── Line (visual separator)
└── Page Footer (page numbers, dates)

Advanced Parameter Design

Cascading Parameters

-- Parameter 1: Country (independent)
SELECT DISTINCT Country
FROM Customers
ORDER BY Country

-- Parameter 2: Region (depends on Country)
SELECT DISTINCT Region
FROM Customers
WHERE Country = @Country
  OR @Country IS NULL  -- Allow "All" option
ORDER BY Region

-- Parameter 3: City (depends on Region)
SELECT DISTINCT City
FROM Customers
WHERE Region = @Region
  OR @Region IS NULL
ORDER BY City

Report Builder Configuration:

Country Parameter:
- Dataset: dsCountry
- Available Values: From query
- Default Value: First value or <All>
- Allow Null: Yes (for "All" option)
Region Parameter:
- Dataset: dsRegion (references @Country)
- Available Values: From query
- Depends on: Country parameter
- Refresh automatically when Country changes
City Parameter:
- Dataset: dsCity (references @Region)
- Available Values: From query
- Depends on: Region parameter

Multi-Value Parameters

-- Dataset query with multi-value parameter support
SELECT OrderID, CustomerName, OrderDate, Amount
FROM Orders
WHERE CustomerID IN (@CustomerID)  -- Multi-value parameter
  AND OrderDate >= @StartDate
  AND OrderDate <= @EndDate
ORDER BY OrderDate DESC

Expression to handle "Select All":

' In dataset query parameter value:
=IIF(
    Parameters!CustomerID.Count = Parameters!CustomerID.ValidValues.Count,
    Nothing,  ' "All" selected, don't filter
    JOIN(Parameters!CustomerID.Value, ",")  ' Specific values
)

Hidden Parameters for Dynamic Behavior

' Hidden parameter: IsExecutive (populated via query)
=IIF(
    User!UserID = "exec@contoso.com" OR 
    User!UserID = "ceo@contoso.com",
    True,
    False
)

' Use in dataset filter or visibility expression:
=IIF(Parameters!IsExecutive.Value = False, "Hidden", "Visible")

' Conditional dataset query:
=IIF(
    Parameters!IsExecutive.Value,
    "SELECT * FROM SalesDetails",  -- Full detail
    "SELECT * FROM SalesDetails WHERE SalespersonID = @UserID"  -- Filtered
)

Parameter Validation and Error Handling

' Validate date range
=IIF(
    Parameters!EndDate.Value < Parameters!StartDate.Value,
    "ERROR: End Date must be after Start Date",
    Nothing
)

' Display as textbox at top of report:
' Hidden expression: =Code.ValidateDates()

' Code section in Report Properties:
Public Function ValidateDates() As String
    Dim startDate As DateTime = Report.Parameters!StartDate.Value
    Dim endDate As DateTime = Report.Parameters!EndDate.Value
    
    If endDate < startDate Then
        Return "⚠️ Invalid date range"
    ElseIf DateDiff(DateInterval.Day, startDate, endDate) > 365 Then
        Return "⚠️ Date range exceeds 1 year (performance may be impacted)"
    Else
        Return Nothing  ' Valid
    End If
End Function

Dataset Optimization Techniques

Stored Procedure Best Practices

CREATE PROCEDURE usp_GetOrderDetails
    @StartDate DATE,
    @EndDate DATE,
    @CustomerIDs VARCHAR(MAX) = NULL,  -- Comma-separated for multi-value
    @IncludeDetails BIT = 1
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Create temp table for customer IDs (if multi-value parameter)
    CREATE TABLE #CustomerFilter (CustomerID INT);
    
    IF @CustomerIDs IS NOT NULL
    BEGIN
        INSERT INTO #CustomerFilter (CustomerID)
        SELECT value FROM STRING_SPLIT(@CustomerIDs, ',');
    END
    
    -- Main query with optimization
    SELECT 
        o.OrderID,
        o.OrderDate,
        c.CustomerName,
        c.Country,
        o.TotalAmount,
        -- Conditionally include details based on parameter
        CASE WHEN @IncludeDetails = 1 
            THEN od.ProductName 
            ELSE NULL 
        END AS ProductName,
        CASE WHEN @IncludeDetails = 1 
            THEN od.Quantity 
            ELSE NULL 
        END AS Quantity
    FROM Orders o
    INNER JOIN Customers c ON o.CustomerID = c.CustomerID
    LEFT JOIN OrderDetails od ON o.OrderID = od.OrderID AND @IncludeDetails = 1
    WHERE o.OrderDate >= @StartDate
      AND o.OrderDate <= @EndDate
      AND (c.CustomerID IN (SELECT CustomerID FROM #CustomerFilter) OR @CustomerIDs IS NULL)
    ORDER BY o.OrderDate DESC;
    
    DROP TABLE #CustomerFilter;
END

Benefits:

✅ Execution plan caching in SQL Server
✅ Reduced network traffic (only required columns)
✅ Parameter sniffing optimization
✅ Conditional column selection (details vs summary)
✅ Easier to maintain and version control

Query Performance Optimization

-- ❌ AVOID: Selecting all columns
SELECT * FROM Orders WHERE OrderDate > @StartDate

-- ✅ BEST: Select only needed columns
SELECT OrderID, OrderDate, CustomerName, Amount
FROM Orders
WHERE OrderDate > @StartDate

-- ❌ AVOID: Complex aggregations in dataset query
SELECT 
    CustomerID,
    (SELECT SUM(Amount) FROM Orders o2 WHERE o2.CustomerID = o.CustomerID) AS TotalSales,
    (SELECT COUNT(*) FROM Orders o3 WHERE o3.CustomerID = o.CustomerID) AS OrderCount
FROM Orders o

-- ✅ BEST: Pre-aggregate in CTE or view
WITH CustomerSummary AS (
    SELECT 
        CustomerID,
        SUM(Amount) AS TotalSales,
        COUNT(*) AS OrderCount
    FROM Orders
    GROUP BY CustomerID
)
SELECT c.CustomerName, cs.TotalSales, cs.OrderCount
FROM Customers c
INNER JOIN CustomerSummary cs ON c.CustomerID = cs.CustomerID

-- Indexing strategy for paginated report queries
CREATE NONCLUSTERED INDEX IX_Orders_OrderDate_Includes
ON Orders (OrderDate DESC)
INCLUDE (OrderID, CustomerID, Amount, Status);

Shared Datasets

Benefits of Shared Datasets:

1. Reusability
   - Define once, use in multiple reports
   - Consistent data definitions

2. Performance
   - Dataset caching in Power BI Service
   - Reduced database load

3. Maintainability
   - Update query in one place
   - Version control and change tracking

4. Security
   - Centralized credential management
   - RLS applied at dataset level

Creating Shared Dataset:
Power BI Report Builder → New → Dataset → Shared Dataset
Publish to Power BI Service Premium workspace
Reference in reports via "Use a shared dataset"

Advanced RDL Expressions

Formatting Expressions

' Currency formatting
=Format(Fields!Amount.Value, "C2")  ' $1,234.56

' Percentage formatting
=Format(Fields!GrowthRate.Value, "P2")  ' 12.34%

' Date formatting
=Format(Fields!OrderDate.Value, "yyyy-MM-dd")  ' 2025-08-18
=Format(Fields!OrderDate.Value, "MMMM d, yyyy")  ' August 18, 2025

' Custom number formatting
=Format(Fields!Quantity.Value, "#,##0")  ' 1,234

' Conditional formatting
=IIF(Fields!Amount.Value > 10000, 
    Format(Fields!Amount.Value, "C0"), 
    Format(Fields!Amount.Value, "C2"))

Conditional Visibility

' Hide row if amount is zero
=IIF(Fields!Amount.Value = 0, True, False)

' Show detail section only for executives
=IIF(Parameters!UserRole.Value = "Executive", False, True)  ' False = Visible

' Toggle visibility based on parameter
=NOT(Parameters!ShowDetails.Value)

' Hide entire tablix if no data
=IIF(CountRows("DataSet1") = 0, True, False)

Aggregation Functions

' Sum with scope
=Sum(Fields!Amount.Value, "GroupRegion")  ' Sum within region group
=Sum(Fields!Amount.Value, "DataSet1")  ' Grand total

' Running totals
=RunningValue(Fields!Amount.Value, Sum, Nothing)  ' Running sum across all rows
=RunningValue(Fields!Amount.Value, Sum, "GroupCustomer")  ' Reset per customer

' Percent of total
=Fields!Amount.Value / Sum(Fields!Amount.Value, "DataSet1")

' Rank
=RowNumber("GroupRegion")  ' Row number within group
=RowNumber(Nothing)  ' Row number across entire dataset

' Previous/Next row comparisons
=Fields!Amount.Value - Previous(Fields!Amount.Value)  ' Change from previous row

Custom Code Functions

' Report Properties → Code tab

Public Function GetSeverityColor(ByVal severity As String) As String
    Select Case severity.ToUpper()
        Case "CRITICAL"
            Return "Red"
        Case "HIGH"
            Return "Orange"
        Case "MEDIUM"
            Return "Yellow"
        Case "LOW"
            Return "LightGreen"
        Case Else
            Return "White"
    End Select
End Function

Public Function CalculateDiscount(ByVal amount As Decimal, ByVal tier As String) As Decimal
    Select Case tier
        Case "Gold"
            Return amount * 0.15D
        Case "Silver"
            Return amount * 0.10D
        Case "Bronze"
            Return amount * 0.05D
        Case Else
            Return 0D
    End Select
End Function

' Usage in textbox expression:
=Code.GetSeverityColor(Fields!Severity.Value)
=Code.CalculateDiscount(Fields!Amount.Value, Fields!CustomerTier.Value)

Complex Layout Logic

' Dynamic page breaks (e.g., break after every 50 rows)
' In row group properties → Page Break → "Between each instance of a group"
' Group expression: =Ceiling(RowNumber(Nothing) / 50)

' Alternate row colors
=IIF(RowNumber(Nothing) Mod 2 = 0, "LightGray", "White")

' Conditional column width (not directly supported, use visibility instead)
' Show "Notes" column only if any row has notes
=IIF(CountRows("DataSet1", "HasNotes") > 0, False, True)

' Dynamic subreport parameters
=Fields!CustomerID.Value  ' Pass to subreport

' Multi-column layout (newspaper style)
' Body → Columns property → Set to 2 or 3

Subscription Configuration

Email Subscriptions

# Power BI REST API: Create paginated report subscription

$headers = @{
    "Authorization" = "Bearer $accessToken"
    "Content-Type" = "application/json"
}

$body = @{
    "startDate" = "2025-08-18T06:00:00Z"
    "endDate" = "2026-08-18T06:00:00Z"
    "frequency" = "Daily"  # Daily, Weekly, Monthly
    "schedule" = @{
        "days" = @("Monday", "Wednesday", "Friday")
        "times" = @("06:00", "18:00")
    }
    "users" = @(
        @{ "emailAddress" = "finance@contoso.com" },
        @{ "emailAddress" = "manager@contoso.com" }
    )
    "parameters" = @(
        @{ "name" = "StartDate"; "value" = "2025-08-01" },
        @{ "name" = "EndDate"; "value" = "2025-08-31" }
    )
    "format" = "PDF"  # PDF, XLSX, PPTX, DOCX, CSV, XML, MHTML
    "attachmentType" = "Attachment"  # Attachment or Link
} | ConvertTo-Json -Depth 5

$workspaceId = "workspace-guid"
$reportId = "report-guid"

Invoke-RestMethod -Uri "https://api.powerbi.com/v1.0/myorg/groups/$workspaceId/reports/$reportId/subscriptions" `
    -Method Post `
    -Headers $headers `
    -Body $body

Subscription Best Practices:

☑ Timing:
  ☐ Schedule during off-peak hours (e.g., 6 AM)
  ☐ Stagger subscriptions to avoid concurrent execution
  ☐ Consider time zones for global recipients

☑ Parameters:
  ☐ Use relative dates (e.g., "Last Month") instead of hard-coded
  ☐ Validate parameters before subscription (test report execution)
  ☐ Document parameter values for each subscription

☑ Recipients:
  ☐ Use distribution lists instead of individual emails
  ☐ Limit recipients (too many = performance impact)
  ☐ Ensure recipients have appropriate permissions

☑ Format Selection:
  ☐ PDF: Best for read-only, layout-preserving documents
  ☐ Excel: For further data analysis by recipients
  ☐ CSV: For data import into other systems
  ☐ Word: For editable documents

☑ Monitoring:
  ☐ Enable subscription failure alerts
  ☐ Review execution logs regularly
  ☐ Test subscriptions before production rollout

Data-Driven Subscriptions

-- Subscription list table (stored in database)
CREATE TABLE ReportSubscriptions (
    SubscriptionID INT PRIMARY KEY IDENTITY,
    RecipientEmail VARCHAR(255),
    CustomerID INT,
    ReportFormat VARCHAR(10),  -- PDF, Excel, CSV
    ScheduleType VARCHAR(20),  -- Daily, Weekly, Monthly
    IsActive BIT DEFAULT 1
);

-- Query for data-driven subscription (SSRS feature, limited in PBI Service)
SELECT 
    RecipientEmail AS TO,
    'Monthly Sales Report - ' + CustomerName AS Subject,
    'Please find attached your monthly sales report.' AS Comment,
    'Attachment' AS AttachmentType,
    ReportFormat AS RenderFormat,
    CustomerID AS ParameterCustomerID,
    FORMAT(DATEADD(MONTH, -1, GETDATE()), 'yyyy-MM-dd') AS ParameterStartDate,
    FORMAT(EOMONTH(DATEADD(MONTH, -1, GETDATE())), 'yyyy-MM-dd') AS ParameterEndDate
FROM ReportSubscriptions rs
INNER JOIN Customers c ON rs.CustomerID = c.CustomerID
WHERE rs.IsActive = 1
  AND rs.ScheduleType = 'Monthly';

Note: Data-driven subscriptions are available in SQL Server Reporting Services (SSRS) and Power BI Report Server, but not natively in Power BI Service. Use Power Automate for similar functionality in Power BI Service.

Embedding Paginated Reports

Power Apps Embedding

// Power Apps control: Power BI report viewer
PowerBIViewer1.ReportType = ReportType.PaginatedReport
PowerBIViewer1.ReportUrl = "https://app.powerbi.com/groups/workspace-id/rdlreports/report-id"
PowerBIViewer1.Parameters = {
    CustomerID: CustomerIDVar,
    StartDate: Text(StartDatePicker.SelectedDate, "yyyy-mm-dd"),
    EndDate: Text(EndDatePicker.SelectedDate, "yyyy-mm-dd")
}

// Button to export report
OnSelect = PowerBIViewer1.Export("PDF")

Web Application Embedding (.NET)

using Microsoft.PowerBI.Api;
using Microsoft.PowerBI.Api.Models;

public async Task<EmbedToken> GetPaginatedReportEmbedToken(
    Guid workspaceId, 
    Guid reportId, 
    string username)
{
    using (var client = new PowerBIClient(new Uri("https://api.powerbi.com"), credentials))
    {
        // Generate embed token for paginated report
        var generateTokenRequest = new GenerateTokenRequest(
            accessLevel: "View",
            identities: new List<EffectiveIdentity>
            {
                new EffectiveIdentity(
                    username: username,
                    datasets: new List<string>()  // Empty for paginated reports
                )
            }
        );
        
        var embedToken = await client.Reports.GenerateTokenInGroupAsync(
            workspaceId, 
            reportId, 
            generateTokenRequest
        );
        
        return embedToken;
    }
}

// Client-side JavaScript (using Power BI JavaScript SDK)
var config = {
    type: 'report',
    tokenType: models.TokenType.Embed,
    accessToken: embedToken,
    embedUrl: 'https://app.powerbi.com/reportEmbed',
    id: reportId,
    settings: {
        panes: {
            filters: { visible: false },
            pageNavigation: { visible: false }
        },
        parametersPanelEnabled: true  // Show parameter pane
    }
};

// Embed the report
var report = powerbi.embed(embedContainer, config);

// Export report programmatically
report.exportData(
    powerbi.ExportDataType.Summarized,  // or .Underlying
    1  // Page number
).then(function(data) {
    console.log('Export completed');
}).catch(function(error) {
    console.error('Export failed:', error);
});

REST API Export

# Export paginated report via REST API

$headers = @{
    "Authorization" = "Bearer $accessToken"
    "Content-Type" = "application/json"
}

$exportBody = @{
    "format" = "PDF"
    "paginatedReportConfiguration" = @{
        "parameterValues" = @(
            @{ "name" = "CustomerID"; "value" = "12345" },
            @{ "name" = "StartDate"; "value" = "2025-08-01" },
            @{ "name" = "EndDate"; "value" = "2025-08-31" }
        )
    }
} | ConvertTo-Json -Depth 5

# Step 1: Initiate export
$exportResponse = Invoke-RestMethod `
    -Uri "https://api.powerbi.com/v1.0/myorg/groups/$workspaceId/reports/$reportId/ExportTo" `
    -Method Post `
    -Headers $headers `
    -Body $exportBody

$exportId = $exportResponse.id

# Step 2: Poll export status
do {
    Start-Sleep -Seconds 5
    $statusResponse = Invoke-RestMethod `
        -Uri "https://api.powerbi.com/v1.0/myorg/groups/$workspaceId/reports/$reportId/exports/$exportId" `
        -Method Get `
        -Headers $headers
    
    Write-Host "Export status: $($statusResponse.status)"
} while ($statusResponse.status -eq "Running")

# Step 3: Download file
if ($statusResponse.status -eq "Succeeded") {
    $fileResponse = Invoke-WebRequest `
        -Uri "https://api.powerbi.com/v1.0/myorg/groups/$workspaceId/reports/$reportId/exports/$exportId/file" `
        -Method Get `
        -Headers $headers `
        -OutFile "C:\Reports\CustomerReport_$customerID.pdf"
    
    Write-Host "✅ Report exported successfully"
}

Performance Optimization

Rendering Performance

Rendering Performance Factors:

1. Dataset Size
   - ❌ Returning 100,000 rows to render
   - ✅ Filter at source, use parameters
   - Target: < 10,000 rows for detail reports

2. Subreports
   - ❌ Using subreports in detail rows (executes query per row!)
   - ✅ Use dataset JOINs or single query with grouping
   - Limit: Max 1-2 subreports per page

3. Images
   - ❌ Embedding large images (> 1 MB)
   - ✅ Resize images, use external references
   - Stored as Base64 in RDL (increases file size)

4. Complex Expressions
   - ❌ Custom code functions called 1000s of times
   - ✅ Pre-calculate in dataset query
   - Evaluate in SQL, not RDL expressions

5. Page Count
   - ❌ 500-page reports render slowly
   - ✅ Provide filters to reduce page count
   - Consider splitting into multiple reports

Execution Log Analysis

-- Query Power BI Premium execution log (via DMV or Log Analytics)

-- Average execution time by report
SELECT 
    ReportName,
    AVG(DATEDIFF(SECOND, StartTime, EndTime)) AS AvgExecutionSeconds,
    MAX(DATEDIFF(SECOND, StartTime, EndTime)) AS MaxExecutionSeconds,
    COUNT(*) AS ExecutionCount
FROM ExecutionLog
WHERE ReportType = 'PaginatedReport'
  AND StartTime >= DATEADD(DAY, -30, GETDATE())
GROUP BY ReportName
ORDER BY AvgExecutionSeconds DESC;

-- Identify slow dataset queries
SELECT 
    ReportName,
    DataSetName,
    AVG(DataRetrievalDurationMS) AS AvgQueryDuration,
    AVG(RenderingDurationMS) AS AvgRenderingDuration
FROM ExecutionLog
WHERE ReportType = 'PaginatedReport'
GROUP BY ReportName, DataSetName
HAVING AVG(DataRetrievalDurationMS) > 5000  -- 5+ seconds
ORDER BY AvgQueryDuration DESC;

Optimization Checklist

☑ Dataset Optimization:
  ☐ Use stored procedures with parameters
  ☐ Return only required columns (avoid SELECT *)
  ☐ Pre-aggregate calculations in SQL
  ☐ Add indexes to filtered columns
  ☐ Limit result set with TOP or pagination

☑ RDL Optimization:
  ☐ Minimize subreports (prefer JOINs)
  ☐ Avoid complex expressions in detail rows
  ☐ Use shared datasets for caching
  ☐ Compress embedded images
  ☐ Remove unused datasets and parameters

☑ Rendering:
  ☐ Test with production data volumes
  ☐ Provide sensible default parameters
  ☐ Limit page count (< 100 pages for interactive viewing)
  ☐ Use demand-loaded subreports (not embedded in detail rows)
  ☐ Consider report snapshots for frequently accessed reports

Enterprise Governance

Naming Conventions

Report Naming: [Department]_[ReportType]_[Description]_v[Version]
Example: Finance_Invoice_CustomerInvoice_v2.rdl

Parameter Naming: Use descriptive names with prefixes
- pm_StartDate (pm = parameter)
- ds_Customers (ds = dataset)
- sp_GetOrderDetails (sp = stored procedure)

Shared Dataset Naming: [Source]_[Entity]
Example: SQL_Customers, SQL_Orders, Azure_SalesData

Folder Structure in Power BI Service:
/Reports/Finance/
  - Invoices
  - Financial Statements
  - Budget Reports
/Reports/Operations/
  - Inventory Reports
  - Fulfillment Reports
/Shared Datasets/
  - Finance Datasets
  - Operations Datasets

Version Control

RDL Version Control with Git:

1. Export .rdl files from Power BI Report Builder
   File → Save As → Local directory in Git repo

2. Commit changes with meaningful messages
   git add Finance_Invoice_CustomerInvoice_v2.rdl
   git commit -m "Added customer tier discount column"

3. Use branches for development
   git checkout -b feature/add-tax-column
   
4. Merge to main branch after testing
   git checkout main
   git merge feature/add-tax-column

5. Tag releases
   git tag -a v2.0 -m "Release version 2.0"

6. Deploy from version control
   - Use Azure DevOps or GitHub Actions
   - Automate deployment to Dev/Test/Prod workspaces

Security Best Practices

☑ Dataset Security:
  ☐ Use shared datasets with RLS applied
  ☐ Never embed credentials in RDL files
  ☐ Use gateway connections for on-premises data
  ☐ Implement least-privilege database accounts

☑ Report Access:
  ☐ Assign reports to appropriate workspaces
  ☐ Use workspace roles (Admin, Member, Contributor, Viewer)
  ☐ Leverage Azure AD groups instead of individual users
  ☐ Audit report access regularly

☑ Parameter Security:
  ☐ Validate parameter inputs to prevent SQL injection
  ☐ Use parameterized queries (not dynamic SQL)
  ☐ Hide sensitive parameters (e.g., internal IDs)
  ☐ Implement parameter defaults that are secure

Change Management

Report Change Process:

1. Request
   - User submits change request
   - Business analyst reviews and approves

2. Development
   - Developer creates feature branch
   - Implements changes in Report Builder
   - Tests with sample data

3. Testing
   - QA tests report in Dev workspace
   - Validates parameters, layout, data accuracy
   - Performance testing with production data volumes

4. Approval
   - Business owner reviews test output
   - Approves for production deployment

5. Deployment
   - Publish to Production workspace
   - Update subscriptions if parameters changed
   - Notify users of changes

6. Monitoring
   - Monitor execution logs for errors
   - Collect user feedback
   - Address issues promptly

Troubleshooting Guide

Issue 1: Slow Report Rendering

Symptoms:

Report takes minutes to load
Timeout errors

Diagnosis:

-- Check dataset execution time
SELECT 
    DataSetName,
    AVG(DataRetrievalDurationMS) AS AvgQueryTime,
    MAX(DataRetrievalDurationMS) AS MaxQueryTime
FROM ExecutionLog
WHERE ReportName = 'SlowReport'
GROUP BY DataSetName;

Common Causes:

Large unfiltered datasets (> 50,000 rows)
Subreports in detail rows
Complex expressions evaluated per row
Missing indexes on filtered columns

Resolution:

Add parameters to filter data at source
Replace subreports with JOINs
Move calculations to SQL query
Add indexes: CREATE INDEX IX_Orders_OrderDate ON Orders(OrderDate)

Issue 2: Page Breaks Not Working

Symptoms:

Report renders as single page
Page breaks ignored

Resolution:

Check row group properties: Right-click group → Group Properties → Page Breaks
Enable "Between each instance of a group"
Ensure interactive rendering respects page breaks: Report Properties → Page Setup → "Enable page breaks for interactive rendering"
Test in PDF export (page breaks more reliable in printed formats)

Issue 3: Subscription Failures

Symptoms:

Email not received
Subscription shows "Failed" status

Diagnosis:

# Check subscription status via API
$subscriptions = Invoke-PowerBIRestMethod `
    -Url "groups/$workspaceId/reports/$reportId/subscriptions" `
    -Method Get | ConvertFrom-Json

$subscriptions.value | Where-Object { $_.state -eq "Failed" } | Format-List

Common Causes:

Invalid parameter values
Report execution timeout
Missing permissions
Capacity overload

Resolution:

Validate parameter defaults
Increase timeout in Premium capacity settings
Ensure service principal has permissions
Schedule during off-peak hours

Issue 4: Export Format Issues

Symptoms:

Excel export has misaligned columns
PDF has cut-off text
CSV missing data

Resolution:

For Excel:

Avoid merged cells in tablix
Set specific column widths
Use simple table layouts (not complex matrices)

For PDF:

Set page size explicitly: Report Properties → Page Setup → Paper Size
Check margins: ensure content fits within printable area
Test with different content lengths

For CSV:

CSV exports detail rows only (no grouping/aggregation preserved)
Use "IncludeHeaders" option in rendering settings
Consider exporting from Excel format for more control

Best Practices Summary

☑ Design:
  ☐ Use shared datasets for consistency and caching
  ☐ Standardize header/footer across reports (branding)
  ☐ Keep parameter count minimal (< 5 preferred)
  ☐ Provide sensible defaults for all parameters
  ☐ Design for primary export format (PDF, Excel, etc.)

☑ Performance:
  ☐ Filter data at source (parameters in SQL query)
  ☐ Avoid subreports in detail rows
  ☐ Pre-aggregate calculations in dataset query
  ☐ Limit result sets (< 10,000 rows for detail reports)
  ☐ Test with production data volumes

☑ Maintainability:
  ☐ Use meaningful names for all objects
  ☐ Document complex expressions and custom code
  ☐ Version control .rdl files
  ☐ Centralize common logic in shared datasets
  ☐ Archive outdated report versions

☑ Governance:
  ☐ Implement formal change management process
  ☐ Regular access reviews (quarterly)
  ☐ Monitor execution logs for performance issues
  ☐ Validate subscriptions after report changes
  ☐ Train users on new report features

Key Takeaways

Paginated reports excel at operational reporting (invoices, statements, detailed listings) where pixel-perfect layouts and printing are required
Use stored procedures for dataset optimization, caching, and maintainability
Cascading parameters provide intuitive user experience while reducing query scope
Subreports are expensive when used in detail rows; prefer JOINs or single-query approaches
Test with production data volumes to identify performance issues before deployment
Shared datasets improve performance via caching and provide consistency across reports
Version control RDL files in Git for change tracking and deployment automation
Subscriptions require careful scheduling to avoid capacity overload and ensure timely delivery
Export formats have different fidelity: PDF preserves layout, Excel for data analysis, CSV for integration
Embedding in applications requires Premium capacity and proper token management

Next Steps

Install Power BI Report Builder from Microsoft website
Design first paginated report with parameters and stored procedure dataset
Test report with production-like data volumes
Configure email subscription for automated delivery
Publish to Premium workspace and assign permissions
Monitor execution logs for performance issues
Implement version control for .rdl files
Create shared datasets for frequently used queries
Document report parameters and data sources
Train end users on parameter usage and export options

Additional Resources

Precision. Performance. Production.