/// <summary>
/// OlapReport for Sales Reason sliced by Product and Date
/// </summary>
/// <param name="pivotValueCellData">The Pivot value cell data.</param>
/// <returns></returns>
private OlapReport GetOlapReport(PivotValueCellData pivotValueCellData)
{
OlapReport olapReport = new OlapReport();
// Selecting the Current cube
olapReport.CurrentCubeName = "Adventure Works";
MeasureElements measureElementColumn = new MeasureElements();
// Specifying the name for Measure Element
measureElementColumn.Elements.Add(new MeasureElement {
Name = "Reseller Sales Amount"
});
DimensionElement dimensionElementRow = new DimensionElement();
// Specifying the name for Dimension Element for Row
dimensionElementRow.Name = "Promotion";
dimensionElementRow.AddLevel("Promotion", "Promotion");
// Adding Measure Elements
olapReport.CategoricalElements.Add(measureElementColumn);
// Adding Row Members
olapReport.SeriesElements.Add(dimensionElementRow);
// Specifying Date Slicer Element
DimensionElement DateSlicerElement = new DimensionElement();
DateSlicerElement.Name = "Geography";
DateSlicerElement.AddLevel("Geography", "Country");
DateSlicerElement.Hierarchy.LevelElements["Country"].IncludeAvailableMembers = true;
if (pivotValueCellData.Columns.Count > 0)
{
DateSlicerElement.Hierarchy.LevelElements["Country"].Add(pivotValueCellData.Columns[0]);
}
// Adding Slicer Element
olapReport.SlicerElements.Add(DateSlicerElement);
// Specifying the Product Slicer Element
DimensionElement ProductSlicerElement = new DimensionElement();
ProductSlicerElement.Name = "Product";
ProductSlicerElement.AddLevel("Product Categories", "Category");
ProductSlicerElement.Hierarchy.LevelElements["Category"].IncludeAvailableMembers = true;
if (pivotValueCellData.Rows.Count > 0)
{
ProductSlicerElement.Hierarchy.LevelElements["Category"].Add(pivotValueCellData.Rows[0]);
}
// Adding Slicer Element
olapReport.SlicerElements.Add(ProductSlicerElement);
return(olapReport);
}
private void DoCellClickProcess(object parm)
{
if (parm is PivotCellDescriptor)
{
string country = string.Empty;
string state = string.Empty;
string city = string.Empty;
string year = string.Empty;
string semester = string.Empty;
string quarter = string.Empty;
string month = string.Empty;
string date = string.Empty;
PivotCellDescriptor cellDescriptor = parm as PivotCellDescriptor;
PivotValueCellData cellData = this.GridDataManager.PivotEngine.GetCellData(cellDescriptor);
if (cellDescriptor.CellType == PivotCellDescriptorType.Value)
{
if (cellData.Columns.Count == 0 && cellData.Rows.Count == 0)
{
modelObject.SqlQuery = modelObject.SqlQuery.Append("Select * from [Sheet1$]");
}
else
{
modelObject.SqlQuery = modelObject.SqlQuery.Append("Select ");
if (cellData.Columns.Count == 0)
{
modelObject.SqlQuery = modelObject.SqlQuery.Append(" Country,State,City");
}
for (int i = 0; i < cellData.Columns.Count; i++)
{
switch (i)
{
case 0:
modelObject.SqlQuery = modelObject.SqlQuery.Append(" Country");
break;
case 1:
modelObject.SqlQuery = modelObject.SqlQuery.Append(",State");
break;
case 2:
modelObject.SqlQuery = modelObject.SqlQuery.Append(",City");
break;
}
}
if (cellData.Rows.Count == 0)
{
modelObject.SqlQuery = modelObject.SqlQuery.Append(" ,Year,Semester,Quarter,Month,Date");
}
for (int j = 0; j < cellData.Rows.Count; j++)
{
switch (j)
{
case 0:
modelObject.SqlQuery = modelObject.SqlQuery.Append(" ,Year");
break;
case 1:
modelObject.SqlQuery = modelObject.SqlQuery.Append(" ,Semester");
break;
case 2:
modelObject.SqlQuery = modelObject.SqlQuery.Append(" ,Quarter");
break;
case 3:
modelObject.SqlQuery = modelObject.SqlQuery.Append(" ,Month");
break;
case 4:
modelObject.SqlQuery = modelObject.SqlQuery.Append(" ,Date");
break;
}
}
modelObject.SqlQuery = modelObject.SqlQuery.Append(" ,ProductCategory,ProductSubCategory,ProductName,Quantity from [Sheet1$] where ");
for (int i = 0; i < cellData.Columns.Count; i++)
{
if (i > 0)
{
modelObject.SqlQuery = modelObject.SqlQuery.Append(" And ");
}
switch (i)
{
case 0:
modelObject.SqlQuery = modelObject.SqlQuery.Append(" Country = '" + cellData.Columns[0] + "'");
if (cellData.Columns[0] != string.Empty)
{
country = " at " + cellData.Columns[0];
}
break;
case 1:
modelObject.SqlQuery = modelObject.SqlQuery.Append(" State = '" + cellData.Columns[1] + "'");
if (cellData.Columns[1] != string.Empty)
{
state = " of " + cellData.Columns[1];
}
break;
case 2:
modelObject.SqlQuery = modelObject.SqlQuery.Append(" City = '" + cellData.Columns[2] + "'");
if (cellData.Columns[2] != string.Empty)
{
city = " in " + cellData.Columns[2];
}
break;
}
}
for (int j = 0; j < cellData.Rows.Count; j++)
{
if (j > 0 || cellData.Columns.Count > 0)
{
modelObject.SqlQuery = modelObject.SqlQuery.Append(" And ");
}
switch (j)
{
case 0:
modelObject.SqlQuery = modelObject.SqlQuery.Append(" Year='" + cellData.Rows[0] + "' ");
year = " of the year " + cellData.Rows[0].Replace("FY ", string.Empty);
break;
case 1:
modelObject.SqlQuery = modelObject.SqlQuery.Append(" Semester='" + cellData.Rows[1] + "' ");
semester = " during semester " + cellData.Rows[1].Replace(" FY", string.Empty).Replace("H", string.Empty).Replace(cellData.Rows[0].Replace("FY", string.Empty), string.Empty);
break;
case 2:
modelObject.SqlQuery = modelObject.SqlQuery.Append(" Quarter ='" + cellData.Rows[2] + "' ");
if (cellData.Rows[2] != string.Empty)
{
semester = string.Empty;
}
quarter = " during quarter " + cellData.Rows[2].Replace(" FY", string.Empty).Replace("Q", string.Empty).Replace(cellData.Rows[0].Replace("FY", string.Empty), string.Empty);
break;
case 3:
modelObject.SqlQuery = modelObject.SqlQuery.Append(" Month ='" + cellData.Rows[3] + "' ");
if (cellData.Rows[3] != string.Empty)
{
year = quarter = semester = string.Empty;
}
month = " on " + cellData.Rows[3];
break;
case 4:
modelObject.SqlQuery = modelObject.SqlQuery.Append(" Date ='" + cellData.Rows[4] + "' ");
if (cellData.Rows[4] != string.Empty)
{
month = year = string.Empty;
}
date = " on " + cellData.Rows[4];
break;
}
}
}
}
if (modelObject.SqlQuery != null && modelObject.SqlQuery.Length > 0)
{
this.GridItemsSource = modelObject.GetItemsSource();
}
this.DescriptionText = "Detailed view of sales quantity" + city + state + country + date + month + quarter + semester + year;
}
}
