Multi-Nested Roll-Up Report Markup Language Proposal
I've done enough "multi-nested" reports with totals and subtotals with both row-wise and column-wise groupings, totals, averages, etc. that it feels like there should be a tool to make building and formatting such reports easier. The banded approach is no longer sufficient.
This is a rough attempt to explore a potential "report markup language" for such reports.
Here is a general example:
<datasource file="myFile.csv" filetype="CSV">
<row field="field_2" span="yes" align="center">
...Field 2 Title
...<row field="field_3">
......Field 3 Title
......<column list="2006,2007,2008" span="yes">
.........<column field="field_4" onNull="N/A">
.........Field 4 Title
.........</column>
.........<sum>Sub-Total</sum>
.........<average divzero="N/A">Avg.</average>
......</column>
...</row>
...<sum color="silver">Total</sum>
</row>
Sorry, I don't yet have a wiki-tized example result. (Dots to prevent
TabMunging.)
Attribute and Tag Descriptions (Not all shown in example)
- append (tag) - A kind of string-equivalent of "sum" where string items are appended to form a kind of list. A space is placed after the delemiter unless "space=no". (This seems like an odd feature, but quite useful.)
- calc (tag) - To provide custom calculations. Can be used in conjunction with "hidden" and "name" to provide complex calculations. Example: <calc leftname="foo" rightname="bar" operator="*"> The order of "calc" and statistic tags determines calculation order (within a group).
- count (tag) - Count for the group
- delimiter - Character for delimiter if a list is given or appended to. Default is comma.
- divzero - Value to display if division by zero would otherwise occur. Default is "div/0".
- displayfield - Optional field used for display value if calculation field is not well-suited for display.
- fillfrom - List values based on all values from the specified item (as specified by "name" attribute). Otherwise, only the current context is used. This can help provide consistently-repeating sub-items and zero- or blank-filling those not present. (Difficult to describe without an example.)
- hidden - To suppress display of an item.
- list - A list of values as an alternative to to a database column ("field"). If both a field and a list is specified, then both are used with the list values coming first.
- onBlank - value to display if cell value is blank.
- onNull - Value to display if cell value is null (blank is default).
- onZero - Value to display if cell value is zero.
- name - Used to reference a row or column by other tags or attributes.
- span - Tells the value to span any inside columns if a grouped level. Otherwise, the value is repeated for each inner instance (if any).
- suffix - Suffix appended to displayed value. Example "%" used indicate percent.
- weightedavg (tag) - Similar to "average" tag, except it is a weighted average. (Perhaps the "calc" tag is good enough to avoid the need for this.) Example: <weightedavg scalefield="empcount">
Also, the location of the titles probably need a bunch of position option tags because there are various ways they can potentially be placed. Paper-based options also need to be addressed (no-page-span, keep-with-group, etc.)
Tags for statistics such as median, standard-deviation, etc. could also be included.
"Field" is used to refer to input table columns to avoid confusion with report "columns".
For now, the input table/sheet is assumed to be pre-sorted.
Actually, multi-nestedness is often a design smell in this day and age as click-able interactive "drill-down" web interfaces can be built rather than try to squeeze all combinations onto a single page. However, the boss/client/user sometimes wants things a certain way and it's not our job to stop them, beyond advice.
--top
This seems like a fine pursuit. I'm interested in developing something similar for user-configurable hovertext in an OCU. Hooking the RDL into the DataModel will probably require greater work, as will numeric formatting and measurement conversions (e.g. Celcius to Fahrenheit). -- GreenHat
But, ReportsSmell -- BlackHat
I don't believe so, but I'll take up my objections on that page.
See Also ReportDefinitionLanguage