31 Jan 2014
4 mins read
Have you ever come across a situation where a report is demanded by the client in excel which requires heavy formatting? Preparation of such a report from PeopleSoft can become a daunting task if a right approach is not followed. If you are ever in such a situation, how do you approach it?
Let me take a scenario and provide a guided path to the solution.
Campus solutions support preparation of a select section of students who have applied for graduation. It’s achieved by providing a selection criteria and then fetching the results.
Once the candidates are selected the objective is to award them the degree at a convocation event. Convocation in a university is quite a formal affair which is attended by students, faculty, dignitaries etc. Lots of people need to seat in an auditorium, all in perfect order. The event can be better managed if all the seats are pre-allotted especially when the participation is huge. (Business process can be written to assign seats to graduating students) To make the seating arrangement seamless, it is prudent to have a pictorial view of the seats or a Seat-map. The map will give a view of each seat with a color code depending on whether it is occupied by Student receiving a degree, Student receiving an award, Faculties, Dignitaries, Reserved or Vacant seats. The map is quite handy for the event manager or admin.
The obvious choice to display this info is excel since size of the cells can be shrunk as desired to view the entire population and also colors can be easily adjusted. The challenge lies in the preparation of this report. (Screen below for reference)
Legend for Seat Map:
S – Student for degree
A – Student for Awards
T – Team leader/Faculty for department
R – Reserved for dignitaries
B – Vacant seats
So how does one go about using Excel to prepare such a complicated looking seat-map? There are two ways of approaching this.
The easier way out is to write a macro in excel (VB code) to convert the seat allocation data in the required format, prepare an excel add-in from the macro and make it available in website. The end user has to download the data as well as the add-in, install the add-in in a system and finally run the same to get the report. This was actually the way we implemented it for one of the premier institute in India. The total time needed to prepare the macro was not more than a day. However, not many clients would agree to this 3 step process. Many would like to have the luxury of simply clicking a button and getting the report generated.
Fortunately, there is another way. First we will take only a sub-set of the data which needs to be generated. It should cover all the relevant cases i.e. all the different color codes and the legend. Refer to the screen below. (Note that each cell also has a comment tag which stores the name/id of the person occupying the seat.)\
Once we have identified a subset containing all cases, the next step is to manually prepare a new excel with this data and format.From the ‘Save As’ option of excel, save the file as ‘XML spreadsheet’. This saved file has the extension as ‘.xml’ but it can be opened from excel with all the formatting intact. The objective is to generate this xml file from Peoplecode by studying the manually generated file. Let’s us now understand the xml (file included for reference) which is primarily divided into 3 parts.
All it takes is a click of a button for the end user to generate and view the xml as a formatted spreadsheet. The saved file even has an excel icon and is opened in excel by default, obscuring all the nitty-gritties.
A colleague of mine also pointed out that charts, graphs and this kind of reports can also be generated via xml publisher by preparing an xmlp file but that is for another day of research.
About the Author
Manufacturing and Consumer
14 Apr 2020
27 Sep 2018
14 Mar 2018
07 Oct 2015
18 Jul 2014
15 Jul 2014
30 Jun 2014
26 Jun 2014
Enterprise Solutions, Manufacturing and Consumer
03 Jan 2014
28 Nov 2013
17 Feb 2012
BI & Analytics
03 Jan 2013
Digital Assurance
04 Feb 2013
06 Feb 2013
Digital Assurance, Enterprise Solutions
14 Feb 2013
18 Feb 2013
21 Feb 2013
27 Feb 2013
Others
01 Mar 2013
04 Mar 2013
Enterprise Solutions
05 Mar 2013
18 Mar 2013
Digital Assurance, Enterprise Solutions, Others
22 Mar 2013
12 Apr 2013
26 Apr 2013
29 Apr 2013
13 May 2013
11 Jun 2013
17 Jun 2013
25 Jun 2013
19 Aug 2013
26 Aug 2013
27 Aug 2013
03 Sep 2013
10 Sep 2013
19 Sep 2013
24 Sep 2013
26 Sep 2013
30 Sep 2013
01 Oct 2013
03 Oct 2013
01 Nov 2013
19 Nov 2013
03 Dec 2013
20 Dec 2013
22 Jan 2014
27 Jan 2014
12 Feb 2014
13 Feb 2014
20 Mar 2014
24 Mar 2014
17 Apr 2014
11 Jun 2014
10 Jul 2014
16 Jul 2014
28 Oct 2014
13 Jul 2015
06 Aug 2015
26 Aug 2015
28 Sep 2015
26 Oct 2015
Infrastructure Mgmt. Services
07 Mar 2016
22 Mar 2016
13 May 2016
23 May 2016
Application Transformation Mgmt.
11 Jul 2016
25 Aug 2016
03 Sep 2016
14 Sep 2016
15 Nov 2016
22 Nov 2016
25 Nov 2016
Business Process Services
25 Apr 2017
Banking and Financial Services
18 May 2017
30 May 2017
23 Jun 2017
27 Jun 2017
18 Jul 2017
26 Oct 2017
Healthcare, Insurance
28 Nov 2017
11 Dec 2017
25 Jan 2018
21 Feb 2018
22 Mar 2018
23 Apr 2018
15 Jun 2018
18 Jun 2018
( Mandatory field * )
The information you provide will be used in accordance with our terms ofPrivacy Policy
Please Check on "I Agree" to register for the blog.