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]._4dbcb4420f4245178761e82592e2384b [PurchasedProduct_Id],
[PurchasedProduct]._917cea8f84d94eafa6b75ae952771363 [PurchasedProduct_Name],
[PurchasedProduct]._2aa152486f9a4407b78eaa558fe3c0de _71e0c489838073012cad4d893f6cc297,
[PurchasedProduct]._888d106ddc6648beab836f649b1dc5e8 _9a539d07d039472fd689757401d1359c,
[PurchasedProduct]._f7e756c52c5648b6b5dd9d9f8237c007 [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
_5f9440467e564d5898b808521ada548d.[Id] _4dbcb4420f4245178761e82592e2384b,
_5f9440467e564d5898b808521ada548d.[Name] _917cea8f84d94eafa6b75ae952771363,
_a4e5cd77b9ef4ba096ccc84c5d73619a.[From] _2aa152486f9a4407b78eaa558fe3c0de,
ISNULL(_a4e5cd77b9ef4ba096ccc84c5d73619a.[UpTo], '3000-01-01 00:00:00.000') _888d106ddc6648beab836f649b1dc5e8,
_a4e5cd77b9ef4ba096ccc84c5d73619a.[Price] _f7e756c52c5648b6b5dd9d9f8237c007
FROM [Product] _5f9440467e564d5898b808521ada548d
INNER JOIN [ProductPriceHistory] _a4e5cd77b9ef4ba096ccc84c5d73619a ON (_5f9440467e564d5898b808521ada548d.[Id] = _a4e5cd77b9ef4ba096ccc84c5d73619a.[ProductId])
) [PurchasedProduct] ON
(
([Purchase].[ProductId] = [PurchasedProduct]._4dbcb4420f4245178761e82592e2384b)
AND
([PurchasedProduct]._2aa152486f9a4407b78eaa558fe3c0de <= [Purchase].[Date])
AND
([PurchasedProduct]._888d106ddc6648beab836f649b1dc5e8 > [Purchase].[Date])
)
LEFT JOIN [Address] [PurchaseDeliveryAddress] ON ([Purchase].[DeliveryAddressId] = [PurchaseDeliveryAddress].[Id])