class PivotService
  {
    private Workspace ws;
    public Workspace Workspace => ws;
    private dynamic opportunity;
    private readonly string QueryName = "BaseQuery";
    public C1PivotEngine engine;
​
  private string GetD365SConnection()
    {
      var config = Properties.Settings.Default;
      string extendProperties = "{\"resource\":\"" + config.Resource + "\"}";
      string conectionStr = $@"Url={config.UrlDynamics};Use Etag=true;OAuth Client Id={config.ClientID};OAuth Client Secret={config.ClientSecret};OAuth Token Endpoint={config.TokenEndpoint};OAuth Extend Properties={extendProperties}";
​
      return conectionStr;
    }
​
​
​
    public void GetData()
    {
      C1.AdoNet.D365S.C1D365SConnection con = new C1.AdoNet.D365S.C1D365SConnection(GetD365SConnection());
      con.Open();
​
      string sql = "select op.opportunityid, op.name, op._ownerid_value,op.createdon, op._parentaccountid_value,op.estimatedclosedate, op._parentcontactid_value, osp._activestageid_value, gc_renewaldate, gc_paymenttermscode, gc_opportunitytype, p.productnumber, p.name as productname, p.gc_salesedition, ps.stagename, opp.priceperunit, opp.quantity, opp.extendedamount, su.fullname as salesrep from opportunities op inner join opportunityproducts opp on opp._opportunityid_value = op.opportunityid inner join opportunitysalesprocesses osp on osp._opportunityid_value = op.opportunityid inner join products p on p.productid = opp._productid_value inner join accounts a on a.accountid = op._parentaccountid_value and a.name like 'account%' inner join processstages ps on ps.processstageid = osp._activestageid_value inner join systemusers su on su.systemuserid = op._ownerid_value LIMIT 105";
​
      var cmd = new C1.AdoNet.D365S.C1D365SCommand(con, sql);
      var connector = new DbConnector(this.ws, con, cmd);
      connector.GetData("Opportunities");
​
    }
​
}

public PivotService()
      {
          Init();
      }
​
private void Init()
      {
          this.ws = new Workspace();
          this.ws.Init("workspace");
​
          //Import data if Opportunities table is null or data is stale in workspace
          if (Properties.Settings.Default.threshhold.Date < DateTime.Now.Date )
          {
              if (this.ws.GetTableData("Opportunities") == null)
              {
                  this.GetData();
                  Properties.Settings.Default["threshhold"] = DateTime.Now.Date;
                  Properties.Settings.Default.Save();
            }            
          }
​
          this.opportunity = this.ws.table("Opportunities");
​
          if (this.opportunity!=null)
          {
              var queryName = this.QueryName;
​
// create base query and execute it
              var settings = new
              {
                  opportunity.stagename,
                  opportunity.salesrep,
                  opportunity.createdon,
                  opportunity.productname,
                  opportunity.extendedamount,
                  Sales = Op.Sum(opportunity.extendedamount),
                  Quantity = Op.Count(opportunity.quantity),
                  QuantityTotal = Op.Sum(opportunity.quantity)
              };
​
              dynamic query = this.ws.query(queryName, settings);
              query.Query.Execute();
​
            //initialize pivotengine and connect workspace & base query
              this.engine = new C1PivotEngine();
              this.engine.Workspace = this.ws;
              this.engine.ConnectDataEngine(queryName);
        }            
      }

private C1PivotEngine CreateFlexPivotEngine(string queryName, string[] colFields, string[] rowFields, string[] valFields)
    {
      var fp = this.engine;
      fp.BeginUpdate();
      this.engine.ColumnFields.Clear();
      this.engine.RowFields.Clear();
      this.engine.ValueFields.Clear();
      fp.ColumnFields.Add(colFields);
      fp.RowFields.Add(rowFields);
      fp.ValueFields.Add(valFields);
      fp.EndUpdate();
      return fp;
    }

public IBindingList OpportunityByStage()
    {
      var fp = this.CreateFlexPivotEngine(this.QueryName, new string[] { "" }, new string[] { "stagename" }, new string[] { "Sales" });
      return fp.PivotDefaultView;
    }

public IBindingList ProductOppportunity()
    {
      var fp = this.CreateFlexPivotEngine(this.QueryName, new string[] { "stagename" }, new string[] { "productname" }, new string[] { "Sales" });
      var fld = fp.ColumnFields[0].Filter;
      fld.Condition1.Operator = ConditionOperator.NotEquals;
      fld.Condition1.Parameter = "close";
      return fp.PivotDefaultView;
    }

public IBindingList OpportunityByRep()
    {
      var fp = this.CreateFlexPivotEngine(this.QueryName, new string[] { "salesrep" }, new string[] { "stagename" }, new string[] { "Sales" });
      return fp.PivotDefaultView;
    }

public IBindingList OpportunityByFiscalQuarter()
    {
      var fp = this.CreateFlexPivotEngine(this.QueryName, new string[] { "stagename" }, new string[] { "createdon", "createdon" }, new string[] { "Sales" });
      var year = fp.RowFields[0];
      year.Range.RangeType = RangeType.FiscalYear;
      year.Range.FiscalYearFirstMonth = 4;
​
      var month = fp.RowFields[1];
      month.Range.RangeType = RangeType.FiscalQuarter;
​
      return fp.PivotDefaultView;
    }