Creating a Sales Dashboard with SQL Server and Syncfusion Dashboards

Microsoft SQL Server is a relational database management system widely used by top industries. It has a large user base across different industries such as IT, healthcare, retail, insurance, finance, education, management, sales, and marketing for their storage and data processing operations. In this article, we learn how to prepare a dashboard to analyze product sales and prioritize products in production with the Contoso BI demo database using Syncfusion Dashboards.

If you are new to the Syncfusion Dashboardsregister for a free trial on our website. At the end of the registration process, you will reach the following page view.

Defining metrics to analyze product sales

Let’s consider the following metrics to analyze product sales:

  • The total sales and total return amounts.
  • Difference in return amounts among product categories.
  • Return quantities by product category during each sales year.
  • Top 10 products with lowest production cost.
  • Top 10 products with good returns.
  • Distribution of return amount by year and month.

Installing Microsoft SQL Server and configuring Contoso BI demo database

Install the SQL Server in your local machine or in a remote accessible server by referring to the Microsoft documentation.

Download, extract, and restore the Contoso BI demo database by referring to the Install Instructions.

Creating a dashboard and data source

Select the New Dashboard tile in the homepage of the Syncfusion Dashboard Platform. The Dashboard Designer page will open with a new blank dashboard created like in the following.

a blank dashboard design view in syncfusion dashboard platform
A blank dashboard design view

Connecting to Microsoft SQL Server database

  1. Select the data source icon on the right. The data source panel will expand as shown in the following.

    an empty data sources panel in syncfusion dashboard platform
    An empty DATA SOURCES panel
  2. Select CREATE NEW in the previous panel.
  3. Choose the category SQL in the Categories drop-down list. As a result, this list shows the data connectors available under this category like in the following.

    Choosing the Microsoft SQL Server option among the data source list of dashboard designer
    Choosing MS SQL Server
  4. Choose the Microsoft SQL connector. The Microsoft SQL data connection window will display in the DATA SOURCES panel like in the following.

    sql server data source configuration window
    SQL Server Data Source Configuration Window

Creating a data source

  1. Name the data source as ContosoBIDataSource at Name field in the NEW DATA SOURCE window.
  2. Fill the SQL Server name where your database resides in Server name field. For example, remotemachine(or)ipaddress,1433 where, 1433 is the port number. The SQL Server running at remote machine should be reachable for Syncfusion Dashboard’s site.
  3. Fill the login details at User name and Password fields.
  4. Choose the database in Database field for Contoso BI demo data. For example, the database name is ContosoRetailDW.

Editing the data source

  1. Firstly, navigate to the dashboard design page and expand the DATA SOURCES panel on the right.
  2. Click the Edit icon near the ContosoBIDataSource data source. The data source window will open like in the following.

    editing sql server data source in syncfusion dashboard platform
    Editing SQL Server Data Source
  3. Drag and drop the FactSales table and related dimension tables such as DimProduct, DimProductSubcategory, and DimProductCategory by using INNER JOIN with their respective keys.
    join editor for the added tables in data source preparation of dashboard designer.
    Join Editor

    Most importantly, the actual relationship among these tables is like in the following image.

    relationship diagram for the added tables in data source editor of designer
    Relationship Diagram
  4. Finally the data source editing view is like in the following image.

    editing view of a data source in syncfusion dashboard platform
    Editing view of a data source
  5. Save the data source.

Configuring widgets in dashboard

Select the widget to configure and click the Widget Settings icon at the top-right corner of the focused border like in the following.

focused view of widget in dashboard in syncfusion dashboard platform
Focused view of widget in dashboard

As a result, the panel on the right side expands toward the left. Choose the ASSIGN DATA tab and start configuring the mentioned fields in corresponding sections as described in the following table.

Widget Title Widget Type Data Configuration
Sales year Combo Box
  • Column(s): DateKey
    • Format: Year
Return amount comparison among product subcategories Doughnut Chart
  • Value(s): ReturnAmount
    • Aggregation: Sum
    • Format:
      • Type: Currency
      • Representation: Millions
  • Column: ProductSubcategoryName
Total sales amount Card
  • Actual Value: SalesAmount
    • Aggregation: Sum
    • Format:
      • Type: Currency
      • Representation: Millions
Total return amount Card
  • Actual Value: ReturnAmount
    • Aggregation: Sum
    • Format:
      • Type: Currency
      • Representation: Millions
Return quantity comparison by product category level in each sales year Column Chart
  • Value(s): ReturnQuantity
    • Aggregation: Sum
    • Format:
      • Type: Number
      • Representation: Thousands
  • Column(s): ProductCategoryName and ProductSubcategoryName
  • Row: DateKey
    • Format: Year
Top 10 products with lowest production cost Bar Chart
  • Value(s): TotalCost
    • Aggregation: Sum
    • Format:
      • Type: Currency
      • Representation: Thousands
  • Column(s): ProductName
    • Filter: Rank Bottom 10 based on Sum(TotalCost)
Top 10 products based on return amount Bar Chart
  • Value(s): ReturnAmount
    • Aggregation: Sum
    • Format:
      • Type: Currency
      • Representation: Thousands
  • Column(s): ProductName
    • Filter: Rank Top 10 based on Sum(ReturnAmount)
Return amount comparison for each month of sales year Heatmap
  • Value: ReturnAmount
    • Aggregation: Sum
  • X-Axis: DateKey
    • Format: Year
  • Y-Axis: DateKey
    • Format: Month

Finally, the sales dashboard is ready.

sales dashboard comparatively product category and subcategory
Sales Dashboard

 

Sharing dashboard

Now, share this dashboard with your sales team to analyze the sales and make wise decisions about product(s) to improve them.

Conclusion

We hope this article helps you build a sales analysis dashboard with Microsoft SQL Server data using the Syncfusion Dashboards. If you have any questions or require clarification, please let us know in the comments section below. You can also contact us through this contact page. Syncfusion Dashboards now comes with a 30-day free trial with no credit card information required. Give it a try on your own and let us know what you think! We are happy to assist you. Design with a smile!

Tags:

Share this post:

Related Posts

Leave a comment