4. Connecting to Databases: SQL Server, MySQL, DirectQuery and Live Connection

4.3 Advanced Options and Native SQL Queries

By Ravindra Bagale · Last updated: 25 September 2026

Clicking Advanced options in the SQL Server dialog shows:

Example 1 – order lines for our six Maharashtra cities (JOIN + WHERE):

SELECT  o.OrderID, o.OrderDateTime, o.ProductID, o.Quantity, o.Amount,
        o.DeliveryTimeMins, o.OrderStatus, o.Platform,
        s.StoreID, s.StoreName, s.Area, s.City
FROM    dbo.Orders    AS o
INNER JOIN dbo.DarkStore AS s ON s.StoreID = o.StoreID
WHERE   s.City IN ('Pune', 'Solapur', 'Nashik', 'Sambhaji Nagar', 'Kolhapur', 'Nagpur')
  AND   o.OrderDateTime >= '2025-01-01';

Example 2 – daily summary per city and platform (GROUP BY):

SELECT  CAST(o.OrderDateTime AS date)   AS OrderDate,
        s.City, o.Platform,
        COUNT(DISTINCT o.OrderID)       AS Orders,
        SUM(o.Amount)                   AS Sales,
        AVG(CAST(o.DeliveryTimeMins AS decimal(10,2))) AS AvgDeliveryMins
FROM    dbo.Orders o
JOIN    dbo.DarkStore s ON s.StoreID = o.StoreID
WHERE   o.OrderStatus = 'Delivered'
GROUP BY CAST(o.OrderDateTime AS date), s.City, o.Platform;