TeamSpace
Quality vs. quantity Microphone
Home Who We Are What We Do What We've Done Why Work For Us Blog Contact Us

Using XPages to develop complex reports - Part 1

Roy Rumaner - February 28 2013 11:00:00 AM


We are often asked to create complicated reports for clients. Recently, I was asked to reproduce an Excel spreadsheet report that was created manually every month.

The request was:

1) Reproduce their existing Excel report. (Original request)
2) Allow them to select any month to report on from this year or the previous year. (Original request)
3) Add an option to set the status of a Program in a color coded Status column. (Additional request 1)
4) Display fields from the documents regarding the various data points below the report. (Additional request 2)
5) Show the documents that were used to calculate specific columns in a table and let the user open the document from the list. (Replaced Additional request 2) Not covered in this posting

Prior to our building this XPage report, the users would spend at least a week calculating all the various data points and then send them to someone who would then create an Excel spreadsheet. With this XPage, they are able to instantly select a month and have the report produce the data they are looking for.

The report pulls data from three different views and also some fields on the associated documents. Initially the report was to be a simple table (Figure 1) along with a drop down combo box (Figure 2) to select the month and year for the report.


Figure 1: Original table request  

Image:Using XPages to develop complex reports - Part 1


Figure 2: Date selection combo box

Image:Using XPages to develop complex reports - Part 1


Because the customer wanted the current month to also be the default date for the report when it first opened the current month had to be the first element in the combo box. The rest of the list had to be all the months of the current year (ex. 2013-03, 2013-02, 2013-01) and the entire previous year (ex. 2012-12, 2012-11, 2012-10, 2012-09...2012-01). I used two For loops in the beforeRenderResponse event (Code Sample 1) to create the viewScope "ssDates".


Code Sample 1: beforeRenderResponse

var mnthC = "";
var currYr = @Year(@Now()) ;
var lastYr = (@Year(@Now()) - 1) ;
var currMth = @Month(@Now());
var rtnArray = new Array(0);
for ( var i = currMth; i >=1; i-- ){
  if(i<10) {
          mnthC = "0" + i;
  }else{
          mnthC = i
  }
rtnArray.push(currYr + "-" + mnthC);
}
for ( var i = 12; i >= 1; i-- ){
  if(i<10) {
          mnthC = "0" + i;
  }else{
          mnthC = i
  }
  rtnArray.push(lastYr + "-" + mnthC);
}
viewScope.put("ssDates",rtnArray);
if(viewScope.yr==null || viewScope.mth == null){
  viewScope.put("yr", @Left(viewScope.ssDates[0], "-"));
  viewScope.put("mth", @Right(viewScope.ssDates[0], "-"));
}

Every time they changed the selection in the drop down, they needed the entire report to recalculate. I used the onChange event in the combo box along which caused a partial refresh to fire refreshing the report panel and all the calculations based on the new date that they chose.  A couple of lines of code in the combo box Event got the newly selected date (Code Sample 2)


Code Sample 2: onChange event for Combo Box

viewScope.put("yr", @Left(getComponent("cmbYear").getValue(), "-"));

viewScope.put("mth", @Right(getComponent("cmbYear").getValue(), "-"));

This created the viewScope array "ssDates" and two additional viewScope variables "mth" and "yr" that are used to hold the report date selected. (Figure 3)


Figure 3: viewScope variables

Image:Using XPages to develop complex reports - Part 1

Internally, the report was using three different views to retrieve the data. Columns 1, 2 & 4 were from one view, column 3 from a second one and 5 & 6 from a third. These views were all keyed off of the date and the key, the row in the table.

Every calculated cell in the report called a specific JavaScript function that was passed a set of parameters and simply returned the values. For example, the column YTD Dev Assignments (Code Sample 3) called the ytdTotal function (Code Sample 4).  (Note: now that I know a little more about Managed Beans, this would have been the perfect place to use them)


Code Sample 3: YTD Dev Assignments

<xp:text
  escape="true"
  id="computedField1">
  <xp:this.value><![CDATA[#{javascript:ytdTotal( viewScope.yr, viewScope.mth, "Program 2", 4, "xpYTD" )}]]></xp:this.value>
  <xp:this.converter>
          <xp:convertNumber                                                                                                
                  type="number"                        
                  integerOnly="true">                                                                                        
          </xp:convertNumber>                                                                                
  </xp:this.converter>                                                                        
</xp:text>


The viewScope.yr and viewScope.mth are passed into the ytdTotal function along with the key for that particular cell, the column of the view and the view name.


Code Sample 4: ytdTotal function

function ytdTotal( yr, mth, opp, col, vw ) {
  var mnthC = "";
  var mnthN = parseInt(mth,10);        
  var total = 0;
 
  for ( var i = mnthN; i >= 1; i-- ){
          if(i<10) {
                  mnthC "0" + i;
          }else{
                  mnthC i
          }
          var key = yr + "-" + mnthC + "~~~" + opp;
          if(!isNaN(@Sum(@DbLookup(@DbName(), vw, key, col)))){
                  total total + @Sum(@DbLookup(@DbName(), vw, key, col));
          }else{
                  total total + 0
          }        
  }
  return total
}

This allowed me to reuse the function every time I needed to do a Year to Date sum regardless of the view or column. As an example, the next column (YTD Total Engagements) uses the same code with the only exception being the column number.


Code Sample 5: column 2

<xp:this.value><![CDATA[#{javascript:ytdTotal( viewScope.yr, viewScope.mth, “Program 2", 3, "xpYTD" )}]]></xp:this.value>

The next column (YTD Closed as Win) is slightly different as it is doing a count of the elements in a field on the document instead of a sum of the view column. (Code Sample 7) It also uses a different view.


Code Sample 6: column 3

<xp:this.value><![CDATA[#{javascript:ytdCounts( viewScope.yr, viewScope.mth, "Program 2", 1, "xpClosed-WIN" }]]></xp:this.value>
 


Code Sample 7: ytdCounts function

function ytdCounts( yr, mth, opp, col, vw ) {
  var mnthC = "";
  var mnthN = parseInt(mth,10);        
  var total = 0;        

  for ( var i = mnthN; i >= 1; i-- ){
          if(i<10) {
                  mnthC "0" + i;
          }else{
                  mnthC i
          }
          var key = yr + "-" + mnthC + "~~~" + opp;
          if(!isNaN(@Elements(@DbLookup(@DbName(), vw, key, col)))){
                  total total + @Elements(@DbLookup(@DbName(), vw, key, col));
          }else{
                  total total + 0
          }        
  }
  return total
}

Column 4, YTD Closed Revenue, also called ytdTotal but used a different view and column. The formatting was also set to currency instead of decimal.


Code Sample 8: column 4

ytdTotal( viewScope.yr, viewScope.mth, "Program 2", 2, "xpClosed-WIN" )/1000

The total row was done in a similar manner. For example, the YTD Dev Assignments Total (Code Sample 9) shows the same four calls, one for each row, and then computes a total and returns the result.


Code Sample 9: YTD Dev Assignments Total

var a:int = 0;

var b:int = 0;

var c:int = 0;

var d:int = 0;

var sumit:int = 0;

a = ytdTotal( viewScope.yr, viewScope.mth, "Program 1", 4, "xpYTD" );

b = ytdTotal( viewScope.yr, viewScope.mth, "Program 2", 4, "xpYTD" );

c = ytdTotal( viewScope.yr, viewScope.mth, "Program 3", 4, "xpYTD" );

d = ytdTotal( viewScope.yr, viewScope.mth, "Program 4", 4, "xpYTD" );

sumit = a + b + c + d;

return sumit

In the next blog post, I will discuss how I added the color cells and their selectors.