In this video, we will try to understand how to create a drill down report and how to create sub reports in SSRS. Remember, as I've said previously, we shouldn't be just doing SSIS we shouldn't be just doing SSIS we shouldn't be just doing SSRS we should be concentrating, you know, across all the three things in an equal way. So in this video, we will do SSRS and we will do two things. One is drill down reports and second one is sub reports, or drill down report would look something like this. So you can see over here, you know, you can see this India over here. So when I click on this, I can see the details of it right.

So if you see Nepal again, when I click on this minus sign, you know it just contrast is when I click on the plus sign, it just expands. So basically, this is kind of a report you'd like to do. With SSRS. So how do you go and create such kind of a drill down report in SSRS. Now, in order to go and create a drill down report, we need to first have a report at place write a basic report at place. So if you remember in our previous SSRS lecture, we had created two reports.

One is the manual SQL Server report. So this was a manual report, which we created manually, and we connect it to SQL Server RDBMS. This was a second report, you know, where we created by using the VIX wizard and we connect it to SSIS cube right? So what I'm going to do is let us copy this bizarre SSIS and let me paste it over here. And let me rename this as drill down. Okay.

And it has opened this report. And if you remember, no, this report actually had parameters. So there was a parameter by This report was displayed. So what I'm gonna do is I'm going to go and remove that parameter, we don't need any parameters, we just want a full blown report. And in that report, then we will go and do the drill down by using the country name. Okay?

So if you remember, this report had a parameter so you can see over here, there was a parameter of states, right? So I'm going to go and delete this parameter at this moment not needed. And this in the data sources, sorry, in the data set, we have one DS report. And for the parameter we had created the states right, so I'm going to go and delete this. So both of them I'm going to go and delete and so let me go and run this report right. So let us click on Run.

And let us see that how this report looks like. So that our report is loading. And there it is, so you can see now this is a complete report what we have. Now what we would like to do is we would like to Go and create a drill down on the basis of this country over here, right. So, on the basis of this country, I would like to create a drill down. So, the first thing is let us go here.

So let us go to the report. And in this the first thing is, let me move this country column to the left hand side. Why because, when you see a drill down, you know, it is mostly seen, you know from the left hand side perspective, right, so, what I'm gonna do is I'm gonna go and delete this column from here, and I'm going to go and insert a column to the left, and this column is going to be of the country name. So that's the first thing. Now the second thing is we want the grouping on this country name column, right? So we want to add a group, okay.

And this group is going to be a row group. Now remember, you can have two kinds of group you can see. One is you can do a column grouping. Once you can do a row grouping, now you want to do when you want to do a drill down, it is a row grouping, right? So I'm going to say okay, this is a row group and it is a parent group and it is grouped by country name. Now, the time I do okay over here, if you watch this, so, you can see now, there is a report at the back end, you can see our report at the back the time I do okay, you can see that he has added a new column over here right.

So, you can see that we have an existing column and he has also added a new column right. So very quickly, if I show you this is called country underscore new one is the new column and this is the old column right? Let us go and first run this and let us see that what that other column is doing. Okay. So I'm going to go and run this. So right click Run, let us see what happens.

So now you can see that that our report has shaped up so you can see the first grouping is an India And then accordingly the the rules of India displayed then we have Nepal Nepal is displayed and then we have us right now, you can see this country name has been duplicated because one is the grouping and one is the data. So, what we can do is we can get rid of one of the columns over here right. So, now, this is the data column and this is the grouping column whatever is a grouping column is always at the left hand side at the very left hand side right. So, let us go and delete this column right and now, let us go and run the report. So, with this what will happen is we will not be seeing one of the country column and we will be seeing one with the country column which actually shows the grouping right.

So, with that our report would look more tidy. So, there my application is running. So, now you can see that this is much more meat right so, India and then the report the detailed report of India Nepal, the Detail Report and us the detail report this is good to know but it is not still a drill down it is static. In other words, I need to see this plus sign over here you know where I can expand and I can collapse this row right then it would really look like a good drill down right. So, how do we go and create a plus sign here and create create a kind of drill down thing you know, where we can collapse and we can expand right. So, let us go and close this and let's see how we can do that.

Now, when you added the group, you can see that below He has created something called as a row groups right and in that he has created the header that means the main group and below that he has created the details of the group. So what we can do is now we can go to the properties of the details of the group and say that when should be this when should this details be visible. So you can see that there is a visibility in the group properties so I can say that the person thing is when the report runs hide all the details and second, when the user toggles on the country name one not remember this country name one is nothing but that extra group column which has been created right? And now let us do okay. Okay. So now we have created a toggle.

So now let us go and run the report. So use Solution Explorer. And let us run the report and hope that we see a drill down in our report, so now that it is you can see now the drill down so you can see that everything is first collapse. Remember, we had said that the visibility is hidden. So now when I click on the plus sign it goes and it shows me the respective countries report. So you can see he's showing me India, and then he's showing me Nepal.

And then he's showing me us. So now I can get this drill down feeling over here. Great now The next thing what we would like to cover is sub report. Now a lot of times what happens is you'd like to display the report in a parent and child format. So in other words, you know, we can have a report, you know, where we have a parent section, where we are displaying the country name, and we are displaying the total sense of the country. And just down below the parent report, we would like to go and show a detailed report about the scenes made in that country.

So in other words, you know, if you want to go and show a parent child kind of report, that's where this sub report component is useful. So let us go ahead and create a very simple report where at the in the parent section, we would like to display the country name and the total sales made to that country in that country. And the second just down below, we would like to go and show the details of the sales made in that country. So if you see logically if I want to create this parent, and In the child report kind of a format, we have to go logically. So the first thing is let us first go and create the parent section. And then we will go and create the child section.

So what I'm going to do is I'm going to go and first create the parent. So let me go here to the report. And let me add a new report. And I will say that this is a parent report, let me add the report by using the wizard. Okay, so I'm going to go and add the parent section. Now remember, in the parent section, we want to display the country name right.

So let me go and copy the server name. All these are repeated now. So that so over here, we will go and write a select query, which will actually go and select the country data. Okay? So I'm going to call and say customer data warehouse, etc. Okay.

Next Query Builder is slow. And in this we will go and select all the records of the country master. So over here I will click on Add table of country and I would like to display now this is the parent section right we would like to display here, the country name and the total sales made. So what we are going to do is we're going to go and make an inner join with the fact customer because the customer seals is lying in the fact customer, right? So it's going to be an inner join between the country ID and FK here, right so I'm going to go and display the country ID I'm going to go and display the country name and we would like to go and display the total Right of so I'm gonna go and display the sum of the customer amount. And because now we are using aggregation, we have to use the GROUP BY clause right?

So I'm going to say okay, on the parent section, you will show the total sales might have made by the country. And you're going to go and group by by using the country ID and country name, in case you are new to SQL. My suggestion is to go and see the SQL Server video section, where we have covered INNER JOIN left join right join group by and a lot of other things right. So, let me quickly go and run this right. So if I run this, I can see that there is a country name and then there is a total sales. Let us not name this as expression.

So we'll say this is total sales, right. And also, in this parent report, I would like to go and see Search by by country. In other words, I don't want to see all the records I want to see for a specific country, right? So I'm going to see having the country ID, right is equal to accurate country ID. So it you know, so I'm putting a parameter here, so that I would like to select country wise and see the parent and the child report, right. So I'll say, Okay, I'll say next, these are all standard.

And Next and Finish. And we'll name this as parent report. Okay, and say, finish. So now you can see the report data section here, which shows the parameter that is country ID, and it shows us the data set the parent data set right. Now, this country ID if you'd like to actually populate from a drop down. So what I'm going to do is I'm going to go and add one more data set just to flourish this country ID value right as a drop down so I'm gonna go and add a data set very quickly and I will use the embedded data source at this moment remember this demo we are already done previously.

So data source I will select data source one query designer and quickly we would like to take the data from your country ID country name and I will say okay, okay. So, this data set to is nothing but your DS country okay. And this DS country, I will bind it with the country ID parameter. So, I will go to the country ID parameter and I will bind this to this country. Okay. So it's available values, get the values from the query.

Remember all these we have done in the previous part of the SSRS video. So there is nothing new in this. Okay. So now if I go and run my parent report, it will look something like this. So I'm going to go and run my parent report and hope that we will see the details of the parent. So, there you can see now I can see the report as per country.

So you can see now I've created the parent section. So, if you see over here, I am seeing India and the total sales made in India I am seeing Nepal and the total sales made in Nepal, right. And now I would like to go and display the child report just down below over here. Okay, it does down below over here. I would like to go and display a child report which will show me more details about the sales right. So we have completed section one we have created the parent section.

Now it is time to go and create the child section. So let us go and create a child report now so this was a parent tip. So let us go ahead and add a child report right. So I'm going to go and add again a new report let us use the wizard again at this moment. So next edit all these are standard we have done it okay copy take this customer data warehouse next. So now I would like to go and display all the data from the fact table right so I would like to display from here the customer code customer name customer amount sale state right.

Also, probably you'd like to display the product name right. So I'm going to go and also make a join with the salesperson states right. So very quickly over here as well. I would like to display the state's name I would like to display the salesperson I would like to display the product name right but This will depend on the country ID. So, in other words, whatever is this data what is coming out from here will depend on the country ID variable right. So, this complete fact table will depend on country ID.

So, what I can do is I can just say your child country ID So, that we can come we will come to know that this ID belongs to the child report right. So, you can see now again this is all done next dabbler standard standard standard. Next, finish finish. So, this we will name it as rename it as child report. Right. Great.

So, we have a parent report we have a child report, but we need we need to now go and Correct connect the parent report and the child report. Right? So we want this child report to be displayed just down below over here, right? And that's where exactly that component comes to us. So let us go ahead and see the toolbox. So that's where we need that component sub report.

So I'm going to go and drag and drop the sub report here down below. So you can see now just below the parent, I want to go and display my sub report, right? So right click on this go to sub report properties. And over here, I will say that yes, I want to go and display in this parent report the child report. And from this parent, I would like to pass the parameter Now remember, the drop down is selected in the parent, right? So I would like to say that, to that child report, I want to pass the child country ID and that is nothing but the country ID from the parent.

So you can see over here, this at the right hand side, this is the parent country Id which is passed to the child country ID right. So, now let us see so we have used the sub report so now let us go and run the parent report and and we hope that we should see the parent and child kind of report view right. So, I'm gonna go and run this. So, there my report is running. So, now if I go and say India, let us see what do we see. So, you would like to see a parent section and there it is, it is working right so you can see I'm seeing the parent section here.

And just down below I am seeing the child section and this child section is displayed because of the sub report components so you can see the parent and the child report. So in other words, you know, you will use the sub report component You know, when you want to create a report with a style of a parent and a child. So that brings us to the end of 20 minutes, 20 minutes of this video and in this video we cover two important concepts one is drill down report and the other one is we covered how to create a parent child report using the sub report component. Now, this is again a request a very humble request, please go to facebook.com slash questpond and please let us know your feedback please let us know on which lab you are, please let us know you know if you are liking this series or not.

And if you have any kinds of feedbacks, any kind of improvements if you think that you want to cover any specific topic, please go ahead and put your request on this facebook.com slash correspond. Now, the next video is on SSIS. So, in the next video, we're going to cover something very important concept called as KPI key performance indicators. So, keep moving ahead. We have we want to cover this 32 hours but we want to cover it in a very, very proper way. So hope that you know this series helps you out thank you so much