Like most of us, I write a number of agents to create customized Excel and web reports for users. Many times, this requires either the creation of a new view or using a NotesDocumentCollection to get a handle on the documents that need to be reported on. The down side of creating a new view is that doing this too often can really impact the size and performance of the database. On the other hand, the NotesDocumentCollection can be quite inefficient and returns an unsorted set of documents unless the FTSearch method is used. The question then becomes how do I get the flexibility of a NotesDocumentCollection combined with the sorting capabilities of a view and present it all to the end user via AJAX calls. The solution i came up with is something that, like AJAX, is not revolutionary in the tools it uses but rather in the way they are used in combination. In all honesty, the motivation behind this method came from my laziness, as usual. I just didn't want to have to write a routine to sort a 3 dimensional array based upon input from web form, but I digress.

If you have never worked with views other than getting a handle to one to get the documents it contains, the first thing you need to understand that a view in Domino is nothing more than a design note. Therefore, it can be easily manipulated via LotusScript. Its Selection Formula and column design can be modified as long as the user has the necessary access rights. Others in the blogsphere have talked about the possibilities of changing Selection Formulas via LotusScript or making wholesale changes to the views using DXL. For my purposes, DXL seemed to be overkill, but it might make sense for a more elaborate view or if you want to move views from one database to another without modification.

One of the best things about doing work with views is that it is incredibly easy to test without impacting a production application. The design of the view can be changed by using a private view and modifying it as often as necessary prior to writing any LS code. Once you have the design you want, create a hidden shared view that will be used for all users. In fact, the view doesn't have to be any more than one based on the Blank template since your agent will be modifying it. While it would be really nice to use a private view for each person, the GetView method of the NotesDatabase class cannot retrieve private views from databases on the server.

So now that we have our view that is going to be manipulated by our agent, we need to make sure that the users will have he necessary rights to get this to work. Every user that will be running your code will need to be an Editor and have the Create shared folders/views privilege selected. This is by far the most glaring weakness to this solution. You might think about using a centralized administration ID to sign and execute the agent, but that would by pass any of the document security you have implemented using Readers Names fields. While giving users this privilege might make your design a little less controlled, the audience that would most likely be running this code is usually sufficiently high enough in the food chain that they really have no clue about creating views on their own. If your application doesn't limit reader access to document, a centralized ID is definitely the way to go.

Once the view and ACL are set, it's finally time to create the form and agent. The form can be anything of your choosing and the only things slightly interesting about it are the AJAX call and the empty DIV to hold the results of the call. Below is an example of the JavaScript function I used in one of my forms:

function showReport() {
  var f = document.forms[0];
  var obj = document.getElementById("report");
  var obj2 = document.getElementById("reportButton");
  var sel = f.selection.options[f.selection.selectedIndex].value;
  var loc = getRadioButtonValue(f.location);
  var dr = f.daterange.options[f.daterange.selectedIndex].value;
  obj2.style.display = 'none';
  obj.style.display = '';
  obj.innerHTML = "Getting report from databases...."
  
  xmlhttp.open("GET", "/" + f.dbname.value + "/InstallReport?OpenAgent&sel=" + sel + "&loc=" + loc + "&dr=" + dr);
  xmlhttp.onreadystatechange = function() {
    if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
      obj.innerHTML = xmlhttp.responseText;
      obj2.style.display = '';
    }
  }
  xmlhttp.send(null);
}

I do a couple of things in this function to indicate to the end user that the call is actually working. First, I hide the button that the user pushed to run the function until the response is returned so that the users don't get click happy. I also set a status message in the report DIV so the end users can know that something is happening. Once the response is received, the response is displayed in the report DIV and the button is displayed. The xmlhttp call passes 3 values from the HTML form to the agent to determine what information to return by. As with most Notes applications, the agent is where all the cool stuff happens. Unfortunately, except for the framework of the response, it may have to be recreated for each report or the values pass will have to contain enough information to be able to generate the view. Don't forget to do a Print "CacheControl: NoCache" as your second print statement so the results aren't cached.

Basically, the agent uses the values passed to it to create a viable selection formula, gets a handle to the view we created earlier, sets the .SelectionFormula property, refreshes the view index, and then walks through the view to get the data to return. Depending on the size of the database that your are modifying the view in, the view refresh is the one thing that's going to take a while, but the database I am reporting on now is 1.7 GB and the response from the agent takes less than 30 seconds the first time it's run, less on subsequent runs. This database is populated with data from an Oracle database hourly, so there is significant changes to the documents on a regular basis.

This is how I am currently returning data from multiple back-end Notes databases without a single refresh of screen. But in thinking about things over the last day or so, there is no reason that this functionality can't be expanded. Instead of just setting the selection formula, I am working on code to add and remove columns for sorting purposes. Below is my first crack at such a function that I have added to my ASND Export Utility:

Function GenerateView() As Integer
	Dim column As NotesViewColumn
	Dim x As Integer
	
	Set reportview = reportdb.CreateView("ASNDExport",reportselect,Nothing,True)
	reportview.AutoUpdate = False
	
	For x = (reportview.ColumnCount - 1) To 1 Step -1
		Call reportview.RemoveColumn(x)
	Next
	
	If paramdoc.first_sort(0) <> "" Then
		Set column = reportview.Columns(0)
		column.Formula = Lcase(Strtoken(paramdoc.reportsetup(paramdoc.first_sort(0)),"~|~",2,5))
		If paramdoc.first_sort_order(0) = "1" Then column.IsSorted = True Else column.IsSortDescending = True	
	End If
	
	If paramdoc.second_sort(0) <> "" Then
		Set column = reportview.CreateColumn()
		column.Formula = Lcase(Strtoken(paramdoc.reportsetup(paramdoc.second_sort(0)),"~|~",2,5))
		If paramdoc.second_sort_order(0) = "1" Then column.IsSorted = True Else column.IsSortDescending = True	
	End If
	
	If paramdoc.third_sort(0) <> "" Then
		Set column = reportview.CreateColumn()
		column.Formula = Lcase(Strtoken(paramdoc.reportsetup(paramdoc.third_sort(0)),"~|~",2,5))
		If paramdoc.third_sort_order(0) = "1" Then column.IsSorted = True Else column.IsSortDescending = True	
	End If
	
	Call reportview.Refresh()
	GenerateView = reportview.EntryCount
End Function

The Export Setup form currently allows you to set a primary, secondary, and tertiary sorting column, but there is no reason not to allow the export to be sorted on as many columns as there are in the export. I have not finished testing this code, so users beware. Once it is fully tested, I will update the project on OpenNTF. This new code will allow me to present sorted reports that don't rely on Excel to do the sorting, so they will be available to the text, XML, and web report exports. I might have to put the modification of the view into a separate agent that is called by the export code.