The client is looking to build dynamic reports out of database tables of data. Their current tool allows these reports to be built by a developer, but this requires a normal development cycle to build, test, and release these reports. They would like to have a system in place that allows them to build these custom reports, joining multiple database objects (tables, views, stored procedures) if necessary, into dynamic data to populate different visualizations.

The closest system to equate it to would be Salesforce Reporting. In there, you can wire up multiple related objects into a grouped, filtered, and sorted data source.  

In previous challenge we built the backend services, models, and database layer of this application.

The goal of this challenge is to build the ASP.NET frontend with jQuery and implement the required REST backend controllers.

Challenge Requirements

Pages in Scope

You will implement “Generate/Edit Report” page flow, and “Report Details” Page.

Architecture

You are provided with an architecture documents, it in extensive implementation notes are provided at method documentation on UML Class Diagrams. Please follow them for implementing the required controllers .

Please read the whole Application Design Specification first. All the details not mentioned in this specification are provided in that document.

Authentication

Authentication and authorization are out of scope for this project, the backend should expect network ID to be passed to the backend.

So in this challenge you need to enable windows authentication in IIS, and use that information to authorize the user.

Front End Classes

This assembly is responsible for implementing all classes on class diagram "Front End Class Diagram" that will be needed to implement the frontend pages in scope of this challenge.

The REST API endpoints of the handlers are configured via web.config like below:

<httpHandlers>

 <add verb="supported http verbs" path="path" type="namespace.classname, assemblyname" />

<httpHandlers>

The path is up to assemblers, it may be like <base-url>/<handler-name>.

Rendering Report Charts

Report charts are rendered in preview report step in Generate report flow and Report details page.

You will use DevExpress ASP.NET Chart Control to render charts, the provided UI prototype includes a placeholder images for the report charts, you are not required to skin the charts in this challenge, you will use the default DevExpress Chart Control theme.

When rendering a report graph, it calls IReportService.SearchReportData to search all data (PageNumber = 0) of the report. The following graph types should be supported:

1. Line Chart

   • - This link contains usage details and an example.

   • - The report.XAxisFieldName indicates the X axis field, other selected fields of the report are shown in Y axis. For every field shown in Y axis, a line is drew for it. For a column to be shown in Y axis, its series values are [row 0 value of the column, row 1 value of the column, … etc.].

2. Bar Chart

   • - This link contains usage details and an example.

   • - The report.XAxisFieldName indicates the X axis field, other selected fields of the report are shown in Y axis. For every field shown in Y axis, a serial of bars (same color) are drew. For a column to be shown in Y axis, its series values are [row 0 value of the column, row 1 value of the column, … etc.].

3. Stacked Bar Chart

   • - This link contains usage details and an example.

   • - The report.XAxisFieldName indicates the X axis field, other selected fields of the report are shown in Y axis. For every field shown in Y axis, a serial of bars (same color) are drew. For a column to be shown in Y axis, its series values are [row 0 value of the column, row 1 value of the column, … etc.].

4. Stacked Area Chart

   • - This link contains usage details and an example.

   • - The report.XAxisFieldName indicates the X axis field, other selected fields of the report are shown in Y axis. For every field shown in Y axis, an area (same color) is rendered. For a column to be shown in Y axis, its series values are [row 0 value of the column, row 1 value of the column, … etc.].

5. Pie Chart

   • - This link contains usage details and an example.

   • - This graph has no X/Y axis. All report's selected fields are shown as separate value. The report data grid should contain only one row of data.

6. Bubble Chart

   • - This link contains usage details.

   • - The report's grid data should contain 4 columns: X axis value, Y axis value, metric name, metric value. Each row represents a bubble in the chart.

Report Detail page

1. Note that chart is rendered in server side, and other content is managed in client side using REST API.

2. When the page is rendered, retrieve report by id, if report.DisplayGraph is true, then the report graph should be shown, as per “Rendering Report Charts” section.

3. It calls SearchReportDataHandler to search report data to show in the table.

4. All the header filters should be converted to valid SQL filter strings, and bound to the ReportDataSearchCriteria.AdditionalFilterStrings. Filter string is like “Table.Column > 1”, “Table.Column LIKE %abc%”, etc. Assemblers may provide filter input according to corresponding column types, if column type is text type (VARCHAR, etc.), allow to enter substring filter; if column type is date type (DATE, DATETIME, etc.), allow to select start and end date; if column type is numeric, allow to input lower and upper bounds.

5. The “Group By” filter in the header should be removed.

6. If “Email” is clicked, it calls SendReportEmailHandler.

7. If “Print” is clicked, it uses the browsers' print functionality to print the current page.

8. For report export button, it is out of scope.

9. “Add to Dashboard” button is out of scope.

10. If “Edit” is clicked, it forwards user to the Edit Report page, this page is same as generate report page but with report data already populated to allow user to edit report instead of create.

11. The upper right “Save” button should be removed.

12. If the “CUSTOMIZE CHART” section data is updated, then it calls UpdateReportHandler to update the report data, and refresh the page so that the chart is updated. The handling of this section is similar to the Generate Custom Reports page, setup step 5, see “Generate Custom Reports page” item (6) for details.

Generate Custom Reports page

1. In the Base Data section:

   • - All database objects are retrieved using GetAllDatabaseObjectsHandler method.

   • - For each database object, its data are shown in a table, the data are retrieved using SearchDatabaseObjectDataHandler.

2. In the Generate Report by Customization section, setup step 1:

   • - The database objects to select should be a multiple choice select, not just single. So that we may join them.

3. In setup step 2:

   • - There should be a field row for each column of selected database objects (though it may not be included).

   • - The Time Period selection should be enabled only if the column type is date or contains “datetime” (case insensitive). By default, it is none. For the drop down values of the Time Period control, the “Total Period” option should be removed.

4. In setup step 3:

   • - The field calculation should allow user enter free form calculation text, this is more flexible, and the calculation string is like “Table.Column / 2 + 3”, “Table1.Column1 + Table2.Column2”, etc.

   • - It can be something like this :

     1. Type represents  a <TableName> or Constant or Operator

     2. Options pick list depends on the selected type, if constant it should change to text field, if operator then it should list all allowed operators, if table name i.e. TableName1 then options will list the columns of that table. When clicking insert it will append the condition to the formula in text area.

     3. This JS query https://hiddentao.github.io/squel/ allows you to validate the sql query, we are paying bonus payment if you implement it.

5. In setup step 4:

   • - The filter calculation should allow user enter free form filter text, this is more flexible, and the filter string is like “Table.Column = 123”, “Table1.Column1 <= Table2.Column2”, etc. It can be same as in step 3.

6. In setup step 5:

   • - All graph types are retrieved using GetAllGraphTypesHandler.

   • - In the Graph Options section, Data tab, this tab is enabled only if select graph type’s SupportXYAxises is true, and it allows user to select a single X axis field, other fields are considered at Y axis.

   • - In the Graph Options section, Notations tab, the Axis Scale input is removed, this info may simply be included in X/Y axis labels.

7. It calls CreateReportHandler to create the report.

8. If preview button is clicked, the preview report page is shown, see “Preview Report page” section for details.

9. In the schedule section, the schedule frequency input is removed, we just uses the hour/minute to specify email time. The scheduling controls are bound to straightforward mapping report properties, after saving report, the schedule will be handled in back end.

10. The same flow is used for edit report flow, the page will pre populate the page with report information to allow users to edit the report.

Preview Report

1. This page accepts report object JSON in request body, it parse report from request body, then searches all report data of the given report, then follow section 1.2.2 to render the chart if report.DisplayGraph is true.

2. It will show report table from the client side if report.DisplayReportData is true, by calling SearchReportDataHandler to get report data in client side.

Unit Tests

Updating unit tests for any backend bugs is out of scope.

Technology overview

- Windows Server

- SQL Server 2012

- Visual Studio 2012

- IIS 7

- C#

- .NET Framework 4.5

- ASP.NET

- Unity 3.5 http://unity.codeplex.com/

- Enterprise Library 6 Logging https://msdn.microsoft.com/en-us/library/ff648951.aspx

- Quartz.NET 2.3.3 http://quartznet.sourceforge.net/

- jQuery 1.11.3 http://jquery.com

- Microsoft OpenXML SDK 2.5 https://msdn.microsoft.com/en-us/library/office/bb448854.aspx

- DevExpress ASP.NET Chart Control http://demos.devexpress.com/XtraChartsDemos/Default.aspx

Supported Browsers

- IE10+
- Latest Google Chrome (Windows & Mac OS)
- Latest Safari (Windows & Mac OS)
- Latest Firefox (Windows & Mac OS)

Existing Documents

- Class Diagrams

- Sequence Diagrams

- Application Design Specification

- ERD

- Source Code that you will use as basis for your work.

GitLab Access

• You can get access to the Gitlab repo group in this tool http://tc-ragnar.herokuapp.com/ragnar/groups/5613f730d601610e000f21bd/290828 .  If you are having trouble with the auto registration tool, please ask in the forums.

• Once added to the team, fork the repository and work off this branch https://gitlab.com/aers-managed-risk/backend/tree/30050556-frontend-assembly

 

Final Submission Guidelines

Submission

• Upload documentation for how to run your submission

• Upload all your solution as git patch files.

• Add elkhawajah as a member of your forked repository

• Winner will be required to submit a merge request on gitlab against the branch specified.