How to create paginated report using Power BI dataset optimized for report server

[tmhalila]

Hi everyone,

I am required to create a report that can be exported in either CSV, Excel, or PDF. I usually achieve this by using paginated reports where the source is an external database.

For the current scenario, I have a lot of transformations in Power Query. Hence, I need to use the Power BI dataset as a data source for my paginated report instead of using an external database. 

Is there a work around to do this in the Power BI report server?

[Ankur04]

Hi @tmhalila ,

I read your response below regarding your organisation does not use Power BI service. If you have this requirement you can consider involving SSAS model and then create Power BI report as well as Paginaged report from SSAS model. this will help you reduce the efforts as well as one model can be used for multiple purpose.

Thanks,

Ankur

[v-yohua-msft]

Hi, @tmhalila 

You can start by publishing a report with a lot of transformations in Power Query to the Power BI service.

Then open Power BI Report Builder. Sign in to your Power BI account. In the report data pane, select New > Power BI semantic model connection. Search for and select the Power BI dataset you want to use.

Use Report Builder to design your reports. You can add elements such as tables, matrices, charts, etc., as needed.

Once you've designed your report, you can export it directly from Report Builder to a Lux, Exel, or Pudfu format. 

Or publish to a Power BI report server.

You can learn more from the following link:

Create a paginated report based on a Power BI shared dataset - Power BI | Microsoft Learn

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tmhalila · Accepted solution]

Hi @v-yohua-msft 

Thank you for the solution! However, our organization doesn't use Power BI Service at all. I discovered from Microsoft's documentation that the Power BI Report server can't utilize the Power BI dataset. Therefore, I have decided to redo all the transformations in the database using SQL.

[luisrh]

I am making some assumptions here in my answer.   If your data source is able to be accessed from within Power BI Report Server thru a database connection, the best way to do this is to use materialized views on your source.   Essentially that layer becomes your transformation layer.  Some of those views can be created through stored procedures, etc.   I assume your need is to use PBI Report server hosted on premises or at Azure or AWS.   Again, hope this was somewhat helpful.  I didn't really get a clear picture of the requirements, thus the assumptions.    

Regards.