Our use of cookies

We use cookies to tailor your experience, gather analytics, and provide you with live assitance. By clicking "Accept" or continuing to browse our site you agree to the use of cookies. For more details please read our Cookie Policy.

Building a SAP Crystal Report with SharePoint list data: How To

When it comes to a reporting solution, Crystal Reports is the de facto standard in the reporting world.

In this blog we will cover how we can generate a Crystal report based on data from a SharePoint List. To do this we will first export the data from the SharePoint list to SQL Server using SQList. Once the data is exported in SQL Server, we will use Crystal Reports to create reports using the exported data. The data export process from SharePoint lists to SQL Server can be configured to be either a continuous process or an On-demand one. So, you can choose either from them depending whether you want the generated reports to be in sync as any point of time or only at specific time.

There will be two steps for us to get the end result. Let’s start with the first step that is syncing the data between SharePoint lists to SQL Server.

You will need a copy of our SQList which you can download a trial from here

Step 1: Export the SharePoint List data to SQL Server table

Launch the SQList Manager. If you haven’t set up a replication yet, your default screen would look like as below. In case, if you had already set up replication before and you would like to use that data for generating a report in Crystal Reports, you can skip the Step 1 and move to Step 2. Otherwise, you can follow along the Step 1.

Click on ‘New replication’ button in the manager. We will be setting up a replication from scratch. As soon as you opt for creating a new replication, SQList Manager will take you through a step by step wizard for setting up a replication.

The first step for you will be to specify the details of the SharePoint Connection. The main details of a SharePoint connection include:

  • Site Name: It is the user friendly name of the connection which you will use to refer the connection.
  • Site URL: It is the URL of source SharePoint Site. It is important to note that it is the URL of the main sites not the URL pointing to a specific list or library.
  • Authentication Mode: Type of authentication the SharePoint Site is using.
  • Credentials: User name and Password of the legitimate user.

Once you have provided the connection details, you can test the connection and save it for further use and click on Next button.

Select the type of replication you would like to perform, whether you want to like to replicate specific list or specific set of lists to be replicated in database. Other available option is to export the entire list connection that matches a specific name.

From the perspective of our blog, we will opt for the first option where we will export a specific list which contains the data for report generation.

As soon as you click, you are presented with a view where you can select a single or multiple lists to export. If you need to make some modification relevant to export, you can perform that with the help of options present in the right corner. We have selected the “Employee Salary” list for the purpose the blog.

Now as you have set up the SharePoint Connection, the next step will be to set up the SQL Connection. You need to provide the basic details related to SQL connection like

  • Connection Name: You need to specify the name for the connection.
  • Server Name: You need to specify the SQL server instance name.
  • Database Name: You need to specify the name of the database where the exported data will be saved.
  • Database Engine: You need to specify the database engine version whether it is SQL server 2008 or which other version
  • Authentication Mode and credentials: Lastly you need to specify the authentication mode to be used to connect to SQL server and the appropriate credentials (User Name and Password).

Replication set up is almost complete. You need to specify the behavior of this replication as in whether you want to start this replication as soon as you click on finish or you want to start this later. Also, you need to specify whether you want to enable the replication. For the purpose of the blog, we will check both the properties.

As soon as you click “Finish”, the replication set up is complete and the replication of the SharePoint list gets commenced. You can always check the status or logs of the replication in the service status.

For reference purpose, let’s check out the structure and data of the list that we have exported. Employee Salary list contains compensation data of employees.

Step 2: Create Report using Crystal Reports

Now as we have replicated our data in SQL Server, then we can use the imported data and generate a sample report (Chart) using this data

Let’s launch the Crystal Reports app. For the purpose of blog, I am using version Crystal Reports 2016 which is the latest version when writing this blog but the same can achieved with any previous version as well.

The start page of Crystal Reports provides you with a bunch of options to create a new report.

  • Create a Blank Report
  • Use a Report Wizard to create a report
  • Cross Tab report wizard
  • Mailing label report wizard
  • OLAP Cube Report Wizard

We will go with the second option and use a step by step wizard to create a report.

Now Click on the “Report Wizard”. Once you click on it, the wizard will open and show you the list of existing connections. It also provides you with the option to create a new connection.

As we don’t have any existing connection, the list is empty for us. You can expand the option to create a new connection.

Once you expand the options to create a new connection, you can see that Crystal Reports provides you with ways to connect to almost any type of data source. You can see that the range varies from files (like XML), SAP stores, Access and Excel and others.

We have our data residing in the report database of SQL Server instance so we will select the OLE DB (ADO) option to connect to SQL Server.

OLE DB defines a set of COM interfaces to provide applications with uniform access to data that is stored in diverse information sources. This approach allows a data source to share its data through the interfaces that support the amount of DBMS functionality appropriate to the data source. For our case, SQL Server is the information source.

OLE DB Provider provides you with lot of options to choose from. Since we have to connect to SQL Server, we will select SQL Server Native Client 11.0 and then click “Next”.

Now you need to specify the SQL Server instance details which will be used to connect to SQL Server. You need to provide the server name, the type of authentication and the source database.

We will be using the windows authentication (Integrated Security) to connect to SQL Server. Once you provide the details, click on Next.

The next step involves configuration changes for advanced settings. If you need to override any settings like time out but we will not change any value and click on Finish.

As soon as you click on Finish, the connection set up is complete. You can see that a new connection will appear under OLE DB (ADO) connections, you can drill down it to select the relevant table ( EmployeeList.EmployeeSalary). Select the table and with the help of arrow button, move it to selected tables pane.

Click on Next to continue.

Now you need to select the fields from the table. We will be plotting a chart of employees against their respective salary. Hence, select the employee name and salary fields and move them into selected fields pane.

Click on Next to continue. If we want to group data we can do it at this stage. Since this is salary data we won’t do this.

Click Next to continue. Next we need to specify subset or filtering on the basis of the columns, if any. For this report we will leave this blank.

The next and final step is to select a template. We will select the Block (blue) template for blog and then click on finish.

As soon as you click on finish, the report will be generated.

So, the report creation is complete and you can save the report for further use. We have chosen a very basic use case for the purpose of the blog but it can be extended a lot further depending upon the requirement, whether you want to export huge chunks of data residing in different lists (supported by SQList). Even if you want to generate a complex report with data coming from different tables is easily feasible from Crystal Reports prospect.

Latest articles

Our Favourite Picks from Power BI Global Summit 2023

17 Mar 2023

Power BI Summit is the biggest Power BI conference of the year which ran virtually from 6th March to 10th March. It brought speakers from the […]
[read article]

AxioWorks Newsletter February 2023: Development of SQList new release is in full swing

28 Feb 2023

Hello Reader, Welcome to another exciting issue of our monthly Newsletter! Each month, we try our best to bring you content that is relevant and […]
[read article]

MS List Templates bundled with Power Automate Flow

20 Feb 2023

Microsoft Lists previously known as SharePoint Lists is the go-to option for maintaining and storing data in the world of O365 and its usage has […]
[read article]

AxioWorks Newsletter January 2023: Kicking off 2023 with some great content

30 Jan 2023

Hello Reader, Welcome to another exciting issue of our monthly Newsletter! Each month, we try our best to bring to you content that is relevant and […]
[read article]

A pragmatic approach to obtaining value from your SharePoint data

20 Jan 2023

Organisations have been storing data in SharePoint for more than 20 years.  This data can be as simple as a List used by a small team or a complex […]
[read article]

Power BI Report Server : An On-Premises Report hosting Suite

16 Jan 2023

Power BI has become the most widely used tool for reporting due its user-friendly interface which allows business users to generate sleek and […]
[read article]

AxioWorks Newsletter December 2022: Good bye 2022, welcome 2023!

29 Dec 2022

Hello Reader, Welcome to the December issue of our monthly Newsletter! Each month, we try our best to bring to you content that is relevant and […]
[read article]

PowerApps Debugging and Troubleshooting made easy with Monitor

28 Dec 2022

Troubleshooting and debugging are an integral part of building and supporting a resilient app especially when it is built using a low-code […]
[read article]

AxioWorks Newsletter November 2022: Is it almost Xmas already?

29 Nov 2022

Hello Reader, Welcome to the November issue of our monthly Newsletter! Each month, we try our best to bring to you content that is relevant and […]
[read article]

Future of SharePoint in the world of Power Platform

29 Nov 2022

Microsoft SharePoint has been the driving force for enabling virtual team collaboration and the modern workspace revolution over the last three […]
[read article]

Get SharePoint and SQL news to your inbox

Stay up-to-date with industry news and trends, SQL and SharePoint innovations and all the latest from AxioWorks by subscribing to our monthly newsletter.

Subscribe to the AxioWorks newsletter