I am a patent attorney. I work for a corporation. Executives don't care to read my patent applications, but they do want to know how many are filed and for which business units. They want this information quarterly. My current practice involves manually scripting a series of searches, one for each type of filing for each business unit. This is not difficult, but it is tedious as I did this serially for each combination of filing type and business unit. The solution uses a global variable that concatenates the required data and then drops it into a text file that is used to create a report in Excel. I can do better. Or at least I would like to.
My options include:
- Create a reporting table wherein each record is a count of the relevant occurrences for the respective business units. Use a script to tally filings each quarter and then create records to keep those tallies around. A dashboard layout(s) can be created to present this data in some useful way. I would need to populate data from past quarters if I wanted to look at data earlier than last quarter.
- Generate the reporting information on the fly whenever someone opens the reporting layout. At present this is easy as the report only looks backwards one quarter. I did this this afternoon and it works. I amended my existing counting script to fill some global fields I created. It looks good, but it is for only one quarter.
- Some sort of self-join might be useful. I imagine that one could create a calculation field that would automagically identify a record in a table as one that should belong to a given category for reporting, i.e. instead of scripting searches one would have a field generate an alphanumeric string that indicates type of filing, business unit, and fiscal quarter. The calculation field might be a bit tricky, but the searching might be a bit simpler.
In any case, I am looking at how to count records with respect to at least three criteria: type, date range, business unit. The counting script I am contemplating would be a nested one wherein there is an iteration for each date range, a sub iteration for each business unit, and a sub-sub-iteration for each type. I am contemplating repeating variables or fields, but those only have a single dimension and I don't think this would work.
So I ask you, how would you count or summarize records so that it could be used in a tabular (actual or pseudo) form or in a chart or as the input for some JS API? How might you store that data (or not) to make this whole thing more robust?