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.

Where Clause Expression

Columns

    • FirstName
    • LastName
    • DateOfBirth
    • IsPreferred
        • City
        • ZipCode
        • Street
        • HouseNumber
        • HouseNumberAddition
        • City
        • ZipCode
        • Street
        • HouseNumber
        • HouseNumberAddition
        • Quantity
        • Date
            • Name
            • Price
            • City
            • ZipCode
            • Street
            • HouseNumber
            • HouseNumberAddition

The small window you are using greatly diminishes your demo experience. Please consider viewing using a larger window.

Behind The Scenes Explanation

This is where you get to see what's happening behind the scenes when you interact with the site on the left side.

The view consists of three (collapsible) parts...

1. Full Code, Queries, And Results

The server handles the web requests that are the result of you interacting with the site.
In doing so, the demo backend code is executed which in turn uses WYSE.

The relevant/notable methods taking part in that process are shown in the first vertical tab.
Basically you are seeing a (partial) stack trace.
It is tree structure of which the nodes are collapsible/expandable for your browsing convenience.

The leaves of the STACK TRACE TREE are where interaction with the database takes place.
That is generally where you will find the generated SQL statements and the JSON of the converted database results.

2. Queries And Results Only

A filtered list of entries where only the database queries and results are shown.

3. API Results

If applicable: this shows the JSON of the call to the server as if it had been an API call.

  • Trace Entries: 4
      ...ReportGenerator.Controllers.HomeController.Start:
      [HttpGet]
      public ViewResult Start()
      {
          var traceTree = _traceTreesRepository.Record();
          traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());
      
          var reportColumns = _reportGeneratorService.GetReportColumns(traceTree);
          var customersReport = _reportGeneratorService.GenerateFullReport(traceTree);
          
          var reportModel = new ReportModel(
              _traceTreesRepository.RootTraceTree,
              reportColumns,
              customersReport);
      
          return View("ReportGenerator", reportModel);
      }
    • Trace Entries: 1
        ...ReportGenerator.Services.ReportGeneratorService.GetReportColumns:
        public Tree</*WYSE*/IReadOnlyListDictionary> GetReportColumns(TraceTree parentTraceTree)
        {
            var traceTree = parentTraceTree.AddChild();
            traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());
        
            // It is good practice to decouple internal ids from externally used ones.
            // WYSE provides the means to do so.
            // However, for a better, easier to follow demo the ids are kept the same here.
            var customerTable = DemoTemplateAliasing.CreateAliasTemplate<CustomerTable>("Customer");
            
            var genericMappings = _genericMappingsRepository.All
                /*WYSE*/.DownstreamOf<CustomerTable>();
        
            var selectBuilder = new /*WYSE*/SelectBuilder<CustomerTable, Customer>(
                customerTable,
                genericMappings);
        
            var select = selectBuilder.Build();
        
            // Default implementation of the interface that only switches from the '.'
            // in external ids and the '_' in an expression's alias
            var aliasTranslator = new /*WYSE*/AliasTranslator();
            
            return select./*WYSE*/GetListDictionaryTree(aliasTranslator);
        }
    • Trace Entries: 1
        ...ReportGenerator.Services.ReportGeneratorService.GenerateFullReport:
        // Generate a report with all columns and no where clause.
        public IReadOnlyList<Customer> GenerateFullReport(TraceTree parentTraceTree)
        {
            var traceTree = parentTraceTree.AddChild();
            traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());
        
            var customerTable = DemoTemplateAliasing.CreateAliasTemplate<CustomerTable>("Customer");
            
            var genericMappings = _genericMappingsRepository.All
                /*WYSE*/.DownstreamOf<CustomerTable>();
        
            var selectBuilder = new /*WYSE*/SelectBuilder<CustomerTable, Customer>(
                customerTable,
                genericMappings);
            // Alternatively...
            //var selectBuilder = new /*WYSE*/SelectBuilder(customerTable, typeof(Customer), genericMappings);
        
            var select = selectBuilder.Build();
        
            return _sqlExecuter.GetItemTrees<CustomerTable, Customer>(
                select,
                customerTable,
                genericMappings,
                parentTraceTree);
        }
    • Trace Entries: 2
        ...ReportGenerator.DatabaseAccess.SqlExecuter.GetItemTrees:
        public IReadOnlyList<TItem> GetItemTrees<TList, TItem>(
            /*WYSE*/ISelect select,
            TList primaryList,
            IReadOnlyList</*WYSE*/GenericMapping> genericMappings,
            TraceTree parentTraceTree)
            where TList : /*WYSE*/IDbDataReaderConvertableList<TItem>
            where TItem : /*WYSE*/IKeyEquatable
        {
            var traceTree = parentTraceTree.AddChild();
            traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());
        
            using SqlConnection connection = _connectionFactory.Create();
            connection.Open();
        
            using SqlCommand command = connection.CreateCommand()
                // A WYSE Render context determines certain (overridable) render settings.
                // Here the context is SqlServer. More SQL flavors are supported.
                // A WYSE render builder is used for stringifying
                // the strongly typed SQL statements.
                /*WYSE*/.For(select,
                    _renderContextFactory.Create(new DefaultQueryExecutionSettings()),
                    _createRenderBuilder);
        
            try
            {
                using DbDataReader reader = command.ExecuteReader();
        
                var items = reader.GetItemTrees<TList, TItem>(select, primaryList, genericMappings)
                    .ToList();
        
                traceTree.RecordDbCommandExecutionSuccess(
                    command.CommandText,
                    command.Parameters.ToDictionary(),
                    items);
        
                return items;
            }
            catch (Exception exception)
            {
                traceTree.RecordDbCommandExecutionFailure(
                    command.CommandText,
                    command.Parameters.ToDictionary(),
                    exception);
        
                return default;
            }
        }

        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])

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])