How to create SSRS Report Dynamically with Dynamic Schema, Dynamic Table Name and Dynamic Columns

welcome to tech brothers with the Dominator in this video we are going to learn how to create SSRS the report or dynamically with the dynamic table schemas the dynamic table or view names and dynamic columns um so I don't know this title makes sense to you guys or not by I'm gonna explain what exactly I'm talking about and what could be the requirement or why we are creating all these the dynamic things or reports so now think about a scenario I have let's say I have twenty tables or some of the views or and I want to create the detail or simple report so for all those views though one thing I can go ahead and create 20 reports or would depend upon the number of the views and tables I have the atom second part I can create something where I can ask the user hey you select your schema or get select your table and select the fields you would like to see and the report should be auto-generated depend upon those the criteria so that's one of the requirement second I have seen a lot of requirements coming from you guys where people ask hey I would like to design a reporter where I can show a drop down for my schema so in this case let's say we have this schema dbo once I show that schema I would like to show the tables in that schema okay fine and then I would like to show the columns for that criteria so DB or customer and these are the columns related to that table and once I select that and hit a view report it should generate the report for those columns from this table um so this give us a lot of flexibility and especially I think when your users first I explain you guys whenever you have a lot of tables of views and are you wanna just consume simple show the detailed report you are not doing a lot of dropdowns and you know drill through ports and all that just simply you want to show the columns and data that's where we can use that other part is where your users you don't want to give them permission on the SS m as the can't installed SMS SSMS on their machines and you don't want to the permission on the database so by doing this type of report they will still able to go there and select the schemas and select the tables and fields what they would like to see so in this case II if I want to remove the ID I can just remove the ID refresh my fields and it will show me the reporter let's say into one more schema Lacetti be tech brothers and i select only one table is there so it show me that table and now I can see all those columns there now here you can select those columns or select all and hit view and you can see that your report is ready to see you can see that and export into Excel if you would like now that's greater in previous video like couple of months ago I did one video where I showed you guys how to handle the situation where the number of columns can change for your view or a table so I showed you guys the that part as well because there are so many scenarios sometimes people remove the columns and or add a columns and you would like to show in your reporter I recommend that watching it is the same concept erm so let's go ahead and create this report first of all I'm gonna go ahead and create the share data source I already have created so right click here provide the name and the connection string second part of you will be coming to the reports tab right click here and say add new item now provide the name I'm gonna call this one no dynamic reporter I'm going to tell you guys at the end the like there are some I don't know there are some bad practices in this way of reporting by most of the time when you have small tables and not whole lot data this works just fine but if you have hundreds of thousands of record though it might take a long time to display the data or bring the data so it's good thing to learn go ahead and start to creating a report here now let's go to the other stored procedure so I'm gonna go you guys show you guys what stored procedure are using here so I wrote this stored procedure so I have alter you can have create I'm going to put the script on the post and in the link will be in the description so you can take from there so you write procedure procedure name I'm going to call this one DP report it's accepting schema name table name and column list so you see that I'm saying what your Maxim it means that I want to save a lot of four columns and in our previous scenario where I showed you guys demon you can select let's say one table and then you will have all the columns of so this can grow pretty huge when you have let's say 20 30 or 50 columns or you want to save that come back here and say as big and that's how you start your store procedure now I'm declaring some variables here declare a dread column names and declare at the red column name war so here I will build the list of here I'm going to get the list of the columns up so I will be getting something like this so I will get let's say ID name and address and what I need I will be putting the parentheses around in my procedure and also I will be converting these column because you see this isn't two year this is your name is worker and this is also worker so I will be converting to the bar chart for the unfit otherwise this is going to throw me at it okay so I have these two variables ax here I'm creating some tables so these type tables will be user during my process so I'm the very first is called the temp underscore data this is a global time table so I can use the in my dynamic sequel and still extract the data after the dynamics equal a session will be completed now here I'm using another table called the temp data final that will use the end when we will put the results at or the records are from the unpoetic query a query output and here I'm using just a simple this is a local temp table just to build the column list and okay I wrap every time and recreate them here I'm just dropping them and them and you know whenever I need them here first let's create this table so once this the column this table is created I insert the values of so remember I see I get depending upon the criteria I I get that values here in the store procedure input and that's like ID comma a name address or whatever people select from here so in this case let's say if we select all these it will be ID and object name that will pass to D this part of so I will insert in this in the table why I'm inserting a table because I want to concatenate that and build something like that so first I want to do this part I want to add the parentheses around because there could be possibility my columns there there are space between my column names oh so let's say if I have a F name so maybe first name something like that for one table if I have that I would like to put the parentheses around that otherwise I will start getting at it oh no that's one part also when I use the UNPO bit query I need here I need all the column types of the same data type err otherwise it will throw me at it and you see that if somebody passed ID that's integer type first name is watch R and this one is watcher as well and that they have different data types so if I will use in the pivot I will get at it all so I put in a table once I put in the table and then it will be like ID from our name and then it will be address there so once it to get into this then I add the parents it says add the world a cast function to them right here so you see that I'm adding the cast and all that and watch your thousand so I converted everything to the you watch your thousand once it's converted then at the end if you see here I Rican cat innate it's the kind of thing and bring them back in this shaper so I bring them all in the parent it'll first part I created with the parentheses the second part I create sorry this is person just the name so first name or whatever the columns are so address and this is one I create it's called column names and the other one is column names water so where I say I cast ID so sorry as watch our thousand so this is how it create demo for me as I do so so did this will keep adding them and like this so I can use in the unpiloted right here so I created these things and save them to variables here and here I use a common table expressions to just first you know once they are in the table they are a comma separated values I use the XML and cross apply to make them in the rows first and then finally again I can catenate them to make comma separated values as I showed you guys here so I can use them in my unfavorite to query and then here what I'm doing I'm declaring a variable called sequel and then I'm declaring another variable called at the rate temper table sequel and here in I'm setting the value called select row number and I'm creating a row number for each of the record and say column names so I have the column names and from here and then say into so this is where I've put first array all the records into the temp data table and from this command table so that comes from our input parameter of a stored procedure so by doing this one then I have I will have of all the data in temper on table and then I will have the wrong number the next part once I have the data I unused on pivot here so right here executed then again I use this query on pivoted and execute it and then dump the data into the our final table so and fine execute if you don't have this one and you will microcell prize like okay why you are using this temp table here if you execute you should be getting the output yeah I will but SSRS I felt like there are some problems when it's executed doesn't show the columns and I have to go and write some time and that's not really behaving I was getting error so I thought might be getting in writing the data into the temp table and then selecting a solve my problem so that's why I did all that I'm going to do one thing here so I'm gonna print these select statements or whatever the statements are and show you guys so you can see that and yeah that's pretty much it so we can actually you know let's execute and now I'm going to go ahead and provide some values to this stored procedure so you can see see if I have something on top no I don't and we say execute this stored procedure the schema is dbo and I'm going to use that table called customer and then the column let's say ID comma name for my dresser if I use this one and execute now you will see some printed oh here see this is one of the printer statement we use to load the data into this so you can see that I have work remember I have added the fences around each of the column so you do that and then dump the data into the temp table if you don't want to just run it it you can run and you can see that I have ID name and address and I added this row number with Emma then let's see what other part was there and then here I use the unfiltered query so I don't really care because we didn't run this statement that's why this is coming to the adder so once we uncomment this should be fine now if we want to just take this part out you don't wanna insert into and I right and pivot so us selecting our and from this table we we didn't create that table and prepared let me show you here and this is where I was talking like I converted each of these columns cast it to the Walcott thousand because we would like to have see more for the unfit otherwise it will throw a cetera so here as we didn't write the directory tampered add a table so we are still getting these errors it's not going to work but that's the whole thing you have these column lists that you provide with a parentheses and then you cast them to the wall car if your you have a bigger column let's say you have a wall or four thousand or something some of them some of the columns you have to change in your query right here in your store procedure right here so you come here and change it now in our cases that's pretty much it I'm gonna remove the execute part and come in the print part and then let's execute our store procedure again I'm taking this very slow because I don't want to just fly on the things I want to show you guys what exactly the technique I'm using so see at the end of what happened we are only getting two columns or so three actually one is odd that's the row number and then we have column name and column value why I'm doing it because with the SSRS as the columns are changing so you'll see you are selecting any schema let's say we are selecting TB selecting this table and then you can select any columns so if I will use the tab like steer it's not gonna work because my column list is changing whenever the user can select anything from the column list term so I don't know how many column he will select so I have to build something dynamic ax and that's where what I did I have the column names and column value and then I have our is just a number that comes with it because we added the row number and then I used the matrix so so instead of using tablets I use a matrix item indices are as to handle that them so if you see here because the matrix can expand automatically depending on the number of the values of so you once you select the column if you have more columns its can expand automatically and then you will see the values so that's why I did all this work like bring the columns okay bring the columns here select the schema select the tables bring the column list and then select all the data from that table into the temp DB and then use the unpiloted and only show the data into d know like column and the value so once you have those two you are good now let's go ahead and take this store procedure and create our SSRS reporter the the whole script is going to be available on the on the tech browsers i t.com you will find the link in the description so you can use it you don't have to make any change really but it is good to idea to understand sometime let's say you want to add one more filter or date or where class maybe with so you would know that okay this is very the data is filtered so if you have another column say okay selected this this this from this code table and you want to put a ver class you can do it i did not use the veiled last year i can could have say okay select the column for where class and then select that and say value and then say greater than equal to less than or like and give them the option and they can select that so they can even filter the data further but i don't want to go for the in detail here you can build this thing dynamic and you see that this is where you will you will come back and add those of parameter values all right now let's go back here and create our report dynamic report first of all we are going to go to data sources right click add data source oh and call this call this one d d s tech brothers and then we are going to use a shear data source okay select that one now go to data set a data set and here I'm going to call this one and be set put it quote data and I'm going to use embedded and tech brothers IT and here I'm going to tell okay use a stored procedure and the name of the reporter the stored procedure reserve Porter now if I will go and hit OK it is going to bring me our that's the row number and this is a our column name and value this looks awesome so you see that three parameters are also created a schema name table name and column lister now if I will run this report is asking me provide my schema name I can type it if I want but it will good idea so we will have these value coming from the drop-down sir so I'm going to go ahead and create three more data sets to populate these parameters 'im let's create the very first called D sector D said schema now go to the embedded okay fine and then get the query I have the query here I'm gonna provide you guys in the post so you can use it so it isn't not really exciting query it is simple select name from the schema sorry select name as a schema name from sISTAR schemas and I'm only selecting the user-defined schemas so this one I am ignoring them so I'm say checking a beer name is not you know like this so if you want to even have this one let's say you want to create a report on system or objects and that's one you want to have it you can have it so make your list accordingly I don't really care about these ones so I'm gonna exclude them and let's go back here here's the query hit OK and now if you go to the schema names here parameter UK's none available values get value from query data set schema and provide the name that field now if I will preview you see that it is given me the drop-down so this is a one awesome feature I like about the parameters so you can make them dynamic come next is the column table name so let's go ahead and create another data set for the table name zone so we are going to create a list of the tables up here I'm going to call this one D set tables I'm bad and I have the query using the system objects already so I'm getting the table name from cyst information schema dart ableism and only the base tables if you are interested to get the views or you want to filter your I don't know if you would have you have many choices though so I'm not sure you are only interested to see the objects see I'm selecting only the base table zone so if you would like to select the views so remove this part them let's say let's get the views as well so we will say we're okay table schema is equal to schema and order by table name so we use and tables are considered the same thing here so I am also before that I was not selecting the views but now I'm gonna go ahead and select the views as well so change your query accordingly whatever you want and remember that here we have to provide scheme at the rates with my name it should be exactly the same so it will calculate it otherwise it's not gonna cascade them so it okay now we go to the table name and here say available values get value from query and tables own fine and it okay now if you preview your selection I see this is grayed out once you select the schema this is gonna show only the objects or table in this case later this schema because we had at the rate schema in the parameter value so for that D tables d set tables data set term so select se we have some of the views demon well now that the next part is getting the list of the columns to the table or the view so go to design one more time let's say create a new data set D sector column list or columns now go here we're getting closer so don't worry I'm not going to take a lot of time so here you see that I'm again using information schema dot columns and then I'm saying where table schema is equal to schema and name is equal to the rate name so these parameters are very important that these are going to make your query results cascading so have them in proper name so you see name here in the left corner report data pan so table name and column list so whatever you have it there so you have to provide also in the schema name so you see here I'm providing schema name as it is and the table name as it is so alright so good now we are all set go to column list and you see that we can have multiple columns for one table so I'm gonna say hello multiple values available values and say from the query columns and hit OK we are all set to here go to preview select a schema select the table let's say customer now you can select the columns this is great the last thing is the left to display the data here so we are gonna go to design and bring the matrix here the column list will go here the data values will go and here the rows will go so we are gonna go ahead and go to our primary your report data that's coming from the stored procedure so you hit bring the are here bring the column name here and bring the value here let's expand a little better to be straightforward I'm pretty tired of creating this I'll report all the way so anyways I hope this will be beneficial to people so here bébé oh now we have a customer and here we select the columns we want and now you can see that we have address ID and name and this row 1 2 3 these are the row numbers we can remove that them if we don't like it so click here this is the part of the group so just remove a delete column and only delete column so hit OK now you can bring it in the middle somewhere here let's change a color layer better so make up or make that look better I'm gonna go with the green background and on the green probably white color you can select any it doesn't really matter more colors so white and then make it bold and if you like italic fine and I'm gonna change the value to be at the point of or whatever all right so we are all good here now we can select any schema let's say we go TB Tech Brothers and then we can select the table here so total sale and then next we can select the columns so this is really good if let's say tomorrow you get a new view or anything in B schema that will show up here you don't have to create a report for that so you see if the columns that come in if I want to see all the columns I can hit the call the columns now you can see it brought all the columns so that's really awesome now let's go back one more time and as you see here we are saying now we have only one object in TB schema here we can set create view and call TB dot VW tbh dot total sale and that's it now if you go back to the reporter you can preview let's say we will select TV it will show you that view as well and now you can select the columns whatever so and then see the report a couple more things if you would like to add to this report you can go to design bring your text box here and now in the text box you can show from which table this report is come in go to the expressions and then here saying you are seeing data for and then here we can add the schema name and go to parameters schema name and plus put this period plus and then go to the table name and that will be I don't know it really helps some time like if the user would know that from which scheme or table it is coming and preview the report let's say we go to this devious scheme and this time go to the customer table and then we go to the ID and get the ID and name only these are the two columns we want to get and you see that you are seeing data for dbo customer and ID and column now if I want to go and say total sale it will refresh columns select all and let's refresh you you are seen that up for DB or total sale and you see all those columns again you want to select TB now it's more like a plane you know so it doesn't really matter so you can select the column what you like refresh and it will show you yeah so this is how you will be creating this dynamic SSRS report the bad part about that that's what I wanted to tell you guys now if you have millions of records so what is going to happen here in the store procedure first it is going to write that tons of records into your temp table okay so we write into temp table next we do on Theater so to create the only two columns the column name and the value so if you have a million rows in this table and then you have like ten columns so think about that so million multiplied ten so you will be having two columns and all the values will be zero so I don't know like let me show you guys I want to show you some this part I would like to show you guys so you can see what exactly I'm talking about so let's all traverse all procedure and now if I will run this store procedure for these three so let's take this one this time we are going to run and write the data into the temp tables so first of all you will see it will take the data from your table and write into the temp data table and add the row number to it because that's also important when you are doing unfit and we want to have these row numbers because these three columns will become the rows and we would like to have them oh and that this is what number is going to help because without that we can't really create this matrix here so now you see that we have 15 sorry five total records oh right here and now if I will run this part that's going to infer that term you will see 14 so if we go back and now take a look on this data so now you see that we have a cheat for ID one name and address that these are the two columns is creating her so we had let me put that together and show you guys first of all right here so you had columns then it build these two columns and have these values as rows so you see that it took ID as a column name and then I 1 name as armor and did this this is the column name and the value this is address and this value and it didn't put the novel so you don't have that that's why you have 14 because it didn't really need know to put it right here and then you see it went to the second row record so you have say ID to name Raza and there is no address so I didn't put anything so it's the same thing for curl it took the okay ID and put the value one name armor and expose it so you see that it is creating two columns with all those values though so if you have million of records and then 10 or 15 or 20 column it was gonna take some time to build this whole thing go and then on top of that then you're going to go to the SSR Esther SSRS is going to transform this the column and value back to your table oh so you see that here we selected all these columns and sorry not sure what happened here let me see ah because we have changed the store procedure and we have select queries that's why let's change the store procedures back to the execute the remove da and leave the print part there put the comments here and execute all trouser procedure so it can work so go back here now you're good let's say you have tech brothers and you have total sale and now when you select all these columns it is going to take these values though see these the column and the value and translate that into the table so - if you go really back here you see column name and value that's the only thing you see here the same thing and now it's going to translate back to your table so it's pretty much doing the same way so on from pivoting it's this was the outcome of the unfetter so that when you will go back you will do the pivot and that this is what you will see that's exactly it is showing you here so it will take some time to work on that part so if we preview right right now you select sales or TB select some column tables so in my case I have 10 or 20 records this doesn't take whole lot but in your case when it comes to millions of record this can be time-consuming processor so I don't really recommend working with these type of datasets when you have a large record but this is the dirty way to achieve but I don't have really other way where we can do this whole thing dynamic oh no again you can if you are fine with that maybe you can create your tables you know in nightly process and then run your subscriptions or the reports at night so you are not worried about like oh I don't care about the time but I care about like okay I don't have to recreate that report for each of the table so I'm gonna go ahead and do this way so if that's your choice this is one way to handle the things then you have dropdowns for users table names and schemas and everything all right thanks very much for watching this video and once again I appreciate you as always listening for a long time and some of the guys I see the comments very nice comments I just say they like my voice very much I'm just kidding bye I don't know somehow I like to go in detail try to explain what I have done here so I hope this will be helpful thanks back

Channel: TechBrothersIT

Views: 26,257

Keywords: TSQL Tutorial, Transact SQL, SQL Server Free Training, TSQL Free Training, Database, SQL Server Free Video Tutorial, TSQL Video Free Training, Step by Step SQL Server Tutorial for beginners to advance level, SQL Developer, SQL Interview Questions, How to create SSRS Report Dynamically with Dynamic Schema, Dynamic Table Name and Dynamic Columns, Dynamic SSRS Report, SSRS Report Dynamically, TechBrothersIT

Id: VgsvoUvjKkE

Length: 33min 53sec (2033 seconds)

Published: Tue Aug 23 2016