Designing a RDL report from multiple tables | RDL Report in Business Central | BC Tutorial

hi everyone in this video we are going to talk about designing reports from multiple tables I am Dr gomathi and in the previous video you can find the introduction to the report how to generate the word layout report how to create an rdlc report how to create a Excel report as well so those videos are very basic and here you can find how to combine multiple tables to generate the report before we proceed I would like to tell you that this is the GitHub page where I'll be uploading all the queries all the objects and all the code which I am using I am explaining you in the video and this is my blog uh so in this blog I'll also upload the details tips on how to create a report how to create a table so you can just walk through this completely so you can find the information the detailed information about what is uh the report and how to do it so this is the complete walkthrough like you will be knowing how to define the data set from multiple tables you will come to like adding fields to a data data item so data item here we will call like it's a table okay so table how to get the data item that is how to get the multiple tables how to define the properties for the table and how to add labels to the report how we can design the RDL report so I'll be telling you how to effectively design the RDL report you can set the filters and also you can build the running report so also you will learn how to aggregate some values and show up in the report so we are going to use four different tables the first table is the customer table this is the business Central object ID which is 18. next one we are going to combine the customer Ledger entry and the object ID number is 21 detailed customer Ledger entry which is the object ID is 379 and sales header the object ID is 336. so what are the field which we are going to get it from these tables okay so custom if you take customer table you can find more than 50 columns okay that is a field column from here so we are not going to uh fetch all the 50 columns we are going to just use only few columns from all these tables so what are the table which we are going to use it so number name phone number address and email from the customer table and these fields from the customer Ledger entry table these fields from the custom detailed customer Ledger entry table and this much fields from the sales header table okay these are all the fields which we are going to add it and what are the properties which you are going to discuss before I show uh show you the code let us understand what are the properties you'll be creating for the table sorry report so you can create a report using the uh T report snippet so once you give you can find that report you have to create with the report ID and the report name and inside that you have to you can specify general property which is specifically for the report okay report level property so those property or usage category which is equal to we'll be setting up for administration so usage category you have various categories for that okay specifically we are going to only use Administration category and next one is if you see the next is an application area so why you should use the administration category because if you go with an Administration category then you are making the report searchable from tell me so tell me in the sense there will be a search bar in your role Center so from there you can find that this is uh this specific report that is which one you are going to generate in administration category okay so and the next one is what uh the application area is equal to all what you're going to do is you're going to just make it uh make this application area is equal to all in the sense if you click on the tell me and if you are trying to search for your report it will show up the report completely okay and uh you can you we are going to define the default layout as rdlc because we are going to create an RDL report so RDL report is what uh the report definition language okay and rdlc layout we have to tell what is the name of our report okay that is what you can write any name here and then you have to keep a DOT and then you have to write RDL which is report definition language which is the extension okay so these are these are the properties apart from this we have so many properties for report but we are going to Define only this much property in our code the next one is the data item property okay so uh in data item property we are going to use these properties which is data item table View so data item table view is to sort the table view based on the field if you want if you have given something for example data item table view which is equal to you have to give sorting sorting is what the method name and inside that you have to specify on what column you are going to sort it okay you are going to sort it based on the number or you're going to sort it based on the name so here as I told we are going to get the number so I am going to sort it based on the number okay so the next one is request field filter property so it will include a specific field whatever the field which you are giving in the filter tab of the request page okay so I'll also show you everything so don't worry so this is the if you give anything like a number I have given number so this number will be included in the uh filter tab of the request page okay print only if detail so it will print data only if at least one of the specified table data item generates the output okay if there is no item in that particular field for example if I am fetching all these data but if there is no entry in the address field then it should not fetch any data from that the last one is the data item link this is used to join two tables because since we are going to use uh four tables now we have to combine these four tables so we have already discussed about this data item link property in query okay if you have missed that part please go and check that so that is the query so how you can Define the data item link which is the property and you have to tell on what two field you are going to combine these two uh table okay before that we'll be defining the table so this is the property for data item next is the column property this is specifically for the column include caption so it will be the caption will be included to the data set okay so so far we have discussed what are the table which we are going to use and what are the properties which we are going to use it in our report that is a code okay report code now I'm going to tell you what is a format you have to design how your report should look like okay when while we are you are sending the dripper to the client how your report should look like so what are the informations you should show what are the fields you should aggregate how to deal with the zero values and what is the orientation you have to keep it for your report okay what are the reports you should show you should show the customer information at the top of the report okay your top of the report should have the customer information for each custom customer the report must show a list of Ledger entries okay how what is The Ledger entry so it should show The Ledger entry for each Ledger entry the report must show a list of detailed Ledger entry Under The Ledger entries the report must display basic sales document headers information for the selected customer and each section of the data for each customer must begin a new page for example if you have first five customers then my customer data first customer retail should show up in the top of the report and then his relevant Ledger entry should be in the list and it should list the detailed Ledger entries and The Ledger entry section and everything okay this is for One customer so the first report should show and that is the first page of the report should show only the first customer information if I go to the next page then it should show the next customer information okay and what are the fields I should aggregate is what the amount field that is the amount field from the customer Ledger entry so you can see that customer Ledger entry here I have the amount field so this should be aggregated okay should be totaled that is it has to sum up and it has to show up for each customer dealing with zero values if you have zero values for example if there is no data or if you have a zero values in any of these fields then how you should deal with it should not show up the zero okay if there are no requires to display the report must not display the data section itself okay that section even that section should not be so show up for example if there are no sales document for a customer the sales header section should be skipped out okay and the amount field must not display zero values and the orientation is we are going to use the landscape okay so what is the portrait portrait will be like this okay like A4 sheet in a normal A4 sheet landscape in the sense if you just turn that sheet how it looks like so this is how my report should look like okay so I'll show you the code like what are all the properties we have discussed so far the same property I'll be showing that over how I have uh connected all these tables together in the report and then I'll show you Lively like how I am going to design the report that is a rdlc report in the report Builder let's see so this is the report which we I have created okay you can find that this is a report name and I have said like so we have discussed right what are the basic properties of the report which you're going to set so the usage category application area default and rdlc layout okay so other than that I am going to tell the first table which is the customer table so that is the first table which I have to give it inside the data item so the customer is the table name where this customer is the user defined name okay you can give the same name or you can even change any name this will accept but this name should be the table name okay that is the data item name and then as we discussed like about what is the data item table view which is used to store sort something so this is sorting based on the number okay request filter Fields if you want to uh that is what filter field you want I want number as the filter field okay and print only if detail true and these are the columns which I wanted in the report that is a number and include caption so this will also include the caption and the name of the customer the phone number address email everything okay so see this is coming inside the data item okay inside data set data set and data item inside the data item this complete column which I want I am just including it okay inside the date data item itself okay I haven't closed this curly place this block okay inside that I am going to create an another data item which is the customer Ledger entry okay so you can also tell data item table view how it should view like I am using a sorting and of course since we are just combining two uh table so we have to use this property this is a mandatory property even if you are not using this property no worries but if you are you if you didn't use this property then it will show up the error so I'll just show you if I am just trying to comment it out and trying to save it okay so what happens it will definitely it will not execute because you have to tell between on which two Fields you are planning to combine okay so that is very very important okay so I'm just using a data item link okay so on what two Fields you are going to combine So based on the customer number is equal to Field of number so in on these two I am going to combine it okay and what are the column entries so entry number customer number is the field of this table okay customer Ledger entry and number is the field of the customer table okay and then what are all the other field from this customer Ledger entry you wanted to use you can write that in the inside the column okay entry number customer number posting date everything okay and once if you include everything again I haven't closed the data item because I wanted to combine everything so I'm just giving data item and ledge entry okay so here I have to link everything so uh based on the customer Ledger entry number so what is the customer edger entry number what is the field I am just going to take it from the previous table okay this is the previous table from that I am going to get the entry number which is equal to uh the number that is a customer number which is equal to customer number okay so this is the common field which I wanted to tell for sure in the data item link property and here these are all the fields which are there in detailed customer Ledger entry okay these fields we are just taking out and other than that we are also uh using the fourth fourth a table which is a sales header and I'm just telling that this is the uh directly linked with the number column number in the customer column so here what are the uh column you want you can just make use of it and here I am closing the uh complete uh thing okay so I'm closing the complete uh report sorry here I am closing the data data set that is a data item here it is starting right so data set is closed here and next one is I am creating a label here okay so the purpose of this label is what so you can create a label uh this will be we'll be using it as a caption in the report layout okay we'll be using that while we uh use I'll just let you know so let us just press Ctrl and S to save and we will just build the extension okay in order sorry we will just build this x uh build this particular package because it has to generate the rdlc report so I am just pressing Ctrl shift and B you can find that multiple reports.al sorry my rdlc report dot RDL is created here okay this is this will be created once if I press Ctrl plus shift plus b okay so uh we can just open that we are just going to open it okay if you right click on this multiple rep sorry Mi rdlc report and click open externally it will open the report Builder so now we are going to see how to design your report based on the conditions like creating an aggregate column and everything and I have designed the rest the report okay I'll show you how I have designed this but what happens if you execute so here uh if you execute this code let us see how the report looks like okay so I'm just going to save this and let us execute this okay so without any filter if you try you will get the data for all these customers and I'm clicking preview and close you can see that the report is getting generated and this is the report okay so entry number uh what is the customer number and what is the that is this is about complete uh information about the customer okay what is the entry number seven because entry number is different you can see that if you click next you can see okay at the end of that particular customer retail so till this we have two thousand okay and at the end of the 2000 that is the customer detail you can see what is the total value for that particular customer okay if you click this is for a single customer okay two thousand and if you want for all the customers then you can uh execute like you can simp without any without any uh filter you can execute it you can just go and search for report for multiple uh filters okay so here comes the report you can see it is nearly 56 pages okay so thousand at the end of every uh every uh customer detail you can see the total this is for the customer number thousand if you go to the next you can see the uh the uh the information about the second customer and in the previous customer detail you can see that uh the number of that customer address name phone and email is available the phone number is not available in the database itself so it is not showing up okay you can some more format it for the better view for example you can open this and if you want to format it in a better way okay you can use that for example if you want this uh two things to be in bold okay just select it make it as bold and also you can select it and make it as bold okay Ctrl B and if you want to adjust the thing you can adjust it okay and if you want to use a different color you can use a different color likewise vice versa you can just try it okay so this is just to show you like how the thing will be generated okay your initial report will look like this so if you want to set up a header or footer you can do like this okay uh here and the report properties as I told if you wanted to change so initially what I did is I have added the list here okay so this is to bring everything under the same group okay and under this list and here you can see the left side okay I'm just making everything to the tablex property you can find you have to connect to the data set item data set results so this is what your result and data set the complete data set click ok so beginning we have just added the report and we have set the page type and we have added the list control list uh control to the thing and then on the right clicking on that you will be having the rectangle property we have changed the color of the rectangle using the fill color and then if you click on the any border you can see the tablets property so in the tablets you have to first connect to the data set name then only your data will be available over everywhere and then you have to right click go to the row group go to the group properties and then the general you have to group based on the customer name that is a number and then go to the page breaks and then you have to select the between each instances and click ok so this is the basic thing which we have done now we have to add the customer data okay um so now we have to go to the insert Pane and then we have to uh drag the table control okay into the list property okay we are going to Okay one minute so we are going to drag the table control we'll just tell like insert table and to the list property okay I have added it and you can resize uh this particular to the half of the width or something like that okay half of the width or half of the height based on how your report should look like because under that we have to Total the sum right the so total the amount So based on that we have to resize it okay uh so the table which contains the table row and the header okay this is the data the header will be here and the data will be available here okay and the three parallel lines in the left border so you can see that this is the uh line which you have in the left border okay this is the three parallel lines the second row identify the data row okay this is the data row and this is the header okay so you can select any table row you can right click on the Shaded border okay you can right click on the Shader border anything go to the tablets property window you can right click on any shaded border of the table okay not on the um not on the list okay go to the tablets property okay and now on this General tab as usual how we have changed how we have done a field set to data result okay so just ensure that you have to set this field to the data result and click ok so let us also this is a different tablex okay and this is different one this is the Shaded area you have to cross check whether this is properly enabled okay so uh the table has already here you can see that the table has three columns okay so since we have to add the customer data we can also add one more column here right click insert column to the right okay you can add one more data here if you want it you can add it okay so now uh what you can do is um so if you want to delete any rows okay you can right click and select the uh you can delete okay I'll delete the row delete a column so all the properties which are related to row and columns you can find it here okay so now we can select the second table row and we will right click on the row and choose the delete row okay we will just delete it okay okay the row is deleted okay and uh the remaining table row right like we can right click on the Shaded border on the left okay and you can also insert a row and you can make it to insert it below okay so this is like how many rows you want you can add it how many columns you want you can add it so here this is basically the data okay here the data will appear so what I wanted is I want three header rows okay I want three header rows not the data row so what I'm going to do is I'm going to right click here and I'm going to delete the row okay it will be asking you delete rows and Associated groups click s and then click on the header row and insert row below so you can see that this is also the header row and this is also the header okay you can find if you click on this icon you can see the list of properties here okay this you cannot see in the data okay only in the header so again do this right click and insert a row below even this will become the header row okay you have now three header rows here I hope you all can understand I have created the three four columns that is I have added one more column and now I have select I have deleted the data row and then I have created the three header rows here okay and if you just go and click on the first and that is the first to sell there is a row is also one this is the first to sell and if you right click on the table and you can see the expression okay so once if you right click you can see the expression here you can see that this is the three important thing so this is the category column this is the item column in the category column you can uh you can see that you have various options okay you can click the parameters okay if you click the parameters in the category column you can find the relevant detail and the value field okay values field okay so now in the value column we are going to double click on the number of customer caption okay so this is the caption so we have clicked the number of customer caption and and once if you click on this you have to check that the parameters so you have to check once if you double click no you can see what is the set expression value for that okay and you can modify this expression because we need the uh customer okay so that is we are going to just modify it we are going to add a add a function called first okay first and then give this completely inside the first expression and you can just click ok ok you can see that the expression is coming up here I just wanted to change the uh property that is uh green is not much visible I'm just going to make it as a very light okay um just okay so this will be fine so what happens in the sense um there is a first which means the first value for the caption fields in the data set is retrieved and this is used as a caption okay that is why I have used the first expression here okay right click on the second cell which is the row one and the column two and you can also go to the expression here and once if you click on the expression in the category field you can select the parameters okay uh category C field okay we will just go ahead and select the fields data set result okay and on the right side you can select the number customer okay and if you double click you can find the expression value for that okay you can see that Fields number of customers dot value okay you can simply click OK you don't want to do any changes here okay so this we are going to use it like uh we are going to uh do that is we have to imp you uh get the all the details of the customer in the page okay so that is why we are going to we are going to do it now okay so in the row two this is the Row 2 okay and on the column one right click go to the expression and you can select okay go to the parameter and select name customer caption Okay click ok double click you can see okay so this is the thing which we want if you want to if it is a parameter you can just make it as is equal to First and then click ok okay and uh if you go to the this is uh Row 2 right Row 2 column two you can select uh to the expression select the field and then you can okay in the second one I don't want anything in the first row in the third column you can go to the expression and you can go to the parameters and select the address customer caption double click here and make it first and click ok here so what we have selected here we have selected [Music] um name customer caption right okay here you have to select go to the expression go to the fields name customer double click and click OK and here you have to select address you have to double click to in the watch we have selected address customer caption right so here you have to select go to the expression go to the fields address customer click ok so I hope you are understanding what we are giving right expression if you then double click it okay address customer double click the expression should come up here then only you can see that here okay expression name of the customer and here we have to go to the expression go to the parameters and here we have to add the phone number of the customer double click you can just make it as first okay first and then click OK and then here we have to add the phone number of the customer go to the expression uh Fields okay I hope you are understanding this is the parameter we are passing so we have to it will come along with the caption if you click on the field it will come properly as the field so click the phone number of the customer click ok ok I haven't double clicked it field phone number click ok okay and then I need the customer details okay these are the customer details which we are fetching out okay so here you have to add the parameters and then you have to add the email of the customer double click it go to the first and then close it click OK and then in the right click expression [Music] fields email click ok ok now you can see that we got this uh this one so this is the um this is about the uh customer information okay and so now let us just check until this whether we have really um uh really done without any mistake just to control save this okay go to the um Visual Studio code Ctrl shift and B check whether we have so what we are going to add data for Ledger entry and the detail regen entry okay so now from the insert we have to add the table control into the list control so this is my list control right okay so this is the table and this is my list control so let me just uh make the list control some more bigger okay so this is the tape this is the um list to control okay we are just we just wanted to add some more data into that is we have to inside the table uh we want the this inside the list we want the table so I'm just adding the table property here okay so for this table also we will be having the table property right so this is the separate table okay so we have to go to the tablex property and we have to ensure whether the data set result is there okay and select click ok so we have to um insert the row okay so try to insert row and here it will be asking like uh to inside the group or outside the group okay so we have to use outside the group below okay so outside the group below this will add another data row to the table and so we have one header row and two data row this is the header Row the first one here you can see if you go to the header row uh okay this is the data row this is the header row okay now we have to choose the First Data row this is the First Data row okay right click on that and you can just go to the add group and then you have to choose the parent group okay and you can see this is the tablex group window okay select you have to group the group by based on what you have to group by based on the entry number customer Ledger entry okay and you can just give add group header okay and then click ok so you can see that this is the group item so again what you have to do is right click on the first row and then you have to insert row so we have to insert 11 columns here so just follow what I'm doing so here if you just click on others and if you click on insert Row in the inside the group which means like it is like a merging okay you are just making it as a uh one particular uh com one particular column on the row you are just trying to merge it okay that is what grouping find so we are just making it anything number customer Ledger because based on the entry number it has the custom the complete details about the customers should come up here okay so we are going to add I have created 11 rows so you have to add the customer number Ledger entry posting date everything we are going to add it now so for that please just follow what I'm doing I'll be doing the task repeatedly like I have to right click okay so this is the second column I have to right click I have to go to the expression the expression window will open and in the category column I have to select the parameters like how we did it in the previous one okay so parameters and I have to pass the entry number customer caption double click on this and make sure it is there and we have to if it is a parameter we have to just we don't want the caption so I'm just making it as first and then I'm clicking Okay Okay click OK your expression will be here okay and again what I have to do I have to do this for everything like in what what should come in the column three column four and column file I'm just configuring it just uh understand what I'm trying to do okay right click on this go to the expression go to the parameters and next one what I wanted is customer number customer Ledger entry double click and make it as first okay and because we need only the uh we need we don't want the caption to be displayed Okay click ok uh double right click and the third one is we need the posting date Okay click the parameters go to the posting date double click on it make the first and then click ok okay then next one is the document type I want the document type here expression parameters document type double click okay we have to repeat this until how many values you want if you want only three values then you can leave it as such okay if you want more values then you have to go ahead with that um so what we have recently taken okay posting take document type is fine document type okay customer number posting date document type which means document number we have to take the document number okay how much you want you can just go ahead with that okay description so if you want only till this so I'm just chopping till this okay if you want you we want amount right so I'm just adding the amount also here because I wanted the sum of the amount so I'm just going to add the amount I'll just leave it still the amount so if you want add some more you can add it okay so amount a customer ledger so I'm just adding the amount here okay and click OK and then other than this we will just remove it delete column delete and delete okay the same process you have to repeat it okay and these are the data from the entry table okay so here you can see that this is the data from um this is the data so First Data will be from customer Ledger you can see that this is the field name and this is the um table name okay number underscore customer number is the field name and customer is the table name okay likewise you can see this is a customer Ledger entry okay and if you just scroll down you can see this is a detailed customer Ledger okay and this is the sales header so whatever you want you have to add it accordingly okay so first one you can see that customer I have added all the data from customer and the detail entity till this okay the remaining amount you can see that this is still the remaining amount so this still this like from data from the customer and the customer Ledger Andrea have added here okay and the second one which I have added is what the information about uh that is the data from the debited uh sorry detailed customer Ledger entry and the sales header okay all these data I have given and here finally what I have added is you have to add the total field okay sum sum is the function which is used to add the data so here it sums up all the value and it will bring the total amount okay of that particular customer so you have to Simply add the sum you don't want to do any calculation any aggregation in your Visual Studio code just do it here once everything is done you can simply save it and then execute it that's it okay it's very very simple uh so execute to save this once you are done save this close this and then press Ctrl and S and control and execute you will get it so I'll be posting the detailed step of doing all these things in my blog so please visit it and if you have any doubt please leave your doubts in the um comment box

Channel: goms tech talks

Views: 10,141

Keywords: Designing a report from multiple tables | RDL report layout, desigining a report from multiple tables | rdl report layout, designing a report fomr multiple tables | rdl report layout, designing a report form multiple tables | rdl report layout, desigining a report fomr multiple tables | rdl report layout, Reports in business central, RDL report in business central, reports in BC, BC video tutorial, business central, business central dynamics 365, business central tutorial

Id: Ho6Fc7qeERc

Length: 40min 25sec (2425 seconds)

Published: Thu Oct 06 2022