Sub Reports in Paginated Reports Power BI (14/20) | SSRS Tutorial

welcome to analytics with nags in this session  we're going to see about sub reports in power bi   page noted reports or power bi report builder what  it mean by sub reports you have one report that is   main report and you have another report the second  report you want to see the contents of the second   report in your first report itself right so  that second report will be embedded in your   main report that is what your sub report concept  conceptus so a lot of people while developing   power bi desktop reports they are same kind of  behavior like this when they choose some parameter   that will pop up a new report or will filter  another report so that is not possible in   power bi desktop reports whereas in power bi  page native reports we can achieve the same   results using sub reports if you are still not  convinced what i'm saying let us jump into demo   if you are new to this channel hit the subscribe  button and press the bell icon for notification   so i have my two reports here one is main  report and another report is called sub reports   so the main report which will show me the product  wise sales amount let it load what meanwhile we   will go and launch our sub report the sub  report will show me the order type on shop   or online order just just to displace it  even it can be anything that can be a big   and there are different scenarios you can go  for a sub report let me cover it up later but i   want to show you what are these two  reports our main report output has came   so now you can see this main report contains  a list of product and my sales amount so   i want my the second report okay this sub report  to be present under this particular report right   the main report now to do that we need to go  and insert our sub report item over here then   configure the sub report here that is as simple  as that before doing that i need to deploy   this main report and sub report in the power bi  services let us do that first let's try to publish as you might aware like we need to have   the premium capacity workspace i'm  deploying it here main report publish that's done and let us go to sub report  and we will do it again as sub report so go back here i will say sub  report awm demo publish once again that's it we are done with  deploying of these two reports   now go to your main report and let's do sub report   drag and drop once you're done click on right  click on the sub report then click on properties   here you have option to choose the subreport  from the services so ensure you deploy it in   the services basically in ssrs you can navigate  to the rdl file or report in the report server but   in case of power bi report builder or paginated  reports you can get it from the power bi services   so i'm just navigating that sub report from here  once you click on it click on open that's it   and if you have any parameters with the sub report  you can configure it right now i do not have any   parameter let us leave it and the second part  of the video i will add some parameters then   configure it let's go by right now here with this  now this is done now what happens once you launch   so this will populate this main report  will populate its data from here   and this sub report will also connect the data  from its own data set that is configured in   your sub report right so let us try to launch  this main report and see what was the result now you can see from this output i mean  let it launch so it takes a little while   so as soon as your main report is populated  underneath you can have your sub report right   so what is the point of having a sub report you  now you might have thought like having the simple   logic over here right the same same  simple report over in the same place   instead of sub report i can add that same  table basically you can do that uh it is like   more or less configuring um your report that there  are different purpose you will go for sub report   basically once you create your main report  the sub report if it is next to that report   then and whatever i designed is very simple right  for simple table you can you will not go for sub   report assume that is very complex logics involved  and you want to make neat design here right   there should not be any for management purpose  the main report will be here and this sub report   can be used for other purposes as well not only  this report the subreport used for this report   this is a common report this is used in report  one and many other reports in those cases you   will create a common report that will be utilized  in many other reports this is purpose number one   and there are some cases like when you choose some  parameter in this report that should be passed to   your first you need to pass it to the subreport  then only this report should be visible kind of   user security all those purposes then you need to  go for your sub report what do you mean by that   basically like um assuming for each product what  let us assume a scenario for each product how   much quantity sold okay that i want to see it  as part of the table here in order to do that   basically i can configure this sub report  adding as part of this table itself okay   this is one way r you can add a parameter to your  main report that parameter can be passed to your   subreport as well there are different scenarios  you can use it let me walk you through one of the   scenario where i will add some parameterized  values in my sub report and that parameter   value will be passed from my main report let us do  that first this report doesn't have any parameter   in order to add a parameter here now when you  click on it the report launches immediately i   need to add a parameter for that i need a data  set so let us try to build a data set so this   is actually the power bi data set so whatever  you query it will be like a dax query you need   to write if you are aware of writing the query you  can write the query here to fetch the distinct of   product name as a parameter for your purpose to  understanding i'm just using the query designer   like over here which will be very easy for you to  um get the dac score if you are not able to write   so it's very simple just drag and drop and then  press ok then your output is done i used only   the sales table so i'm not going for any dimension  right now so just drag and drop product name which   will if you execute the query which will fetch me  the list of um distinct product names so if you   want to see the query just press ok then it will  generate our dax query for us because power bi   data set is internally a tabler model so you see  evaluate summarize column it's just a summarized   columns function okay so this data set is nothing  but um my product list let me name it as a product   list okay this is to get my distinct values  of for my parameters so add parameters   so if you are viewing this for the  very first time you minute about how   why i am doing this because adding parameters  i have separate video just go and watch it   so available values so go to data set  product list value product name label name excuse me so once you are done your  parameter is ready once you run this   now you have ability to choose which particular  product okay report parameter name so based on the   parameter now you can see this will change okay  i'm sorry this is not changing because i need   once you add that parameter you need to filter  your data set here so come over here add filters   then add product name then go back here  so these things i have explained in detail   how you need to configure just watch it out my how  to add parameter video now once you choose any of   the product in your sub reports right you see the  values are changing based on the values you choose   okay this is fine now going back to my main report  now what i need to do here i need to configure   that uh parameters first of all before  doing this first publish this report that is your sub report the first case  whatever i shown you it's very simple   without any parameter nothing just two reports  you will have main and sub reports you are   doing it on your i mean just importing or  embedding your sub report in the main report   now it is little tricky like i've added  some parameterized report in the subreport   and the value for this parameter which will come  from my main report now the sub report is ready   i published now go to subreport properties check  it out the parameters um let us try to reconfigure   from the beginning because once we published  so insert sub report then configure browse so sub report demo click on open there we go  go to parameters use these parameters to run   the sub report okay so you need to add that  parameter so now you see that whatever you   deployed in your subreport this is the name  we deployed report parameter one what value   you need you need the value to be passed thus  first of field's name that is this from here okay   now you see what we have configured how we  are configured this report is able to run   okay with first of product name we are passing one  parameter that is first of product name because   we are not passing anything from this table just  we will try to launch and you will understand what   i'm trying to say once you pass it the main report  will run after that the entire the second report   will be fetched only for the first product that is  chain for this chain or whatever the first value   okay that is passed over here but i want this  is one of the scenario you will pass it okay   either this main report also should contain  parameter that you will choose then one this main   report also filtered for that parameter subreport  also filtered for the parameter that you can do it   now this is passed as first off you can see  from here parameters first off you can see   from this expression first of product name so  that is why it is passed as first value instead   i can have that sub report executed for each  row so you can add column here click on this   add column um insert column  outside the group right okay   here i just drag and drop this sub report  here when you drag and drop what happens you see the magic here for each row the respective  product name is passed so the table also will be   filtered that sub report also will be filtered  these are the different scenarios you will use   your sub reports let us wait for the output how it  becomes now you can see chain is 6 6 and i think   for everything it is passed as a first value  okay instead you can pass this value here   subreport properties parameters product name  so you see now the entire thing is changed   when you put inside the table the parameter  instead of first off you get the product name   okay when you put outside it will be either  first off or maximum value something like that   so when you run this report this time for  each row for each product the sub report is   executed the output is generated within that  column itself so this is another example or   different scenarios you will go for your  sub reports let's wait for the output there you go so now you can see this table for  chain it is 3 comma one and gene's denim six   comma six and there is no offline shop so this  is how the data changes dynamically the report   is filtered so i hope you understand the concept  behind the sub report and there are different ways   you can use the sub report to make your  report dynamically work if you like this video   hit the thumbs up button and comment below for  queries do remember that data is your asset

Channel: Analytics with Nags

Views: 12,614

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: 9Di0DEuurL0

Length: 15min 44sec (944 seconds)

Published: Tue Mar 22 2022