Reporting from the systems we use, the LIMS, LIS, ELN and such, is challenging. Getting data onto a report and in a format that meets the users needs or for regulatory purposes, is not trivial. Today, I will give you three high-level tips on how to get that data out and in a usable format. A lot of your reporting success or failure will depends on the tools you are using, too, of course.
Tip # 1: Temporary Data
Find a way to dump just the data you want into a table. That table will be built with a variety of text, numeric, and other field types, so that you can dump a variety of data into it. If you don’t have enough of some field types for a new report or data need, just add to it. The fields are generic.
So, for example, suppose I want to build a report with the sample text ID, formatted result entry, and the result status. That is actually just three text fields that could be dumped into a generic reporting table with at least three text fields in it (called something exciting, such as, TEXT_FIELD_1, TEXT_FIELD_2, TEXT_FIELD_3). Of course, it’s useful to have other information in there, as well. Sometimes, we will have one of these temporary tables around for every complex report or data need so that they don’t end up with too many fields on them. We usually have a field for the user currently requesting the report with a date/time stamp, so that if multiple people or the same person multiply request reports, that we can get the right data back.
And, of course, deleting records each time is critical so that the table doesn’t grow. After all, it’s temporary. You don’t need to keep it around.
I ran into a regulated laboratory with complex reporting needs who was struggling to meet all their complex report demands, as their queries were getting crazy-complicated. I suggested this to them and they almost fainted before insisting they were regulated and they couldn’t do this. I never did convince them that this was not an uncommon option. In addition, many regulated laboratories do do this.
Tip # 2: Pivot Tables
Sometimes, we struggle with the reporting tool in order to get the data in a format that will trot horizontally across the screen. As much as you might struggle with it, a pivot table might actually be the easier choice over the reporting tool. There are tons of great examples of how to pivot your data, whether for SQL Server data or Oracle data, and here is my own general explanation of how it works: You pick a field to pivot on, a field that has some kind of definite value (such as a result number) and then the other values can be pivoted to show horizontally (such as the actual results). I don’t know if that helps but that’s my best attempt at explaining it in my own words.
Tip # 3: Just Write a Program
Just write a program to gather and format the data to be used by the report or even to write the report, if you don’t need fancy formatting.
Right about now, someone who just read that is shrieking “But you then have to validate that program!” Yes, you do. And you have to validate all those complex queries you could do in its place, too, so it’s about the same, either way.
A good programmer with good tools can knock-out a program to format data much more easily than you can do with all these drag-and-drop tools. Those are fine for simpler reports but the complex ones are sometimes just easier to program.
But I would write the program from within your system, whenever possible. For example, write the LabWare LIMS program with LIMS Basic and you can more easily enforce all the security rules.
Finally
There are more and more tools out there to extract your data and change its format so it can be used in reports, statistical packages, and other places its needed. Some are actually pretty useful and do a great job for their own purpose. But there is usually some major drawback to any method you select.
Gloria Metrick
GeoMetrick Enterprises
http://www.GeoMetrick.com/
Another option is a stored procedure that returns a record set. This can combine temp tables (created on the fly – not ahead of time) and programming. It can be used as source for most report packages.
I totally agree with these two paragraphs:
“Right about now, someone who just read that is shrieking “But you then have to validate that program!” Yes, you do. And you have to validate all those complex queries you could do in its place, too, so it’s about the same, either way.
A good programmer with good tools can knock-out a program to format data much more easily than you can do with all these drag-and-drop tools. Those are fine for simpler reports but the complex ones are sometimes just easier to program.”
Many folks are so scared of development that in many cases it goes beyond anything reasonable. You have to look at things from the whole point of view, not just a self induced bias.
John, you put the issue about fear together in such a clear manner that I hope those who live off fear will recognize themselves in it. System Administration shouldn’t be done with fear but with purpose and with procedures.
Marc, the other thing about that suggestion is that the other thing you can do with that is improve performance. So, where views might get pretty slow, if you create a stored procedure that builds a table, whether it’s a temp table or a permanent one, you can have just the right amount of data in it and build onto it at some intervals. Then, you can index it and do other performance-related things to it. Thank you for including this.