How to Create a DataSet in SSRS

This page assumes you already know how to create http://msdn.microsoft.com/en-us/library/aa237251(SQL.80).aspx.
In this page I only point out the DataSet configuration specific to the PowerShell DPE.

The DataSet Query (Command Text)

Traditional SSRS DataSets execute SQL statements against a relational database. The PowerShell DPE does absolutely no SQL queries, it is all about PowerShell. Kinda confusing since this is all part of SQL Server, but that's the beauty of it ;-).

The text for the DataSet query is just a PowerShell script. It can as simple as 'dir' like the illustration below, or it can be your super sneaky clever script that mashes up disparate objects into something fascinating for presentation purposes. You're such a show-off!
[image:DataSet-Dir.JPG]

DataSet Fields

The fields for the DataSet are generated based on the output of the PowerShell script. This is done automatically, but if you're free to hard code them if you must. For example, if the output of the script is FileSystem objects, then you would get properties such as:
  • Name
  • Parent
  • LastAccessTime

Testing Your DataSet Query

My sanity test for PowerShell DPE DataSet Query strings is Out-GridView. If my script produces output that looks good in Out-GridView then it is ready to be plugged into a DataSet. This creates a neat division of labour:
  1. The PowerShell guru creates a super-sneaky-clever script to Get-Stuff
  2. The SSRS report designer takes the script and creates DataSets, and reports that consume that DataSet

Here is an example of a slightly more complex script, shown in both SSRS and PowerShell ISE.
[image:DataSet-AD.JPG]

ISE-AD.JPG

Last edited Apr 19, 2011 at 9:52 PM by CraigMartin, version 5

Comments

No comments yet.