With the ease of use that comes with the LightSwitch platform, it is, well, _easy _to understand why some developers like it. You can quickly create business apps in Visual Studio LightSwitch without much work and even without writing a single line of code. In this article I’m going to walk through how to create a simple LightSwitch HTML application using ActiveReports. If you haven’t already done so, you need to install ActiveReports on your machine. You can download a free, full featured, 30 day trial version from the ActiveReports website. We start off by creating a LightSwitch HTML application called ARinLightSwitchHTML.

Connect to an external Data Source

On the ‘Start’screen, you can either create an internal data table for this application or you can connect to an external database such as a SQL Server, Access database, etc. In this example, we’ll connect to an external database. Click the ‘Attach to external Data Source’ link in the middle of the screen. clean_data-source-wizard I’m going to connect to the Reels database on my SQL Server and so will select ‘Database’ and ‘Next’ to bring up this box: 2 Click ‘OK’ after entering your server and database names in their fields. On the Choose your Database ObjectsI'm simply going to select all the tables. 3 Click Finish. This brings all of the tables into your application. 4

Add an ActiveReports Web Service

In order to use ActiveReports in this project, we need to add a web service to the ARinLightSwitchHTML.Server project. The web service will import all the needed ActiveReports assemblies as well as register them in the web.config file. To add the service, right-click on the server project and select Add, then New Item. 5 On the Add New Item screen, select ActiveReports 10 Web Service. Change the file name to ActiveReports.ReportService.asmx. We need to move a copy of this file to the ARinLightSwitchHTML.HTMLClient project as well. To do so, right-click on the ARinLightSwitchHTML.HTMLClient project and select Add, then Existing Item. 6 In the Add Existing Item window, navigate to the ARinLightSwitchHTML.Server project, select the ActiveReports.ReportService.asmx file, and click Add. 7 Now you have the ActiveReports.ReportService.asmx file in both the Client and Server projects of your application. 8

Design Queries to Use in ActiveReports

Before we can create an ActiveReports report file, we need to prepare the queries that we will bind to. To do so, in your Visual Studio Server Explorer, right-click the Data Connections node and select Add Connection. 9 Once again, we will connect to the Reels database on my SQL Server. 2 After clicking OK, the connection shows in the Server Explorer. Right-click the new connection and select New Query. 11 On the query page, execute the following query:

SELECT Movie.Title, Product.InStock, Product.StorePrice

FROM MediaType

INNER JOIN

(Movie INNER JOIN

(Product INNER JOIN MovieProduct

ON Product.ProductID = MovieProduct.ProductID)

ON Movie.MovieID = MovieProduct.MovieID)

ON MediaType.MediaID = MovieProduct.MediaType

WHERE (((MediaType.MediaID)=1))

ORDER BY Movie.Title

Execute the query to get the results: 12 Going back to the Server Explorer, Close the connection: 13

Create an ActiveReports Page Report

In your Solution Explorer, right-click the ARinLightSwitchHTML.Server and select Add, then New Item. In the Add New Item window, select ActiveReports 10 Page Report and rename the new file to rptParamQuery.cs. Click Add to bring up the report.

Data Binding

In order to design the report and bind to data, open the ActiveReports Report Explorer 10 in Visual Studio by opening the View menu, then Other Windows, then Report Explorer 10. To bind the report to a data source, in the Report Explorer right-click on Data Sources and select Add Data Source. In the Report Data Source - General window, enter Products for the Name field. In the Connection Properties tab, enter the Server and Database names. Click OK. Next, right-click on the Products Data Source just created, and select Add Data Set. This brings up the DataSet - General window. On the General tab, enter ProductsDataSet for the Name field: On the Query page, enter the following query and click the green validate icon. Then click OK.

SELECT Movie.Title, Product.InStock, Product.StorePrice

FROM MediaType

INNER JOIN

(Movie INNER JOIN

(Product INNER JOIN MovieProduct

ON Product.ProductID = MovieProduct.ProductID)

ON Movie.MovieID = MovieProduct.MovieID)

ON MediaType.MediaID = MovieProduct.MediaType

WHERE (((MediaType.MediaID)=1))

ORDER BY Movie.Title

36

Designing the Report Layout

Now, let’s design the report. From the Visual Studio Toolbox we’re going to add a TextBox and Table control to the top of the page. From the ProductsDataSet node in the Report Explorer, drag and drop the Title, InStock, and StorePrice fields onto the Detail row of the Table in Column1, Column2, and Column3, respectively: 19

Adding Parameters to the Report

For the parameters, we're going to create a second data set. To do so, right-click the Products data source node and select Add Data Set In the DataSet - General window, enter MediaType in the Name field. Click on Query from the left pane, and enter the following in the Query field:

SELECT 0 as MediaID,'All'as Description

FROM MediaType

Union SELECTMediaID, Description

FROM MediaType

ORDER BY Description

In order to show the MediaType as a set of parameters, we add it the Parameters node in the Report Explorer. To do so, right-click the Parameters node in Report Explorer and select Add Parameters. On the General tab of the Report - Parameters window, change the Name field to MediaType and Text for prompting users for value field to Select a media type. On the Available Values tab, select the From query radio button. From Dataset dropdown, select MediaType, Value field = MediaID, Label field = Description. On the Default Values tab, select the From query radio button. Set the Dataset = MediaType, and Value field = MediaID. Click OK. 37 Finally, select the TextBox on the rptParamQuery report page and set its Value property to: =Parameters!MediaType.Label & " Movies in Stock".

Adding Scripts for Client-Side Rendering

In order to render the reports on the client side, we need to add the following JavaScript files:

  • GrapeCity.ActiveReports.Viewer.Html.js
  • GrapeCity.ActiveReports.Viewer.Html.min.js
  • GrapeCity.ActiveReports.Viewer.Html.css

You can find these files at the following directory on your machine: C:\Program Files (x86)\GrapeCity\ActiveReports 10\Deployment\Html In your Solution Explorer, right-click the Scripts folder under the ARinLightSwitchHTML.HTMLClient project and select Add, then Existing Item. Navigate to the directory above and add the two *.js files: 25 Next, right-click on the Content folder under ARinLightSwitchHTML.HTMLClient project and select Add, then Existing Item. Navigate to the directory above and add the GrapeCity.ActiveReports.Viewer.Html.css file.

We now need to pass references to these scripts. Open the default.htm file under the ARinLightSwitchHTML.HTMLClient project.

Add these lines toward the end of the elements:

And in the elements, add the following lines just before the document.ready function:

Save default.htm.

Add a Screen

In the Visual Studio Solution Explorer, right-click the Screens folder and select Add Screen​. 27 In the Add New Screen window, select the Browse Data Screen template on the left. On the right side, change the Screen Name field to rptParamQuery. Leave the Screen Data field as (None). clean_add-new-screen When the screen opens, we will add a Custom Control to it. To do so, under Rows Layout, click the + Add node and select New Custom Control. 29 In the Add Custom Control dialog box, click OK. This adds a custom control to the screen. We need to point this new Custom Control to the ActiveReports viewer. In order to do so, with the new Custom Control highlighted, 31 go to the Properties window, and click the Edit Render Code link. 32 This opens the rptParamQuery.Islm.js window. In the _ScreenContent_render _function, paste the following:

$(function () {

    viewer = GrapeCity.ActiveReports.Viewer({

        element: element,

        reportService: {

            url: 'ActiveReports.ReportService.asmx'

        },

        uiType: 'desktop',

        report: {

            id: "rptParamQuery.rdlx"

        }, 

    });

});

33 At this point, the application is completed. You can run the application and view it in your default browser: 34

Conclusion

LightSwitch provides an easy way to build business applications in Visual Studio without much effort or a lot of code. In this blog, we built a simple LightSwitch HTML application using ActiveReports with run time parameter inputs. The application has been attached here and is available for download. Download: ARinLightSwitchHTML