Pivot reports with dynamic pivoted columns (by day)

This is similar to the troller pattern documented elsewhere. It's a pattern for bulk display of pivotted data.

You need a view which returns all of the unpivoted data, but with all the metrics "Packaged" into a single fields.

There's two basic types of columns:

1. Those that you want to group/filter by (e.g. Country, postcode, customerid) and

2. All the metrics, the finest granular details. (These are packed into a single column called 'Details')

CREATE View dbo.[CustomerPerformance_NumberedDays]
as

with Days as (
    select 
    num,
    DateAdd(d, num*-1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) as [Date] from nums 
    where num <= 31 and num > 1
)
Select --top 1000
    cj.Country, 
    cj.Region, 
    cj.PostCode,
    cj.Name as Customer, 
    cj.Id as CustomerId
    d.[Date], 
    d.num,
    p.SalesTotal + '|' + 
    NumberComplaints + '|' +
    NumberCallsOutbound + '|' +
    TargetSalesTotal + '|' +
    s.supportPerson + '|' + 
    s.supportComment
    as Details
from
    CustomerJourney cj
cross join [Days] d
left outer join 
... e.g.SalesHistory p
   on p.id = cp.CustomerID and DATEADD(DAY, DATEDIFF(DAY, 0, p.Time), -1) = d.[Date] 
outer apply
    (Select top 1 
        i.Value as supportComment, 
        i.CreatedUser as supportPerson, 
        i.CallDateTime as CallTime
    from CustomerCalls i
    where callType = 'Support'
        and i.CustomerID = cj.id
    and DATEADD(DAY, DATEDIFF(DAY, 0, i.CallDateTime), 0) = d.[Date]) as s

outer apply (select top 1 -- or aggregate...
... other metrics on that date for that customer, e.g. Complaints, expenses, time spent, refunds... anything...
) as Intervention


GO

That view is, in turn, used by a dynamic pivotting stored procedure

CREATE PROC dbo.CustomerPerformance_NumberedDays_Pivoted
as
BEGIN
    DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)


      IF EXISTS (SELECT * 
                 FROM   dbo.sysobjects o 
                 WHERE  o.xtype IN ( 'U' ) 
                        AND o.id = Object_id(N'dbo.CustomerPerformanceResult')) 
        DROP TABLE dbo.CustomerPerformanceResult 



    select @cols = STUFF((SELECT ',' + QUOTENAME([num]) 
                        from dbo.CustomerPerformance_NumberedDays as w
                        group by [num]
                        order by [num] desc
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')

    set @query = 'SELECT url_Country, url_Region, url_Postcode, url_Customer, Country, Region, Postcode, Customer, ' + @cols + ' 
                into dbo.CustomerPerformanceResult
                from 
                 (
                    select 
                    ''/Pivotted/CustomerPerformance?countryFilter='' + Replace(country,'' '',''%20'') as url_Country, 
                    ''/Pivotted/CustomerPerformance?regionFilter='' + Replace(region,'' '',''%20'') as url_Region, 
                    ''/Pivotted/CustomerPerformance?postcodeFilter='' + Replace(postcode,'' '',''%20'') as url_Postcode, 
                    ''/Pivotted/CustomerPerformance?customerFilter='' + Replace(customerId,'' '',''%20'') as url_Customer, 
                    Conuntry, 
                    Region, 
                    Postcode, 
                    Customer, 
                    [details] 
                    from dbo.CustomerPerformance_NumberedDays
                ) x
                pivot 
                (
                    min([details])
                    for [num] in (' + @cols + ')
                ) p '

    execute(@query)
end    

Column names are looked up from a little dictionary. So the num of "3" will be given a display label based on the date returned from this view:

CREATE View dbo.CustomerPerformance_DayHeadings
as
select 
    num,
    DateAdd(d, num*-1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) as [Date] 
from 
    nums 
where 
    num <= 31 and num > 1 --@NumDays

Assuming the website receives this as a DataTable, it can be displayed via a View like this....

(Not show: you'll always need a custom class for unpacking the Detail column. And you need to load

class PivottedReport {
    public Dictionary<int, ColumnInfo> ColumnInfo { get; set; }
    public DataSet data { get; set; }
    public IEnumerable<Filter> filters { get; set; }
}


@using System.Data
@model PivottedReport

@{
    ViewBag.Title = Model.Name;
    int i = -1;
    int colName = 0;
    var urlColumnNames = new HashSet<string>();
}

<style>
    @@media (min-width: 1200px) {
        .container {
            max-width: calc(100% - 30px);
        }
    }
</style>

<div class="breadcrumb">
    @Html.ActionLink("Projects", "Index", "Home") &rsaquo;
    @Html.ActionLink(Model.Project.Name, "Index", "Project", new { project = Model.Project.Slug }, null)  &rsaquo;
    @Html.ActionLink("Pivotted", "Index", "Pivotted", new { project = Model.Project.Slug, filter = (string)null }, null)
    <text>&rsaquo;</text>
    @Model.Name
</div>

@Html.ActionLink(" ", "Export", "PivottedReport", new { project = Model.Project.Slug, PivottedReport = Model.Slug }, new { @Class = "glyphicon glyphicon-cloud-download btn pull-right", title = "Export CSV" })



<h1>
    @Model.Name
</h1>
@if (Model.yyFilter != null)
{ 
    <div class="display-label">
        Filter:
        <a class="" href="/Pivotted/CustomerPerformance" title="Remove yy filter"> @Model.yyFilter &times;</a>
        @if (Model.xxFilter != null)
        { 
            <text> &rarr; </text>
            <a class="" href="/Pivotted/CustomerPerformance?yyFilter=@Model.yyFilter" title="Remove xx filter">@Model.xxFilter &times;</a>
        }
    </div>
}
else { 
    <p>Select a xx or a yy below.</p>
}

@foreach (DataTable dataTable in Model.data.Tables)
{
    int r = 0;
    <table class="display PivottedReport">
        <thead>
            <tr>
                @foreach (DataColumn column in dataTable.Columns)
                {
                    colName = -1;

                    if (column.ColumnName.StartsWith("url_")) {

                        urlColumnNames.Add(column.ColumnName.Substring(4));
                        continue;
                    }
                    if (int.TryParse(column.ColumnName, out colName)) {
                        <th data-type="@column.DataType.ToString().Replace('.', '_')"
                            data-orderable="false"
                            class="nosort" title="@Model.ColumnInfo[colName].Title">
                        @Model.ColumnInfo[colName].Display
                        </th>
                    } else {
                        <th data-type="@column.DataType.ToString().Replace('.', '_')">
                            @column.ColumnName
                        </th> 
                    }
                }
            </tr>
        </thead>
        <tbody>
            @foreach (DataRow row in dataTable.Rows)
            {
                r++;
                i = -1;

                <tr>
                    @foreach (var cell in row.ItemArray)
                    {
                        i++;

                        if (dataTable.Columns[i].ColumnName.StartsWith("Key_")
                            || dataTable.Columns[i].ColumnName.StartsWith("url_")) { 
                            continue; 
                        }

                        if (cell != null && cell is string && cell.ToString().StartsWith("http"))
                        {
                            <td>
                                <a href="@cell.ToString().Split("|".ToCharArray()).First()" target="_blank" title="click to view in new tab">@cell.ToString().Split("|".ToCharArray()).Last()</a>
                            </td>
                        }
                        else if (int.TryParse(dataTable.Columns[i].ColumnName, out colName))
                        {
                            // Column name is numeric? then it is part of the pivot section, and is given a nicer name and decoded here.
                            var detailCell = new DetailCell(cell.ToString());



                            if (detailCell.SalesTotal == 0.0)
                            {
                                <td class="zero @detailCell.Class" title="@detailCell.Title">
                                </td>
                            }
                            else {
                                <td  class="@detailCell.Class" title="@detailCell.Title" style="position:relative">
                                    <div 
                                        class='dataBar' 
                                        style="height:@(detailCell.Height)px;">
                                    </div>
                                </td>
                            }
                        } else 
                        {
                            <td class="plain">
                                @if (urlColumnNames.Contains(dataTable.Columns[i].ColumnName)) {
                                    <a href="@row[dataTable.Columns["url_"+dataTable.Columns[i].ColumnName]].ToString()">@cell.ToString(cell.GetType())</a>
                                } else {
                                    @cell.ToString(cell.GetType())
                                }
                            </td>
                        }
                    }
                </tr>
            }
        </tbody>
        <tfoot>
        </tfoot>
    </table>
}