How to Clean up an Unwieldy Report (Carefully!)
While the ideal first step in writing a good custom report is writing down your plan, mapping out your queries and variables, and design, we all know the specifics in a large report can change while it is being built, and plans do not always pan out the way we intend. As a result, reports can turn into a tangled web of ideas, some that worked, and some abandoned. This kind of "progress" can lead to a slow clunky report that is possibly even returning unreliable data.
If you have a custom report that seems like it could use some cleaning up, here are some things to look for: Identical queries. Maybe you have a query called Clients and another called Stayers [not that this has ever happened to me!?!]. You compare the filters and the result objects and there are very few differences. Consider getting rid of one of them. This is a huge change, so tread carefully. Be sure to go to the report itself and be sure all references to the query you are getting rid of can point successfully to the query you are keeping, save a new version of your report in case this breaks everything, then remove the unneeded query. This is a great way to simplify your report and speed it up. Of course, the best way would have been to not create redundant queries in the first place, but hey we can't all be perfect.
Result objects that are never used in the actual report. Perhaps when you were designing the query, you felt like you should throw in a bunch of result objects just in case you needed them. Now you're done with the report, and it turns out three or four of the result objects you threw in were not needed. Take. Them. Out. This alone will shave a lot of time off the run time. Be positive, though, that they are not being used in any variables!
Variables that are not referred to anywhere. Experimentation is good, but when the failed experiment is over, sometimes it seems smart to leave the more experimental variables around in case you need them later. When you are to the clean-up phase of your report, make the move to get rid of these. Do this with great caution. It is best to write down all your variable names, then go through each and every column and cell if applicable and mark all the variables in use, then see what you're left with. Check each variable for references to other variables so you don't miss any. Be sure to save a new version of your report before deleting in case this breaks everything.
Look over the names of your variables. Do they make sense? If someone else had to take over maintaining this report, would they know what is what? Are you consistent in your naming convention? It can be helpful to have a consistent way of naming your variables. I will speak no more on this because I do not have a consistent way of naming my variables!! It is something I am working on.
Write a thorough Report Manual. Doing this forces you to examine each and every tab, column, and calculation so that you can explain it clearly for users (or future report developers). Simply going through these steps will bring out questions that are best answered prior to the release of the report. It helps with simplifying the report where possible, because the act of explaining the logic for everyday end users forces you to think in terms of simplifying where possible.
Hopefully your report writing process is so smooth this post will be totally irrelevant to you! If not, please be very careful in your efforts to clear the cobwebs while leaving the important things in place. Use versioning, and keep it simple!