Report Generator Demo
This is where you can play around with the hierarchical report generator by supplying a where clause in plain text and (de)selecting columns of the various lists (4 tables and a subselect) involved.
For a tutorial on how to construct expressions and what the extra options in this demo are click here.
For an overview of the content in all the base tables used in this hierarchical report click here.
The small window you are using greatly diminishes your demo experience. Please consider viewing using a larger window.
Command Text:
SELECT
[Customer].[Id] [Customer_Id],
[Customer].[FirstName] [Customer_FirstName],
[Customer].[LastName] [Customer_LastName],
[Customer].[DateOfBirth] [Customer_DateOfBirth],
[Customer].[IsPreferred] [Customer_IsPreferred],
[Customer].[HomeAddressId] [Customer_HomeAddressId],
[Customer].[BillingAddressId] [Customer_BillingAddressId],
[CustomerHomeAddress].[Id] [CustomerHomeAddress_Id],
[CustomerHomeAddress].[City] [CustomerHomeAddress_City],
[CustomerHomeAddress].[ZipCode] [CustomerHomeAddress_ZipCode],
[CustomerHomeAddress].[Street] [CustomerHomeAddress_Street],
[CustomerHomeAddress].[HouseNumber] [CustomerHomeAddress_HouseNumber],
[CustomerHomeAddress].[HouseNumberAddition] [CustomerHomeAddress_HouseNumberAddition],
[CustomerBillingAddress].[Id] [CustomerBillingAddress_Id],
[CustomerBillingAddress].[City] [CustomerBillingAddress_City],
[CustomerBillingAddress].[ZipCode] [CustomerBillingAddress_ZipCode],
[CustomerBillingAddress].[Street] [CustomerBillingAddress_Street],
[CustomerBillingAddress].[HouseNumber] [CustomerBillingAddress_HouseNumber],
[CustomerBillingAddress].[HouseNumberAddition] [CustomerBillingAddress_HouseNumberAddition],
[Purchase].[Id] [Purchase_Id],
[Purchase].[CustomerId] [Purchase_CustomerId],
[Purchase].[ProductId] [Purchase_ProductId],
[Purchase].[Quantity] [Purchase_Quantity],
[Purchase].[Date] [Purchase_Date],
[Purchase].[DeliveryAddressId] [Purchase_DeliveryAddressId],
[PurchasedProduct]._3e3e38037d5a4725a591b35b4176ebbe [PurchasedProduct_Id],
[PurchasedProduct]._eaa34c7d88394ec18c25c6951257e777 [PurchasedProduct_Name],
[PurchasedProduct]._1193c3f81e9a4384997711b04735d841 _306f1c7170425448d4bb917ae4047dec,
[PurchasedProduct]._68fe5bf6a35e442cac2e66ccc08b2af6 _fac46b3db863bb9fe0b8e30faf239186,
[PurchasedProduct]._015a1e9313c641c8a56b960ab682f0e8 [PurchasedProduct_Price],
[PurchaseDeliveryAddress].[Id] [PurchaseDeliveryAddress_Id],
[PurchaseDeliveryAddress].[City] [PurchaseDeliveryAddress_City],
[PurchaseDeliveryAddress].[ZipCode] [PurchaseDeliveryAddress_ZipCode],
[PurchaseDeliveryAddress].[Street] [PurchaseDeliveryAddress_Street],
[PurchaseDeliveryAddress].[HouseNumber] [PurchaseDeliveryAddress_HouseNumber],
[PurchaseDeliveryAddress].[HouseNumberAddition] [PurchaseDeliveryAddress_HouseNumberAddition]
FROM [Customer] [Customer]
LEFT JOIN [Address] [CustomerHomeAddress] ON ([Customer].[HomeAddressId] = [CustomerHomeAddress].[Id])
LEFT JOIN [Address] [CustomerBillingAddress] ON ([Customer].[BillingAddressId] = [CustomerBillingAddress].[Id])
LEFT JOIN [Purchase] [Purchase] ON ([Customer].[Id] = [Purchase].[CustomerId])
LEFT JOIN
(
SELECT
_900ce3356aa346688e1ccf5b85f501b1.[Id] _3e3e38037d5a4725a591b35b4176ebbe,
_900ce3356aa346688e1ccf5b85f501b1.[Name] _eaa34c7d88394ec18c25c6951257e777,
_6d9ad9bceed6451ea9bb786767722511.[From] _1193c3f81e9a4384997711b04735d841,
ISNULL(_6d9ad9bceed6451ea9bb786767722511.[UpTo], '3000-01-01 00:00:00.000') _68fe5bf6a35e442cac2e66ccc08b2af6,
_6d9ad9bceed6451ea9bb786767722511.[Price] _015a1e9313c641c8a56b960ab682f0e8
FROM [Product] _900ce3356aa346688e1ccf5b85f501b1
INNER JOIN [ProductPriceHistory] _6d9ad9bceed6451ea9bb786767722511 ON (_900ce3356aa346688e1ccf5b85f501b1.[Id] = _6d9ad9bceed6451ea9bb786767722511.[ProductId])
) [PurchasedProduct] ON
(
([Purchase].[ProductId] = [PurchasedProduct]._3e3e38037d5a4725a591b35b4176ebbe)
AND
([PurchasedProduct]._1193c3f81e9a4384997711b04735d841 <= [Purchase].[Date])
AND
([PurchasedProduct]._68fe5bf6a35e442cac2e66ccc08b2af6 > [Purchase].[Date])
)
LEFT JOIN [Address] [PurchaseDeliveryAddress] ON ([Purchase].[DeliveryAddressId] = [PurchaseDeliveryAddress].[Id])