Adhoc reporting page in your web application

The problem

You would like to add into your project the same (or similar) page for adhoc reporting as in our EasyReportDemo sample project.

The solution

1) EasyQuery assemblies, controller, and view

First of all, you need the controller and view file which will give your users the access to query builder page.

  • Reference the following EasyQuery assemblies in your project:
    • Korzh.EasyQuery.NET45.dll
    • Korzh.EasyQuery.Db.NET45.dll
    • Korzh.EasyQuery.Mvc.NET45.dll
  • Copy EasyReportDemo\Controllers\EasyReportController.cs file to YourProject\Controllers\
  • Change the namespace in EasyReportController.cs to the one used in your project (e.g. MyCompany.MyApplication)
  • Copy EasyReportDemo\Views\Home\EasyReport.cshtml file to YourProject\Views\Home\ folder
  • Add both files it into your project
  • Add EasyReport action to your HomeController. Make it open EasyReport view page:
public ActionResult EasyReport()
{
    return View("EasyReport");
}
  • Copy EasyReportDemo\App_Data\NWindSQL.xml file to YourProject\App_Data. Create App_Data folder if necessary.
  • Add App_Data\NWindSQL.xml into your project. Set it's “Build Action” property to “Content” and “Copy to Output Directory” to “Always”.

Try to build your project. Our demo uses SQL Server Compact database (it's more convenient DB type for distribution), so all references to SqlCe classes and namespaces might not be compiled in your project. Simply comment them for now - we will modify them later.
Run your project and check if the new page can be opened by /Home/EasyReport URL. The page will look quite ugly (since we haven't added any CSS files and scripts yet), but it must be opened without errors.

2) Style and scripts for the new page

Now we need to brush up the style of our new page. We have created special CSS files with default styles for EasyQuery widgets and EasyReport page. You need to copy those file into your project's folder, include them into the project and then link them to your view page. Additionally, you will need to add some JavaScript files. First of all, we need JQuery and JQuery-UI scripts. Secondly - eq.all.min.js file which contains all EasyQuery widgets.

So:

  • Copy easyquery.css and eqview.css files from EasyReportDemo\Content\ to YourProject\Content\
  • Include these CSS files into your project.
  • Copy eq.all.min.js file from EasyReportDemo\Scripts to YourProject\Scripts and include this file into your project.
  • Add jQuery, jQuery-UI and jQuery scripts into your new EasyReport.cshtml view at the beginning of "Scripts" section. You can skip this step if jQuery scripts are already included in your layout file (..\Views\Shared_Layout.cshtml).
  • Link jQuery UI styles. Again this step is not necessary if those .css files are already linked in your layout file.

All EasyQuery script and CSS files (eq.all.min.js, easyquery.css and others) must be already included in your view page since there were there in original EasyReport.cshtml you copied from our sample project.

Here is an example how your new view file may look like after all these changes:

@{
  ViewBag.Title = "Adhoc reporting";
}
 
<link href="http://code.jquery.com/ui/1.10.3/themes/smoothness/jquery-ui.css" rel="stylesheet" type="text/css" />
<link href="~/Content/easyquery.css" rel="stylesheet" type="text/css" media="screen" />

.    .    .    .    .    .    .    .

@section Scripts {
    <script src="http://code.jquery.com/jquery-1.11.1.js"></script>
    <script src="http://code.jquery.com/ui/1.11.2/jquery-ui.js"></script>

    <!-- EasyQuery script files --> 
    <script src="@Url.Content("~/Scripts/eq.all.min.js")" type="text/javascript"></script>
    <script src="@Url.Content("~/Scripts/eq.view.report.js")" type="text/javascript"></script>
    <script src="@Url.Content("~/Scripts/easychart.js")" type="text/javascript"></script>
}

After that please try to open EasyReport page once again. Now it must look much better but still does not provide any functionality since we haven't added any server-side code yet.

3) Updating EasyQuery controller, connecting to your DB

Currently, we use our demo model for NWind database - just to make sure that everything works as expected and we don't have any problem in our server-side code, view markup or scripts. Now it's time to make the final modifications and connect this new query builder page to your project's data.

But at first, we would like to make an overview of what happens "under the hood" when you open that page. Your new "advanced search" page contains few JQuery UI widgets provided by EasyQuery script (eq.all.min.js): QueryPanel and ColumnsBar. Those widgets make AJAX calls to your server-side code to perform the following tasks:

  • To get data model.
  • To load/save queries.
  • To build an SQL statement.
  • To execute the generated SQL and get the result set.

All these actions are implemented in EasyReportController class. The basic functionality is provided by the methods of the special eqService object of EqServiceProviderDb class. Each action method (like GetModel, GetQuery, ExecuteQuery and others) simply gets the request's parameters, pass them to the corresponding method of EqServiceProviderDb, pack the results into a proper format and returns them to the client. So, in most cases, you will not need to modify any action method in EasyReportController class. Instead, you will need to change some options of eqService to make it load your model, connect to your DB or to generate SQL statement with the correct syntax.

So, here is what we are going to do:

  • First of all, you will need your data model. To create it - run Data Model Editor (installed with EasyQuery library), connect it to your DB and follow the instructions. After performing all these steps save your model into some file. Let's call this new model file MyModel.xml. For more information about creating a new model with Data Model Editor please read this article.

  • Copy MyModel.xml into App_Data folder, add it to the project and set its “Build Action” and “Copy to Output Directory” properties to the same values as for NWindSQL.xml file from the previous step.

  • Now you need to tell eqService how to load your data model instead of NWindSQL. You can use DefaultModelName property for that. Additionally, you will need to modify the connection string and the formats for generated SQL statements. Let's suppose you use MS SQL database. So the initialization part of EasyQueryController for your case will look this way:


private EqServiceProviderDb eqService;

public EasyReportController() {
    eqService = new EqServiceProviderDb();

    eqService.Paging.Enabled = true;
    eqService.DefaultModelName = "MyModel";

    eqService.StoreModelInSession = true;
    eqService.StoreQueryInSession = true;
            
    string connectionString = ConfigurationManager.ConnectionStrings["Default"].ConnectionString;
    eqService.Connection = new SqlConnection(connectionString);

    //EqServiceProvider needs to know where to save/load queries to/from
    eqService.DataPath = System.Web.HttpContext.Current.Server.MapPath("~/App_Data");   

    //You can set DbConnection directly (without using ConfigurationManager)
    //eqService.Connection = new SqlConnection("Your connection string goes here");

    //to support saving/loading models and queries to/from Session 
    eqService.SessionGetter = key => Session[key];
    eqService.SessionSetter = (key, value) => Session[key] = value;

    .   .   .   .   .   .   .   .   .

    //other parts of EasyReportController's constructor will be the same as in original file

That's all! Now your application has its own form for adhoc-reporting!

4) Alternative ways of model loading

If you use Entity Framework for working the DB - it's necessary to create the model “manually” using Data Model Editor. EasyQuery allows you to load your model either from your .edmx file (in case of using DB-first approach) or from your DbContext object. In both these cases, you will need to:

  • reference Korzh.EasyQuery.EF6.NET45 assembly in your project
  • define your own ModelLoader delegate handler in EasyQueryController's constructor and call model loading code there.
    Here are the examples:

Loading model from some .edmx file:

public EasyReportController() {
    eqService = new EqServiceProviderDb();
    .    .    .    .    .    .    .    .    .
    
    eqService.ModelLoader = (model, modelName) => {
        string edmxFilePath = System.IO.Path.Combine(eqService.DataPath, "MyModel.edmx");
        model.LoadFromEdmx(edmxFilePath);   
     }; 

    .    .    .    .    .    .    .    .    .

Loading model from the DbContext:

//loading from .edmx file
public EasyReportController() {
    eqService = new EqServiceProviderDb();
    .    .    .    .    .    .    .    .    .
    
    var context = new NorthwindDbContext();

    eqService.ModelLoader = (model, modelName) => {
        model.LoadFromDBContext(context);   
     }; 

    .    .    .    .    .    .    .    .    .