Oracle XML Publisher – Excel Sheet – Single, Multi Sheet Samples

hi in this session we'll discuss about how do we use excel template designer reports so as you know that like we have a concept called XML publisher so in this far in this particular example publisher tool the predominantly RTF templates are the mostly used templates but innovation today what we do is try to introduce a concept called XML template excel templates and using excel templates how can we design a report and in Excel templates we have two particular major important concepts one is just having it having a single sheet report and other one is a multi sheet report so what we do is we'll try to understand what is the pre-quiz sign to design the template using xml templates you sorry using excel templates and how can we generate the data in a single sheet or a multi sheet okay and one but one more basic thing in the excel templates is that output can be in only excel sheet it cannot be in any other format so that's a thing we have to consider in mind when you are designing the designing the report in report using excel sheet templates so let me show you like over to the configuration as a developer we have to do it okay so using excel sheet template so these are the things you have to consider and the version of ERP which I have considered is Oracle ERP 12 one three and the first thing is you make sure that this particular lookup value is set so by default it may be disabled in your thing and make sure that the value for this XLS code enable this one and the tag for this one should be x0 underscore XLS underscore templates underscore output okay and this is what I mention here and the next thing is and for this one you just make to make sure that there is another lookup which is there make sure that is also enabled so these two lookups so here you need to add up the tag here and this next lookup you can make sure that this is enabled and the other one also get enabled and the other thing is once you enable that you can see Microsoft Excel if you don't see Microsoft Excel as a type then you cannot design the excel then you know I mean to say that you cannot use the excel sheet templates in your system so these are the free cosign for these lookups I'm into selected to design the register and Excel template and the XML publisher make sure that these lookups are done properly okay now let's see how do we go ahead and design the Excel she templates so I'll try to show you the data model and my excel sheet template which have designed so let me open my data model here so in the data model I just have a very simple simple data model just say EMP table and DPG table so I'm just generating a tabular kind of group a single group report and this single group report I mean to say like I have a single group here this will generate the EMP data so now how can i generate a tabular report using excel sheet templates it's quite easy so what we can does let us understand what is the thing I have to consider so what you do is open you like a design a new excel X file make sure that it should be excel as not xlsx it should be XLS only it supports only XLS and once you once you create a new sheet and you know like the initial sheet this is what the sheet will be and you know like there will be something like if you see any of the standard templates which article has already provided you will see this particular information called X 0 underscore metadata so this is not required so what you can do is just design your columns and then in the next line the first line we should be you heard column headers and the next line what you have to do is these particular things you have to design your X 0 tags so the syntax is very easy so the syntax for these exterior columns is like this so just X 0 underscore followed by the element name nothing but in our case in my case one minute it should be prefixed by X Divo underscore and followed by the element in question mark EMP I know this my element like this ok like this ok so this is a syntax for each element if you observe here for each column I just mentioned this particular limit here XD 100 score question mark EMP number and the same way I just designed these all things like EMP name job manager hydrate salary and the name so there is one more thing here so there is no need to install the XML which is the excel template plug-in for your excel sheet that's not required what you can do is so just click on formulas and click on name manager so here you can create new names now if you observe in my case at this place I have this column right department name EMP name EMP number all this so for each individual columns you have to have this one and also there should be one group element you have to design that so what you have to do is to design the group element select all this columns here like this and then click on name manager and you can I mean you have to define a new name and if you see my group here this micro where I selected the columns from can you see you it shows the what you say in the position right from A to G right a to G dollar two nothing but like what you call the column a the second row from a to – G – that is what it means right just click on edit and you can see that right this how we have to mention this this is my syntax for the group so now our report is ready I mean this error template is ready the next step is registration process your template the registration process just a normal the way you register with active template the only thing is while registering the template you have to select the template type as Microsoft Excel that's the only thing remaining process just as it is so click on template let me show you the existing stuff which I have done exits el-tee yeah this is one EMP excel sheet report so yeah so I just need to register the template with a pipe excel sheet default output type as Excel and the template type can you see the template type it is Microsoft Excel so the only thing is here you know like it cannot like if you consider the RTF kind of thing what you do is you you'll be uploading the data data into rdf and you can just insert all the text right but here as a developer we have to write manually everything okay now let us say if you want to mention one more extra thing let us I would like to display some extra in some ways I mean do if you want to add up a tag here what is the thing you have to do it right so that that out you may have it what you can do is let us say I don't have Department number II right so maybe in my case let me open my data model so do I have Department number I don't have it right so let me add up here Department number so let us say in this data model I will add up one more column d dot d pj n Row one minute and let me add up the tag here Department n whoa and once you add up the tags here so or can you add up the equitable specific tag isn't it so what you can do is let us sell add up here department envel write department I know now what is the syntax so let us see that so copy the element name now we just need to follow the syntax xD wander score question mark then followed by our element name that's it yeah copy this now go to the excel sheet now click on name manager right you can just simply click on you and here can you see the position here the refers to which position refers to which cell I mean to say in the name you just need to mention that right it refers to sheet 1 h2 this is my head this is my column just simply click on OK and make sure that you change the group also because initially it was referring till G right so now the group code change it to H click on edit and now what you do is you can just simply like simply you can do like also just click on this particular refers to position and you try to select all the columns which it should be part of a group so this is the one this is my group now now click on OK click on close now change the column for this one change the color I mean right yeah now a template is ready so make sure that you upload the latest data model as let's template into the instance click on update so upload the latest template now okay so now let's select the template click on browse select the template here this is my XLS template apply go to data definition and XXL T and the score EMP click on go update the data definition update file browse latest data model apply you now let's run the report from the server EMP XSLT EMP Excel report this is my concurrent program this is my template name click on submit click on view output and let's see the output right perfect so we got the department number we got all the employ details right ok so this how we can design an excel template in Oracle e-business ODE we'll check out one more sample where we would like to display the data in multiple sheets based on the department so now what we do is like whenever you have a data and where you have a hierarchical data and you want to display the data in in different sheets what is a possible way we can do it right so let me show you another sample so now let me show the data model which I have so this is very simple one in this one what I have is I've considered two queries one is for the department details another one is the employee details so this minor query Q 2 is minor query and Q 1 is my parent so nothing but department data and my data so here I'm so my this is my G underscore Department is my parent and G underscore EMV is a child ok so this our data model where the data will get generated hierarchical manner for each particular department you will have a list of employees so that's the data will get generated here and you can also see the data here so I have a list G Department first then G Department number in this department it will show the list of employees there so it will get displayed now how can you see how can we display the data in multiple sheets that's the thing right so the total logic depends upon the excel sheet templates so open by I will just open the template here and the way we design about exit the tags earlier we have to design the same way if you observe here this is my department tags this is my implied tag range this all my employ data and this is my department data now the logic to generate the data in multiple sheets depends upon the X 0 underscored metadata definition where we have to define this one so so this should be a thing we have to follow here and generally by like how can you know that like how can you know all these columns and what you have to mention right so this is standard template where you can get from that information and followed by the data constrains what should do is here there is a tag called x0 sheet this are in this one we have to mention on what basis you want to generate the sheet nothing but on which group you want to generate the data into multiple sheets so in our case so based on the number of based on the number of records which are available in the GN this could be part mind group that number of sheets we want to have it right it's just simply mention that group name so just simply follow this particular syntax ok and based on this what happens is it will generate the number of sheets and what is the name of sheet you want to keep it here in my case I would like to mention the name of the sheet as department name followed by the number of employees in a given department so here we just mentioned sheet name is my tag name data constraints tag name so just a simple syntax department name followed by the number of employees in a given Department that's a syntax I mentioned here okay so the registration process again same now let us see what will be the hot output how it does the output look like ok xx LT EMP multi sheet report Submit so the depends upon the data model as the less the excel sheet things Excel she ate eggs yo constrain syntax so now I'll click on view output click on save it is let me open this yep now going to see for accounting I have twenty one employs research there are eight employees sales there are four employees operation there one employ right and this how it goes on based on the number so you can naturally this you can hide this particular consumer metadata this should they should not be shown to the business user what you can do is while uploading the template just hide this will hide this particular sheet and then uploaded that should that is how it should be ok so just see here click in the employee table we have how many records we have yes so count rows so we have thirty sixth course and if you see what happened is like according to the department like a twenty one in the accounting department that I ate in the research department right and there are four people in the sales department twenty nine plus four thirty three thirty four and then and yeah so there may be like that there could be two implies who does not have any department because we have performed equal join right so that is how it was generated so this petition report will be helpful where you want to design the hierarchal data I'm into silica let us say you want to see the inventory data for each operating unit and there is no really that will be single centralized centralized research team which will maintain the inventory information and they want all the data on a single sheet they don't want to have multiple outputs for each particular inventory this particular report will help you out in generating all the inventory data for the total key business soared in a single sheet right nothing but let us say consider client is having ten inventory ten ten what you sit in inventories okay and they want to have all the data all the inventory data data based on inventory organization so generally what is expectation what we do is like we will ask the business user to submit the report and mention the organization based on that will be generating the output right but if users want if a business user say that they want to have the data in this particular sheet kind of fashion a multi-hit kind of fashion you can consider this particular temperature this particular template approach this one of the very good a sample for you know like good good this is this is this particular multi sheet is one of the good advantage whenever you want to generate the data like for a business page on the inventory or based on the purchase order types something like that right in a hierarchical manner this will help you out and this will then this particular one most businesses are likes if they see is if they consider that this particular output type okay thank you

4 thoughts on “Oracle XML Publisher – Excel Sheet – Single, Multi Sheet Samples

  1. Hi sir in this video you have showed XML data template for multi sheet report can you pls tel me how to generate that data template

Leave a Reply

Your email address will not be published. Required fields are marked *