public virtual void Pivot2()
{
PivotTable pivot = new PivotTable();
SetupPivot(pivot);
pivot.BaseSql = "select o.*, c.name, p.name productName from orders o inner join customers c on o.customerId = c.customerId inner join products p on o.productId = p.productId";
pivot.Function = SqlAggregationFunction.Count;
pivot.ValueField = "productName";
pivot.RowField = "productName";
PivotColumn pivotCol = new PivotColumn("name", SqlDataType.String);
pivotCol.Values.Add(PivotColumnValue.CreateScalar("c1", "John"));
pivotCol.Values.Add(PivotColumnValue.CreateScalar("c2", "Lucinda"));
pivotCol.Values.Add(PivotColumnValue.CreateScalar("c3", "Mark"));
pivot.Columns.Add(pivotCol);
pivot.WithTotal = false;
SelectQuery pivotQuery = pivot.BuildPivotSql();
DataTable data = Fill(pivotQuery);
if (data == null)
{
return;
}
WriteTable(data);
}
public virtual void Pivot1()
{
SelectQuery baseQuery = new SelectQuery();
baseQuery.Columns.Add(new SelectColumn("*"));
baseQuery.FromClause.BaseTable = FromTerm.Table("orders");
PivotTable pivot = new PivotTable();
SetupPivot(pivot);
pivot.BaseQuery = baseQuery;
pivot.Function = SqlAggregationFunction.Sum;
pivot.ValueField = "quantaty";
pivot.RowField = "customerId";
PivotColumn pivotCol = new PivotColumn("date", SqlDataType.Date);
TimePeriod currentYear = TimePeriod.FromToday(TimePeriodType.Year);
pivotCol.Values.Add(PivotColumnValue.CreateRange("PreviousYears", new Range(null, currentYear.Add(-1).PeriodStartDate)));
pivotCol.Values.Add(PivotColumnValue.CreateRange("LastYear", new Range(currentYear.Add(-1).PeriodStartDate, currentYear.PeriodStartDate)));
pivotCol.Values.Add(PivotColumnValue.CreateRange("FollowingYears", new Range(currentYear.PeriodStartDate, null)));
pivot.Columns.Add(pivotCol);
pivotCol = new PivotColumn("productId", SqlDataType.Number);
pivotCol.Values.Add(PivotColumnValue.CreateScalar("product1", 1));
pivotCol.Values.Add(PivotColumnValue.CreateRange("product2", new Range(2, 3)));
pivot.Columns.Add(pivotCol);
SelectQuery pivotQuery = pivot.BuildPivotSql();
DataTable data = Fill(pivotQuery);
if (data == null)
{
return;
}
WriteTable(data);
Console.WriteLine("Drill down");
SelectQuery drillDownQuery = pivot.BuildDrillDownSql(SqlConstant.Number(1), "LastYear");
data = Fill(drillDownQuery);
if (data == null)
{
return;
}
WriteTable(data);
Console.WriteLine("Drill down");
drillDownQuery = pivot.BuildDrillDownSql(null, "LastYear");
data = Fill(drillDownQuery);
if (data == null)
{
return;
}
WriteTable(data);
}