Reports with SQL data source, from Powershell

 
Last time we met, I showed an exciting way how to create and preview SharpShooter reports from command line, without Visual Studio. Why should you care?

1. Everyone can use a script. Analysts and other business people (and some developers ) often don’t have time or skills to write code in Visual Studio. A powershell script would be a decent alternative for them, as well as a good boost for those who just start learning SharpShooter.
2. It’s faster to run a script than to write code. A script can knock off a report in a matter of seconds – compare this to 8 minutes (!) of Creating a Simple Report video on YouTube.
3. Reports-related code is always the same. Adding ReportManager, creating report slots… everyone would benefit if we extracted such code and made it available for those who don’t like reinventing the wheel.
4. The script for SharpShooter is open source. You can learn how it works, change it in any way you like, extend it, contribute back your ideas and generally get more control over the development process.

But my last example didn’t use any data source, and could only kick off Report Designer and Report Viewer – ok for a starter but not good for “production use”. So this time we’ll move forward and build a report which will display data from a SQL Server data source.

Preparing the data.

For our report, let’s run a script which will create a separate database with two tables:

CREATE DATABASE MyTest
GO
USE MyTest
GO

CREATE TABLE Authors (
  Id    BIGINT NOT NULL PRIMARY KEY IDENTITY(1,1),
  Name  NVARCHAR(100) NOT NULL
)
GO

CREATE TABLE Books (
  Id       BIGINT NOT NULL PRIMARY KEY IDENTITY(1,1),
  AuthorId BIGINT NOT NULL,
  Name     NVARCHAR(100) NOT NULL,
  CONSTRAINT FK_Books_Authors FOREIGN KEY (AuthorId) REFERENCES Authors(Id)
)
GO

That’s it. There’s no data in the tables yet but that’s fine: to build a report we need only database schema. Now we should come up with the query to fill up the report – let it be the one to return books along with their authors:

USE MyTest;
SELECT 
  book.Name   AS BookName, 
  author.Name AS AuthorName 
FROM Books book
INNER JOIN Authors author ON author.Id = book.AuthorId

Generating the report.

In PowerShell console, I navigate to C:\Reports where I have two files – ReportHelpers.ps1 with all the necessary code to create/preview a report and select.sql with the above SQL query, just for convenience.
 
(Want to see source code of ReportHelpers.ps1? Feel free to flick through on BitBucket!)
 

 
Then (see image below) I include the powershell script into the current context and generate the report, passing over the query for data source setup:
 

 
The script saves the report in advance under an autogenerated name, which we can obviously change at any time. Also, notice that I provide only the query but not the connection string – if the latter is missing, the script would try to build the connection string on its own (using local SQL Server and trusted connection).
 
After a few seconds Report Designer pops up, with already prepared blank report, and when I drop a DataBand on the page – surprise, surprise! – in the DataSource properties I see PowershellDataSource, the data source generated by our script.
 
Finally I drop two textboxes which will display book name and author name respectively (see the image below), save the report and close Report Designer.
 

 

Displaying the report.

Recall that we don’t have any data in our tables yet – so let’s insert some:

USE MyTest
GO

INSERT INTO Authors (Name) VALUES ('V.S. Ramachandran') 
INSERT INTO Authors (Name) VALUES ('Jeff Hawkins')
INSERT INTO Authors (Name) VALUES ('Ray Kurzweil')
INSERT INTO Authors (Name) VALUES ('Christof Koch')
GO

INSERT INTO Books (Name, AuthorId) VALUES ('A Brief Tour of Human Consciousness', 1)
INSERT INTO Books (Name, AuthorId) VALUES ('The Tell-Tale Brain', 1)
INSERT INTO Books (Name, AuthorId) VALUES ('On Intelligence', 2)
INSERT INTO Books (Name, AuthorId) VALUES ('The Singularity Is Near', 3)
INSERT INTO Books (Name, AuthorId) VALUES ('The Quest for Consciousness', 4)
GO

And then preview the report with the following call (I didn’t re-save the report under a different name, so am using the autogenerated name):

     Show-Report "C:\Reports\97fd7d7b891a.rst"

 
..which brings up the following preview:
 

 

How it works.

As usual, you’re welcome to browse the script on BitBucket and below I’ll concentrate on the most interesting parts.

• New-Report has a hardcoded xml template of a blank report with SQL Server data source. While creating the report from the template, the script fills in the data source details from its parameters (query always gets passed as a parameter, connectionString is either passed over or created inside the script).
• Report Designer works only in STA mode, while Powershell console runs in MTA by default. To fix that, we yield another powershell command under STA – but then it’s not easy to pass parameters (connectionString and query) to the command. My solution is to create environment variables, use them and delete before exit. Works fine!
• SharpShooter gets installed in different folders on x86 and x64 platforms. The script needs to access some .dlls but they can be either in “Program Files” or in “Program Files (x86)”. The script uses pointer size to detect the correct path:

     $programFiles = ${env:ProgramFiles}
     if ( [IntPtr]::Size -eq 8 ) {
        $programFiles = ${env:ProgramFiles(x86)} 
     }
  

Summary.

As we have seen again, PowerShell is a decent alternative to Visual Studio when it comes to working with SharpShooter reports. Editing powershell scripts can be, at times, faster that coding in Visual Studio – and surely a powershell script is a lot more transparent that a compiled .NET application.
 
You can create a new report with

   New-Report  -query:"file with the query or plain-text query" 
               -connectionString:"connection string, optional"

 
And you can preview a report with

   Show-Report -file:"rst file with the report"

 
I bet there’s a lot more we can do here. Stay tuned!