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 2

Roy Rumaner - March 5 2013 03:30:00 PM

If you remember, in my last post I discussed an Excel report that the client wanted us to recreate in XPages.

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

Now that number 1 and 2 were done they, of course, asked for more. No problem, that is what we live for and love to do. Number 3 was an interesting concept. I knew it could be done, I knew that with HTML and some Dojo I could give the user a way to select a color from a set of colors but I was not sure how to do it.

What I needed was a way to indicate a status rating per line using color bars (Figure 4). (Personal Note: I have to add a shout out to my friend Mike McGarel for his assistance on the Dojo code for this piece.)

I added computed fields to the end of the table to display the selected colors. (dp_e, dp_g, dp_p, dp_d, total)

Figure 4: Status color bars

Image:Using XPages to develop complex reports - Part 2

The code for each Status field (Code Sample 10) set the CSS. Each color field value was set by the scriptBlock (Code Sample 12).

Code Sample 10: Computed color bar field

<><![CDATA[#{javascript:return "border: 1px solid #000; height: 18px; background-color: " + getComponent("ecs").getValue();}]]></>

I also needed to give the user a way to select the colors for each Status field (Figure 5). Because I wanted to show an actual color (Figure 6) instead of the name of the color I had to use a repeat control (Code Sample 11) to get this to work.

Figure 5: Status color selectors

Image:Using XPages to develop complex reports - Part 2

Figure 6: Color bar repeat control

Image:Using XPages to develop complex reports - Part 2

Figure 7: Selection panel

 Image:Using XPages to develop complex reports - Part 2

Code Sample 11: Table cell (Program 1) with embedded color bars and repeat control

              style="background-color:#fff;"> -- Default -- </option>
                              <xp:this.value><![CDATA[#{javascript:return "<option value='" + rowData + "' style='background-color:"+rowData+"'></option>" }]]></xp:this.value>

The hidden fields (ecs, gcs, pcs, dcs,tcs) to the right of the repeats held the choices made in each respective row. The status field, the selected color and the ID of the hidden field are passed to the scriptblock and the background color in the appropriate row is then set.

Code Sample 12: changeCell script block

      <xp:this.value><![CDATA[function changeCell(targetId,selColor,hiddenFieldId) {,'backgroundColor',selColor);

As clients do they then asked to be able to save and restore the selected colors. The application already had a Keyword Profile system built into it so it was a simple matter of creating a new Keyword and storing (Code Sample 13) and retrieving (Code Sample 14) the color values. The code to retrieve the color values was added to the beforeRenderResponse event so that the table would display the set of colors that were previously saved.

Figure 8: Keywords METRICS document

Image:Using XPages to develop complex reports - Part 2

Code Sample 13: Writing the color values to the Keywords document

var ec = getComponent("ecs").getValue();
var gc = getComponent("gcs").getValue();
var pc = getComponent("pcs").getValue();
var dc = getComponent("dcs").getValue();
var tc = getComponent("tcs").getValue();
var kwView:NotesView = database.getView("keywordsLU");
var kwDoc:NotesDocument = kwView.getDocumentByKey("METRICS");
if (kwDoc == null){
      var kwDoc:NotesDocument = database.createDocument();
      kwDoc.replaceItemValue("form", "keywords");
      kwDoc.replaceItemValue("keyword", "METRICS");
      kwDoc.replaceItemValue("kwNotes", "********");
kwDoc.replaceItemValue("value", ec + "~" + gc + "~" + pc + "~" + dc + "~" + tc);

The user also asked that the color be set each time they reopened the report. TO do this I added some additional code to the beforeRenderResponse to do a quick @DbLookup for the "METRICS" Keyword in the Keyword table, retrieve the string of colors and set the Status colors accordingly.

Code Sample 14: Additional code added to the beforeRenderResponse event

var colors = @DbLookup(@DbName(), "KeywordsLU", "METRICS", 2);        
              var x = "alert(An error has occurred in the keyword color lookup. Please notify tech support: code 12A1)";
              sessionScope.put("values", colors);
              getComponent('ecs').setValue(@Word(colors, "~", 1));
              getComponent('gcs').setValue(@Word(colors, "~", 2));
              getComponent('pcs').setValue(@Word(colors, "~", 3));
              getComponent('dcs').setValue(@Word(colors, "~", 4));
              getComponent('tcs').setValue(@Word(colors, "~", 5));

I think it ended up looking pretty good and the users were very happy with the results. Of course once again they asked for more changes.

Figure 9: Final result

Image:Using XPages to develop complex reports - Part 2

In the next post I will discuss how I added in the selective view panel.