Creating custom reports – an introduction Monitor Master Version 1.0
Copyright © Argo Interactive Ltd. 2003. All rights reserved. t: +44 (0) 1252 705705 f: +44 (0) 1252 705706 www.argogroup.com
INTRODUCTION While collecting data, Monitor Master stores results from tests within a SQL database. User-selectable reports are generated from this data using a combination of XML, XSL and SQL. Although Monitor Master’s reports deliver a wide range of data, it cannot cover everything – this is where custom reporting comes into play. In order to create custom reports, knowledge of SQL (especially the MySQL variant), XML and XSL is essential – and assumed. Knowledge of the Monitor Master SQL database schema is also required.
BASIC DECONSTRUCTION OF AN EXISTING REPORT
When generating the list of reports available to a user, Monitor Master scans through a directory located within the current configuration – for an English default installation, it is C:\Program Files\Argogroup\UbiquinoX Monitor Master\config\cfg\reports\testrun. In 5.3, the following folders are available: -
Compliance errors and broken links
-
Content listings
-
Device errors and usability
-
Performance graphs
-
QXP
-
Reference
-
Site maps
-
User events
Inside of each of these there can be sub-folders containing reports relevant to its parent, and inside these can be more sub-folders and so forth. Each report requires two files – main.xsl and queries.cfg, and Monitor Master looks at both of these to determine if the report is valid or not. We will look at the Performance graphs\Delta timings report as an example. Inside this folder are four files – main.xsl, queries.cfg, constants.xsl and templates.xsl. Although Monitor Master only requires the first two, you will find that quite a few other reports have the other two – this is for convenience.
QUERIES.CFG
The queries.cfg file is an XML file with embedded SQL queries. Its purpose is to query the SQL database, with the eventual output being XML. It is written in XML.
MAIN.XSL
main.xsl is used to convert the XML produced from the queries.cfg file into HTML. At Argogroup, all of our reports are XHTML compliant, so the XSL produces well-formed XHTML. If the report has templates.xsl, then main.xsl is quite short as it is the templates.xsl file that does all the main conversion work.
CONSTANTS.XSL
This file is included by main.xsl, and is used to set up constants, such as field numbers for the XML data, which speeds up the processing of the XSL.
TEMPLATES.XSL
This file is also included by main.xsl, and normally performs the main operations required to convert the XML into XHTML.
Copyright © Argo Interactive Ltd.
Page 2 of 17
March 2003
CREATING A SIMPLE REPORT The first thing to do is to create the folder path for the report we will be generating. Inside the testruns folder, create a folder called ‘Test reports’, and then another inside that called ‘Create test runs’. Into that folder, create a (blank) file called “main.xsl” (we will be filling that in later), and a file called “queries.cfg”. This file should look like: testrun.SequenceNo SELECT COUNT(testrun.SequenceNo) FROM testrun This is probably the simplest query you can make – what it will do is tell you how many test runs you have selected in the GUI. However, we’ll describe how it does that later. Even without a valid main.xsl, this report can still be used. Simply start up the Monitor Master GUI, select a number of test runs, right-click on one of the selected ones and “Save report as”, using our new report. Change the type to XML (this means that the resultant XML is saved rather than the XHTML – avoiding the need for a valid main.xsl). If you get an error while trying to save the report, you only need to save the new queries.cfg file. However, if there is no error and you want to change the queries.cfg file, then press F5 in the GUI. The resultant XML file should look like: 28 However, for clarity, here is the ‘pretty printed’ version:
Copyright © Argo Interactive Ltd.
Page 3 of 17
March 2003
28
HOW DID IT DO THAT? The queries.cfg file is used to generate SQL queries, whose data is used to fill in the XML file. This file is split into sections: Query 1 Query 2 Query 3 Each of the queries will produce a corresponding section in the XML: Column names for query 1 Results for query 1 Column names for query 2 Results for query 2 Column names for query 3 Results for query 3 In our example, we only had one query, and this was: testrun.SequenceNo SELECT COUNT(testrun.SequenceNo)
Copyright © Argo Interactive Ltd.
Page 4 of 17
March 2003
FROM testrun So, what does this mean? The first part of the XML (dbreporttoxml:keys) tells Monitor Master what basic search criteria to use. For tests that involve reporting on selected test runs, only one key is defined (in dbreporttoxml:key), namely testrun.SequenceNo. This is the SQL table and column name to key off. The second part (dbreporttoxml:select) describes what we are going to do – SELECT from the database. This can be extended with other types of SELECT queries, such as SELECT DISTINCT. The third part (dbreporttoxml:fields) provides a list of fields we wish to select from the database. This can be simply a column name, or a more complex SQL query. In our example, we are only looking at one field – COUNT(testrun.SequenceNo), which only returns one row. If we wanted to return more fields, then we would put more dbreporttoxml:field elements into the document. Note that the ‘alias’ attribute sets the name in the results document, as described later. The next part (dbreporttoxml:from) tells Monitor Master what table(s) to read the data from. The dbreporttoxml:extra-joins element is used when joins are required to read data from multiple tables – for example, you can use an inner, left or outer join. dreporttoxml:extra-wheres allows more search criteria to be applied to data – for example, you may only want to fetch test runs of a particular name. We will build up our example later on with extra where clauses. The last element (dbreporttoxml:order-bys) tells the database to return the information in a particular sort order. When Monitor Master parses the queries, it constructs SQL statements from the XML. In our example, the SQL statement is: SELECT COUNT(testrun.SequenceNo) FROM testrun WHERE (***); *** is a special field that Monitor Master fills in based on the keys. For the key in our example (testrun.SequenceNo), this becomes something like: testrun.SequenceNo = 17 AND testrun.SequenceNo = 23 if you had test run 17 and test run 23 selected in the GUI. Going back to our results, we had: 28 The element is taken from the name of our query (rs meaning ‘result set’).
Copyright © Argo Interactive Ltd.
Page 5 of 17
March 2003
The element describes the “heading” of the results, and it gives each column name (or “field” name) within an element retrieved from the database – in our case, we only selected one column, and it was given the name “Counter”. This name was taken from the “alias” attribute for the field we requested. If we did not have the alias attribute present, then the name would simply be the column query. The “id” attribute is a unique ID giving the position of the column in the result table, and the “type” attribute is the type of the result being fetched – even though we are fetching a number, we will still see a string. There can be several elements, each giving us a row from the database. Each element within an element corresponds to a column, as described in the element.
CREATING THE XHTML
Now we have our query working fine, we can use the results to generate XHTML. The easiest way to do this white testing your XSL is to save the XML from the query, and add the XSL processing instruction to read “main.xsl” at the start of it: 28 Before we can use it, we need to create a simple main.xsl: Loading the XML with the XSL processing instruction into Internet Explorer will simply produce an HTML file containing the number of tests selected:
That’s it – we’ve generated a very simple custom report. You can use it within Monitor Master – simply select a number of tests, right click on one of them, “View Report...” > “Test reports” > “Count test runs”. You may need to press F5 to refresh the menu if your report is not present.
MAKING IT PRETTY
Monitor Master uses a set of common XSL libraries to produce a standard reporting set, and these are included by main.xsl in all of Monitor Master’s standard reports.
Copyright © Argo Interactive Ltd.
Page 6 of 17
March 2003
These common XSL libraries are located in the folder above “testruns”, in a folder called “_Common”. In 5.3, these are: -
content.xsl is used to display the content in the content detail window, and should not be included.
-
foldups.xsl is used to produce the folding sections.
-
script.xsl contains JavaScript for various functions.
-
simulator.xsl is used to produce a small simulator display of the content (normally WML or SMS).
-
simulator-null.xsl has the same API as simulator.xsl, except it produces no content.
-
stylesheet.xsl contains the main CSS stylesheet.
-
util.xsl has utilities for splitting lines, and criticality functions.
-
xml.xsl converts XML into displayable XHTML.
-
xml-null.xsl is similar to simulator-null.xsl, where it can be used in place of xml.xsl, but produces no content.
We can use some of these in making the count test runs look like a proper Monitor Master report:
Test runs selected:
Copyright © Argo Interactive Ltd.
Page 7 of 17
March 2003
UbiquinoX Monitor Master Report Now, that’s quite a mouthful! However, it is fairly easy. There are two templates in this transformation – the first deals with the generation of rows of data (or in our case, one row of data), and the second creates all the HTML around that data. Note that the first is often put into a ‘templates.xsl’ file. If we use this report now, it should look something like:
Copyright © Argo Interactive Ltd.
Page 8 of 17
March 2003
Note that if you haven’t changed the number of selected items, then Monitor Master will use the previous data (it caches the last few requests for speed). You can click on the foldups to open/close sections – exactly in the same way that Monitor Master works (which is not surprising considering it is using the same code!)
A MORE COMPLEX REPORT
The next report we’ll generate is an extension to this one, giving us a list of all the test names selected, and how many of each of them selected. This report will be “Test run name count”. The first thing to do here is to work out our SQL query. For this, we need to fetch data from two tables – test runs and tests. The query we need is: SELECT COUNT(testrun.testid), test.name FROM testrun, test WHERE test.id=testrun.testid GROUP BY testid; From the mysql command line, we get: +-----------------------+---------------------+ | COUNT(testrun.testid) | name | +-----------------------+---------------------+ | 4 | Environment test | | 2 | MMS test (empower) | | 3 | MMS Sender OTA | | 1 | SMS Clear (COM1) | | 1 | MP4 | | 1 | ASF | | 1 | ASX 2 | | 2 | Short message 8888 | | 13 | SMS receive (COM11) | +-----------------------+---------------------+ This shows us that we have 4 “Environment test” tests, 2 “MMS test (empower)” tests and so on. Note that this query does not show those that are selected; Monitor Master will insert this part of the query. In 5.3, Monitor Master does not have a “dbreporttoxml:group-by” element, so we need to fake one using the “dbreporttoxml:extra-wheres” element. The queries.cfg file should look like: testrun.SequenceNo SELECT COUNT(testrun.testid) test.name FROM testrun, test AND (test.id = testrun.testid) GROUP BY testid This will produce an XML document such as: 4Environment test 2MMS test (empower) 3MMS Sender OTA 1SMS Clear (COM1) 1MP4 1ASF 1ASX 2 2Short message 8888 13SMS receive (COM11) The XSL used to generate the report is very similar to the first example, but has some slight alterations for the new data:
Test run name Number of test runs selected UbiquinoX Monitor Master Report
Copyright © Argo Interactive Ltd.
Page 11 of 17
March 2003
This produces a report such as:
CONVENIENCE XSL As indicated earlier in the document, the main.xsl includes two convenience XSL templates – constants.xsl and templates.xsl.
CONSTANTS.XSL
The constants.xsl file sets up constants that can be used by the templates – for example, the position of the column names in the results set. In our previous examples, we have used hard-coded ‘magic numbers’ for each of the field numbers (namely ‘1’ for the number of test runs encountered, and ‘2’ for the test run names).
Copyright © Argo Interactive Ltd.
Page 12 of 17
March 2003
A constants.xsl file could be generated: With the current queries.cfg, this will set “$TestRun.Name” to 2, and “$TestRun.Count” to 1. Why would we want to do this? For three reasons: 1) It allows us to change the number, and order of the results coming back from the SQL query without having to change the XSL files 2) We do not need to remember magic numbers; we can use names instead. 3) These indexes are searched once, which speeds up the XSL (as much as 120 times faster!)
TEMPLATES.XSL
The templates.xsl file normally converts each query into a set of results displayed in a section on the report. For example, you could have the same results being returned with different sort orders. The actual work would be performed in templates.xsl, which is called in main.xsl. In our example, the XSL would be:
Test run name
Copyright © Argo Interactive Ltd.
Page 13 of 17
March 2003
Number of test runs selected
MAIN.XSL The main.xsl will need to be changed to use these:
UbiquinoX Monitor Master Report
Copyright © Argo Interactive Ltd.
Page 14 of 17
March 2003
EXTENDING TEMPLATES Take the following example:
Copyright © Argo Interactive Ltd.
Page 15 of 17
March 2003
Here, we have the same information published, firstly sorted by name, and then sorted by count. There are two ways we can do this: 1) Creating two queries, one with a SORT BY test.name, the other with SORT BY COUNT(testrun.testid) 2) Using one template, and passing parameters to say how we’re sorting it. Although option 1 is better (since databases are designed to sort, whereas XSL’s sort isn’t as optimised), we will discuss option 2, because the techniques can be applied elsewhere. The first thing to do is to modify main.xsl. The centre part of the code is modified to look like:
This uses the “ShowResults” template twice, the first time passing “by name”, “$TestRun.Name” and “text” in as parameters, the second time passing “by count”, “$TestRun.Count” and “number”. The first parameter defines what text to put in brackets after the title, the second parameter is which field to sort by, whilst the third defines what sort order to use. Next, templates.xsl is changed to allow sorting: Test runs selected () Test run name
Copyright © Argo Interactive Ltd.
Page 16 of 17
March 2003
Number of test runs selected There, that’s it.
TAKING IT FURTHER Please feel free to look at the provided Monitor Master reports to see how we’ve written them for inspiration and ideas – there are some pretty complex reports available to you, and you should find something useful there.
FOR FURTHER INFORMATION
For further information on Monitor Master or the Argogroup technologies and how Argogroup can help your business - please contact the Sales Department on +44 (0) 1252 705705, or email
[email protected]
Copyright © Argo Interactive Ltd.
Page 17 of 17
March 2003
