iPACS Reporting: Projects table exported as Excel
The iPACS reporting engine is a powerful tool to access iPACS’ object relational database (where for instance projects, patients, studies, datapoints, study planner events, etc… are stored) as well as the file-based WebDisk and export the data in for instance PDF, Excel Spreadsheets, CSV, plain text, XML, etc… In this example I would like to show a small example on how to export the projects table in form of a native Excel spreadsheet, including links and formats.
Creating a new reporting template
To create a new reporting template, select the Report entry from the WebDisk menu and fill out the New Template form on the right hand side, selecting ‘Excel Spreadsheet (XLS)‘.
This will create a new *.tt file you and sends you to the editor (see image at top, click for full size). You can open the file at any later time with a right click on it and selecting ‘Edit Template’, either from the Report page or directly from the WebDisk. The code should look similar to this here.
[%- MODE=xls %]
[%-
USE date;
XLS.newWorksheet('Projects');
XLS.add('Projects Example');
XLS.end(); # end column
XLS.add('Author', 'Christian Lackas');
XLS.add( date.format( date.now(), '%Y-%m-%d') );
XLS.end();
XLS.newWorksheet('Another sheet');
XLS.add('More content here...');
XLS.print();
-%]
If you click on Process a spreadsheet will be produced for you and you can download an XLS file and open it in Excel, LibreOffice or OpenOffice:
The source code
The reporting template is written in a framework called Template Toolkit, which is easy to learn and uses [% statement %] to mark its statements. The first line tells iPACS that we would like to work in XLS mode:
[%- MODE=xls %]
other options would be pdf, csv, txt, xml, etc… In xls mode we have an XLS object at our disposal that allows us to easily add data to columns and rows, e.g.
XLS.newWorksheet('Projects');
creates a new worksheet for us labeled ‘Projects‘. To keep things simple the XLS module now only creates this worksheet for you, however, also switches to this area, so all subsequent commands will be executed on this worksheet, until we create another one. To add data to the selected worksheet we can use XLS.add(…). This is a row based operation, and add() accepts any number of parameters, which are filled into the respective columns, alternatively you can use multiple add() calls, so this is the same:
XLS.add('Col1', 'Col2', 'Col3');
# same as
XLS.add('Col1');
XLS.add('Col2', 'Col3');
To complete a row call XLS.end(), and then you start at the left-most column column of the next row again.
At the very end, we have to output the entire document with
XLS.print();
after which you should close the template code with %].
Accessing the iPACS database
The iPACS database can be accessed via various methods, in this case we are using the ipacs object and request the current user, since we would like to print out all projects (plus some of their stats) that the user has read access to. In a first example just get the number of projects on your iPACS with ipacs.user.projects(‘r’).count:
[%- MODE=xls %]
[%-
XLS.newWorksheet('Projects');
XLS.add( ipacs.user.projects('r').count );
XLS.print();
-%]
In a next step we are looping through all the projects and dumping some of their information into the spreadsheet, line by line:
[%- MODE=xls %]
[%-
XLS.newWorksheet('Projects');
# Add headers
XLS.add( 'ID', 'Path', 'Name', 'Comment' ); XLS.end();
FOR prj IN ipacs.user.projects('r').all;
XLS.add( prj.id, prj.projectpath, prj.projectname);
XLS.add( prj.comment );
XLS.end();
END;
XLS.print();
-%]
First we add the column headers in line 5, then comes the [% FOR prj IN projects %] construct, which is terminated with an END. Everthing within this block is executed for each project found and the variable prj becomes the respective project object in each iteration. Don’t forget to close rows with XLS.end(), otherwise your data will be continued in the same row.
The XLS module of the reporting template does not only allow you to write columns, but also add formatting. For instance, you will have noticed that the column widths are not optimal and parts of the path and name are hidden. This can be adjusted using the XLS.set_column( sta, end, width ) function. If you add these lines before the [% FOR %] loop:
XLS.set_column(0,0, 10); # column A
XLS.set_column(1,2, 30); # column B, C
XLS.set_column(3,3, 40); # column D
you will get this output with optimized column widths:
However, the formatting options don’t stop here, you can add more, e.g. make the headers in bold blue and increased size, and show the ids in gray, the comments in italics:
[%- MODE=xls %]
[%-
XLS.newWorksheet('Projects');
# Add headers
XLS.set_column(0,0, 10); # column A
XLS.set_column(1,2, 30); # column B, C
XLS.set_column(3,3, 40); # column D
fmt_header = XLS.add_format( bold => 1, color => 'blue', size => 14 );
fmt_id = XLS.add_format( color => 'gray' );
fmt_comment = XLS.add_format( italic => 1 );
XLS.add( {format => fmt_header}, 'ID', 'Path', 'Name', 'Comment' ); XLS.end();
FOR prj IN ipacs.user.projects('r').all;
XLS.add( { format => fmt_id }, prj.id );
XLS.add( prj.projectpath, prj.projectname);
XLS.add( { format => fmt_comment }, prj.comment );
XLS.end();
END;
XLS.print();
-%]
At first we create formats and add them to the worksheet with XLS.add_format(), and then use them for the respective cells in the XLS.add( { format => fmt }, …) call.
In a final step, we should add another column before comments that contains the number of patients that are stored in a project and mark the names of empty and non-empty columns with formats:
[%- MODE=xls %]
[%-
XLS.newWorksheet('Projects');
# Add headers
XLS.set_column(0,0, 10); # column A
XLS.set_column(1,2, 30); # column B, C
XLS.set_column(3,3, 15); # column D
XLS.set_column(4,4, 30); # column E
fmt_header = XLS.add_format( bold => 1, color => 'blue', size => 14 );
fmt_id = XLS.add_format( color => 'gray' );
fmt_comment = XLS.add_format( italic => 1 );
fmt_no_patients = XLS.add_format( color => 'red', font_strikeout => 1);
fmt_has_patients = XLS.add_format( color => 'green', underline => 1);
XLS.add( {format => fmt_header}, 'ID', 'Path', 'Name', 'Patients', 'Comment' ); XLS.end();
FOR prj IN ipacs.user.projects('r').all;
XLS.add( { format => fmt_id }, prj.id );
patient_count = prj.patients_rs.count();
IF patient_count;
XLS.add( { format => fmt_has_patients }, prj.projectpath, prj.projectname);
ELSE;
XLS.add( { format => fmt_no_patients }, prj.projectpath, prj.projectname);
END;
XLS.add( patient_count );
XLS.add( { format => fmt_comment }, prj.comment );
XLS.end();
END;
XLS.print();
-%]
The new construct IF … ELSE … END allows us to set different formats based on the value of the patient_count variable, which is filled by requesting the patient resultset (that is the collection of all patients in this project) from the prj object and again calling the count method. And here is a preview of what the final output could look like:
The reporting module of course allows many more things, e.g. access to all meta-data of all images stored in the database, all datapoints (such as quantification results) as well as access to the WebDisk.
Please let me know should you have any questions or comments.