Row Groups and Column Groups in Paginated Reports in Power BI (7/20) | SSRS Tutorial

welcome to analytics with nags this is another  video in the page noted reports where i am going   to talk about row groups and column groups in page  nutrient reports if you can see from the bottom   here you have row groups and column groups once  you started designing your page needed reports   you will find something when you have table  visual in your chats i mean in the page noted   reports you will find some columns added when  you keep adding the records here right so how   what is the purpose of it and when to use it that  is what we are going to see as part of this video   if you're new to this channel hit the subscribe  button and press the bell icon for notification   this channel contains sound 5000 inr with  contents which covers msbi power bi sql server   so please utilize this content freely available  to you and share with your friends and colleagues   with this note let us begin our today's topic  so i'm in page noted reports so i'm going to   create a simple region and product category  reports let's try to add a data source i will   quickly add it we are already familiar with  how to add such a data source here so let me   quickly do it for you so this is your data source  and i will add a data set here i will try to   choose the data source from here whatever created  previously just now a few minutes back now   i need to choose the what are my data sets as  i indicated in the report it is sales report by   region as well as the product category so i need  all those things so let us try to get the quantity   order quantity and the sales amount from the  fact only these two things i needed from here and   from product i have a product name let's get the  product name from here and the product subcategory   english product subcategory and product category  name and then as i said i need the region i have   this region from the sales territory i will bring  region country and group okay and also i would   like to get some year so let us go for calendar  year now from the dim date so these are the fields   i need other quantity product category subcategory  region country and group so these are my fields i   require for this report let's try enter and as  we see in our previous issues when you choose   the date the date is related three times in  the fact table so all the columns are mapped   so we will face issues so try to remove  it otherwise you will not get the reports   right so only two only one join that is only based  on the sales order date other two joins you can   remove it just click on it okay i'm not doing  any naming for that i mean just i can say like   sales okay i'm doing it very quick because these  are the things preliminary things that we have   already discussed so we have a data set now let  us try to understand this row book row groups   and column groups so let me bring this table  here and as soon as you drag and drop you see   the details figure what i'm trying to say is like  let us try to add some values over here category   and region and i will say order quantity  now you can see there is no column groups or   row groups only in the row groups we have  something called details the three lines   over here which indicates like whatever you got  in this data set say for example thousand rows   all those thousand rows will be displayed as part  of this table that is what it means so when you   click on run and see the reports you can see the  report keep goes on for different pages because   it is designed like a details this is what  not i want i want to build something like a   group group of report like i want if the if  there is five countries i want to display five   countries in a row and if there are three product  category three product category in the column   just like a pivot iron matrix table so either you  can do the table or matrix using both i will show   the table using table um visual how you can do it  and similar results you can achieve using a matrix   as well so table so basically uh to uh to give a  short explanation these row groups column groups   use it to group by a fewer columns in your data  set to display the results that is what it will do   now i don't want uh the details anymore and  before doing that i need to choose like the   details i don't want uh now so i can uh del uh  before deleting i can choose the data set and i   can assign a data set to this table so where i can  assign it you can once you choose this tablex here   right you can have data set name okay once you  come here you have data set only single data   set that's why it's appearing sales you map the  data set name to the in general tab first thing   is data set name once you click on the table  then it will appear you see ensure you are   touch the table here then i don't want a table  anymore just click delete rows okay then here   coming to you want country wise data right  uh i will have a group first then followed   by a country so i want instead of adding directly  the sales territory group right click and do the   ad group i want row groups which is nothing but  your group by so i want the parent group first so   before first group what you want i want the parent  group as the group the sales territory group okay   when i press ok then it is fine on the next column  what i want i want the country so here next again   in the row group you say like i know art group  you say like child group so child group will sit   under the parent group so now once i add you can  able to understand child group i want the country   okay now you see here one level down and  also you can see from the diagram instead of   three lines okay earlier in details  three lines up here now it was like   uh open parenthesis like over here so we have  two groups here one is sales territory group   and then one for the country we have added it  and also i can add the total for these groups so   like add total after so either you can do after or  before i will do the after once you keep like this   then you will find something like uh you will  have only one entry for this group underneath   if you have three countries it will display so  like this it will display for you so this is what   your row group first you need to add a parent  group underneath you will have a child group   then similarly let's try to run this report  without having anything and we will see how   it looks now you can see the report looks like  this you have a group europe north america pacific   underneath you have the different countries so  you do not map anything over here either you can remove these columns without the column groups  you can still hold the um same report by mapping   directly the order quantity or sales amount  whatever it is but i want to show you the   row column groups as well how it looks like now  when you right click on it you can go to ad group   in the column group either you can add again the  row group or column group i want to do the column   group now so there is no column group earlier that  is why only parent group appearing first you need   to add parent group then only you can go for a  child group so once you have a parent group for   the column group in this i want to see different  product category okay once i had that one you can   see the product category started appearing here at  the top that will be map already then what values   you want to show so once you click on it you can  see like i want to see the order quantity i don't   want to go for order amount now let us see like  how it appears now once you added it here let's   try to run the report and see how it behaves  now you can see from the diagram this is the   parent group for the row group and this is the  child group for the row group okay two things   appear and this is the parent group only only  one group available in the column group that is   uh product category so these three appear what  is these two blank rows you can see these two   blank rows appeared so basically once you design  your report how it will expand based on the data   after expanding there are two blank rows so  that also appeared so this is how you need to   imagine okay adding the column and row groups  and in this you want to add some summation or   average of those things you can still add it no  problem with that so this is how you need to add   now in this i will again add some kind of child  group here let us try to add it so i will add um   over here like child group i  will say like um instead of   product category i will say calendar year okay  for each year how much it has appeared so this   is the child group let us try to see how it  looks like now now you can see from the diagram   in the accessories below each year you got the  data so this is how you can add the child and column groups the parent groups and child groups  so now let us quickly format uh these records   you can press the shift ctrl i will say blue  bold and for this also i'll say blue bold and i will give some color to this  um over here let's go for hello sim   and for this i will say like um light  blue let's see how it looks like you can see from the diagram this is looking   just a little neat like you can still have  a ability to format for this also you can   choose the same color so that it will  appear properly and you can also name it so now you can see from this you have  a total and for the total you can have   different colors if you want and this  is how you can able to configure your   row groups and column groups basically you  need to do the group by of your tables and   i can still have the same results using the  matrix by default i hope the matrix is having the   your what you call the row group and column  group let's try to expand this report and then   let's see how it looks now you see by  default in the table visual you have   three lines by default matrix is part of you  already had two groups over here that's why   when you have a matrix visual uh you already  uh choose one column here the same column   of a we will repeat here you will get some  data in the grouping so similarly we can add   over here what we had category name and how to add  a new column just go here add group child group   then here i will say product group and the country  here okay sales group country same thing and   i need to choose this guy i mean that group then  add total once you add a total so this will appear   once and underneath whatever countries appear  that will appear started appearing here   so let us do the same formatting here  and let's try to add another group   the child group here and i can say calendar year  again and let's try to launch it now and see the   results so you might have asked me why we need  to go for the table or matrix if you can able to   achieve some result i mean wha what why we need  to exactly go for such a things i'm pretty much   not sure i will definitely come back with some  answers but these are the some of the properties   you can think of when to go for a table when to  go for matrix that is different part of the story   i will take a separate video if time permits but  you need to understand what is this row group and   column group when to add how to add it that is the  main purpose of this video if you like this video   hit the thumbs up button and comment below for  queries and do remember that data is your asset

Channel: Analytics with Nags

Views: 22,756

Keywords: paginated reports in power bi, paginated reports power bi report server, paginated reports, power bi reports, power bi report builder, paginated report, ssrs, in power bi, desktop, paginated reports without premium, paginated reports sample, paginated reports tutorial, paginated reports in ssrs, paginated reports embedded, report server, power bi

Id: NPxMcvXIIk0

Length: 14min 59sec (899 seconds)

Published: Mon Feb 14 2022