How To Create A Paginated Report In Power BI Service From A Published Data Set

[Music] thank you in this video I'm going to show you how to create a paginated report in power bi service from a data set that you may have published already so I have a workspace where I have a data set called management insights SB which is just a plain power bi report so if you looked we've got our data set here and we've got a report we are going to move over to these three dots here and click on here and create paginated report and this will build us a paginated report based on the tables and data that we have in our in our power bi data set it it's a start but it can be quite limited so I'm going to show you how to create it and then how to deal with some of the limitations so the first thing that you can see here is we've got filters where we can apply filters and this is where we will build our report so what we're going to do is we're going to take the year from our um from our data set and then we are going to add in a product so these are all tables that are linked in our data set and then I might want to know the customer name and then I need to have a measure and for this I'm going to use total sales which is here so you can see as as the date is being put in we get our table based on um the information we've popped in and there is is some functionality where you can actually move some of the rows and um you can you can get it to look slightly different so here if we click on the format button which is the same in power bi data set there are different styles that we can apply to um to our data but we can't actually change anything all we can do is just apply these these particular um styles if we look at editing we can see here we can move from page to page it's a little bit slow but it shows you the different pages you can either edit it or you can have a look at it and view it when you would actually save it our table format it gives you the option to include the totals and if you're not going to do anything to the report at all then you can leave the totals in there but I'm going to show you how how we can change our report you can view in print layout or in web layout just to get an idea of what it looks like and if we go to the home page you can see that there is the option to move through the different pages of the table so we've got 14 or 14 or you can export to Excel or to a PDF CSV and all the different options are there for you to export so this is a really good start but it is quite Limited in that you can't actually apply you can't have a parameter here so that you can't filter and you can't sort and you can't you can't change any formatting but what you can do is you can save it and then you can download the report so I'm just going to call it save and I'm going to call it um stimmer sales report and then I'm going to download the report and you see your report will end up here and it's your um your your report Builder report if you click on it now what it will do is it will open up Microsoft report builder for you if you haven't got report Builder you will need to download it and the download page is here they've just I've just downloaded a new one so there are changes being made to report Builder so this is us we're in report Builder and you can see within report Builder we have our data source which is linked to our Management in insights data set so in report Builder a data set is a data source but a data set is a query and this is our query here where we've got our dates our products our customer our key measures and our total row so if I run this report now you'll see it will it will run and it will show us what the report will look like then it should look pretty much the same as we had in um in the service so if we click back on design in design view we're able to apply parameters and filters that we can't apply in the service but what I'd like to do is I'd like to be able to apply a parameter of year to this report so I can only show specific years as as a starting point so if we click here I'm clicking on the tablets this is the tablets which is the actual table the data range that is holding the reports that we have and we're going to want to filter this rather than filtering the whole data set we're going to filter this table to be able to create a filter what we need to do is we need to create a like a bespoke query which will just show us the dates to do this if we click on data set we're going to add a data set our data source is management insights and if we go to query designer it will show us all of the fields and the tables that we had before in our um our power bi data set so I've just pulled year in and if I click execute the query you'll see it gives me the distinct values within the year table and you can see it's an evaluate summarize columns so if I press ok and that gives me data set 2 which is the year in parameters if I right click and add a parameter the parameter I want to call it year The Prompt is here and the available values that I want I want to get them from the query because we've just created our query and actually I should have called it year and our value field is here and our label field is here so we press and we press that and you can see it's called at Year and that puts a parameter here what we want to do is we want this parameter to now filter this data region so with my tablet selected I've clicked on here you want to go to this top left and then click on tablets properties and I wouldn't have a filter here where I'm going to add in my year parameter so my filter is on date year it's text and it's going to be equals and the value that I want it to equal is this parameter and we've only got one so I'm basically saying look at the parameter for year and then make this tablets equal to whatever is in that parameter so we can press ok we can also put some sorting in so we can change our sorting options and perhaps we might want to sort by customer name so we can have it A to Z and we press ok now this is going to fail and I'll show you why it's going to fail in a moment so if we press run it's gonna fail to preview and the reason it's going to fail is because what we're trying to do is to apply a filter to our table of data but this aggregate function which is our total can't be filtered because that's already been defined so if we go back to our design view what we do is we get rid of it and then what we can do click out and then click in is we can create our own expression so if we go into here our common functions is an aggregate let me press sum and the field that we want to sum is our total sales so we said what we've got is a dynamic calculation which will change based upon um whatever it is we choose so if we press run we can see now if we press 2015 we can click on view report and it will show us everything for 2015 in the products by that particular customer name let's just sort out this total sales so we've got a little bit of formatting so when we click on our total sales what we want to do is we want to make it a number to make a currency text box properties number let's make a currency press ok and of course we can we can apply some filtering if we want to make it make the rows look better um add some formatting to the rows over here but we can have a background color if we want to have a background color we could make it purple and then our font could be White and run select our value let's do 2016 this time and click view report and we've got a nice a nice little report where we can actually filter by the different years and we can do the same by product name and by customer you'll want to check the print layout so you can have a look to see what it looks like and of course you would measure so that you would have your report clearly in the middle and you could change your column sizes so how you can take your power bi report from your power bi service report um we've taken our table and what we've done is we have created another table we've enabled filtering to happen and we've added some basic formatting so now if I publish this will publish it right back to Power bi service so we'll go to our paginated report and notice it's it's not premium with filters and publish and let's open it yeah put in 2016. view the report and you can see we've got the year we've got the product we've got the customer name and we've got the total sales so I hope you found that useful it's um I'm excited to see how paginated reports are going to evolve within power bi service I hope you enjoyed this video have a good day bye hey everyone Thanks for tuning in to Enterprise DNA TV if you enjoyed the contents covered in this particular tutorial please throw the video a like it really helps us and we really appreciate it also don't forget to subscribe to the Enterprise DNA TV channel we have a huge amount of content coming out all the time from myself and a range of content creators all dedicated to improving the way that you use power bi and the Power Platform lastly check out Enterprise DNA's website Plenty of resources and further learning that you can access very easily all the best take care [Music] [Music]

Channel: Enterprise DNA

Views: 31,114

Keywords: Power BI, Power BI DAX, Enterprise DNA, Power BI Tutorial, Power BI tutorial for beginners, DAX Tutorial, DAX tutorial for beginners, power BI desktop, power query for power bi, learn power bi, learn dax, learn Power Query, M language, Power Query M code, Sam McKay, Power BI Service, Power BI Paginated Report, Microsoft Report Builder

Id: MW_p-OMChcI

Length: 13min 42sec (822 seconds)

Published: Wed Apr 12 2023