In this thread I asked about reporting to PDF file, and whether it was worth us implementing SSRS (not something we are at all familiar with) to get the PDF-reporting ability. My other option is to leverage our existing purely-HTML based APP to have a PRINT CSS style-sheet suitable for PDF.
To put it into perspective I said I would describe how our APP etc. works.
We create some tables (e.g. SSMS) and then "Import" the DDL into our Metadata tables. Our Metadata holds Table/Column name, datatype (more detailed than SQL - e.g. forced uppercase), and lots of other properties -
e.g. legends for Data Entry forms and alternative narrow label for report column heading, and so on.
We use CMS Templates for output. They have a {MyColumnName} tag where data should be substituted, and also some modifiers such as {MyColumnName.PROMPT} to provide the data entry legend/prompt for a particular column, similarly {MyColumnName.COLHEAD} for the narrow column heading label - if there is one; COLHEAD defaults to PROMPT, and PROMPT defaults to Column Description. 90% of the time the Column Description is suitable for all uses
Thus if we decide to change, say, the COLHEAD (Metadata) for a column that is, immediately, used wherever that TAG appears in any CMS template - no rebuilds or anything else required.
That's true of all the Metadata - so if we change the column size, or the datatype from INT to FLOAT, or anything like that there are no CMS changes necessary. Obviously we would have to change the underlying table to match (in practice we can either change the table DDL itself and refresh the Metadata from it, or change the Metadata and then [from that] produce a DDL Script to change the table, so as an aside we can deploy a newer version of Metadata to a client's database, as part of an upgrade, and create a DDL script, from that, to modify the DB to match what the Metadata has become)
A very high proportion of our tables are relatively simple CRUD, or that same simple CRUD plus a few relatively minor bits of custom code. So we have focused on mechanically generating all the basic stuff so that the only work we actually have to do is where bespoke/custom code is needed
Once a new table is created we mechanically generate the CRUD. That is, usually, three CMS templates: a Search Criteria Form, a Results List template and a Data Entry Edit Form. We have metadata for all forms - basically "Table-A" and a Form Items List of "Col-1, Col-2, ...". and then appropriate CRUD SProcs are also mechanically generated - Get, Save, Delete and also Find (matching the Search Criteria Form).
Both the Data Entry Form and the Save Sproc includes logic for data validation, based on the Metadata. So for example the Form will limit the width of form field to match the Metadata (which itself is based on the VARCHAR column width), and where a Foreign Key is defined in the Metadata that will be enforced by the Save SProc (i.e. the operator gets a user-friendly data-validation-message, rather than an error message!) and also the FKey provides a Lookup by default - either a Select List (for a small table) or a [...] button that allows the user to FIND a record (for a large table)
We usually have an Audit Changes History table, for each actual data table (i.e. OLTP APPs), so we also auto-generate a Trigger that stores previous changes into the corresponding Audit table
So: I can edit the Form Items for, say, a Name and Address table and the Search Criteria Form to have Form Items for Name, Phone and Postcode. The CMS template for the form will be generated for those forms - basically something like
<table>
<tr>
<th>{MyNameCol.PROMPT}</th><td>{MyNameCol.INPUT}</td></tr>
<tr>
<th>{MyPhoneCol.PROMPT}</th><td>{MyPhoneCol.INPUT}</td></tr>
<tr>
<th>{MyPostCodeCol.PROMPT}</th><td>{MyPostCodeCol.INPUT}</td></tr>
</table>
I can set properties on each Form Item definition - e.g. override the PROMPT label (this one time), disable the column value as an Sproc @Parameter, or use a LookUp select list (that defaults to whatever is defined for that column anyway), and so on.
So the user types in Criteria Values for some/all those form-fields and they are provided as parameters to a Find Sproc. The Find SProc will be auto-generated with parameters for @MyNameCol, @MyPhoneCol, @MyPostCodeCol and suitable (basic!) code to find rows based on those criteria. (The Form Item Metadata allows choices for Being / End (range), Contains, Equals,and so on.
I also create a Results List Definition, for the columns I want in the report. So lets say NameID, MyNameCol, MyPhoneCol, MyPostCodeCol, CountryCode, ActiveInactiveFlag and that generates a matching CMS template which uis used to format the output from the Find Sproc.
Report CMS templates have sections for Header, a repeat section for the Body Rows, and a footer (and a bunch of other conditional / sub-report / AJAX-drilldown / whatever capabilities)
In this example I would also want a link-ICON based on the NameID that went to a full Record Card report for that name record and/or a CRUD Data Entry Form.
Each of these CMS templates is associated with a PAGE (in typical HTML/Website context). So a PAGE has a PageID and that has Security requirements, a Branding skin, and then a list of all the associated CMS templates. For the Create / Update / Delete part of a CRUD set that's usually the Edit Form CMS template, plus a report template for the Audit Table to be able to see a history of all changes to that record.
When necessary I can then edit the automatically generated CMS Template and/or the generated SProc to add some bespoke code - so I can edit / customise anything to do with a specific page - we have CMS Template editors in the APP, so I can use those to modify the Edit Form or the Audit History templates (in this example).
Any changes to Metadata are dynamic, the only exception being if we rename a column or its datatype. All the Metadata is fine, the Column Name and Data Type are just data element not a PKey / FKey, but of course the SProcs have to be regenerated.
For a report such as an Invoice (the original nub of this question) I basically need a few CMS templates and an Sproc. The Sproc would output (say) resultset for the Invoice Header (a single row with Client name,address, Invoice Number, Date etc.) and then a second multi-row resultset for all the items on the invoice.
The first Order Header resulset could include the Sales Tax and Invoice Total amounts, in which case my template (with all formatting/layout removed) would look like
{ClientName}
{ClientAddress}
{INVOICE_ITEMS.INSERT}
{SalesTax}
{InvoiceTotal}
or I could output the SalesTax and InvoiceTotal as a separate, 3rd, resultset with its own template.
The issue I am grappling with is how to have dynamic page breaks, with subtotals, when the location of the page breaks is governed by both "number of address lines" and also "wordwrap on multi-line Invoice Items descriptions" and the size of the piece of paper.