Scaffolding in Report SharpShooter

 
Hello everybody, we’re continuing a series of posts about “powershell coding”, promoting a dead easy way to create SharpShooter reports without ever touching Visual Studio.
 
When we just started looking that route two months ago, our first post was simply displaying Report Designer or report preview – not exactly impressive but it allowed to avoid all the boring steps of creating DataSets, ReportSlots, implementing “render completed” event handlers and other humdrum stuff.
 
This time we make a leap forward: from now on, it’s possible to automatically “pre-design” the reports. By that I mean when Report Designer opens up, it will already contain a DataBand with a Header and TextBox elements representing all the data in your SQL query.
 
In software world this is often called scaffolding – and finally we have a chance to enjoy its salutary effects in Report SharpShooter

Show me how it works!

First off, you should grab this PowerShell script, save it somewhere (here we save to C:\Reports) and navigate to that folder from PowerShell console:
 

 
Then you’d need to include the script into the current context with

   . .\ReportHelpers.ps1

and create a new scaffolded report with

   New-Report -query:"..." -connectionString:"..." -scaffold

• query parameter is a must-have, we can provide either a SQL query itself or a path to a .sql file containing the query
• connectionString is optional (by default the script would build the one for the local SQL Server, trusted connection and master database)
• and the scaffold switch, if present, tells the script to generate the headers and detail items for each of the column from the query.

Now imagine we want a report listing all tables in master database of the local SQL Server. With PowerShell script, it will be as simple as that:
 

 
Which, in turn, brings up an already generated report layout:
 

 
And that’s it! Can you believe that? Completely done! Now we can, optionally, save this report under a different name, as our script suggested an autogenerated one. Finally, we close Report Designer and show the report – again from PowerShell console – with the following command:

   Show-Report -file:"...path to .rst file..."

And the above command brings up the following preview:
 

Summary.

As you can see, scaffolding will get you up and running pretty quickly, but of course the “one size fits all” autogenerated report won’t always be the solution you need.
 
Most likely, you’ll want to alter fonts and layouts, add some scripts, and do a bunch of other important changes. However, you would have started from the scaffold prototype anyway, so it’s often useful to have it done for you automatically.
 
So, give it a try – grab the open source powershell script from BitBucket, play with it and let me know what you think of it!