Long post to provide details, sorry.
I’m looking for an efficient (performant) way to generate a report which will be required monthly (mostly). It’s rather simple in terms of content, but once the requirement was modified my approach is not good. I’m guessing I’m missing an obvious approach to do this, so ask for the community to suggest a better way.
The report summarizes client transaction line items (simply a count of line items), by client name. This is very easy to do with a simple summary field using Count and sorting on client name. A Sub Summary when Sorted part, followed by a Trailing Grand Summary is used.
The tables for this element are essentially: Clients, Submissions, SubLineItems
A Find is performed to set the month (or entire year), and omit a few transaction types; then sort on Clients::clientName.
Output looks like this (omitting some additional fields):
ABC, Inc _______137
DEF & Company _12
GHI Co., Ltd. ____81
That was all grand, but then it was requested to break out the number of transactions of “Type B” (subset of all line items) in separate columns. The summary as first designed showing all items, but now it would/should look like this:
ABC, Inc ..............20 -- 117 -- 137
DEF & Company .12 ---- 0 ---- 12
GHI Co., Ltd. .......70 ---- 11 --- 81
Totals ................102 --- 128 -- 230
***Sorry, "columns" lost positioning upon posting. Should've inserted as a table. :-/
First column has the B type jobs which is determinable by a boolean true check box (=1).
Second column has the “normal” jobs which do not have the checkbox populated (isEmpty / 0 / false, typically empty).
The third column has the total jobs (all non-cancelled line items) submitted by each client for the month.
Some line items are cancelled after submission, so are removed for the count. “Cancelled” is a boolean checkbox (plus other indicators).
Execute SQL can extract the data, but as these are all unstored, even defining the fields in the table is problematic, let alone actually using the calculations on a layout, plus the summary process. Obviously. The eSQL approach could be tolerated if simply checking for a single client via script, but this needs to include all clients active during the given month.
Current table in question (Sub_Line_Items) only has several thousand records (fewer than 10,000), but will expand greatly. Active current client list only several hundred (fewer than 1,000), but growing rapidly.
I’m hoping you brilliant folks here will point me to what I’m missing. My brain is mush this week (been crunching a mapping task) and I’m not visualizing how I might do this with TOs and relationships, etc. Please show me the light!