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:
Those that you want to group/filter by (e.g. Country, postcode, customerid) -- we can call these
facetsAll the
metrics, the finest granular details. (These are packed into a single column called 'Details' -- see the+ '|' +concatenations below)
See how the two types of columns are treated below.
Note also, that this relies on a nums table. A nums table is a handy thing to have! (I believe the writings of Itzik Ben-Gan are where I learned of a nums (or Numbers) table.)
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, -- Facets
cj.Region, -- Facets
cj.PostCode, -- Facets
cj.Name as Customer, -- Facets
cj.Id as CustomerId, -- Facets
d.[Date], -- Facets
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 shown: you'll always need a custom class for unpacking the Detail column. And you need to load a datatable with that info.
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") ›
@Html.ActionLink(Model.Project.Name, "Index", "Project", new { project = Model.Project.Slug }, null) ›
@Html.ActionLink("Pivotted", "Index", "Pivotted", new { project = Model.Project.Slug, filter = (string)null }, null)
<text>›</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 ×</a>
@if (Model.xxFilter != null)
{
<text> → </text>
<a class="" href="/Pivotted/CustomerPerformance?yyFilter=@Model.yyFilter" title="Remove xx filter">@Model.xxFilter ×</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>
}