Power BI Paginated Reports: Visualizing Data

yo what's up this is Patrick from Guyana cube and in this video we're continuing our paginating report series where we're gonna add a data visualization to our report stay tuned okay so we started building our first paginating report in the first video where I talked about adding data sets data sources and parameters which is great right so we kind of laid the foundation because what's a good report without data but now we're gonna add some visualizations a data visualization so you can actually see the report because just having today they're not showing the data it's kind of pointless right okay so in this video I'm gonna show you how to add a matrix and add some functionality to the matrix and then it will talk about expressions and things like that and then we'll continue in subsequent videos to kind of pretty it up alright so you guys know how I like to do instead of all this talking let's do what let's head over to my laptop ok so the first thing you want to do is you know if you're following along you can use a report from the last video if not you can download the starting point from the in the steps below and then click on open it and I'm using the report builder remember in the first video I said you can use Visual Studio or a report builder I mainly continue using report builder click on insert in the ribbon and then you'll see the matrix element there's lots of different visuals here but I'm gonna use this data region call the matrix so click matrix you have a couple of choices you can just insert the matrix you use a wizard typically I don't use Wizards but I like the matrix visitor cook wizard wizard wizard because it gives me a good starting point so I'm gonna go ahead and choose the matrix wizard here choose my d/s country sales if it's if you didn't name them the same and the subsequent video in the previous videos right I just remember use the one that contains more columns than anything right so the stored procedure that I created has you know the month key and sales and quality and stuff like that that's the data set 20 use click Next and then what you want to do is so in the row group so we're gonna add the country and the salesperson and then the value is we're gonna add sales we don't need any column groups right now right click Next how do I want this to look you up three three different options I like the the last option of them all and if I wasn't doing a video I will keep this checkbox labeled expand collapse groups check but I want to show you how to set this up manually so I'm gonna go ahead and uncheck it okay so uncheck that click Next it gives you a preview and click finish there we go alright so I'm gonna pull this down just a little bit so it's not all clumped up like that right so there's my matrix and if I run this you see the parameters so I'll go ahead and choose 2012 choose all countries click view there's my report not the prettiest thing but it's absolutely there so let's start the format this up right so go back into design view alright we're gonna give a little space over here to sales person let's format this to the left a little space let's drop the font of this down just a little bit there we go and then what we're gonna do is I want to underline this give me a nice bowl underline so you see in the ribbon up here in the border I'm gonna choose bottom but then here this is a great thing about paginating reports I have full control over almost every element of this report so what I'm gonna do is I'm gonna come right here to the border width and change it to two there we go and I'm gonna repeat the steps down here for this one too and so I'm gonna say I want the top for this one and then go back to that border width and change it to two oh boy I'm getting excited right and then here I don't want any borders on this so none and then for each one of my salespeople right I want the bottom you see Patrick y'all you're doing so much work I am and what I want is the color right the border color I don't want it to be black I want it to be a very light gray there we go so now let's run this see how our report looks so there's our report will choose 2012 all countries click view nice and pretty you know you know the values here aren't that great but let's let's continue alright so now we have our matrix we kind of have it formatted the way we want and we scroll down you can see there's page 1 there's page 2 right nice but what I want to do is I don't want to show the salespeople when I initially render this report I want to have them hidden and to do that right down here in a row group section right click on the one labeled salesperson go to group properties choose visibility say hi and there's a checkbox labeled display can be toggled by this report item check that box and choose sales country because I want to toggle based on sales country expanding collapse right so I'm going to click OK and before I move on before I show you how this looks what I'm gonna do is click on the one at the top level for sales country the some sales I'm gonna click because I want that bowl and I want to format this you can format these a lot of different ways if you look up in the ribbon there is a ways to format but what I want to do is because I want to do multiple here if you look over here you'll see a section labeled number so let's expand this out right here and right there it says format what I'm gonna do is put currency 0 because I want this to be a currency I don't want any decimal places all right and I'm gonna expand this one out just a little bit there we go and now let's run this report getting excited choose 2012 and all see what we're looking like now that's a nice-looking report I have to say so myself if I expand the United States out you can see there's the individuals in in that particular one right I am excited alright the next thing you want to do that a few more columns here so let's have 4 additional column so I'm going to right click insert to the right so you're just clicking it make sure the header all the little border around the matrix appears and then right click on the one and choose the right and then another one to the right and four more here we'll talk about the expressions and things so let's just let's name them up alright so the first one we're gonna call country percent and I don't like how big these are so let's do all four of these at the same time let's just drop this down to nine one more well there we go something I call country percent overall percent and then we're gonna have one for quota and then we're gonna have one for variance got it so these are my four columns or hey and now what I'm gonna do is in my country percent we're gonna add an expression so we're gonna right click here and go to expressions and it's really nice the first thing we want to do is I'm going to use a function so reporting services has its own expression language and this expression language I wish I could tell you it was just a straightforward but it's kind of like a combination between a couple of language it's not that difficult you just need to get used to it the different functions and things that's available so we're gonna do some the fields so it exposes lots of things here so I'm gonna save some of sales right it's like that and what I want to do is I want to decide divide the sum of sales by the total for all the sales within a given country right the context and so now what I'm gonna do is because some in addition to just accept an expression also has another it accepts another parameter for scope and so I want this to be within the scope of the country that I'm toggling okay and so this would be sales territory country just like that right need to wrap that in double quotes so wrap that in double quotes so basically I'm taking the sales for that I'm taking the total sales for that salesperson and desire dividing it for the country from which that salesperson sells stuff all right the total of sales for that one all right and so you can see here's this some of the some of the sales divided by the sum of the sales for the entire country go ahead and click OK right we'll test this out before we do too much so we'll go here run our report 2013 oh and click view report it's okay it's all one because I put it in the wrong sale but I want you to see how this works right so putting the same sale and it's just one because it's dividing the total by the total the total for that country by the total for the entire country what I'm gonna do is take this expression and move it to exactly where I want it all right which is for each salesperson so I'm going to delete it there and go to expression here and paste my expression there click okay and remember we definitely want to format this so we find our number section and this is a percentage so we're gonna say p2 so I want two places right and so now what I'm gonna do is run this and what you'll see is I'm gonna go ahead and choose 2013 choose all click view and now if I expand United States all right you'll see what percentage each person has within the United States so easy just the piece of cake so let's go ahead and add two more expressions and one more simple value so like the quota for example we have a field in our data set for quota and if you wanted to add it to your matrix all you need to do you see this little icon that appears the feel icon I choose quota and I just click quota for each one and it will automatically add quota multi-select just by clicking on one cell holding shift and pushing up locate my format section this is currency 0 and now if I run my report you can see how my matrix is building out pretty nicely just about adding these different things to it so I click Oh click view look at that right there's my quota and if I expand this out see a hundred percent cuz that's only one person but if I go to na you can see how it breaks it up into those three which should add up to a hundred percent and then United States right I'm gonna go ahead add two more expressions to this report just so you can see how to do it just so you kind of get this repetitive nature going so you'll understand all right so the next one I'm gonna add overall percent it's a pretty easy one I'm just gonna copy it up to the clipboard I'll make sure notepads available in the comments below some kind of text file with something not a notepad like a text file so you can have access to all these expressions that I create so I'm gonna go here and this is my overall percent and so I want this one in the first two so mego expression right pasted you can see that I'm using a different I'm using the different context because for the first one I want the entire data set right I want to see what they're for the overall for every country for the complete total across the entire data set as opposed to just the data set in the at the country level I want this at the data set level all right so that's why I'm specifying the name of my my data set so you can see the s country sales so I'll click OK and that's bolded and then for overall percent one more time bear with me let me grab this on my clipboard there we go and make it this and we'll paste that in there and click OK and again these are percentages one thing you gotta have your equal sign there so when you paste these expressions in make sure you have your equal sign and the tell-tale was I saw some instead of seeing expression there so I'm going to go ahead and choose expression click OK and now if I run this I should get some funky percentages and then if we just format them as p2 you'll see see how it's all funky here if I go back and choose both of these and then go to my format section my number section for format then type in % 2 then rerun it and now you can see my nice percentage if I expand collapse you can see what's going on right there right now I continue to continue this for the variants I actually have a measure for you guys I'm sorry an expression so used to talking about power bi but I have an expression here for this also and so it's pretty straightforward you go here to expressions make sure you have your equal sign in there see how I've deleted it make sure you keep your equal sign and then all you do is repeat this for each one and then since these are percentages we'll call them P 2 and now when we run this and bam there's our perfect report we can do some other things I'm gonna show you how to pretty this up a lot more in the next video I already went wait longer than I wanted to in this video but you guys get it in the next video I'm going to show you how to and some other things to it to make it a little more visual alright alright so you guys if you have any questions comments criticisms whatever they are post them in the comments below this is your first time visiting the channel you know what to do hit that subscribe button and if you like my video a big thumbs up as always from Adam and Patrick thanks for watching see you next video

Channel: Guy in a Cube

Views: 24,923

Keywords: paginated reports, paginated reports in power bi, paginated reports in power bi report server, paginated reports in power bi service, paginated reports in ssrs, power bi paginated reports, paginated report, business analytics, business intelligence, power bi, power bi premium, power bi report server, rdl, report builder, sql server, sql server data tools, sql server reporting services, ssdt, ssrs

Id: gleYD97WScI

Length: 13min 40sec (820 seconds)

Published: Wed Dec 12 2018