SQLTeam.com | Weblogs | Forums

App / Report Builder


#1

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.


Generate Invoices as PDF from SQL data
#2

I have had some experience converting a very basic HTML table to PDF via https://wkhtmltopdf.org/, which was one of the better utilities I've found.

You mentioned in your earlier thread "that leveraging the CSS in the current web pages may convert readily to PDF". My experience does not bear that out, and my layout was VASTLY simpler that what you've described here.

HTML is simply not a page-based layout engine, and CSS is not up to the task either. Any @print directives you place are at the mercy of the browser's rendering engine to be interpreted correctly. The Webkit-based util I mentioned seems to ignore or misinterpret them entirely. Just dealing with internal printer margins, PDF renderer page margins, and table internal margins was an amazing headache, and I consider myself very well versed in CSS and HTML (although it's been a few years, but the general form hasn't changed much).

If you've seen that meme about "I moved the image 1 mm in Microsoft Word and it generated 4 new pages" you'll understand what I went through.

I also could NOT get the Windows version of wkhtmltopdf to generate properly, it was always scaled down. Ended up using the Mac version which did work correctly. I still don't understand why that was...same command line params on both platforms, still rendered the PDF wrong on Windows, I gave up trying to figure it out.

Please note that there was nothing dynamic about the page size or contents: the layout was fixed, positioning was absolute for most elements, and it still wouldn't align. I was almost literally making millimeter changes with almost a full inch of extra space that would suddenly jump to a new page.

For what you're describing you need, I can't recommend it. Go with SSRS. It handles all the page elements, headers and repetition, word-wrap, etc. for you. HTML does not, CSS does not, and the PDF renderer probably won't either. Even if your current solution works flawlessly in a browser, or even all browsers, you will very likely be very frustrated with the conversion. I can't say for certain that SSRS will render a PDF better, but it will be a lot easier to fix than wrangling HTML + CSS directly.


Unattended SSRS generation of PDF files
#3

Amazing stuff you put together there!! it's like a whole echo system. impressive. I also do a lot of web development for our company, using node/angular/express for api with sql server back end. I could never imagine putting my reporting in html.

I cannot add anything more than what robert_volk has said. SSRS is just too easy compared to tweaking pixels with html.


#4

Robert, I'm very grateful, thank you; your real-world experience is invaluable. I've read all sorts of good things about WebKit approach and it seemed like that might be a solution ... nothing like real first hand experience though, and I have been in the situation you describe too many times - fiddling about trying to get something to work as it supposed to, often trying to do the work as if "through a letterbox" and unable to see the whole room and thus no chance of being able to actually do a good job, and wasted so much time that I could have written the whole thing from scratch :frowning:

Flip side for me is that we have no experience of SSRS and a) no time for the learning curve and b) even if I did climb the learning curve I would still be a beginner, for a while, and I need a Pro Solution.

So I probably need to bring an expert in and just get the problem solved. The important thing is that both of you think that this is doable, relatively easily, in SSRS, so I'll most likely have my best-foot-forwards in starting down that road. Who knows, I might even get to like SSRS!


#5

While SSRS has a learning curve, it's not entirely different from MS Access reports. If you've done those, you'll get comfortable pretty quickly. I would even suggest using Access reports as prototypes to give your SSRS folks guidelines on what you're looking for.

And if you want a REALLY interesting project: all SSRS RDL files are nothing but XML, so if you get familiar enough with that part of it you could possibly generate an RDL file from your current templating system. If you've ever done code generation, or used the T4 template engine in .Net, or ever heard of BIML for SSIS packages, it's basically the same technique. If you expect to have to do this a lot it may be a worthy investment of time, anyone who uses BIML will vouch for how much more productive they are when using it.


#6

I love its flexible formatting. I rarely worry about any column width formatting, just output the data and let the browser worry about whether the user has their huge 4D screen in landscape, as I was expecting, or if they have turned it round to portrait :slight_smile:

Also love the ability to "inject" a portion of a report into the middle of some HTML.

I expect SSRS has solutions to this problem, but here's what I do:

Let's say I have a SQL result-set and when the ParentKeyID changes I want to output some extra "stuff". Probably a header row or similar, but maybe some more intricate changes to the template itself.

SELECT [AdditionalTemplate] = CASE WHEN ThisID = LastID THEN NULL ELSE MyTemplateString END
    , CustomerName
    , Col1
    , Col2
    , ...

and then in my main template I have have

{ROWTEMPLATE_START}
{AdditionalTemplate}
<tr>
<td>{Col1}</td><td>{Col2}</td><td>...</td></tr>
{ROWTEMPLATE_END}

and the content of my Additional Template could be something like:

<tr>
<th colspan=999><h1>{CustomerName}</h1></th></tr>

Or I can just do something like

SELECT CustomerName
     , [CustomerNameStyle] = CASE WHEN Status = 'Expired' 
                             THEN 'MyHighlightCSSClass'
                             ELSE NULL
                             END

and template:

<tr>
<td class="{CustomerNameStyle}">{CustomerName}</td></tr>

I could do that with some programming at the point of delivery (in the APP) of course, but that separates part of the process between the SQL and the APP - I would have to deploy both SQL and APP if that changed. This way I can just change & deploy the Sproc (and perhaps the CMS template, but that's just a row of data in the database).


#7

SSRS has amazing and flexible conditional formatting to the hilt :slight_smile:
Let's get started with installing SSRS, we can guide you to the end goal. Once done with installation, within minutes, you will have your first report up and running within hours.

Let it go Kristen, let html reporting go. :grin:


#8

Hehehe ... definitely not doing that, we have amazing flexibility and its all integrated into all other aspects of the APP - just probably different to what you are familiar with in SSRS.

But I'll be happy to generate pixel-perfect alignment in PDF files using SSRS :slight_smile:


#9

One thing to be aware of when building a report that is going to be exported to something other than HTML is that you have to be more aware of page size, orientation and margins. Right-click outside the report pallet and you can turn on the Ruler - then right-click and go to properties.

Set the orientation and the desired margins - default margins are 1 inch...if you use the defaults then you will have 2 inches less space than the size of the paper. If you select landscape the default size is 11 x 8.5 which means you only have 9 inches of pallet - using the Ruler you then make sure you don't go further than that 9 inches.

If you go outside of the defined margins - when rendering the PDF those items outside the margin will generate a separate page. It can be quite annoying to figure out if you are not looking at the ruler and aware of the margin settings.


#10

Ah, fantastic, thanks. I've been having to reset the Page Setup each time I ran the report, I knew there would be somewhere to define the amounts of course, but I hadn't had time to read the DOCs. That's answered the question for me, many thanks. (I need A4 paper over here, rather than Letter, too)

One thing I've done, in error, was to widen a column in a Tablix, planning to then make a column to the right more narrow to compensate, but in the process I have increased the width of the "background" for the report, and then when that has been used in a Sub-Report its done the "too wide for PDF page" and made a multi-page poster!!, just as you describe.

Would be nice if i could constrain a report so that it cannot be made wider than some MAX that I set (or gives me a "If you do this it will exceed the Max Width, OK Y/N?" type prompt)


#11

That would be nice...the alternative is to turn on the Ruler (which would be nice if you could make that default too). In earlier versions we also had the ability to turn on/off a grid and the ability to set the grid size but they removed that functionality...the grid was nice because you could set it to be very fine and make small adjustments...now to make those small adjustments you have to go to the properties window and type in a value...