Child pages
  • Writing Reports for the OSP Reports tool
Skip to end of metadata
Go to start of metadata

Writing New Reports

To write a new report you have to have extensive knowledge about the Sakai database and you need to be good at writing SQL queries. While Sakai is a collaborative effort amongst many schools and companies to write software for al of us to use, there isn't a standard way to store data in the database. You'll soon discover that some tools store their data as XML snippets in the database while others store normalized data in separate columns in the database tables. If you want access to that data in your report, you will need to become familiar with the data storage mechanism for that tool and will have to figure out how to write clever SQL statements to get the data out.
The Data Warehouse
To alleviate this "non-standard" data storage problem and to make it easier to write SQL queries, some tools have plugins written to export their data into normalized "data warehouse" tables in the Sakai database. While this is data is not "live" data, it may be easier to write queries against. Many of the OSP tools have corresponding data warehouse tables already.

Report Definition file

When Sakai starts up it reads the report definition beans from reports-definition.xml (webapps/osp-reports-tool/WEB-INF/reports-definition.xml). The reports defined in this file are the only reports that will be available to Sakai users with access to the Reports tool. In order to change the reports, the reports definition file needs to be changed and the Sakai instance needs to be restarted.

The reports definition file is an xml file that contains report definition bean elements. Each report definition bean has the following properties:

  • idString  - a unique string that identifies this report
  • title - the name of the report as it appears to users in the tool interface
  • query - the SQL statement or list of statements that will be executed when the report is run.
    • The SQL statement uses question marks to denote placeholders for user defined parameter values. For example, to create a report that displays site names, the report designer may request that the user be allowed to enter a partial site title that will be used to filter the report results to those site titles that contain that string. The SQL statement for such a report would be as follows:

      select TITLE from SAKAI_SITE where TITLE like ?
      The question mark is a placeholder for the value of the parameter (in this case a string) that will entered by the user.
      If the SQL statement has more than one placeholder for values, the values will be replaced in the order that the report parameters are defined (see below).

    • If multiple statements are defined, the first statement will be executed as the main report. These results will sow up in the main <reportResult> element. Results from subsequent statements will show up in <extraReportResult> elements with an index sttribute. All the statements must have the same query parameters in the same order. This feature can be used to gather domain aggregate data such as count, average, min/max, etc.
  • resultProcessors - There are a few Result Processors and Result Post Processors available to create results
    • org.theospi.portfolio.reports.model.ResultProcessor.defaultArtifactLoader
    • org.theospi.portfolio.reports.model.ResultsPostProcessor.csv - This processor creates a comma separated
      value file as the results.  While not useful for further XSL transformation and display in the browser, this processor is useful for exporting report results out of Sakai so that that can be manipulated by a spreadsheet or some other application.
    • org.theospi.portfolio.reports.model.ResultsPostProcessor.rowcolumn
  • keywords - keywords related to the report
  • description - a text blurb that will be presented to a user of the tool.
  • defaultXsl - a bean that denotes which report view will be the default view.
  • type - a comma separated list of site types that the report will available to (such as 'admin,project,course,portfolioAdmin,portfolio')
    • access to run OSP reports are based on:
      • the types of sites that have access to specific reports as defined by the "type" property
      • the deployment of the tool into a worksite (you can't run reports from a "course" worksite that doesn't have the tool)
      • the permission settings for the report tool in that worksite.
  • reportDefinitionParams - a list of parameters that will be used by the SQL query. 
    • Each parameter has properties as follows:
      • title - The name of the parameter as it will be shown to the user. This could be considered the prompt or label that the user will see on the "Edit Report Parameters" screen.
      • paramName - the name of the parameter as it will appear in the xml results. This will be useful to know if creating a new report view (XSL transformation).
      • description
      • type - The following parameter types are available:
        • int - integer
        • float - floating point
        • string
        • date
      • valueType - The method used to populate this parameter
        • fillin - user is presented with a text input field to enter text
        • set - user is presented with a drop down menu of possible values
        • mutliset - user is presented with a list where they can pick more than one value. The options in the list are hardcoded (not from a SQL query)
        • sql - user is presented with a dropdown menu of possible values derived from a SQL query
        • multisql - user is presented with a list where they can pick more than one value. The options in the list are determined by a query configured in the report definition. need example here
        • static - user is not allowed to enter a value.  The value is fixed by the report definition.
      • value
        • The following "value proxies" are available. If these value proxies are users, the parameter value will be replaced by the  current tool users session information.  This mapping is performed (and could be extended) by the replaceSystemValues method in ReportsManagerImpl.java.
          • {userid}
          • {userdisplayname}
          • {useremail}
          • {userfirstname}
          • {userlastname}
          • {worksiteid}
          • {toolid}
      • xsls - a list of transformations available to the user on the Report Result View screen to view the xml results of the report.
        • Each xsl entry in the list has the following properties
          • xslLink - the relative path to the xsl file in the osp-reports-tool directory
          • isExport - set to true if the report is to be exported
          • title - the name of the Report View that will be presented to the tool user in the dropdown menu of available "Result Views" for this report on the Report Result View screen
          •  

Report Configuration file

The report configuration file is a list of the OSP reports that will be read in during system startup. Each report listed should have a corresponding report definition bean in reports-defintion.xml.

Parameters

  • No labels