cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Power BI Report Builder- Report having multiple sheets

Hi All,

  Did anyone developed a report using Power BI Report Buider having multiple sheets?. Like in Excel , One Excel file can have multiple sheets and each sheet connect to seperate Oracle Sql query and fetch data in same sheet. For Example, Report will have two sheets , one for Employee details which internally fetch output from oracle sql query (Select * from Employee), and second for Departments details which internally fetch output from second oracle sql query (Select * from Departments) and so on...I know that it is possible in Power BI Desktop but I am looking for anyone who can help me get it done in Power BI Paginated Report Builder.

Thanks,

JitK

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver I
Resolver I

Re: Power BI Report Builder- Report having multiple sheets

Ok, say you have a data like

CompanyKeyQtyKg
A1
A2
B2
B3
C2

and would like to show sum(QtyKg) for each Company in separate pages/sheets

First, add a matrix and design it how you like it.

1.PNG

 

 

 

 

 

 

 

 

 

 

 

 

right click over "CompanyKey" group under "Rows groups" and select "Group properties" and then "Page breaks" tab and click "Between each instance of a group"

2.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

By doing this, your report shows your report as an each group on separate pages. And if you export to excel, on separate sheets. 

But, for excel there is still a problem. sheet names are default to Sheet1, Sheet2.... and son on. To rename those sheets; click on to select "CompanyKey" group under "Rows groups" and then at the right you will see "Properties" pane. Drill to "Group -> Group -> Page Name" property and open expression combo box, then set what ever you want for the expression which will be shown as "Sheet names" in excel.

3.PNG

View solution in original post

8 REPLIES 8
Highlighted
Resolver I
Resolver I

Re: Power BI Report Builder- Report having multiple sheets

First add a report which contains Employee table, then add a sub-report within the same report for Departments. Then add pagebreak between main report (under table/tablix properties) and subreport. Then export to Excel, you will see two seperate sheets.

Highlighted
Frequent Visitor

Re: Power BI Report Builder- Report having multiple sheets

Hi Saglamtimur

 

 Thank you. I checked for the subreport option in power bi report builder and it is disabled for me always. I am using version 15.0.1517.0. Please let me know in which version this option is enabled and appreciate if you provide me step by step guide for creation of subreport which works same way like one report contain multiple sheets.

 

Appreciate your help.

 

Thanks

JitK

 

Highlighted
Resolver I
Resolver I

Re: Power BI Report Builder- Report having multiple sheets

You should design subreport first, and publish powerbi service.

Then, on main report, insert subreport item, right click over greyed out subreport and select "Subreport properties". In general tab you will see "Use this report as a subreport". Then browse your workspace and select previously published subreport. This should work.

 

This is for two or more dataset and exporting to multiple sheets.

 

For single dataset, but multiple sheets, it has different steps.

Highlighted
Frequent Visitor

Re: Power BI Report Builder- Report having multiple sheets

Could you please elaborate more on @For single dataset, but multiple sheets, it has different steps. I am looking for it. Appreciate!

 

Thanks

Highlighted
Resolver I
Resolver I

Re: Power BI Report Builder- Report having multiple sheets

Sure, when I have time.

Did your current problem solved?

Highlighted
Frequent Visitor

Re: Power BI Report Builder- Report having multiple sheets

Not yet.. I am still looking For single dataset, but multiple sheets and that is my current issue. 

 

Thanks

Jitendra

Highlighted
Resolver I
Resolver I

Re: Power BI Report Builder- Report having multiple sheets

Ok, say you have a data like

CompanyKeyQtyKg
A1
A2
B2
B3
C2

and would like to show sum(QtyKg) for each Company in separate pages/sheets

First, add a matrix and design it how you like it.

1.PNG

 

 

 

 

 

 

 

 

 

 

 

 

right click over "CompanyKey" group under "Rows groups" and select "Group properties" and then "Page breaks" tab and click "Between each instance of a group"

2.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

By doing this, your report shows your report as an each group on separate pages. And if you export to excel, on separate sheets. 

But, for excel there is still a problem. sheet names are default to Sheet1, Sheet2.... and son on. To rename those sheets; click on to select "CompanyKey" group under "Rows groups" and then at the right you will see "Properties" pane. Drill to "Group -> Group -> Page Name" property and open expression combo box, then set what ever you want for the expression which will be shown as "Sheet names" in excel.

3.PNG

View solution in original post

Highlighted
Frequent Visitor

Re: Power BI Report Builder- Report having multiple sheets

Thank you much.. This works for me

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors