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]._aa7bd1b71fbb43c5818eaac540f7cbbe [PurchasedProduct_Id],
[PurchasedProduct]._af04b58826ea45c9bf788c8a74087a3c [PurchasedProduct_Name],
[PurchasedProduct]._49300691bb004d12b74c1b0e8accc56e _fea97f7d4a9a67bdf7a156c886244e9e,
[PurchasedProduct]._5f9f90006778461482175d92246aa971 _41feadaefcd7441dd2b4d63c01d195a6,
[PurchasedProduct]._c90cbd21e63f4ccdb26353ae672a2834 [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
_f148638d39194f10a3f8c43e074a9d60.[Id] _aa7bd1b71fbb43c5818eaac540f7cbbe,
_f148638d39194f10a3f8c43e074a9d60.[Name] _af04b58826ea45c9bf788c8a74087a3c,
_ff1b1f39f80048269ad0663a854285e3.[From] _49300691bb004d12b74c1b0e8accc56e,
ISNULL(_ff1b1f39f80048269ad0663a854285e3.[UpTo], '3000-01-01 00:00:00.000') _5f9f90006778461482175d92246aa971,
_ff1b1f39f80048269ad0663a854285e3.[Price] _c90cbd21e63f4ccdb26353ae672a2834
FROM [Product] _f148638d39194f10a3f8c43e074a9d60
INNER JOIN [ProductPriceHistory] _ff1b1f39f80048269ad0663a854285e3 ON (_f148638d39194f10a3f8c43e074a9d60.[Id] = _ff1b1f39f80048269ad0663a854285e3.[ProductId])
) [PurchasedProduct] ON
(
([Purchase].[ProductId] = [PurchasedProduct]._aa7bd1b71fbb43c5818eaac540f7cbbe)
AND
([PurchasedProduct]._49300691bb004d12b74c1b0e8accc56e <= [Purchase].[Date])
AND
([PurchasedProduct]._5f9f90006778461482175d92246aa971 > [Purchase].[Date])
)
LEFT JOIN [Address] [PurchaseDeliveryAddress] ON ([Purchase].[DeliveryAddressId] = [PurchaseDeliveryAddress].[Id])