Create a BASIC SALES REPORT with PAGINATED REPORTS in Power BI // A Step by Step Guide

in this video I'm going to show you how you can use some of the basic visualizations available to you within paginated reports in parbi we're going to go through it step by step together from scratch so you can follow along as well all of that and more so without further Ado let's get started hi my name is Fernand and welcome to the solution abroad YouTube channel breakover tips tricks and best practices when working with powerbi I upload new videos every week so make sure you hit that subscribe button and the bell icon to get notified when a new one is out pated reports in parba lets you create reports that is intended to fit in a page for printing and there are plenty of use cases in which you might want to do this so today we're going to go through how you can create a simple sales reports uh using paginated reports using basic visuals so we're going to start here from the report Builder which is basically a tool that lets you create your paginated reports so think of it as the harbi desktop where you would typically create your interactive reports but in this case we're using report Builder so this is something that you can download from your powerbi service quite easily so to start uh we need to set up our report data on the left hand side first of all so we're going to go to data source and create a new data source from here going to name this one Northwind and we're going to leave it to a SQL Server because that's where we're putting our data from uh you might have your data somewhere else or if you don't have any data you can just simply select enter data and that will let you import your own data from from scratch so but for now we're going to leave it to SQL Server going to hit build so that we don't have to type or write the connection string ourselves I'm going to type Local Host on the server which is because my server or my database is hosted locally and then we're going to choose Northwind which is the database name that I want to use so if you hit okay and test connection just make sure that it's successfully connected and that's your data source done the second thing is we need to create a data set here for the information that we want to visualize in our report so what I'm visualizing is a sales reports that lets you see or each country that you select it will give you a report of you know how how much in sales did they make which categories of products are the highest or lowest and a line chart that shows us basically the trend of of those sales and when they happen across the dates that we have um in our order dates so we're going to name this one orders and we'll use the Northwind data source that we have just um created and then query designer will let us choose the tables and columns that we want to bring in it's important to only bring the ones or the columns that we need just to keep our report you know optimized so we're going to go to the tables here we look for a few things we're going to look for the category name because we need that so just click that that under customers we want the region or the country this is the value that we will need to filter later let me just think if there's anything else here under not under employees no under order details we want the unit price and quantity we need to calculate the total sales based on that the on the orders we need the order dates to visualize when they're ordered so we can show it in a line chart the products product name name if we need it and I believe that's it so I think this should be everything that we need we're just going to click autod detect here and that's because the relationships are already set up in our SQL Server so we don't really need to create those manually so I just click autod detect and it just finds or tries to find a relationship uh between these columns across all of these different tables that we we have selected from so we're going to hit okay as you can see it's created our select statement here we'll hit okay and that bit is done so there's nothing here yet in the reports we're just setting up our data for now so let's create another data set which is what we're going to use for our parameter we're going to call this one country and North Wind as a data source once again and then from here we'll simply just go to the customer country and just import that one by itself hit okay and then we'll go to parameters add parameter P this one country all this one country under the available values this is the drop down that we will find on the parameter we'll just go get values from a query take it from the country the value and the label to be just the country and then that's it so now if you run the report will be empty but you should be able to select a country from the list so if you report there's no nothing there except the the time and date that you run that report which we're going to going to customize in a little bit um we also need to change a few things here before we jump into the reported itself the first is on the data set properties of the orders we just want to make sure that it's being filtered by the um the parameter so we're going to choose the parameter country here so if the country in the parameter is equals to the country in the orders table so only show those orders basically but if you hit okay that should be done and uh last thing I just want to create a new calculated field here which will be just sales and I just want to calculate unit price multiplied by quantity like this hit okay so just see make sure that there are no errors y okay so now let's customize this report a little bit uh let's remove this title here and let's add it well we need that title for the page but we want to put it in a header just so that when the reports uh and if it does do a page break the header is always there let's insert an image which will be the logo so import R recordings all files ah here we go so we'll just import this logo we're just going to use the default logo Microsoft logo just as an example I'm going to insert a text box here which will be the name of the report uh sales report and we're going to put the name of the country that we want to put we want to report on so let's see maybe it's better if we just do it like this and make the font slightly bigger like something like this and one thing that we want to make sure of is this part to be dynamic so to do that we're going to create a expression so we're going to go text properties well actually maybe not text properties let's delete that and insert a placeholder the label will be it doesn't really matter I think country is fine and the value needs to be whatever is in the country field so that should be okay so now if we run this report we select one of these countries as you can see it's giving us the name of that country also just notice that in this drop down it's m it's replicating the countries which actually is not something that I want so I'm just going to go back to the country properties here on the data sets um I think if I just do s s distinct and then add an order by country I'm hoping this will be okay yeah so let's see if that works as you can see now it's giving us a country for just a unique list of the country and it's sorted alphabetically which is what I wanted so that you can easily find the country that you're looking for so if you select a country hit view it will just give you why does it just give me oh okay not sure why perhaps I need to make sure that the fields are here N Field country oh I see I've messed this one up as well so it needs to be value the drop down needs to be the country from the orders data set so that way it's being filtered depending on what's being selected in the parameter so now if that should work so not true Argentina be France you view report should give you the France information perfect so now you saw how easy it was for me to add a dynamic expression on a text box on our title we want to try to use that to visualize let's say a kpi card to show or give us a total of the total sales which is basically one of the more basic things that you would add in a typical powerb report so we're going to do it by inserting a text box like this going to add the value and then total celles we want at the bottom now we want this in the middle and we want the value to be somewhat big like this just make some space like this and then we're going to simply just replace this with the total sales so to do that it's going to right click create placeholder again label um sales and the value is it needs to be the sales here but instead of first it needs to be a sum because it needs to be an aggregate of the values in this sales column so if we hit run there we go so it's giving us the total sales value for that uh country so how much have they sold so I don't like this formatting so we're just going to change this up a little bit so right click under placeholder properties now numbers we're going to go to currency remove the decimal places add a comma separator and that should be fine I'm just going to make this slightly bigger just so that if the value is too big it will not uh go to the next next line so let's hit preview again and here we go so that looks a little bit better now it gives us total sales using the pound sign having a comma and it just gives us the value that we are looking for next let's see if we can add a bar chart here that gives us a breakdown of how much were sold across the different product categories to do that we can go and hit insert here and from here insert a chart and to make it easy for us we're just going to choose chart wizard um which will give us some UI to kind of choose and select from we're going to choose the data sets the orders data sets to work with from here and we want to add a bar chart like this in the category section we want to add the category name and then on the values we just want to add the total sales so let's keep it simple like this and hit next it just give us a preview and then we'll hit finish so you might want to customize this a little bit more there are a lot of options available to you so a few things maybe and in powerbi um you have the on object interaction which works similarly here in the paginated report so you can just simply select the visual that you're you want to customize then click the elements that you want to change so in this case I click this Legend here and instead of maybe I just want to delete the legend because there are no other categories that I have so that's going to be redundant then on the chart title I'm just going to call this one categories or product categories then if you hit run and choose a country here we go so it gives us a breakdown of the total sales by all of these different categories pretty easy okay so now that you know how to add a bar chart and a kind of kpi card chart let's see if we can add the line charts to show these sales and how they fluctuate over time so to do that we're going to try to use a line chart so under insert we're going to go to chart once again and hit the chart wizard choose orders hit next choose line hit next and then from here we're going to add on our series the order dates and then on the values we're just going to Simply use the sales so I believe this should be it order date yep that should be fine or maybe now the series needs to be ordered date I that right feel like I'm doing something wrong here okay let's let's just preview this and see if this is correct so let's drag this down the bottom here just make some space for it hit run choose a country ah okay so that's that is correct so it gives you for every single uh dates that we have how much of those sales are being sold so you might want to customize this a little bit further so either change the axis so in this case maybe a good one to change would be the bottom part so you can like before just click that Legend right click change the horizontal axis properties instead of category just use scaler that will just convert this into something that is easier to read so if you hit run choose another country and here you go so that's a lot more easier to kind of read because it's in a series type line chart if you want to learn more about paginated reports and how they work I covered this in previous videos the most recent one was about creating invoices so if you want to learn more about how you can Implement that go check out that video thanks for watching as usual give this video a like if you found it useful give it a dislike if you didn't so not to do better for next time ask your questions in the comment section box below so I can help you and you can help others if you like this video we have a patron page where you can support the channel and get exclusive perks like Early Access demo files and credits at the end of these videos thanks again for watching and see you in the next one bye-bye

Channel: Solutions Abroad

Views: 3,214

Keywords: solutions abroad, power bi, powerbi, power bi tutorials, power bi for beginners, beginners guide to power bi, data analytics, dax, data modelling, data visualisation, business intelligence, how to power bi, power bi how to, power bi best practices, power bi tips and tricks, power bi standards, power bi patterns, power bi help, power bi tips, power bi 2023, paginated reports, power bi paginated reports, report builder, ssrs, power bi report, paginated report

Id: NA66yUw1L4Q

Length: 15min 37sec (937 seconds)

Published: Mon Oct 23 2023