Example #1
0
        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);
        }