Tuesday, September 25, 2012

Use SSRS for Reports in LightSwitch

This tutorial should work for all versions of LightSwitch but I haven’t tried this with the new Visual Studio 2012, I only have Visual Studio LightSwitch 2011 stand alone.

I usually use DevExpress XtraReports for all my reporting needs but just for fun I installed Microsoft SQL Server Reporting Services (SSRS) to use with SQL Server 2008 R2.

What this tutorial is and What it is not.

Setting up SSRS is beyond the scope of this tutorial. This tutorial will focus only on creating an SSRS report then calling the report from LightSwitch with the Spursoft LightSwitch Extension - SSRS Viewer.

What do we need:

We need to create the datasource for the SSRS Report

I created a database named demos in SQL Server

Create two tables

1st one will be named CoffeeFlavors with the following column definitions:

id – int,  Primary Key,  not null,  Identity = Yes

coffee – varchar(50),  not null

comments - text, null

The 2nd table is named SSRS with the following column definitions:

id – int, Primary Key, not null, Identity = YES

coffeeid – int, not null

dayOfWeek – varchar(9), null

comments – text, null

Fill the tables with some data
House BlendNULL
San Francisco BlendNULL
Christmas BlendNULL
French RoastNULL


Now we need to setup the relationships:

Create a new Database Diagram and add the CoffeeFlavors and SSRS tables we just created then drag and drop the CoffeeFlavors id field to the SSRS coffeeid field, accept all the defaults. You should now have a ONE (CoffeFlavors) to MANY (SSRS) relationship.

Now save this diagram using any name you want, I saved mine as dbo.master

We now need to create a Stored Procedure that our SSRS Report will call.

Create a new Stored Procedure called aggregateCoffeeChoices and insert the following:

We need 2 parameters

@startDate date,

@endDate date

And for our Select statement enter

SELECT DISTINCT dbo.CoffeeFlavors.coffee, dbo.SSRS.date,dbo.SSRS.dayOfWeek,
SUM(dbo.SSRS.coffeeid) AS count
FROM dbo.CoffeeFlavors INNER JOIN     
dbo.SSRS ON dbo.CoffeeFlavors.id = dbo.SSRS.coffeeid
WHERE (dbo.SSRS.date BETWEEN @startDate AND @endDate)
GROUP BY dbo.CoffeeFlavors.coffee, dbo.SSRS.date, dbo.SSRS.dayOfWeek

Helpful Hint: If you struggle with getting all the JOINS and SQL just right, try creating a View then copy and modify the SELECT statement. The View Designer will create most of the SQL for you.

You can now close the SQL Server Management Studio if you want.

We need to create a folder to hold our Report

Assuming you have Reporting Services configured correctly and can connect to your Report Server. Open SQL Server Reporting Services Configuration Manager. Navigate to the Report Manager URL link, click the Report Manager URL link

On the SQL Server Reporting Services Home page click the New Folder button

Name this new folder Demos

For leave the Reporting Services Home Screen open, we will visit this screen again shortly

We now need to connect our Report Management Service to this database

Open Microsoft SQL Server 2008 R2 Report Builder

Choose the Chart Wizard

The next screen is asking you to choose a dataset, we need to create one so just accept the default and click Next.

We now need to set a new Data Source Connection, click New then change the Name field to something more meaningful like SSRSDemoDataSource.

Make sure the Connection type is set to Microsoft SQL Server then for Connection String click the Build button.

Select your server from the Server Name dropdown,

then select your database and click OK.

I always like to click the Test Connection button just for a sanity check at this point.

Click OK and make sure your new connection is highlighted

Click Next

You should now see the Design a Query Screen

Click the + disclosure box for Stored Procedures in the Tree Control under Database View

Choose the Procedure we created earlier – aggregateCoffeeChoices

We will be changing the values for our @startDate and @endDate parameters later but for now just accept all the fields and parameters and click Next

The next screen is the Choose Chart Type

You can choose any type you want but I chose Line

Click Next

Arrange Chart Fields

Under Available fields, drag coffee to the Categories box, drag date and dayOfWeek to the Series box and drag count to the Values box.

Click Next

Choose a style

I chose Corporate

Click Finish

You should now see the Report Builder Designer

I usually make the the chart control a little bigger so I can see all the data.

We now need to change the parameters.

Click the Parameters disclosure button and right click @startDate then select Parameter Properties

Uncheck the Allow null value checkbox

Select the Default Values option in the Navigation panel

Select the No default value Radio Button

Click OK

Repeat the above steps for the @endDate parameter
The default settings presents the user with disabled calendar controls for our parameters and NULL checkboxes checked next to each control. In my experience this only confuses the end user so by
changing the settings above we enabled the calendar controls and removed the NULL checkboxes and the UI is now more intuitive for the user.

Click the Run button give the report a startDate and an endDate and click the View Report button to see if our chart is the way we want it, if not return to the Designer and make any necessary changes.

Once your satisfied with the chart in the viewer click the Save icon above the Ribbon Run tab

Save this report to the Demos folder we created earlier

Go back to the Reporting Services Home Screen and navigate to the report you just saved in the Demos folder.

Click the reports link

Either leave this screen open or in the Address Bar copy and paste the page’s URL to some place like Notepad, just so its handy, we will need the address for our SSRS viewer extension.

If you haven’t installed the Spursoft LightSwitch Extension, please do so now

Open Visual Studio LightSwitch and create a new project, for this demo I chose the C# version.

Name the project SSRS_Demo

Although you could create a table in the ApplicationData local database, connect to an existing DataBase, WCF RIA Service or an ODATA source, for this tutorial we won’t be connecting to any of these directly.

Create a new Screen and name it demo

Click the Add Layout Item and select Group

Select the Group disclosure button and select SSRS Viewer

In the Properties Panel paste the Reports URL in the Reporting Server URL text box.

We now need to write some code for the viewer

With the demo screen open click the Write Code disclosure button and select demo_Created

In the demo_Created function enter the following code:

partial void demo_Created()

            // Set up our SSRSViewer’s Event Handler

            this.FindControl(“Group”).ControlAvailable += new EventHandler<ControlAvailableEventArgs>(DisplayInternalBrowser);


demo_Created  is called just after the screen is displayed and all the controls are present on the screen, this function will set the event handler function for when the specified control is available.

Now lets create the Event Handler function

public void DisplayInternalBrowser(object sender, ControlAvailableEventArgs e)

            // Get a reference to the SSRSControl and set the SSRS Viewer on our
            // screen to this reference

            Spursoft.LightSwitch.Extension.Presentation.Controls.SSRSControl ctrl = (Spursoft.LightSwitch.Extension.Presentation.Controls.SSRSControl)e.Control);

// Set the PopupWindow name for in browser applications i.e. Web App,
// this will allow us to reuse the same window for additional calls
// otherwise anytime a new call would fire off, a new window would be launched
// Desktop or Out of Browser applications will just ignore this

cntrl.PopupWindowName = “ReportWindow”;



First a little walk through of our Desktop app version

When you first create a LightSwitch project your projects Application Type is set to Desktop by default.
The SSRS viewer in a Desktop setting is an embedded browser, so the report will look like it’s native to your application.

Run the project

When the home screen is displayed your SSRS Report will be shown in the demos tab.

Once your satisfied, close the application

Under the Projects Root Node double click the Properties node

This will open the Projects Properties Panel

Select Application Type and choose the Web Radio Button

Run the application again

This time when the application finishes launching a new pop up window will launch,
NOTE: you may have to disable your browsers PopUp Blocker.

The Spursoft LightSwitch Extension has many other great features besides the SSRS Viewer so I encourage you to check it out in the Visual Studio Gallery.        

Additional Resources:

MSDN Forum discussing the SSRS Viewer with Derek from Spursoft - this where I got the code for this example.

The LightSwitchHelp website – Michael Washington has a great article on Printing SQL Server Reports with LightSwitch using acustom Silverlight Control

Hope this helps.


My PowerApps Videos

Demo of using the PowerApps Office365Users Connector and Microsoft Graph to surface User information from Azure Active Directory with the Us...