Mega Report 01

From PikaDocs

MegaReports: Finding needles in the haystack

MegaReport is the true workhorse of the PIKA CMS system. Using MegaReport you can retrieve a massive amount of information from the data your colleagues have collected. MegaReport truly opens the PIKA universe in terms of database access, just watch out for the data quicksand!

First, a confession: I admit that I felt incredibly empowered the first time I opened up the MegaReport and discovered that it allowed me to gather all sorts of information in one go. So, in a subliminal compulsion to wear out my mouse, I clicked EVERY button and selected as many delimiters as possible… Imagine my surprise, when I found the PIKA Penguin sorting furiously under a particularly ugly looking SQL statement, something like:

SELECT cases.annual1, cases.income_type3, SUM(cases.user_id) as Sum, 
cases.case_id AS case_id_deleteme 
FROM cases LEFT JOIN contacts ON cases.client_id = contacts.contact_id 
WHERE 1 
AND (cases.user_id IN (48,1024,16,42,1010) 
OR cases.cocounsel1 IN (48,1024,16,42,1010) 
OR cases.cocounsel2 IN (48,1024,16,42,1010)) 
ORDER BY cases.case_id, cases.cocounsel1 
GROUP BY cases.annual1, cases.income_type3 
LIMIT 1000 [nativecode=1064 ** You have an error in your SQL syntax near 
'GROUP BY cases.annual1, cases.income_type3 LIMIT 1000' at line 2] 

While the software tester in me felt a slight twinge of satisfaction, I quickly realized that it was a NAPE situation and felt betrayed by my own data greed. Humbled (and realizing I wasn’t been paid to break software anymore), I scaled back the scope of my exploration to one more pleasing to the MySQL database engine deity.

The report ended up looking something like this:

Does this look familiar? Well, this is only about HALF of it!

As you might have guessed, while PIKA handled the massive request graciously, the report generated could not be printed on one sheet of paper. Since custom 8.5” x 60” paper is probably not in your budget, this article is the first part of a series discussing how you might approach MegaReport and other PIKA reports to get the best results.

I thought it might be helpful to remind you of the difference between “data” and “information.” Not quite as poorly defined as the distinction between facts and evidence, there is, nonetheless, a useful concept to remember here: data is not information. “Data” is value-neutral, even if the input process presupposes some selectivity. Creation of “information” is the purpose-driven combination of data.

Part I. How to plan, plan, and then plan some more in less than 5 minutes

Data retrieval planning will help ensure that you’re getting the right data to adequately convey your purpose. For data to become information, you have to make value judgments and decisions about the purpose of the information you seek to create. The most obvious types of information PIKA helps you create are client referral packages, funding reports and attorney timesheets. Each one of these information types are created by judgments about what is relevant. In other words, always ask “does the data I want support the information I need to convey?” If ‘yes’, click away. If ‘no’, resistance is not futile!

My first suggestion is that you begin a MegaReport session using a text editor (or pencil and paper). Write down what you need and why you need it. That way, when you open up the MegaReport window, you can easily resist the gotta-click-it impulse and stick with getting the information you really need. Then, based on that purpose statement you can start generating sample reports.

Another approach is to use the MegaReport Worksheet, which is a hard copy representation of the MegaReport interface (log into: www.pikasoftware.net/files/ and browse to MR_WS1.pdf or MR_WS1.doc). As you can see, there are 142 cases fields, 45 client fields and 23 activity fields to display in the base installation of PIKA 3.03.

You’re going to want to narrow your search. Trust that a few minutes of planning before you try to generate a report (especially if you have a slow internet connection), will pay off in terms of a cogent report, a well-documented paper trail and the freedom to delegate while you focus on more exciting aspects of your practice.

Finally, elect to show the SQL code. It’ll take a few seconds to copy and paste it into the Worksheet, so that the next time you run the query, you can make sure you get the predicted results. If you get an error, you will have a trail that Aaron, Dave or I can follow. You can also modify the query for use in Access or another SQL format. Your techie might actually prefer SQL to English!

The next article in this series will show you how to optimize a query to focus on your goal. If you have a favorite (or ugly) report to share, feel free to send it to regina AT pikasoftware DOT net !