DriveWorks Pro 16: How To: Integrate the DriveWorks CPQ Template with SQL Server [send feedback...]

How To: Integrate the DriveWorks CPQ Template with SQL Server

The DriveWorks CPQ template is perfect for working with and automating company systems. Out of the box, the DriveWorks CPQ template uses Group Tables to store data. However, this is not an ideal deployment scenario.

Group Tables are not designed to handle large amounts of data and large numbers of concurrent users. To overcome this problem you should use SQL Server. SQL Server databases can handle large numbers of users and large amounts of data. This makes it a perfect storage place for CPQ and company data.

In this Help File topic we will look at how you create an integration between the DriveWorks CPQ template and an SQL database.

Integrating the DriveWorks CPQ template with SQL Server

DriveWorks has native support for SQL Server connections. This makes it really easy to integrate the DriveWorks CPQ template with an SQL database.

Requirements:

  • SQL Server
  • DriveWorks Pro 16.0 and above
  • DriveWorks CPQ Template

Basic Integration

Basic integrations can be slow and inefficient. SQL data needs to be handled carefully.

It is possible to create a basic integration with SQL by entering an SQL Query into a View's List Query.

This can be done by navigating to Dashboard > Admin Panel > View Manager. Select your View and enter a new List Query for the View.

Example:

QueryDataValues( "Server=MACHINE-NAME\SQLEXPRESS;Database=Customers;"@"SELECT [CustomerName],[Address] FROM [Customers] WHERE ID <> @(DWConstantRefreshID) ORDER BY [CustomerName] ASC", "", "", TRUE)


This query gets data from the Customers table in our SQL database. The WHERE clause is being used to refresh the data. The WHERE says find everything in the table where the ID does not equal the RefreshID. Because the RefreshID is negative, it will return every value because the ID column only contains positive values.

This basic integration will work but it has some limitations. This SQL Query is not efficient and it will slow down with large amounts of data. A rule like this could potentially cause a slow implementation.

To overcome this, you need to modify your ListView Project to make it SQL ready.

Advanced Integration

This advanced integration means that you cannot search data using the default search box on the ListView. You would need to add a search clause into your Query.

An advanced integration will improve this SQL integration. You will be using SQL Queries that will improve performance and only get the required data.

Step 1. Create a ListView Project for SQL

  1. In DriveWorks Data Management, open the CPQ Group.
  2. Go to the Projects tab on the left-hand side and find the ListView Project.
  3. Select the ListView Project and press Copy Project in the Command Bar.
  4. Create a copy of the ListView Project called ListViewSQL.
  5. You now have a Project that you can work on and modify.

Step 2. Create your SQL Query and View:

To improve performance, your query must only get the required data. You need to write a query that limits the amount of data obtained.

Example:

QueryDataValues( "Server=MACHINE-NAME\SQLEXPRESS;Database=Customers;", @"SELECT [CustomerName],[Address] FROM [Customers] WHERE ID <> @(DWConstantRefreshID) ORDER BY [CustomerName] ASC OFFSET @(IfError(( PagingReturn-1)*30,0) ) ROWS FETCH NEXT 30 ROWS ONLY", "", "", TRUE)


This is the same query as before but it is now using an OFFSET and FETCH clause. With this clause it is now possible to fetch a page or window of results. This allows us to be a lot more efficient with the data being returned. This means we can return only the results needed for a page and no more.

This query uses the PagingReturn value to calculate what data to show. This is a control value within the ListViewSQL Project. The value determines what row the query starts at and what data is returned.

Creating your View:

  1. In the CPQ template, go to Dashboard > Admin Panel > View Manager.
  2. Select Add at the bottom of the View names to add a new View.
  3. Enter your new View name.
  4. Select List from View Type.
  5. Paste your SQL Query into the List Query box.
  6. Press Add View to add the View.

Step 3. Modify your ListViewSQL Project to handle SQL data

The query you just created relies on the ListView's page number and needs to refresh every time the page is changed. By default the ListView does not do this.

To modify this behavior, you need to carry out the following:

  1. In DriveWorks Administrator, open the ListViewSQL Project.
  2. Navigate to Specification Macros in the task explorer.
  3. Find the Specification Macro called SetPageNumber.
  4. Add a Run Macro task after the Set Page Number task.
  5. In the Macro Name property, enter RefreshSourceData.
  6. Connect the Run Macro task to the Set Page Number task.
  7. Save the DriveWorks Project.

When you now select the next or previous page the data will be refreshed from SQL.


Step 4. Modify Table Data Variables

In the ListViewSQL Project there are several Variables that are used to produce the final table of data. Since we are using SQL, some of these Variables are no longer required.

To remove these Variables, we are going to bypass them by changing one Variable. Follow the steps below:

  1. In DriveWorks Administrator, open the ListViewSQL Project.
  2. Navigate to Define Variables in the task explorer.
  3. Under List > Table Data you will find a Variable called FinalTableData. Build its rule.
  4. Leaving the function as it is, replace DWVariablePageData with DWVariableCurrentTableData.
  5. Press OK on the Rules Builder.
  6. Save the DriveWorks Project.

Step 5. Create a button to access your View

A button is required to access this new View. Use the View Buttons View in the CPQ template to add a new button to a View. You can find out more in View Buttons.

The new ListViewSQL Project must be added to the Group Table called ProjectsList. It will then appear in the DriveWorks Project to Run drop-down.

Table of Contents