Apr 6

Written by: Michael Washington
4/6/2014 2:05 PM 

 

You can create fully integrated Visual Studio LightSwitch HTML reports using ComponentOne’s ActiveReports. The difference between this article and the ActiveReports solution covered in the article: Creating LightSwitch HTML Reports using ComponentOne Active Reports is the following:

• The set-up is much easier because ComponentOne has created Nuget packages to perform the set-up for you.
• This article will demonstrate creating reports with parameters
• This article will use intrinsic data at report run-time. This means the report will consume and enforce any business rules and security you have built into your LightSwitch application.

To demonstrate the use of ActiveReports in creating reports in a Visual Studio LightSwitch application, we will create a report for the application created in the article: An End-To-End Visual Studio LightSwitch 2013 HTML5 Application.

 

Set-Up

Starting with Visual Studio 2013 (or higher), install the ActiveReports 8 (or higher) Trial (or full product).

See the Installation guide and Documentation & Demos.

Free Technical Support (Submit questions using the online support portal at no cost during your 30-day evaluation.)

Connect To The Database To Design The Report Query

At run-time we will consume and enforce any business rules and security we have built into the LightSwitch application by calling the LightSwitch data layer directly, however, we will create a direct connection to the database to help us design the query.

First, we build and run the application.

We can connect to the database by opening the Server Explorer tab and right-clicking on Data Connections and selecting Add Connection.

Enter:

(localdb)\v11.0

for the Server name and Use Windows Authentication.

Click the dropdown for database name and select the file at:

..\Bin\Data\ApplicationDatabase.mdf

We use the Test Connection button to test the connection.

Click the OK button

The database will show and we can use New Query…

SELECT     Orders.OrderDate, Products.ProductName, Products.ProductPrice, OrderDetails.Quantity
FROM         OrderDetails INNER JOIN
                      Products ON OrderDetails.OrderDetail_Product = Products.Id INNER JOIN
                      Orders ON OrderDetails.OrderDetail_Order = Orders.Id

…to create and test the query that we can use for the report.

We are careful to Close Connection before trying to run the project.

For more information see:

Working with SQL Server LocalDB in LightSwitch Projects in Visual Studio 2012 (the directions also work with Visual Studio 2013).

 

Install ActiveReports Support Files In The LightSwitch Application

We need to install the Client and the Server Nuget packages.

Open the Nuget Package Manager Console.

Select the HTMLClient in the dropdown and enter:

Install-Package ActiveReports.LightSwitch.Client –verbose

…at the the PM> prompt and press the Enter key.

(Note: if it hangs, use the task manager to close Visual Studio and perform the steps under “Here are the steps to rollback if you need to…” (below) and perform the install again)

You can also install Nuget packages using the dialog.

To install the server package, right-click on the Server project and select Manage NuGet Packages…

Search for ActiveReports and click the Install button next to ActiveReports Server for LightSwitch.

The Nuget packages add several files to the project and update the default.htm page.

Here are the steps to rollback if you need to…

• In the HTMLClient project…

               

• Uninstall the package and make sure you answer No to this:
   
   
   
• Double-click Reports.lsml to open it, then right-click and delete it (if you don’t open it first you won’t be able to delete it (you will get: error: locked by another component)).
• On the Server project…
  • Uninstall the package.
  • Delete the Reports folder and its contents (note: if you modified the CodeBasedReportContext.cs file it will still be there).

 

Create The Report

Select View / Other Windows / Report Explorer 8 to display the Report Explorer.

To supply data to the report at run-time, open the CodeBasedReportContext.cs file and update the GetDataSource method with the following:

 

    var data = serverContext.DataWorkspace.ApplicationData.OrderDetails.GetQuery().Execute();
    var list = from OderDetails in data
                select new
                {
                    OrderDate = OderDetails.Order.OrderDate.ToShortDateString(),
                    ProductName = OderDetails.Product.ProductName,
                    ProductPrice = OderDetails.Product.ProductPrice,
                    Quantity = OderDetails.Quantity
                };
    return new GrapeCity.ActiveReports.Data.ListDataSource(list.ToList());

 

To design the report, double-click on the CodeBasedReport.cs file in the Server project to open it.

To supply data to the report at design-time, in the report designer, select the database icon on the detail line, select the SQL tab, and enter a connection string and a query for the report and click OK.

For the connection string use the following format:

Data Source=(localdb)\v11.0;AttachDbFilename= {full location of your .MDF file} ;Integrated Security=True;Connect Timeout=30;MultipleActiveResultSets=True

(replace {full location of your .MDF file} with the full path and location of the .mdf file in your project, at: ..\Bin\Data\ApplicationDatabase.mdf)

If your data is in a database external to LightSwitch you can simply connect directly to it.

Enter the query in the Query box and click OK.

We can drag and drop fields to design the report.

See: Documentation & Demos for assistance.

We can use the Preview button for a real-time preview of the report.

 

Create The HTML Screen

Open the Main screen in the Client project and add a button to open the Report screen.

Run the project and click the Show Reports button…

The report will show.

Creating a Report That Takes a Parameter

The first step to creating a report that takes a parameter is to make a WCF RIA Service method or a query that takes a parameter and returns the desired result.

In this example we will create a query that takes a parameter.

Right-click on the OrderDetails table and select Add Query.

Design the query above and save it.

Next, we add the following method to the CodeBasedReportContext.cs file:

 

        public static object GetOrderDetailsByOrder(int? intOrderId)
        {
            using (var serverContext = ServerApplicationContext.CreateContext())
            {
                var data = serverContext.DataWorkspace.ApplicationData.OrderDetailsByOrder(intOrderId).Execute();
                var list = from OderDetails in data
                           select new
                           {
                               OrderDate = OderDetails.Order.OrderDate.ToShortDateString(),
                               ProductName = OderDetails.Product.ProductName,
                               ProductPrice = OderDetails.Product.ProductPrice,
                               Quantity = OderDetails.Quantity
                           };
                return new GrapeCity.ActiveReports.Data.ListDataSource(list.ToList());
            }
        }

 

We right-click on the CodeBasedReport.cs file and select View Code.

We change the CodeBasedReport method to the following:

 

        public CodeBasedReport()
        {
            //
            // Required for Windows Form Designer support
            //
            InitializeComponent();
            instance = this;
            if (!CodeBasedReportContext.IsPreview())
            {
                HttpCookie cookie = HttpContext.Current.Request.Cookies["intOrderId"];
                if (cookie != null)
                {
                    this.DataSource = CodeBasedReportContext.GetOrderDetailsByOrder(int.Parse(cookie.Value));
                }
            };
        }

 

Open the Reports screen and add a Integer parameter.

In the Properties for the parameter, select the Is Parameter box.

Open the Report.lsml.js file and add the following line:

 

document.cookie = "intOrderId=" + contentItem.screen.intOrderId.toString();

 

Remove the Show Reports button from the Main screen (as it will no longer work now that the Reports screen requires a parameter).

Open the AddEditOrderDetail screen and add a Show Reports button to open the Reports screen.

When we run the project and select an Order we can click on the Show Reports button…

…and see the report for the selected Order.

 

Additional

• To add another report, make a copy of the Reports folder (in Windows Explorer) and rename/refactor its contents. You will also need to add the corresponding .asmx file to both client and server projects and make sure it points to the right class. Also, the client-side JavaScript for the reports screen needs to reference the proper URL/id (and if you need a second one you will have to create it manually).
• If you want to add parameters to the report and allow the end-user to set them at runtime in the Parameters tab of the ActiveReports viewer:
  • Add a LightSwitch parameterized query to your data source.
  • Edit the CodeBasedReportContext.cs file and add a static member function with the appropriate parameters and pass them to the LightSwitch query (the second TODO comment).
  • Using ActiveReports Report Explorer, add matching parameters to the report itself (integers must be of type String).
  • Edit the CodeBasedReportProxy.cs file and modify the SetParameters method to pass values to the static member function created in step 2 (see the TODO comment).

 

Special Thanks

A special thanks to John Juback, and Dan Beall at ComponentOne for their assistance in creating this article.

Links

• Creating Multiple Reports in a Single Project Using ActiveReports
• ActiveReports : Cloud Licensing
• ActiveReports 8 User Guide NetHelp / PDF
• ActiveReports 8 Server Administrator Guide NetHelp / PDF
• ActiveReports 8 Server End User Guide NetHelp / PDF
• ActiveReports 8 Server SDK NetHelp /PDF

Download Code

The LightSwitch project is available at http://lightswitchhelpwebsite.com/Downloads.aspx

(you must have Visual Studio 2013 (or higher) installed to run the code)

(you must have the Active Reports 8 (or higher) Trial or full product installed to run the code)