Reporting Services – Christopher Finlan

Leveraging ChatGPT to Generate RDL Output for Paginated Report Files

Introduction

Report Definition Language (RDL) is an XML language that provides a robust, standardized format for defining reports. It’s commonly utilized in conjunction with SQL Server Reporting Services (SSRS), which allows you to create, deploy, and manage reports for your organization.

This blog post is aimed at guiding you on how to leverage the power of ChatGPT, a state-of-the-art AI developed by OpenAI, to assist in generating XML output for RDL files. The beauty of this approach is that the AI can generate the necessary XML code based on your inputs, which you can then copy and paste into an .rdl file, creating a functional report almost instantaneously.

Step-by-Step Guide to Create an RDL File with ChatGPT Assistance

Step 1: Defining the Objective of Your Report

Start by clearly stating the goal of your report. This will help ChatGPT understand the type of data that needs to be included and how it should be structured.

User: I need to create a report that shows the sales revenue by product category for the current year.

ChatGPT: Sure, for such a report you would need access to sales data including product details, sale dates, and revenue. Do you have a database where this information is stored?

Step 2: Defining the Data Source

Once you’ve clarified the objective of your report, the next step is defining the data source. This includes providing the type of database you’re using, server name, database name, and the necessary authentication details.

User: The data is stored in a SQL Server database named 'SalesDB' on the server 'Server01'. The authentication method is SQL Server Authentication.

ChatGPT: Great! Let's create the DataSource XML for your RDL file.

At this point, ChatGPT will generate the necessary DataSource XML.

ChatGPT:
```xml
<DataSources>
    <DataSource Name="SalesDB">
        <ConnectionProperties>
            <DataProvider>SQL</DataProvider>
            <ConnectString>Data Source=Server01;Initial Catalog=SalesDB;User ID=your_username;Password=your_password;</ConnectString>
        </ConnectionProperties>
    </DataSource>
</DataSources>

Step 3: Defining the Dataset

The Dataset comprises the SQL query that retrieves the desired data from the database.

```chat
User: The necessary data can be retrieved with the following SQL query: 'SELECT ProductCategory, SUM(Revenue) as TotalRevenue FROM Sales WHERE YEAR(SaleDate) = YEAR(GETDATE()) GROUP BY ProductCategory'.

ChatGPT: Great, let's use that query to define the Dataset in your RDL file.

ChatGPT then generates the corresponding Dataset XML.

ChatGPT:
```xml
<Datasets>
    <Dataset Name="SalesData">
        <Query>
            <DataSourceName>SalesDB</DataSourceName>
            <CommandText>
            <![CDATA[SELECT ProductCategory, SUM(Revenue) as TotalRevenue FROM Sales WHERE YEAR(SaleDate) = YEAR(GETDATE()) GROUP BY ProductCategory]]>
            </CommandText>
        </Query>
        <Fields>
            <Field Name="ProductCategory">
                <DataField>ProductCategory</DataField>
                <rd:TypeName>System.String</rd:TypeName>
            </Field>
            <Field Name="TotalRevenue">
                <DataField>TotalRevenue</DataField>
                <rd:TypeName>System.Decimal</rd:TypeName>
            </Field>
        </Fields>
    </Dataset>
</Datasets>

Step 4: Defining the Report Layout

The report layout defines how the retrieved data should be presented in the report. This typically involves specifying items such as tables, matrices, or charts.

```chat
User: I want to present the data in a simple table with two columns: one for the Product Category and one for the Total Revenue.

ChatGPT: Alright. Let's create the report layout XML for your table.

ChatGPT then generates the report layout XML based on your requirements.

ChatGPT:
```xml
<ReportItems>
    <Tablix Name="SalesTablix">
        <TablixBody>
            <TablixColumns>
                <TablixColumn>
                    <Width>3in</Width>
                </TablixColumn>
                <TablixColumn>
                    <Width>2in</Width>
                </TablixColumn>
            </TablixColumns>
            <TablixRows>
                <TablixRow>
                    <Height>0.25in</Height>
                    <TablixCells>
                        <TablixCell>
                            <CellContents>
                                <Textbox Name="ProductCategoryHeader">
                                    <Paragraphs>
                                        <Paragraph>
                                            <TextRuns>
                                                <TextRun>
                                                    <Value>Product Category</Value>
                                                </TextRun>
                                            </TextRuns>
                                        </Paragraph>
                                    </Paragraphs>
                                </Textbox>
                            </CellContents>
                        </TablixCell>
                        <TablixCell>
                            <CellContents>
                                <Textbox Name="TotalRevenueHeader">
                                    <Paragraphs>
                                        <Paragraph>
                                            <TextRuns>
                                                <TextRun>
                                                    <Value>Total Revenue</Value>
                                                </TextRun>
                                            </TextRuns>
                                        </Paragraph>
                                    </Paragraphs>
                                </Textbox>
                            </CellContents>
                        </TablixCell>
                    </TablixCells>
                </TablixRow>
                <TablixRow>
                    <Height>0.25in</Height>
                    <TablixCells>
                        <TablixCell>
                            <CellContents>
                                <Textbox Name="ProductCategory">
                                    <Paragraphs>
                                        <Paragraph>
                                            <TextRuns>
                                                <TextRun>
                                                    <Value>=Fields!ProductCategory.Value</Value>
                                                </TextRun>
                                            </TextRuns>
                                        </Paragraph>
                                    </Paragraphs>
                                </Textbox>
                            </CellContents>
                        </TablixCell>
                        <TablixCell>
                            <CellContents>
                                <Textbox Name="TotalRevenue">
                                    <Paragraphs>
                                        <Paragraph>
                                            <TextRuns>
                                                <TextRun>
                                                    <Value>=Fields!TotalRevenue.Value</Value>
                                                </TextRun>
                                            </TextRuns>
                                        </Paragraph>
                                    </Paragraphs>
                                </Textbox>
                            </CellContents>
                        </TablixCell>
                    </TablixCells>
                </TablixRow>
            </TablixRows>
        </TablixBody>
        <TablixColumnHierarchy>
            <TablixMembers>
                <TablixMember />
                <TablixMember />
            </TablixMembers>
        </TablixColumnHierarchy>
        <TablixRowHierarchy>
            <TablixMembers>
                <TablixMember />
                <TablixMember />
            </TablixMembers>
        </TablixRowHierarchy>
        <DataSetName>SalesData</DataSetName>
    </Tablix>
</ReportItems>

Step 5: Assembling the Full RDL File

At this point, you have all the necessary XML for your RDL file. You can put these pieces together into a full RDL file, ensuring that you include the necessary boilerplate XML for an RDL file. Don’t forget to adjust connection string information for your data source, where necessary.

Here it is as an example:

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
    <DataSources>
        <DataSource Name="SalesDB">
            <ConnectionProperties>
                <DataProvider>SQL</DataProvider>
                <ConnectString>Data Source=Server01;Initial Catalog=SalesDB;User ID=your_username;Password=your_password;</ConnectString>
            </ConnectionProperties>
        </DataSource>
    </DataSources>
    <DataSets>
        <DataSet Name="SalesData">
            <Query>
                <DataSourceName>SalesDB</DataSourceName>
                <CommandText>
                <![CDATA[SELECT ProductCategory, SUM(Revenue) as TotalRevenue FROM Sales WHERE YEAR(SaleDate) = YEAR(GETDATE()) GROUP BY ProductCategory]]>
                </CommandText>
            </Query>
            <Fields>
                <Field Name="ProductCategory">
                    <DataField>ProductCategory</DataField>
                    <rd:TypeName>System.String</rd:TypeName>
                </Field>
                <Field Name="TotalRevenue">
                    <DataField>TotalRevenue</DataField>
                    <rd:TypeName>System.Decimal</rd:TypeName>
                </Field>
            </Fields>
        </DataSet>
    </DataSets>
    <ReportSections>
        <ReportSection>
            <Body>
                <ReportItems>
                    <Tablix Name="SalesTablix">
                        <TablixBody>
                            <TablixColumns>
                                <TablixColumn>
                                    <Width>3in</Width>
                                </TablixColumn>
                                <TablixColumn>
                                    <Width>2in</Width>
                                </TablixColumn>
                            </TablixColumns>
                            <TablixRows>
                                <TablixRow>
                                    <Height>0.25in</Height>
                                    <TablixCells>
                                        <TablixCell>
                                            <CellContents>
                                                <Textbox Name="ProductCategoryHeader">
                                                    <Paragraphs>
                                                        <Paragraph>
                                                            <TextRuns>
                                                                <TextRun>
                                                                    <Value>Product Category</Value>
                                                                </TextRun>
                                                            </TextRuns>
                                                        </Paragraph>
                                                    </Paragraphs>
                                                </Textbox>
                                            </CellContents>
                                        </TablixCell>
                                        <TablixCell>
                                            <CellContents>
                                                <Textbox Name="TotalRevenueHeader">
                                                    <Paragraphs>
                                                        <Paragraph>
                                                            <TextRuns>
                                                                <TextRun>
                                                                    <Value>Total Revenue</Value>
                                                                </TextRun>
                                                            </TextRuns>
                                                        </Paragraph>
                                                    </Paragraphs>
                                                </Textbox>
                                            </CellContents>
                                        </TablixCell>
                                    </TablixCells>
                                </TablixRow>
                                <TablixRow>
                                    <Height>0.25in</Height>
                                    <TablixCells>
                                        <TablixCell>
                                            <CellContents>
                                                <Textbox Name="ProductCategory">
                                                    <Paragraphs>
                                                        <Paragraph>
                                                            <TextRuns>
                                                                <TextRun>
                                                                    <Value>=Fields!ProductCategory.Value</Value>
                                                                </TextRun>
                                                            </TextRuns>
                                                        </Paragraph>
                                                    </Paragraphs>
                                                </Textbox>
                                            </CellContents>
                                        </TablixCell>
                                        <TablixCell>
                                            <CellContents>
                                                <Textbox Name="TotalRevenue">
                                                    <Paragraphs>
                                                        <Paragraph>
                                                            <TextRuns>
                                                                <TextRun>
                                                                    <Value>=Fields!TotalRevenue.Value</Value>
                                                                </TextRun>
                                                            </TextRuns>
                                                        </Paragraph>
                                                    </Paragraphs>
                                                </Textbox>
                                            </CellContents>
                                        </TablixCell>
                                    </TablixCells>
                                </TablixRow>
                            </TablixRows>
                        </TablixBody>
                        <TablixColumnHierarchy>
                            <TablixMembers>
                                <TablixMember />
                                <TablixMember />
                            </TablixMembers>
                        </TablixColumnHierarchy>
                        <TablixRowHierarchy>
                            <TablixMembers>
                                <TablixMember />
                                <TablixMember />
                            </TablixMembers>
                        </TablixRowHierarchy>
                        <DataSetName>SalesData</DataSetName>
                    </Tablix>
                </ReportItems>
            </Body>
        </ReportSection>
    </ReportSections>
</Report>

The example provided here should be fully functional, but there are some considerations to keep in mind:

Database Schema: The SQL query included in the report depends on your database schema. The given example assumes that there’s a table called ‘Sales’ with the columns ‘ProductCategory’, ‘Revenue’, and ‘SaleDate’. Make sure that your database contains this table and these columns or adjust the SQL query to match your actual database schema.
Authentication: The example uses SQL Server Authentication for simplicity, but you might be using a different authentication method, such as Windows Authentication or Azure Active Directory. Make sure to adjust the connection string to match your actual authentication method.
Report Design: The example includes a very basic report design that outputs a simple table. In a real-world scenario, your report might require additional elements, such as a header, footer, grouping, sorting, filtering, images, subreports, etc. RDL is very flexible and allows you to create complex report designs.
Data Types: The provided XML assumes that ‘ProductCategory’ is a string and ‘TotalRevenue’ is a decimal. If your actual data types are different, make sure to adjust the <rd:TypeName> elements accordingly.
Data Source Security: The provided example includes the username and password in the connection string for simplicity, but this isn’t secure. In a real-world scenario, you should consider more secure methods to store and retrieve your credentials, such as storing them in the Reporting Services Configuration Manager or using Integrated Security.
Error Handling: While this isn’t strictly necessary for the RDL file to be functional, it’s a good idea to implement error handling in your reports, such as displaying a meaningful message if the SQL query returns no data.

As long as your report’s requirements align with what’s defined in this XML, you should be able to generate a functional report by replacing the placeholders with your actual values. However, this is a basic example and real-world reports can get much more complex. For more advanced features, you might need to manually edit the RDL file or use a tool like SQL Server Data Tools or Report Builder, which provide a GUI for designing reports.

Conclusion

Leveraging AI models like ChatGPT to generate RDL file content allows you to quickly and accurately create complex reports. By providing your specifications to the AI, you can obtain a ready-to-use XML output for your RDL file, reducing the time spent on manual coding and minimizing the potential for human error. Happy reporting!

This blogpost was created with help from ChatGPT Pro.

How to Create Professional-Looking Invoices using Power BI Paginated Reports

In this blog post, we will explore how to create professional-looking invoices using Power BI Paginated Reports. We’ll start with an overview of Paginated Reports, then dive into creating a custom invoice design, and finally discuss how to publish and share these invoices.

Preparing Data for Invoices

To create an invoice, we first need to prepare the underlying data. This includes data about customers, products, and transactions. You can import data from various sources, such as Excel files, SQL databases, or other data sources supported by Power BI. Once you have imported the data, you can create relationships and perform data cleaning and transformations as necessary.

Designing the Invoice Layout in Report Builder

To start designing the invoice, open the Report Builder tool and create a new Paginated Report. The Report Builder interface consists of a design surface, a report data pane, and a properties pane. Begin by organizing your report data in the report data pane.

2.1. Add a Header

To create a header for your invoice, click on the ‘Insert’ tab, and select ‘Header’ from the dropdown menu. In the header, you can include elements such as your company logo, address, and contact information. To add an image, use the ‘Image’ tool from the ‘Insert’ tab and position it in the header.

2.2. Add a Title

Add a text box from the ‘Insert’ tab to include the invoice title (e.g., ‘Invoice’). Customize the text’s font, size, and alignment as needed.

2.3. Customer Information

Add a table or a series of text boxes to display the customer’s name, address, contact information, and invoice number. You can use expressions to bind the text boxes to the appropriate data fields in your dataset.

2.4. Invoice Line Items

Insert a table from the ‘Insert’ tab to display the invoice line items. Bind the table to your invoice data source and configure the columns to display the product name, quantity, unit price, and line item total. Apply formatting and styling to match the overall design of the invoice.

2.5. Summary and Footer

Add a summary section to display the subtotal, taxes, and total amount due. You can use expressions to calculate these values based on the line items in the invoice. Finally, add a footer to include any additional information, such as payment terms or a thank you message.

Publishing and Sharing Invoices

Once you’ve finished designing the invoice, save the report and publish it to Power BI Report Server or Power BI Premium. To generate individual invoices for your customers, create a parameterized report, allowing you to filter the data for a specific customer or invoice number. Share the published invoices with your customers by providing them with a link or exporting the invoices as PDFs, Word documents, or Excel files.

Conclusion

Power BI Paginated Reports offer a flexible and powerful solution for creating professional-looking invoices. By leveraging the Report Builder tool, users can create custom invoice designs that meet their specific

This blogpost was created with help from ChatGPT Pro.

Mastering Expressions and Functions in Power BI Paginated Reports: Unleash Your Reporting Potential

Power BI Paginated Reports are a powerful tool in every data analyst’s arsenal. These versatile, high-quality reports allow for a seamless presentation of large amounts of data in a consistent and easy-to-read format. In this blog post, we will dive into the fascinating world of expressions and functions in Power BI Paginated Reports, showcasing their capabilities and providing you with a step-by-step guide to help you make the most of your reporting experience.

Section 1: Understanding Expressions and Functions

1.1 What are Expressions?

In Power BI Paginated Reports, expressions are used to define the content and appearance of report items, data regions, and groups. They are written in Report Definition Language (RDL) and allow you to perform various calculations, conditional formatting, and data manipulation tasks.

1.2 What are Functions?

Functions are pre-built pieces of code that can be used within expressions to perform specific tasks, such as mathematical operations, string manipulation, date and time calculations, and more. Power BI Paginated Reports offers a rich set of built-in functions, making it easier for you to create dynamic, data-driven reports.

Section 2: How to Use Expressions in Power BI Paginated Reports

2.1 Creating Basic Expressions

To create an expression, you’ll need to use the Expression Editor. Follow these steps:

In the Report Builder, select the textbox or other report item you want to add the expression to.
In the Properties pane, locate the property you want to set with an expression, then click the drop-down arrow and select <Expression…>.
In the Expression Editor, type or build your expression using the available functions and operators.
Click OK to save the expression.

2.2 Examples of Common Expressions

Here are some examples of expressions you might use in your Power BI Paginated Reports:

Concatenating strings: =Fields!FirstName.Value & " " & Fields!LastName.Value
Calculating the sum of a field: =Sum(Fields!Sales.Value)
Applying conditional formatting based on a value: =IIf(Fields!Revenue.Value > 10000, "Green", "Red")

Section 3: Working with Functions in Power BI Paginated Reports

3.1 Accessing Built-In Functions

To access built-in functions, follow these steps:

Open the Expression Editor (as explained in Section 2.1).
In the left pane of the Expression Editor, you’ll see a list of categories, such as Common Functions, Text, DateTime, and more. Click on a category to display the available functions.
Double-click a function to add it to your expression.

3.2 Examples of Functions in Expressions

Here are some examples of functions used in expressions:

Calculating the average of a field: =Avg(Fields!Sales.Value)
Formatting a date: =Format(Fields!OrderDate.Value, "MM/dd/yyyy")
Counting the number of items in a dataset: =CountRows()

Expressions and functions in Power BI Paginated Reports provide endless possibilities to create dynamic, data-driven reports that are both visually appealing and informative. By mastering these techniques, you will enhance your reporting capabilities and stand out as a data analyst. Now it’s time to put these skills to the test and create stunning, insightful reports with Power BI Paginated Reports!

This blogpost was created with help from ChatGPT Pro.

An Exclusive Interview with Paginated Report Bear: The Fun Side of Reporting

Introduction

In the world of data analysis and reporting, we often get caught up in the technical aspects and overlook the fun side of things. Today, we’re excited to share an exclusive, light-hearted interview with the internet’s favorite data reporting mascot, Paginated Report Bear! Join us as we delve into the bear’s thoughts on paginated reports, Power BI, and what makes him so passionate about reporting.

The Interview

Me: Thank you for joining us today, Paginated Report Bear! Let’s start with the basics. How did you become so passionate about paginated reports?

Paginated Report Bear: Well, it all started when I stumbled upon a beautifully crafted paginated report in the woods. The way it presented the data in such a precise, pixel-perfect manner was mesmerizing. From that moment on, I knew I had found my true calling – to spread the joy of paginated reports to the world!

Me: That’s quite an inspiring story! What do you think makes paginated reports so special compared to other reporting formats?

Paginated Report Bear: Paginated reports are like a canvas for data. They allow you to design highly customizable, print-ready reports that can span multiple pages with ease. Plus, they’re perfect for handling complex data scenarios, and who doesn’t love the satisfying feeling of flipping through a beautifully formatted, multi-page report?

Me: So true! Now, we know you’re a big fan of Power BI. Can you tell us about your favorite features in Power BI for creating paginated reports?

Paginated Report Bear: Absolutely! I love how Power BI offers a seamless experience for designing paginated reports using the Power BI Report Builder. It’s packed with awesome features like Document Maps, Interactive Sorting, and Custom Pagination, which make it super easy to create dynamic, user-friendly reports. And let’s not forget the amazing Power BI community that’s always there to help and share their knowledge.

Me: You’ve definitely become an icon in the Power BI community. How does it feel to be such a beloved figure?

Paginated Report Bear: Oh, it’s truly humbling! I’m just a bear who loves paginated reports, and the fact that I can bring a smile to people’s faces while they’re working on their reports is simply heartwarming. I’m grateful for the opportunity to connect with the community and share my passion for paginated reports with everyone.

Me: Before we wrap up, do you have any tips or advice for Power BI users who are just starting to explore paginated reports?

Paginated Report Bear: Absolutely! First and foremost, don’t be afraid to experiment and try out different features – that’s how you’ll discover the true potential of paginated reports. Also, make use of the wealth of resources available online, such as tutorials, webinars, and blog posts, to enhance your skills. And remember, the Power BI community is always there to help, so don’t hesitate to ask questions and learn from fellow users. Most importantly, have fun with it!

Conclusion

We hope you enjoyed this lighthearted, exclusive interview with Paginated Report Bear! His passion for paginated reports and Power BI serves as a reminder that reporting and data analysis can be fun, engaging, and enjoyable. Keep experimenting, learning, and embracing the power of paginated reports – and don’t forget to have some fun along the way!

This blogpost was created with help from ChatGPT Pro.

Use Outlook, Microsoft Flow and Paginated Reports to create Power BI Report and Dashboard E-Mail Subscriptions with PDF attachments

I hear this ask quite a bit these days – I want to be able to create e-mail subscriptions with PDF (or other file format) attachments of my full Power BI report or dashboard. Well, you can actually do this already, thanks to Microsoft Flow and Paginated Reports in Power BI Premium or SQL Server Reporting Services/Power BI Report Server if you chose to do so.

If you read my blog post last week, I showed you how to use Flow to take your e-mail attachments and save them to a OneDrive for Business folder. While I did it for PDF’s in that example, you can do it for any attachment type, including images, which is what you get today as an attachment when you subscribe to a Power BI Report or Dashboard. The challenge is if you want an attachment of a full Power BI report, you only get one report page image per subscription. However, you can stitch those items together into a paginated report that’s hosted either in Power BI Premium or SSRS and set that up as a subscription with a full report attachment. Here’s how –

Let’s say I have four report pages total in my Power BI report. I’ll create four subscriptions, 1 for each page.

Next, I’m going to create a paginated report where I show each of those images on a different page. Since they are optimized for landscape, I am going to change my report properties to landscape in Power BI Report Builder.

With paginated reports, you can surface images one of three ways in your report. You can do it either as an embedded image, an external image, or from a Database.

Embedded images are what I used for the Paginated Report Bear sample report – I literally just saved the image files in the rdl you could download. That doesn’t help in this use case.

If I want to use an external images for each of the files, which is a URL image I point to, than I can create a Flow where I save the e-mail attachments from my Power BI subscriptions to a folder in OneDrive (personal), and then use the embed URL from each of those images as external sources for my report. Each time I save a new image with the same name, my embed URL doesn’t change, so I can update the images as often as I like using Flow and my report will always show the latest. I just need to add four images to my paginated report, each with a link to one of the report page URLs, and I’m done!

Anyone who cares about security is going bonkers right now, since sticking them in my personal OneDrive means they’re publicly available to anyone in the world. A better (and safer) solution I’m using is saving them to an Azure SQL database, and then surfacing them as “Database” images in my paginated report. My flow looks like this for that scenario –

What I did here was create a table in an Azure SQL database with three fields, and the Attachment field is an Image field. (This is an admittedly sloppy table written for a blog post vs. production use.) I also narrowed down the scope of the attachments by limiting the flow to those e-mails from the following address (I could be even more precise, but dinner is waiting in the other room) –

Now when my Flow is triggered (it checks my inbox every minute), if it finds a new item matching that rule, I see a new entry in my SQL table like so.

With them saved to a SQL database, I have a few different options how to surface them in my report. Either I do them as standalone items with a page break in between (Check out these two pages when I export out the report out to Word) –

With this method, I’m setting my SQL query to always show the most recent image for each page of my report.

Or perhaps in a table as a collection showing different report states over time if I want to show all the report images

Now, with my images saved in a paginated report, I can load it to either the Power BI service OR SQL Server Reporting Services, setup my subscription, choose the output format (PDF, Word, etc.) and there you have it – full report attachments of my Power BI reports sent as e-mail subscriptions!

Thanks for reading!

Ten reasons why you should download and learn to use Power BI Paginated Report Builder

Happy weekend all!

Yesterday was a big day for the Power BI team, as we released the first edition of Power BI Paginated Report Builder. Why is this such a big deal? Well, while Report Builder has been around for years, many Power BI users have not only never used the product, they’ve never tried to build a paginated report, period. Now they can do just that, and I’m going to cover the top ten reasons why, if you use Power BI, you should download and learn to use Power BI Paginated Report Builder.

Download Power BI Paginated Report Builder

1. It’s Free.

It’s completely free to download and use. Who doesn’t like free?

2. It doesn’t require Power BI Premium (or even Pro) to use it.

As many in the community know, Paginated Reports are available in Premium workspaces only (Here’s a link to up vote making this feature more widely available in Power BI – Paginated Reports in Pro and Premium). However, just like Power BI Desktop, there’s no Power BI license required to use it locally. So not only can you author reports, you can render and view them like you would in the service. Take a look at the sample report Paginated Report Bear created back in November in the tool –

I have a similar experience as I would as a consumer in Power BI, including several key items I’ll cover shortly.

3. You can use it to connect to any Power BI dataset in Premium to build reports

As my colleague Christian Wade announced last week, you can now use the XMLA endpoint to access your Power BI datasets in Premium. Paginated Report Builder supports this connectivity option as well, and it’s super easy to do. Simply copy the connection string from the “Settings” tab of your dataset in the Power BI service –

Then create a new “SQL Server Analysis Services” data source in Power BI Report Builder. Type the phrase “Data Source =” in the Connection string dialog, paste the copied string from Power BI in there and hit “Test Connection”. You’ll be asked to sign into Power BI, and assuming you’ve followed these simple steps, it’ll connect successfully and you can start building paginated reports with it!

4. You can connect to any Power BI dataset via XMLA, even if that capacity or workspace doesn’t also support Paginated Reports

While Paginated Reports are only available in a P1 SKU and above (or A4 SKU and above), the XMLA endpoint is available in every SKU, including down to A1 or EM1. So you can create and use Power BI datasets from any workspace that supports the XMLA endpoint when authoring your reports. The only restriction is that when we support publishing reports with Power BI datasets to the service later this month, you just need to publish it back to a workspace that does support Paginated Reports. Don’t worry that your dataset might be sitting in a different capacity or workspace – we’ll explain more when we announce support in the official blogpost.

5. With the ability to use the same Power BI datasets used for your interactive reports, you can easily create basic paginated reports for scenarios as simple as exporting tables of data, or a print-friendly view of your Power BI Desktop report.

I’ll be the first to admit many things in Paginated Report Builder are harder for report authors to achieve than they are in Power BI Desktop. But for many basic scenarios, like creating a simple table I know users will want to export out large amount of data from, Paginated Report Builder makes that very, very simple and is often times a better option. I’ll have a walk through in a follow-up post where I can show you how to do either of these scenarios with your Power BI datasets, in some cases in a matter of minutes.

6. Paginated Reports published back to Power BI don’t have any data export limitations

I covered this in an earlier blogpost – Yes, you can export unlimited** rows of data from Paginated Reports in Power BI, but if your organization has Premium workspaces that support Paginated Reports, this is worth keeping in mind. Now that you can create paginated reports against your datasets in Power BI Premium, having a simple paginated report for export scenarios might help unlock certain scenarios you couldn’t before for your users.

7. You can print your reports right from Paginated Report Builder

You probably noticed in the earlier screenshot there was a print option for a report you’re viewing in the tool. You weren’t imaging it – you can print out your report right from the toolbar, and can even look at a Print Layout view of your report while interacting with it. This option isn’t even available in SQL Server Reporting Services (!), and is a great way to see how your report will look once you do print it out.

8. You can export your report from Paginated Report Builder to several different formats, including PDF, Word, Excel and PowerPoint.

In addition to the ability to Print, you can also export to several different formats right from the toolbar when viewing your report. This is a powerful capability that few tools have in their authoring environment right out of the box.

9. In a future update, we’ll have support to connect to Power BI datasets in non-Premium workspaces when authoring reports.

This will make this even more of a no-brainer, as it’ll open up all the scenarios we’ve discussed in this post to any Power BI dataset. Look for more details on this in the coming weeks.

10. There’s a lot of material to help you get started

A great place to start is with Patrick LeBlanc from the Guy in a Cube channel, who I work closely with. He has put together several videos around Paginated Reports to help get you going. Additionally, you should see several more in the coming weeks as more and more functionality is announced, plus the blog posts I’ll be adding as well. I’ve added the playlist from YouTube below.

Paginated Reports playlist

I could keep going, but ten feels like a good place to stop on a lazy Saturday. If you’ve never tried paginated reports before, now is your chance! Go download Power BI Paginated Report Builder today and learn what all the fuss is about.

Thanks for reading!

Ask Me Anything Unanswered Chat Questions Answered

Last week, I participated in an “Ask Me Anything” session where I answered questions around both Paginated Reports in Power BI, as well as questions around SQL Server Reporting Services and Power BI Report Server.

As I wasn’t able to get to all the questions posted in the chat, I said I’d answer those I didn’t get to in a follow-up post on my personal blog. So, without further adieu, here’s a recap of the related unanswered questions with my responses. I’ve broken them into sections focused on Paginated Reports in the cloud, as well as the on-prem offerings (SSRS/PBIRS).

Paginated Reports in Power BI

“any update on parameters? right now they are only on the page header, any chance that this area will get a makeover?”

– You’ll see us doing work to rationalize the toolbar in paginated reports with Power BI reports to the extent it makes sense. There are certain patterns for paginated reports authors prefer in terms of parameter layout/grouping that we don’t want to disrupt, but the idea is to make the chrome around the reports look/feel similar across both report types.

“Will the RLS will be available in paginated reports too?”

– Yes, when paginated reports support connecting to Power BI datasets, we will respect RLS. We’ll also support it against other data sources when we support user-based authentication for them.

“Is there a recommended resource for testing / exploring paginated reports – i.e., a sample paginated report that can be used as a template?”

– Great question. I’ll put together a list of resources in a follow-up post, but at a minimum recommend you download Report Builder and follow the tutorial.

“Is printing of multi-page table the main advantage of SSRS reports over Power BI tables/matrix?”

This is another good question – it isn’t an either/or, but rather which is the best tool for what you need to accomplish. I’m going to cover this in a separate post as well.

“Is there plan to allow embedding Paginated report in SharePoint Online as we can today with PowerBI Report?”

– Yes, this is planned.

“power BI service will we get subscriptions for paginated reports in the same way we have now in SSRS? Also will this be extended to Power Bi reports and dashboards in Power Bi service. Including Data Driven Subscriptions?”

– Yes! You’ll see the current subscriptions in Power BI evolving to give you the options you have with SSRS today for both report types. This includes scheduling, the ability to send with specific filters/parameters applied, attachment support, etc.

“for now I’m not able to find paginated reports using the search function on the Home page. Any timing when/if this is coming?”

This is a known bug that we’re working with other teams to get resolved. You should see this fixed in the next couple months.

Power BI Report Server/SSRS

“can we share a .pbix report through emails?”

We aren’t planning to add subscription support for Power BI Reports in Power BI Report Server in the short term.

“Would love to hear about how RLS will be implemented to PBIRS.”

This work is well underway for the January 2019 release of Power BI Report Server. It will be similar to what you do in the service today, where you set the roles in the desktop and assign users to those roles in the server through the web browser.

“And along the same lines, what about standard templates, such as headers/footers?”

This is an improvement we’d like to look at for the Power BI service as it relates to Paginated Reports, so we’d also look to bring it back to the on-prem product as well.

Looks like the other questions related these areas were all addressed either in the session or in the chat itself. If you have additional questions, feel free to check the existing FAQ, or leave a comment in the blog comments below!

Download Paginated Report Bear’s sample paginated report for Power BI

Hi everyone, it’s guest blogger Paginated Report Bear!

Well, actually, Mr. Chris agreed to type for me since I don’t have fingers. Anyways, if you watched my intro video, I promised you all I’d make a sample report and share it by the end of the week. Since I always keep my promises (EDITOR NOTE: no he doesn’t), here is my first paginated report that you’re free to download and try out in Power BI! It uses the Enter Data feature that was introduced a few months ago, so you can upload it as is into your Power BI (Premium) workspace and give it a try. If you don’t have Premium capacity, use the steps in Mr. Chris’s last blog post to spin up a capacity and try it out yourself. The report sample has a couple cool tricks in it, like dynamic images based on the parameter you select, so it might be a nice reference for you in the future.

I’ll be creating more samples and talking more about paginated reports in the future (or not, depending if my son gets tired of doing this).

Thanks for reading!

Paginated Report Bear Sample Report

How to use A SKU’s to try out Paginated Reports in Power BI without upfront cost or long term commitment

Since the Eagles game doesn’t start until later, thought I’d put this together for folks.

As many of you know, earlier this month we announced a preview of Paginated Reports in Power BI. While folks were excited about this, there were those who were disappointed it was only available (for now) if you had Power BI Premium.

“I don’t have Premium (yet . . .),” they said, “But I still want to try this out and use it. How can I do so without committing to it for a month and paying $5000?”

The good news is, there is an easy way to do this. See, as I’ve stated in previous interviews, you can try all the functionality out by spinning up an Power BI Embedded A SKU capacity, which is available to purchase through Microsoft Azure. While normally A SKU’s are specifically used for embedding scenarios, there is no licensing restriction against using them internally if you’d like. However, generally this makes little sense vs. purchasing a P SKU for most use cases, since each user would still need a Pro license to access the Power BI portal AND it’s almost $1000 more per month if you run it all the time.

But if you just want to try out the new functionality that is available only on Premium capacity, the big advantage of A SKU’s is you can stop/start them just like any VM in Azure, and you’re billed by the minute vs. having an initial upfront monthly cost. This means you can spin up an A SKU, turn on the paginated reports capability in your Power BI portal, and start using it. And when you’re done, you can go into Azure and pause the capacity until you want to use it again. Since you’re only billed for the time it’s running, you can try this new functionality out for around 14 cents a minute! (this figure is based on my fuzzy math being done while my son is playing Fortnite right next to me, so forgive me if this is off by a few cents one way or another). Let’s walk through how you’d go set this up yourself –

I have my own Power BI subscription (yes, I pay out of pocket for this), since I like to have access to the exact same experience as all of my users do (or I forgot to turn it off when I joined the team . . .). In any event, I have a Pro license, but when I check the Admin Portal, I can see I have no Premium capacities right now.

Note another tab there which says “Power BI Embedded” – I don’t have any of those capacities, which you purchase in Azure, spun up currently either.

To get one of those going, I’m going to head to the Azure Portal and login with my credentials that I also use for Power BI. Why? Because I’m the only user, so I’m also the admin. Now, I can do a search for “Power BI Embedded”. This will take me to the management page, which looks like this

I hit the “Create Power BI Embedded” button, which, if you haven’t signed up for an Azure account yet, you’ll be prompted to do so, and this includes a $200 credit for 30 days. If you have signed up for Azure, you will skip this step (duh).

Now that I have that setup, I’ll go back to the portal and get back to the previous screen. Here, I can click the button again to setup a new Power BI Embedded A SKU. You need to select at least an A4 capacity size or higher to use Paginated Reports, so I’ll pick an A4 in my home location to spin up. (NOTE: Paginated Reports are also supported in multi-geo scenarios, even during our public preview, so I could choose other regions outside my home region if I wanted to.)

Everything looks good, so I’ll hit create and wait for it to finish deploying.

If I check my Power BI Portal, I see that it is also showing as being deployed there under the Capacity Settings –> Power BI Embedded tab

Once it’s completed deployment, when I click on the capacity name, I’ll see I can manage this capacity just like I would any premium capacity in Power BI.

Under the Workloads, I see the two new preview workloads, and I’ll set Paginated Reports to “On” and assign 50% of the memory to that workload. I can also try out the new Dataflows workload as well, but I’ll save that for another time.

After a few moments, I’ll see the message has changed from starting to Ready, and my workload is now ready to use. I’ll assign all (one) of the workspaces for my organization to this new capacity.

Cool! Now, I can upload my first paginated report to the workspace and view it. I’m using a vintage Halo sample report, and it renders without a hitch.

But now I want to stop using the capacity and not get charged (since I finished this blog post). No problem – I can go back to the Azure portal and just pause the capacity until I want to use it again.

When I’ve paused it, I can no longer view my paginated reports in Power BI, but they aren’t deleted or otherwise affected. They’re still there waiting to be used again when the capacity is started up, and I can delete them if I need to.

And that’s it – in less than an hour, including the time it took to type this blogpost, I created a new Azure subscription, created my first Power BI “Embedded” A4 capacity, turned on the Paginated Report workload, assigned a workspace, uploaded and viewed my report, and then paused the capacity to stop the billing on it. Whew!

Thanks so much for reading through the post today, and I hope you all take some time to try out the new paginated reports in Power BI Premium. And if your organization doesn’t already have Power BI Premium, use this walkthrough to give it a try yourself!

Happy Thanksgiving (week)!

Adventure Works brand package now available!

Super short post today.

With the release of an updated test/demo Azure VM for Power BI Report Server on Monday, we thought it made sense to provide a new Adventure Works brand package for everyone to use in their demos/presentations to go along with it. Feel free to download and use with Reporting Services 2016/2017 or Power BI Report Server – https://1drv.ms/u/s!Au6-0xX27UdgnOIapntXPaoPcqqOWQ

Thanks!

Introduction

Step-by-Step Guide to Create an RDL File with ChatGPT Assistance

Conclusion

Share this:

Conclusion

Share this:

Share this:

Share this:

Share this:

Share this:

Paginated Reports in Power BI

Power BI Report Server/SSRS

Share this:

Share this:

Share this:

Share this: