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

Why Support Legacy SharePoint Versions Like SharePoint 2010?

16 Nov 2023

In the fast-paced world of technology, where new versions and updates are continuously rolled out, a recurring question often arises: “Why […]
[read article]

Why Companies are choosing SQList over Power Automate

30 Oct 2023

In the realm of synchronisation services, AxioWorks’ SQList and Microsoft’s Power Automate (earlier known as PowerApps) have emerged as […]
[read article]

AxioWorks Newsletter October 2023: An exhilarating month.

28 Oct 2023

Hello Reader, As we step into the season of autumn, we are thrilled to share with you the latest developments and opportunities unfolding at […]
[read article]

Why Choose SQList for SharePoint Data Extraction Over DIY Solutions

16 Oct 2023

In the ever-evolving world of technology and data management, the question often arises: “Why should I use a product like #SQList when I can […]
[read article]

AxioWorks Newsletter September 2023: An exhilarating month.

30 Sep 2023

Hello Reader, September has been a bustling and exhilarating month for us at AxioWorks, filled with advancements, collaborations, and a whole lot of […]
[read article]

The Collaborative Pathway: Bridging Minds in the AI Ecosystem

19 Sep 2023

In recent days, I found myself immersed in an enlightening conversation with a friend, a fellow entrepreneur steering his ship in the vast sea of […]
[read article]

AxioWorks Newsletter August 2023: A month of milestones and innovation.

28 Aug 2023

Hello Reader, September seems to be a month of unexpected milestones and continued innovations at AxioWorks. As the summer wanes and the autumn […]
[read article]

Use Graph API in Power Automate to read large Excel files from SharePoint

28 Aug 2023

Power Automate, a part of Microsoft’s suite of tools, is increasingly becoming a preferred choice for automating workflows and business […]
[read article]

From Punch Cards to Python: Still Hitting the Keys in my 50s

16 Aug 2023

I spent the entire weekend writing code, not because of deadlines to meet or million-pound dreams, but for the kick that I still get from doing it. […]
[read article]

AxioWorks Newsletter July 2023: Unveiling AxioWorks’s latest successes and exciting innovations

27 Jul 2023

Hello Reader, We hope this monthly update finds you in good spirits. As always, we are eager to share the latest developments and exciting ventures […]
[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