So, let us start with the next lab that is lab 12 and lab 12 we will start with SS RS that is SQL reporting services. So, what have we done till now? So our data is lying in the CSV files. So, our raw data was lying in the country dot CSV, it was lying in states dot CSV and whatnot right. So, from there, our SSIS package extracted, transformed and loaded the data into our OLAP database into our OLAP our DBMS that means into this, so, into this you know, we had created the star schema and snowflake design and in that the SSIS package went ahead and loaded the data. After that we said okay, this is very good, but you know on this if you want to do analysis, then you know it can become very slow.

So what we did is we used SSIS and we created our pre calculated cube. So, you This fleet calculated cube, our data is currently lying. Now definitely this data has to be displayed out right to the end user. And that's where exactly we use SSRS. SSRS is a reporting service, which helps you to go and fetch data from the cube that is from SSIS. It can also fetch data from SQL Server as well, right.

So let us go ahead and create a SSRS project. And let us go and display this data onto the screen. So the way we had used SQL Server Data Tools to create SSIS project and SSIS project in the same way, we need to now go and add SSRS project so you can see all these projects I'm adding to one solution. So you can see that this is the SSIS project. This is the SSIS project. And this is the solution.

Remember I said you know in SQL Server Data Tools, the way the project is organized at the top, we have a solution and incident The solution, we have multiple projects. So in this let us go and add one more project. So I'm going to go and add a new project. And this time, I'm going to go and select SSRS template. In this, you can see this business intelligence node. And in this you can see the reporting services node.

So click on the reporting services node. And you can see that there are two templates here. One is the report server project. And the other one is the report server project wizard. Now the report server project is an empty report. It's an empty report template you can see at the right hand side it is written, it's an empty report template, that means you have to build things from scratch.

So what we'll do now is we will go and select the report server project wizard because you know you are at the initial stages of learning. So, let us make our life easy. So let us use this wizard and create the project okay. And later on, I will show you how to go and create a SSRS project from scratch. So let us first use the easy way There is a wizard. So let us name this as customer report, or customer SSRS.

And I'm going to go and press here. Okay. So the time I press OK, you can see that it has started up a wizard here, right? So I will say next. The first thing it asks is saying that Okay, so can we can you tell me? Where is the data stored?

Okay, well, you can see that SSRS is a very beautiful beast, actually. It can connect down to any kind of data source, you can see that it can connect to our DBMS like SQL Server, or it can connect to a cube or it can even connect to XML or it can even connect to SAP so you can see that SSRS reporting services can literally connect to anything. But at this moment, our How do you say our ready made analysis is stored into SQL Server Analysis Services, right? So we are going to go and use this Let us click on Edit and over here we need to provide the SQL Server Analysis Services server. So, let us go back again to our Management Studio and over here let us go and copy the server name. So, to copy the server name select Analysis Services and this is my server name your name can be different and we say Ctrl V and our cube at this moment is SSIS customer right.

So, let us go and test the connection and let us say OK, and there it is, and let us do on next. Now the next thing it pops up is a query builder. Now, if you remember I said that when you want to go and query Analysis Services cube, we need to use MDX multi dimension queries. The way we use SQL to query to our DBMS in the same way, we use MDX, to query the cube. So at this moment he is saying that Okay, please specify the MDX query why MDX because I have selected SSIS as the data source. So he's saying over here, please go and specify the MDX query to fetch the data.

Now, at this moment, I have not taught you MDS MDX. So, what I'm going to do is I'm going to go and use the designer, and we will go and take the help of the designer to write the query. Later on, we'll be having a dedicated video on MDX right. So let us go and click on this query builder. So the time we click on this query builder, it actually pops up the necessary things you know, to be unnecessary things by which we can go and write our query. Okay.

So at this moment, we will not do MDX we will just use the designer and he will help us to do the MDX. So, what we'll do is we want to create a report where we want to display The customer amount we want to display the salesperson name we want to display the product we want to display the country. So, let us go and select whatever we have your right and also I would like to also display the date on which the sales happened. Right. And I will do okay. Now the time I do okay, you can see that he has he has written down the MDX query for you.

So don't worry at this moment what this query is we are going to learn MDX later on how to write it. Okay. And I'll do a next the next thing he asks is saying that Okay, so you want a tabular report, or do you want a metrics report? I will discuss about the metrics report later on. So at this moment, just select tabular we just want columns and rows simple one. I'll do a next if you see Typical report, it can have three sections.

One is it can have a header, where probably you want to display the page number you want to display the name of the company or something later on we can have reports of many times for example, you want to have countrywide report right. So, you can have sing India and then you can have records then you can have us and then you can have records right. So, in other words, you know, we can say that report can have three sections. First one is the page where you display the page number you display the name of the company, second group group means basically, you know, country wise report or in a statewide report and the final one is the actual data that is details. So you can see over here, this report wizard is giving you a full chance of saying that Okay, tell me which one of these fields will go on the page, which one of these fields you want to group and get the report and which things will go into details?

So at this moment, we do not really have Have page and group data. So what we will do is we will just go and put all of this data into detail. So what I'm gonna do is I'm going to go and select these fields, and I will just add it to the details, and I will do a next and he says that Okay, go ahead and select your look and feel. So I just like the look and feel of corporate, I'm going to take that blue color blue Microsoft right. And I will do a next and this thing will be speak later on, and we'll do a finish finish. So, you can see that the report is displayed in the design view.

And at the right hand side in the Solution Explorer, you can see that your SSRS project has been created, right. And your actual report you know, which we have just designed is nothing but this report one dot RDL. RDL stands for report Definition Language. We'll talk about that later on. But you can see at this moment at the left hand side you can see this design View and the right hand side, you can see your solution, right. So you can see this is designed.

And this is preview. So what we'll do is let us quickly go and see the preview first how it looks like. So I'm going to go and click on preview here. So the time you click on preview, he will actually go and show you the report with the data. But you can see that he has not displayed the data, but he's showing you some error over here. Let me simplify for you what exactly this error is.

Now SSRS reports needs a web server needs IIS web server to run. So you can see that over here. He's saying that okay, no, I'm trying to connect to a channel and etcetera, right. So what we can do is for now, just to go and run this report, click on the solution explorer. And if you remember, you know this name of the report at this moment, I have kept as report one, okay. And the extension is dot RDL.

So, right click on this and say, right click on this and say run okay. So now this will run inside your Visual Studio, you know without IIS and without a web server. And you can see that that our report is running inside the report viewer. So this one here, what you see is an SSRS report viewer. And you can see that he has provided you a ready made toolbar, you know, by which you can go and do a print, you can, you can go and you can do a next and a previous, you can also go and export your report into various formats, right. So this is this ready made toolbar here.

And down below, you can see your report. So the first thing is, you know, what I would like to do here is I would like to go and change this report one to something like customer sales, I would like to go and change some of these field names, right. So let's go and close this and let us go to the design view here. And it'll start doing the changes. So let us change this report to some better text. We're seeing customer report right?

Now, you can see over here, one is this is the column so you can see that this is the salesperson name, it's a column. So, you can go and change this column text like you know to person name or I can say sales man named sales man name right. So, you can see the top one is a column and the bottom one is the field right. So, you can see that this is sales person and this is at the bottom it is the field from where the data is getting fetched. Right? Good.

And again, just to see if this changes have been deflected. So you can go to View Solution Explorer. So at this moment, our customer report is not hosted inside is right. So because it is not hosted inside is we have to see right click on this RDL file and say run is loading and I should see the report now, so that you can see the changes you can see the column name changes as well right good. Now, very quickly, you can see this file here report one dot RDL and I said that the full form of RDL is report Definition Language. So, what exactly is this file?

This RDL file is nothing but it is an XML file. If you go and if you say view code, you can see if you try to do a view code here, you will see that the internal format of this file is XML okay. So, you can see that whatever you are doing on the screen, you know, whatever you are doing over here, right is getting affected here. So, you can see it's an XML file internally, right. So, remember this RDL is a XML File internally, the way if you remember, in all our previous projects, you know, everybody had a file, for example, if you remember SSIS, it had this DTS x file, right? And internally, what was this DTS x file, it was like XML.

So, you know, the same thing holds true for RDL. So RBL also internally is nothing but a simple XML file. And this XML file runs under IIS web server. So we'll talk about that architecture later on. But for now, you can remember that this is XML file internally. Good.

Now, this report is good, but we would like to go and you know, put a search for this report, okay. So I would like to have a small search here saying you know, like search by country name, okay. So let us go ahead and put a search so to put a search, click on View and first thing click on report data again, Repeat. The next demo, if you want to do is on this report, I would like to go and put a search. So click on View and click on report data. The time you do that, something like this comes before you.

Okay? So you can see that this toolbar name is report data. So as the toolbar says report data, it provides data to the report. So to provide data to the report, we need at least two things. First, is we need to know that where that data resides. So if you remember when we ran the wizard, at one moment of time, we provided the SSRS cube server name and what exactly is the cube right?

So that is stored here in the data source. So in this data source, if you double click on this, it actually tells you where your server is located. So if you remember, when we when we ran the wizard we provided the server name and everything over here. So that is the first thing. The second thing is We also provided the MDX query, which we had fired on the cube. So that is stored into this data set.

So, if you see this data set here, it opens up the same query designer, what we have seen previously, right. So remember, this report data provides report data to your report, right. And the two prime things in this are first thing is it actually tells you where your server is there. And second, it tells you what kind of query you have fire so data set for the query and data source for the server name, location server location. Now, if you remember I said that at the top, this report is the text and the bottom this one is the field. This field actually comes from here, for example, now, let us say I go and delete this field.

Let's say I go and I delete this field here. So I'm going to go and delete this right now. OK, so the salesman name has gone right. So either you can drag and drop this salesperson name and put it here like this, okay? Or either you can go and put from here. Okay, you can see now because a drag and drop from here, he created a new column if you see when I go and drag and drop from here, he actually creates a new column here right.

So in case you do not want a new column, what you can do is you can go and click over here and provide the feeling. So, this is good, our report is running. But I would like to now go and search by using state name let us see. Okay, that means I want to go and parameterize my report, and that's where this parameters comes into picture. Now, you can go and add parameters to the report in two ways. Either you can right click on this parameters folder and say add parameter and specify things from here.

All you can go to the easy route you can go and do either MDX query designer So, let us first go the easy route here. So, let us click on the query designer right and in this unit we will say I would like to go and do a select on the states especially on the states name and when it is equal to a parameter you can see this last column over here which says parameter right and I will just say okay now, the time I do okay you can see that he has created a parameter here called as dim states name okay. So, that is good and let us go and run this. So, let us see that what happened to the parameters is that, so, let us go and run this view Solution Explorer and let us go and run and see the output. So, I'm going to run run it and you can see that that it has the full You can see that he has automatically created a drop down right and then also he has done a multi select Okay, so let's do like this first thing let us go here and let us remove the drop down Let us start slowly.

So I'm going to go back again to my report data Remember to go to report data, you have to click on View and you have to click on report data it is a very, very typical menu which is very hidden inside okay. So I would like to at this moment, I don't want a drop down. So, what I can do is I can go to parameter properties here and I can say that please do not allow multiple values, right and second outlet to make it a simple text. So you can see that he's actually trying to get the value from that country name field right. So I will say that don't get the values from the query specify the values you know, in other words, make it a text. So, let us now go Let us save this.

Let us see how it looks like So what I've done is I've just changed the drop down at this moment to a simple text. So like right click run again. So SSRS is all about, you know, going out going around and playing around this RDL file hunting for things and that really creates a report very easy. Okay. So, there it is. So, if I go and specify your Punjab and if I save your report, I should be able to see my report and internally, it is going and firing the MDX query.

So there it is working right, good. Now, we would like to this is nice this report is working. But we would like to have a drop down here. If you remember when we previously created the parameter, it had a drop down, right. So we would like to actually also have a drop down because if you see internally, there is a separate table for states, right. I purposely We need to drop down so that I can teach you how the drop down can be created from scratch.

So if you visualize now, let us go to our report data. So we need to drop down, right. So this is the report, and the data of this report comes from this data set one. Now, if you want to drop down here, then we need to get the drop down data from somewhere, right. So what we will do is we will create one data set for the state's master. So this data set one actually fits fetches the record.

So I'm going to say I'm going to go and rename this data set one as DS report data, okay. Good. And I will go and create one more data set which is specifically for the drop down so I'm going to go and add a data set here. And I will say this is d s states, okay? And in this I'm going to go and use the same data source one so you can see I'm saying use a data set embedded in my report. So at this moment, if you remember, we have a data set here right in this this data source one, we have this data set, right?

So I'm using the same one and I will say query designer. So, in this I want to go and count the state's ID and I want to go and get the state's name okay. But you can see now, it only fetched this you cannot fetch the state's table straightforward, right. So what we will do is we will go and also select the, the facts so that we can get this state stable here. So you can see now, I have fetched the facts in and that's why I'm getting the state's data as well. If this was SQL, then you can go and fire to the master table very easily.

But because this is not SQL it is MDX right. That's why it is very difficult to see the database Now the fact fine. So I'm going to go and fetch this data that is, you know, no problem. And I will see okay. Now let us go and connect this data set with this parameter. So I'm going to go here to the properties.

And I will say that get the data, get the data from the query, get the data from the D estates. And currently, because the search is on the name, I'm going to go and use the name right. And let us go and run the report. Let us see what do we see. So use Solution Explorer, I'm going to go and run this report over here. So let us see what the output is.

So that you can see now there is a drop down, right? So we say Maharastra, and I will do a view report. And I hope that I will get the display as per Maharastra. And that is working. If I said Punjab, that is working. Great.

So you can see that things Working right nice. And also let us say, if you wish to display totals, for example, one of the things what you would like to do is, this is the report here, you know, so somewhere down below, I would like to go and display the total, you know, so, at this moment, if you see we are seeing the customer amount, but I would like to go and display the total year. So in SSRS, we have a toolbox in this toolbox. It provides some primitive things like line and text box and image and sub report etc, which you can which can use, you know, to customize this report right. So what I'm going to do is I'm going to go and draw it and drag and drop a text box here right down below. And inside this text box, I would like to display the sum of the customer amount.

So in order to go and display the total inside this text box, we need to go and we need to specify an expression or expression is a formula gate can be a formula. So to specify an expression, you need to go and right click on this and you can see that there is something called as expression. So let us click on this expression here. And in this you know, you can go and specify formulas like sum count, etc, right? For example, now I want to display the sum of the total amount. So I will go to this data set here.

And in this data set, we have this Ts report data, right. And in this you can see that there is some customer amount, right? So, either we can go and type the formula out, or you can just double click on this. If you double click on this, it says sum and the field name right and from which data set this value is coming, right. So, I'll do okay. And let us again go and run this.

So view Solution Explorer, all these are same, right click, and I will say run. So there my application is running. And if I go and just display the report For Maharastra you can see that he shows me the report and down below you can see the total right so again to display total right click and say expression and once you go here you can go and write the formula. Now the way I have displayed total you can also use other functions as well you can see that down below there is a common function and in that you can find there are lots of ready made formulas given over here right. For example, if I go to aggregate, you can see that I can also display average, I can also display the max and mean etc. So, whenever you want to go and give out a formula, right click expression, okay, right click expression, great.

Now, when you right clicked on this, there are two things one is that is expression and the other one is textbox properties. Okay? So this expression is actually for the formula. You can see this effect Here, it seems this is for the formula for the value means what what data is going to be displayed inside will be evaluated by this expression. But now many times you want to go and change the background color change the look and feel change the font depending on certain criterias right. So, if you want to go and you know change the visuals, depending on certain conditions, then you have to use the textbox properties.

So, remember this is for the value and this is for the UI, right. So, let us say I want to go and say that okay, if the value is less than 10, then then make the background color as green. Or if the value is more than 10, then make the background color as red I want to give some kind of visual look and feel depending on the value right. So, what I can do here, I can right click on this and go to textbox properties. So, as soon as you go to the textbox properties box, you can see that you can go and No change the UI look and feel conditionally. So you can see for example, let us say you want to change the alignment horizontally or vertically you can see that there are small small FX buttons here.

So you can go to this FX and write the code over here in the box, you know saying that okay if this is true, then it should be left you know, if this is thing you know, it should be right or whatever it is, right? So literally, you can see you can change the font, you can change the border color, you can change the background, you can make the visibility false or true. So in other words, the textbox properties. dialog box is mainly meant when you want to do conditional when you want to conditionally decide you know, how your UI has to behave right. So at this moment, you know, we say that we want to change the background color, depending on the following conditions. So you can see I have clicked on this film menu, and I'm going to go and click on this FX here.

So here now I can go and write a expression. And depending on the expression, I can set these colors. So I need to go and write a if so remember that expressions always starts with equal to, so I will say your i f, okay? And this if there are three parameters, first thing, what is the value? So the value at this moment is the sum. Remember, this textbox is a sum, right, so, I'm saying here, if this sum goes greater than 10, then comma look at the way I'm doing things.

So, if it is true, then please show let us say red color as a background, or elles show blue color. Light blue. What is light blue? Yeah, light blue. Ah, there the hell is light blue. There it is.

Okay, so you can see now The VBA I've written this expression if the sum is greater than 10 then show red color or show light blue. And please try to use this constants menu and try to and try to select the colors from here. So that you do not make mistake in writing the color value here, right? So I'm gonna say okay and IBC okay. And let us see. So, remember expression, you will do it for data, while textbox properties you will do it for the UI behavior remember, this one is for the data evaluation and this one is for the UI behavior.

So, let us go and run this. So, view Solution Explorer, let us quickly go and run this and let us see if things are working or not. So, that our application is running. So, let us see one of the reports and we expect the background color to change and you can see if the background color if the background if the value I'm sorry if the total amount is more than 10 it should show the red color and that is working if it was less than 10 then we would have seen the light blue color. So, you can see that how conditionally my UI look and feel is changing. Now, expressions and textbox properties are not confined to single text boxes like this, if you wish you can go and apply on a table cell as well.

For example, on this customer report or table what we are seeing I can go and apply on this cell expression and textbox properties, okay. So, for example, I can let us go and apply a textbox properties here. So, let us try to see how it looks like. So, I'm going to say effects. So, again here I will say, is equal to if, if the customer amount is greater than 10. Then show look at the way I'm clicking things.

Show red color as a background. or show a light blue color Okay, I like blue color because of the Microsoft logo or blue right. So, let us go and run this let us see. So, now let us go and see what the data is seen and we expect the background color to change accordingly. So, it should change for the totals as well as it should change for individual data as well. So, you can see now, even the textbox properties have been applied on to the individual rows as well right.

Now, let us the way we have done example for textbox properties, let us do example on the table level for expression Okay. Now, let us say I want to go and create a running column here. So, I want to go and create a column on the right and this running column this column will show me the running total. Okay. So what do you what do you mean by running total running total means basically So if I go and run the program here very quickly, just to make you explain what exactly I mean by running total and how we can achieve the same by using expression run. We'll talk about deployment later on.

So what I'm going to do is I'm going to go and use expression and apply it on a column. And on that column, I would like to go and display the running total means what? For example, now, take this record over here, the master record. So in this you can see there are a couple of values here, so that is 12. Right? So what I've got to do is, in this column, I would like to display the running totals, this is 12.

So it should show 12 over here, and then this is 12 plus 1224. It should show it over here. So here I would like to see the running total in this column. So that that can be achieved by using expression. Remember, the time you say you want to modify data. It is a expression, the time you say you want to modify the look and feel the behavior right conditionally, then it is textbox properties, right?

So over here again, I'm going to go and apply an expression. So again this running value is the aggregate function. So if you go and see in this aggregate, you can see that there is something called as running value, which will help us to get the running total. So I'm going to go and click on this running value and I will say okay, I want to go and do this total. So, the first one is the field on which this running has to be applied. The second one is what do you want to do?

Do you want to do some running some are running average or running something right. So what kind of algebraic operation you want to apply? So I will say I want to do a sum at this moment, comma, and scope. This woman just type nothing means it can be applied all over. And obviously Okay, So, now if I go and run this, so, let us go and run this view Solution Explorer is right click and let us do a run and let us hope the best. So there it is.

So if I go and say Maharastra and if I say view report and there it is, now you can see the first record is 12. So the running total is 12. The second record is 12. the running total is 24. Let us go and see if we have some more records here. Okay, there it is. So thousand thousand and then thousand plus 1240 is equal to 240.

Right? So you can see that the magic of expressions, how it helps us to manipulate the values internally. Right. Now this report, what we have currently is a report which is created using the wizard. But there are times you know where you'd like to go and create a create a report from scratch. batch, you want to have your own tables, you want to put your own coloring for example, you can see over here, he has added his own tables and own coloring, but probably you'd like to go and create a report from scratch, you know where you do not want this dark blue color, right?

So for that, what we can do is we can go and add a new report. And, sorry, add a new item. And rather than selecting this wizard, we can select this report over here. So I'm going to go and click on this report and I will say that this report is a manual report or you know, a report from scratch okay. So this is not created by the wizard. Now, you can see that because you said that you want to develop the report from scratch, what he has done is he has actually opened up an empty design surface for you.

And now you can use the SSIS Toolbox and create the report from scratch. So for example, I would like to say you're okay, this is Customer reports. So, in other words, now, it is your responsibility to go and create this report from scratch. Now, to display data on this report, you have to either use the table or you have to either use the metrics. Now table is nothing but what you have done previously remember this wizard has created a table with columns and rows. So, in case you want to go and display the data in a simple row and a column manner, then you will use this table but in case you want to go and display data in a intersected way or in a pie voted way, then you will use metrics.

So, what do I mean by pie voted by what it means something like this, let me just open up Excel and I will just show you what exactly is by voted. So metrics means the report becomes something like this for example, in metrics, you will have fields both on the x axis and on the y axis means you will have fields on The columns as well as on the rows. So, probably you would like to do something like this. So, here you have product right and year you have let us say country okay. So country. So, now the way this will be you will say okay must be I can take the product up here okay.

So, the way the report will be India right and let us issues so, the sales is hundred then we say okay Nepal, let us say it is shirts, okay. So, this is 200 and probably for India, the sales is something else you know, 90 and probably Nepal for shoes has sold 80 right? So you can see now, you know this is a matter of support a method support means on the center, you have a value like a measure or something and you want to go and see the report in a multi dimensional way. So specifically when you are viewing reports from cubes Then this kind of report is very useful. So, what we will do now, we are developing the report from scratch that is good, let us go and learn how to do metrics reporting, okay. So, let me again go back to my application here.

So, let us drag and drop this metrics over here, right now, it is asking for the data set, it is asking again for queries. So, you know at the end of the day the report needs data set the report need queries right. Now, in the previous example, when we ran the wizard we had used SSIS. In this example, let us go and use SQL Server let us go and read the data directly from SQL Server. So, with that, you know, we will understand that how to create the report from scratch. We will also understand that how to create a matrix report and we will also come to know how to use SSRS to connect to SQL Server right.

So, what I'm going to do is I'm going to say okay, this is an a data set which is embedded I'm going to go and create a new data source and this time I will connect to SQL Server. So let me again go back to my SQL Server here. So let me connect to my database engine. Let me copy the server name. So we can see now the things are the same, right? One of the thing is that now I'm using SQL server to connect I'm using SQL Server for the data and customer data warehouse and I will say okay, and we say okay, and right.

Now, we have specified the data source that is good, but we also need to go and specify the query right? So let us open the query designer. Now, you know, when you're firing on SQL, SQL Server, you need to write SQL right. So either we can take the help of the designer or we can write the write the SQL manually as well. So previously, also, if you remember, we had a designer for MDX so we did not write the MDX queries. So what we'll do for now is not write any kind of SQL query.

Let us use this designer. Okay? So let us go and start adding the tables. So I'm going to go and add all the three tables for six tables, right? So that is good. And you can see the beauty of this designer is that you know, down below, he's actually constructing my SQL as well, right?

So that is really nice. Okay, so what I'm going to do is, let me go and close this grid closes SQL, right. So I will say, Okay, I want to go and fetch data, I want to see the customer code, I want to see the customer name, customer amount, sale state. And from here, I want to see the state's name, I want to see the sales person name. And also we need to create a relationship now between the product and the product idea. So let me do that very quickly.

Okay, so I want to select from a production same same way I would like to go and also do a join with a country and from this I want to select the country right. So, let me go and check my SQL. So, I can see INNER JOIN, inner join, inner join, right, cross join, so, this cross join with time we don't want. So, again Let us fix that as well. So, we are going to go and do a join on the date if you remember this Okay, we will not we will not use the time at this moment, you know, this will really okay. So, we just want to display the customer data, we want to display the relevant product salesperson status states and the country data right.

And let us do execute. And things are coming up nicely right. So now let us go and press OK. And the time you press OK on that SQL is seen out here. Right. So remember, previously when I created the report, I was trying to connect to SSIS. So the query language was MDX one Now I'm connecting to our DBMS.

So the query languages, SQL, and I will just do, okay. Now in order to go and put the columns and things over here in the report, we need to go and pull up the report data. Remember, whatever data sources data set, you do, everything goes into the report data. And every RDL file will have its own report data. Okay? So over here, I want to go and drag and drop, let us say, we want to display the customer amount in the center.

But I want to display it state wise. And I want to display it product wise. So at the top, I'm gonna go and put a product right. Great. So now let us go and see this report. Let us see how it looks like.

So I'm going to go to my solution explorer, and this time, I'm going to go and run this manual report. So right click on this and say run. So there you can see our report. And you can see that how a matrix report looks like. Now, wherever you are seeing empty columns for example, you can see Punjab does not have any entry for shirts right over here. So because you know, there are no records of Punjab with shirts, okay.

But wherever there are entries he showing the value appropriately here, right. So metrics report is nothing but at the center, you have some number like customer amount, and you would like to go and see it in a multi dimension way like saying, Okay, show me state wise, and show me product wise, right. Great. So we have created a report, a custom report from scratch. We used the metrics kind of view, we saw the metrics and have a view and we connected to SQL Server very nice. Also, one of the things you know, as an end user, which I would like to see in my report is sorting.

For example, over here now Take the situation you can see that I'm trying to display the data for Punjab and in this I can see that the date is ascending. So, you know basically firstly three 2014 and then fy 2015. Now let us say that you want to go and make a descending right. So, what you can do over here you can go here you can select this dates column, right click on it and go to tablets properties okay. So, go to tablets properties, click on sorting and then say that okay I want to go and sort on the date and I wanted from the greater to the smaller from set to a right and i will say okay and it does go and run this report. So, now, let us go and see again the data for Punjab and I should be able to see that it is sorted in descending manner you can see now, the record is first is fy 2015.

And then it is three 2014. Right. So it is now from set to eight, that is descending. But now and users are greedy, they want good and good things. And you can also ask for something like this, you know, end user can say that, okay, this is very good. But can I have a button here?

On which if I can click, and I can sort this interactively. In other words, let me decide on runtime that I want to do a descending sort or ascending sort. So again, SSRS is very good in that. So to enable interactive sorting, click on the date header column, right click on it and go to textbox properties. And in that, go to interactive sorting, and then say enable sorting on this text box. And we'll say sort by date.

Right? And we'll see Okay, so now let us go and run This application again. So, let us run this. Remember I'm doing interactive sorting on the first result created report. So, let's see. So, there it is and let us go to Punjab.

Let us save you report and let us see what happens. So, because you have enabled interactive sorting, you will see that there is a small icon on the date side, right. And when you click on this icon, you can see the dates are getting sorted accordingly. So, if you see, now this is descending and this is ascending, right. Great. So, remember, if you want to have interactive sorting, right click on the header column, text box properties and interact to sorting And then go and put the field name what you want for the salt.

Now, this report one is the one which we have created using the wizard and we have used MDX and this manual report is the report which we have created manually from scratch and we have used SQL, right. So what I'm going to do is I'm going to go and rename this. So, this is V zard MDX right, it is using SSE SLC rather SS s right. And this one is is manual. So, manual and it is actually using SQL Server right. So, we have created from scratch, okay.

So, in other words, you know, this is using MDX query and this is using SQL. Okay. Now, what we'll do is, we have Do we have done the combo boxes the drop downs by using MDX. Let us do Drop down by using SQL, because you will find that there are some small small differences, which I think that you should know when you are working with SQL, right. So let us say we'll take the same report and here we want a drop down, right, which is a state's drop down. So I don't want to see all the states, I just want to see the states which is selected in the drop down, right.

So again, remember what we did first let we have to first go to the report data. Because report data is where all the magic happens, right? So I'm going to go here. And the first thing is for the drop down, I need a data set. Now this data set is for this grid, right? Remember, so this data set one is for this grid, right?

And we need to create a data set which is for the state's first thing. So I'm going to go here and create. I'm going to use the shared one what I have Data Source one, and I'm going to go and first get the states. Okay? So I will add the state's table. And we want the state's ID and we want the state's name, right.

And I will just say, okay, right. And I'll say, Okay, so this data set to here is nothing but it is. Ds states, right? So I'm going to go and name this data set as DS, the states, right. So now in order to get a drop down here, we have to create parameters. So that step is all same.

So either you can go and create a parameter from here, or either Remember I said, you can go and create the parameter from this data set. So what I'll do is let us go and create the parameter from the data set at this moment, right. So I'm going to go here and say C, enter. Now this is SQL, right? So I hope that you have an understanding of SQL in case you do not have an understanding of SQL, then I would suggest you to go and watch the SQL Server videos what we have on questpond. Right?

But here what I would like to do is I would like to say that that fact customer dot states ID is equal to add the rate states, states states ID, okay? And I will just say, Okay, now the time I do okay, you can see that in the parameters, he has created the states ID parameter, right. So that's why I'm showing you this demo. We have already seen the demo with parameters in MDX, but you know, in SQL it is bit different. So, I want to emphasize the point, you know, how different it is from MDX. Now, in order to make this parameter, the state's ID parameter as a drop down, we have to connect this data source with this parameter right.

So we have to go to the parameter properties and we will go to available values and we will Connect this DST it's to the parameter right. So, one the value field internally internally will be ID and on the UI side we will see the name right great. And now let us go and run this report. So, there you can see the states data is flourished in the drop down. So if I say Maharashtra and if I say View Report, I will see the relevant data. And okay, I think I don't have any record for master let us say Punjab.

So at least for Punjab, I should see some data there it is. If I say New York I should see some data. So you can see now things are working. Good. Now, I would like to go one step ahead. This is good that my states are getting finished.

But how about if I want to do a cascading drop down? a cascading drop down means you know if I can have a country drop down and when I select The country, I want the respective states to populate. So I don't want all the states to populate, I would like to have a country drop down a country parameter and on the change of that country drop down, I would like to flourish the relevant states. So, how do we do that? Now, the first thing is we need to go and create a query, which will go and query the country Master, because if we want to get a drop down of a country Master, we need to have a DS country query right. So, let me go and add a data set.

And let me use this use data set embedded with the report and the data source one query designer and let us go and fetch the data of the country first, because depending on this, you know, the states will be flourished, and we only want the data which is new, right which is Which we know where the is new is true if you remember this is this column is new is coming from that STD right? So I will say here Where is new is equal to one. Okay? So only the countries which are active we want and we'll say okay, so this is our DS country, right? So I'm going to go and name this as DS country. And this DS country, we need to go and connect with a parameter, right?

So I'm going to go and add a parameter here called as country ID, right? And country ID, okay. And this parameter will be a string, definitely a text. And the values will come from a query, so I'll say, please take from DS country, the value field is country ID, right? So get values from the query values field is country ID, and the label field is country name. No.

Good. So now, you can see that I have ran the report. So I can see that the country is flourished. But you can see the states is also flourished, right? We don't want this what we want is, if I select India, then I would like to see only Maharashtra and Punjab. If I select us, then I want to only see New York here, right?

So in other words, we need to now connect both of these data sets in some way. Right? So how do we do that? So we are almost done. Now we just need to connect this drop down with this drop down, right. So for that, what we have to do is we have to connect the parameters.

So what do I mean by that? This D estates over here will be flourished in Oh by this country ID so In other words, what I have to say here is I have to go to the query. And I have to say here that this country ID is equal to this address country ID, that means this parameter, right, so I'm going to say here at the rate country ID. So now what has happened is this D estates is dependent on this country ID, and this country ID is coming from this data set country. So the way they are connected, the country ID is flourished from this country drop down, and this country ID value is passed to the D estates. So in this way, the cascading will happen now.

So I'm going to now do Okay, all right. And now let us go again and run this report. So I hope that we should now see a cascading drop down behavior. So you can see that there are you know, drop down sort of our scene. So if I now select you Let us say, you will see that he has shown your New York if I now say India, it will show me the 11 states here right and then I can select the state and see the data for that states. So, you can see now, how we are able to achieve cascading dropdowns because we have connected two parameters with each other.

So, in other words, now, the way it is operating internally, this country parameter is getting flourished because of the DS country data set. And this parameter is an input to the DST it's so, because he is the input to the D estates in a he will not get flourished until he gets flushed. So, depending on his value, now, he is getting flourished. Good. And very quickly in case you are guessing that you know how that connection is happening. What I've done is I've just made a small modification in our table here in the RDBMS in the states in the in the state's table, I have put a country ID column.

So because of this You know, we are able to achieve that one to many relationship internally. Great. So, that brings us to the end of one hour of SSRS lecture. And with that, we complete eight hours of training and what a great way to end day one, this one hour of SSRS lecture completes that full msbi circle. So, if you remember, there were three important pillars of msbi one is SSIS other is SSIS and SSRS. And in this eight hours we have touch based all these three pillars in an equal way.

So, you know, if you are able to complete at least this eight hours of practicals what I have shown here, I can guarantee you that you are okay okay msbi developer, you can go in a project you can exclude things you can deliver things in a certain way. level. But nonetheless, you know, go to the next level where we want to become a good msbi developer, right. So that is what the next levels of lecture will now start. And after that our final goal is to become the best msbi developer, right. So at this moment, congratulate yourself even I am congratulating myself because I have completed eight hours of training.

And if you're really completed the practicals, then I would say that you should congratulate yourself and be ready for the next level of training where I would like to take you to a more advanced level of msbi training. So thank you very much. And please do put your feedbacks on facebook.com slash questpond. Do tell us that yes, you have ended the eight hours of training and you are ready for the next level with that what happens is we get a confidence that yes, people are seeing our videos because if you are not seeing our videos, then this whole effort goes Smith's right. We want you to see every hour of training what we take So, thank you very much and just wanted to revise that following our the agenda, which was completed in this one hour of SSRS training and the next lecture now will be on SSIS.

So, Henceforth, what we will do now, within database equivalently all the three pillars, so, we will do SSIS then we will do SSIS then we will do SSRS SSIS SSIS SSRS So, the next lecture is on SSIS, where we are going to talk about a very nice feature in SSIS called as profiling. So, if you go here very quickly in this solution explorer So, sorry. So, over here if you go to SSIS, right. So, if you go to any SSIS control flow If you open the toolbox, so, you can see this component here called as data profiling tasks are very, very important component when it comes to to check you know that what your quality of data is coming in. Because when we talk about msbi, what is MSP? MSP is all about data right?

So, there comes a moment you know, where you get the data into your system and you want to just check the quality saying that okay, is this data good in harmony are nulls in this you know, how many data is having what characters right? So if you want to go and check your quality of the data, then this is the king in that. So the next lecture 20 minutes 30 minutes of the lecture is dedicated to data profiling task. Thank you very much, and keep watching the series and hope that you become a true professional msbi developer. Thank you.