NoSQL databases are popular because of their speed and cost advantages over more structured databases, and in this blog, we're walking through how to use NoSQL in a .NET report (FlexReport) using FlexReportDesigner.

Why use NoSQL in a .NET Report?


The majority of data in an enterprise system remains unstructured. NoSQL databases perform better on unstructured information by indexing text natively. Operations like piped aggregation and map-reduce use this feature, which compared to SQL databases are faster and cheaper in terms of CPU cycles.

NoSQL and .NET Reporting


NoSQL database enables your application to retrieve your data model at runtime and does not confine your application in a rigid schema. Because of this independence from schema, you won’t need to create ETL(Extract-Transform-Load) routines when the structure of your report changes. Other advanced features like search alert actions on data ingest, can extract named entities from directories such as those listing people, places, and organizations, which allows text data to be better categorized.

The above scenarios generally apply on Reporting, and having a NoSQL Database would be an added advantage for your reports.

Before we move on to the walkthrough, let's look at DocumentDB and the driver used to connect with it.

DocumentDB


DocumentDB is a NoSQL document database accessible to Azure users via a service designed to natively support JSON and JavaScript inside the database engine. By using a single document instead of querying number of joins, DocumentDB provides simpler, straightforward and more performant result. In addition to this, your document could be partitioned to use horizontal scaling at very low latency.  DocumentDB could also work as ‘Database-as-a-Service’ for other NoSQL databases like mongoDB. This along with native .NET support makes DocumentDB one of the most sought after NoSQL database for .NET developers.

DocumentDB ODBC Driver


DocumentDB ODBC driver enables you to connect to C1ReportDesigner or C1FlexReportDesigner with DocumentDB. You can also connect to SQL Server Integration Services with DocumentDB. This driver offers features to help you normalize data in DocumentDB. With this driver, you can represent the data in your DB as tables and views. It also lets you perform SQL operations against tables and views, including group by queries, and other CRUD operations.

Now let’s move on to the walkthrough and how to connect DocumentDB ODBC Driver with DocumentDB and use the connection to bind with FlexReport.

Step 1: Install DocumentDB ODBC driver


  1. Download ODBC driver from here : (x64x32) and complete installation steps.



  1. Verify that this driver is installed, by checking the list in Drivers tab in "Open ODBC Data Source Administrator."


Step 2: Connect ODBC with your DocumentDB database

  1. Open "ODBC Data Source Administrator"


1

  1. Click Add. "Create New Data Source" dialog appears.



  1. Select Microsoft DocumentDB ODBC Driver and click Finish


2

  1. Fill the required data in the "DocumentDB ODBC Driver DSN Setup"



  • Data Source Name: Your own friendly name for the ODBC DSN. This name is unique to your DocumentDB account, so name it appropriately if you have multiple accounts.

  • Description: A brief description of the data source.

  • Host: URI for your DocumentDB account. You can retrieve this from the DocumentDB Keys blade in the Azure portal, as shown in the following screenshot

  • Access Key: The primary or secondary, read-write or read-only key from the DocumentDB Keys blade in the Azure portal as shown in the following screenshot.


3

  1. Click on Test button to test that your setup works. If successful, you'll see this dialog.


4

  1. Click OK to add this DSN.


Step 3: Connect your DSN with C1ReportDesigner or C1FlexReportDesigner

  1. Open C1ReportDesigner or C1FlexReportDesigner application.

  2. Create new report.

  3. When report wizard appears, select Data Provider as ODBC Data Provider.

  4. Click on ... button besides Connection String text box.5

  5. Select the data source from Machine Data Source. Complete the authentication by adding Host and Access Key.

  6. You report is now connected with DSN.

  7. Select your database from Data source tab.6

  8. Select your fields that will be included in your report7

  9. Design your report using the fields added in previous step.


Step 4: Generate your report with designer:

8

Note:

Although this walkthrough steps through Microsoft DocumentDB, this is applicable to any other Document Store provided you have its ODBC driver available.

Read more about FlexReport