CREATE INVOICES DYNAMICALLY using PAGINATED REPORTS in Power BI // Beginners Guide to Power BI

in this video I want to show you how you can create simple invoices using report builder in order to create paginated reports in harbi we're going to go through the basics of how you can import your data how to format your pages and how you can add parameters so that you can select your customers and generate individual unique invoices for them all of that and more so without further Ado let's get started hi my name is fernan and welcome to the solutions abroad YouTube channel or Rover tips tricks and best practices when working with powerbi 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 so I covered paginated reports recently and it got a lot of attention and I got a lot of requests to cover paginated reports some more so today I want to do that however instead of going through the basics of everything with regards to the report Builder and paginated reports I want it to be a bit more practical So today we're going to go through a real life scenario of of creating invoices for your customers coming from a database so we have a database that I have so we have a local database for our Northwind Traders installed in my machine and that's what we're going to use to create our invoices in this case we already have a bunch of data in tables or in in the database we just want to be able to create and generate invoices in a printable format so we can print them off and send them to our customers and this kind of requirement is where paginated reports really shine so just to give you an idea of what we're going to try and build today so this is what the report needs to look like in the report Builder so you have the parameter here that lets you select the customer that you want to look at so if we select one and we hit view report you will see that the invoice updates by itself automatically it updates the invoice number which needs to come from the database as well as the dates for that invoice our information and the customer information as well so their address and here at the bottom is the invoice list so all of the items that they that were invoicing the breakdown as well as the total at the bottom here as well as some Bank information that we might want to include for them to pay into so this is a typical invoice that you might want to build it might look a little bit different but the formatting is not too important it's to build the kind of elements that are more important specifically the dynamic elements so what we're going to do is we're going to start with an empty one so we're going to start by opening up report Builder which if you haven't installed it yet it's easily available and it's now available for pro users as well so think of report Builder as kind of the parbi desktop version but for paginated reports so it's a software that you need to install in your local machine in order for you to build paginated reports if you don't know how to get started with report Builder and paginated reports in general I did cover it in in that previous video that I mentioned earlier so check it out if he wants to start there but I think you should be able to just follow along with me in this video as long as you install the report Builder so here we are in the report Builder and we have an empty report here we have nothing in the page here except this execution time expression so if you hit run you will just be shown an empty report which is the time and date of when I'm running this report as of so we don't really need that we're going to move that later but the first thing that you need to do in the report Builder is to import your data source so this is where your data sets will come from and to do that is actually pretty simple so you're going to go to data source here and hit add data source now we're going to name this one Northwind keep it simple here and you can create and choose the connection type here so our data comes from a SQL Server so we'll leave it as it is however if you don't have a database to tap into and it's just an Excel file you can just simply copy and paste that data directly into your report Builder to report off of it and for that option you'll need to just use enter data it's super simple but in the future I'll try to cover how you can do that quite easily so here you can type the connection string or what I prefer to do is just hit the build here and then we're going to type Local Host that's because it's in my local database and from here we're going to choose Northwind because this is the database that where our data tables are and just make sure you your connection is okay so just test connection if that says is exceeded then you're pretty much good to go and uh we'll just hit okay so now you've imported your very first data source into the report Builder next we'll bring in a few tables and columns from this data source so for an invoice we need you know some order information obviously and we need the customer information because we're going to build the invoices to them so we're going to do that from the data set here so we're going to hit add data set we're going to name this one first of all we're just going to compile the one for orders we're going to choose the Northwind data source that we have just created and then from here you can type your own SQL query if you're familiar with it however if you're not familiar with it it's actually pretty s it's much more simpler if you click the query designer here at the bottom it will just give you a UI to work with essentially to build your SQL statements so let's have a look at the tables that we have available for us so we have a few things here that we need to get so we need the category name for example because we want to group or rather I want to group my products by categories I want to show the product name I need the unit price well we're going to get the unit price and quantity from the so orders we need customer ID because we need to link that back to the parameter I will show you why we need that later and then on the order details we need what do we need we need the order ID unit price quantity I think let me just think yeah so order dates we're going to use the order ID and Order date for the invoice title but typically you would need to have an invoice number or the invoice date so but for now just to keep it Dynamic we're going to just use the order date the earliest order date as the invoice date so we just need to make sure I think to hit the autod detect here just so that the relationships across all of these tables are automatically detected and you know for this database it's it shouldn't be a problem for us to just do that and then we just hit okay and you'll see that it's autop populated the the SQL query for us so this is going to be our orders table here if we hit okay so here we go so you've imported a data sets now with a table with just the columns that you need now it's important for efficiency sake to only import the data that you're going to use to visualize or filter your reports so as you can see like we were getting data from various tables but we're not importing everything we're just importing the ones that we need because that's sort of more efficient you know the less data that you have in your data sets the less data that the data set needs to pull from from the data source so we need another one for the customers so we're going to click add data sets again going to name this one customers and again we'll select Northwind and then we'll select query designer from here we're going to go to the customers did I get customer ID from the orders I need to to create that it's okay so for now we're going to get customer ID company name address City post code and Country as well as a phone number cuz that's the minimum that we need to add in our invoice so we'll hit okay cuz it all coming from the same table shouldn't be any complications there and here we go one thing you just need to make sure that the customer ID is there because this is we're going to make a relationship to this or filter them by customer ID later onwards so the last thing that we need to do here is to just create the calculation where we typically create the total amounts the sales amount instead of doing it as a measure we can create this as a calculated field so under orders if you right click on that you should have this option adds calculated fields which will open up this section here the fields we're going to call this one amounts and then the field Source we're just going to click expression here which lets us add some values or calculations or some sort of operators but for this one we're just going to keep it simple so it's unit price multiplied by quantity which hopefully that should work now that we've configured the data to start with let's start to customize how we are visualizing the actual invoice page so there are a few sections here that I don't want like for example this title here I'm just going to delete that as well as the execution time the only thing that I'm going to do is insert insert a header so we have a header in the footer so these two are static on every page that means that even if your a sales table expands or page breaks into another page it will always show the elements within these sections so this is a good place to add for example your logos your invoice details like who you are and who who you're invoicing to or your bank details you might want to have them on every single page in your paginated reports so we've just added the header here so we're going to insert an image and for this one I'm just going to name this one logo and um we're going to import the data import the logo so for this one I'm just going to use the Microsoft logo just for this demo make it slightly like this and then we're going to insert a text box at the top here so we're going to just pull this one in Force number one 2 three and then invoice and then we're just going to add the date date here 12 of Jan 2023 okay so let's have a look and preview this so this is how your report looks like so far so nothing too crazy right so let's start by customizing this a little bit to make it look a little bit more like an invoice so first let's just make these a little bit bigger make the dates slightly smaller like this and then bold this one out and then we're going to add some Expressions to replace some of these values so this invoice number for example we want to just get it from the order number and for this one we're just going to look for the order ID just drag it into there as well as the dates so the dates we're going to use the order dates simply by just dragging it like that now it's in a text box and it's not in a sort of any scope what we call it so at the moment the text box doesn't know where that data is coming coming from so we need to be explicit with that as well as that it's not aggregated so it doesn't know if it needs to be getting the first value from that list or the last value or it needs to be summed so we're going to need to say that or apply those ourselves the first thing is we're just going to need to change the summarization to look for the first it's going to make this slightly bigger and then for the order date also get the first value and then another thing that we just need to make sure I'm just going to go and click expression here is um to specify which data set it's coming from to be explicit so we're going to say this one is coming from the orders which is going to be the exact same thing that we'll need to do to the dates here as well so right click expression orders so now if you hit run you'll see that it will just take the very first value that it can find from the data set nothing too complex but uh the idea is that later on when we have this option to select the customers it will only always take the first value that it will find for that customer so one thing that you might have noticed the dates formats you can actually customize that as well so under placeholder properties you will be able to go to the number and then under dates just choose the format that you want so let's choose this one so if you run that it will just give you the format data that you want perfect so let's expand this header bit here and let's insert another text box CU we need two text boxes one for from and one for to and to just keep it simple and just to speed up this process I'm just going to copy the data from this template that I've already created here going to open up your report Builder here we are so where it's coming from is always going to be the same but we want the one where it's going to to be a little bit or different so for that one we need to get the company information of who we are sending it to so let's start replacing this with the data from our customers area so company name we want address we want City we want country and post code we can add the phone number too but I think you get the idea so the only thing like before we just need to make sure to be explicit with what we want to do with these so we'll just summarize them by getting the first and then we just need to go on each one of them and just make sure that we are referencing the data sets that they're coming from so the customers like this okay so if you run that you should be able to get or see the first the first customer in that list so we're just going to make sure just give it a bit more space like this and uh yeah that's it so now let's customize the footer which will be basically the same thing we just want to add this whole bit at the bottom make a bit of space there insert a text box and we're pretty much ready to work on the middle bit which is the the table Matrix format that we want to show so for this one you can go to insert and you will see this option here table now you can insert a an empty table and start from scratch but then easier way is to just use the table wizard so the table wizard lets you choose which data set you want to build the table from and if you hit next here we just chose the order so it will give you all the available fields in that data sets we have some options here to build this table so for now what we're going to do is just drag in the category name in our Ro groups because we want them grouped by row and then we want the product name in the values uh unit price quantity and the amount all right then from here it will just give you a preview of how that will look like I don't want the Plus or minuses so I just want to untick expand and collapse groups so it will just give me the full breakdown of the groups along with the products within those categories and we'll leave everything else as it is and we hit next this is the final preview that's fine and then we'll just hit finish now it's created a nice table for us here if we hit run you will see that it's pretty much ready to go like the only thing that we need to do is obviously to just update the parameters but we I would like to update some of these like for example the formatting of these numbers which is actually pretty easily done so the if I just check the unit price and the total amount has these decimal points so I want to add and change those formats so we're just going to do the same thing that we did for the headers so under placeholder properties number we're going to go currency and then we're just going to add the comma separators we'll just do that for all of these so number currency comma separators oh this one needs to be placeholder properties currency commas the same thing here so now if you preview this if we hit run as you can see now it gives you you those groupings and it shows you the unit price and quantity in the right format as you can see if we go to page two it's giving us you know obviously more and more pages but even if you go to the next Pages it will always show you the header and the footer which is exactly what we want now the last thing that we need to do is to add this parameter for our customers so that we are able to filter this data to only show us the the orders by customers so what we're going to do first of all is we need to create a new data set so this is the data set that we will use for give you the ability to filter the the page using a parameter we're going to just call this one customer parameter use the north wind as our data source in the query designer it's simply just the customer table we just need to bring in two things the customer ID and the company name so this will be the fields that we will need to use for the parameter okay so no spaces on the names so we're just going to remove the space so now that that data set is set up we're just going to go on the parameter section here and create a new parameter we're going to call this one customer uh select a customer this is the The View that your your users will be able to see an available values we're going to get it from a query which we've already created as you saw customer parameter the value is customer ID but what it's showing on the parameter is the company name so we'll hit okay there and then obviously we want to filter the whole page so we need to filter the data sets with the parameters so cuz that you will see that if I just do it as it is now you'll see the parameter is available you'll be able to select something here but the values won't really update because we haven't really linked it to anything else as you can see that's not changing so we just need to make sure that it's hooked up to the data sets themselves so under the data set orders we're just going to go to data set properties and here under filters we're going to add a filter here to say if the customer ID is equals to the value from the parameter so the customer ID that the parameter is selected is it equal to the customer ID from my table and that will filter the orders table need to do the same thing for the customers table so data set properties filters adds an expression customer ID is equals to that value in our parameter hit okay and that's pretty much it so let's select a customer here hit view as you can see everything is changing so the invoice number is changing the date is changing who is going to as well as their kind of address as well as the date underneath if I choose someone else view report there we go as you can see you can now easily create simple invoices using uh the report Builder and that's really it for this video I hope that sort of demystifies how you can kind of use report builder in a real world sense rather than just showing you how and you know what other functionalities are available in the kind of report Builder or paginated reports thanks for watching as usual give this video a like if you found it useful give it a dislike if you didn't so I know 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 patreon 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: 7,665

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, ssrs, power bi paginated reports, power bi invoice, report builder, ssrs invoice, power bi dynamic

Id: hafca1MPDwI

Length: 21min 17sec (1277 seconds)

Published: Mon Oct 09 2023