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:
- Spursoft LightSwitch Extensions – this is a paid extension, no trial version
- Microsoft SQL Server2008 R2 RTM - Express with Advanced Services
- Microsoft Visual Studio LightSwitch 2011 or greater.
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
|San Francisco Blend||NULL|
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
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
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
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.
Choose a style
I chose Corporate
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
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.
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.