Category / Section
How to work on Drill-through support in OlapClient Silverlight
1 min read
Drill-Through feature which enables the user to drill through any value and see the raw data which results in the given value. This can be achieved in the grid is placed in OlapClient Control by enabling the Hyperlink support for the cell values in the grid and using the Link_Click event to form the query. Then the CellSet returned from that query will be set as an itemsource for the GridDataControl to show the raw data.
Steps to Create a Drill-Through support on OlapClient Silverlight
- Create a Silverlight Project along with Web application.
- Add the OlapClient control in MainPage.xaml .
- Create a ChildWindow in the Silverlight Project and add the GridDataControl in that window.
- Create a WCF service as “OlapManager.svc” and inherit the IOlapDataProvider interface.
- Explicitly implement the IOlapDataProvider. The connection to the cube database is done with the help of this WCF service.
- Initialize the connection with the OlapManager service. Then create a report and assign the OlapDataManager with current report to OlapClient.
- Then set the “IsHyperlinkCell” property of ValueCellStyle in OlapGrid to true.
- Handle the LinkClick event of OlapGrid and add the following code in that event.
C#
private void OlapGrid_LinkClick(object sender, Syncfusion.Silverlight.Grid.Olap.LinkLabelEventArgs e)
{
var OlapManager = this.olapClient.OlapDataManager as OlapDataManager;
var olapReport = this.olapClient.OlapDataManager.CurrentReport as OlapReport;
bool flag = false;
var cellData = OlapManager.PivotEngine.GetCellData(e.CellDescriptor);
var query = "\0";
var filter = "\0";
for (int i = 0; i < olapReport.CalculatedMembers.Count; i++)
{
if (cellData.Measure.ToString() == olapReport.CalculatedMembers.List[i].ElementValue.ElementName.ToString())
flag = true;
break;
}
if (flag)
{
MessageBox.Show("Calculated measures not having Drillthrough facility");
}
else
{
flag = false;
if (cellData != null && cellData.Measure != null)
{
for (int i = 0; i < olapReport.CategoricalElements.Count; i++)
{
if (olapReport.CategoricalElements.List[i].ElementValue.ToString().Contains("MeasureElements"))
{
flag = true;
break;
}
}
if (flag)
{
if (cellData.ColumnInfo.Count >= 0)
{
query = "drillthrough Select{[Measures].[" + cellData.Measure.ToString() + "]";
if (cellData.ColumnInfo.Count == 1)
{
query += "*{" + cellData.ColumnInfo[0].UniqueName + "}";
}
else
{
int temp = 0, j;
for (int i = 0; i < olapReport.CategoricalElements.Count; i++)
{
if (olapReport.CategoricalElements.List[i].ElementValue is DimensionElement)
{
for (j = temp; j < cellData.ColumnInfo.Count - 1; j++)
{
if (cellData.ColumnInfo[j].UniqueName.Contains((olapReport.CategoricalElements.List[i].ElementValue as Syncfusion.OlapSilverlight.Reports.DimensionElement).Hierarchy.UniqueName) && (cellData.ColumnInfo[j + 1].UniqueName.Contains((olapReport.CategoricalElements.List[i].ElementValue as Syncfusion.OlapSilverlight.Reports.DimensionElement).Hierarchy.UniqueName)))
{
++temp;
}
else
{
++temp;
break;
}
}
query += "*{" + cellData.ColumnInfo[j].UniqueName + "}";
}
}
}
query += "} on 0";
}
if (cellData.RowInfo.Count >= 0)
{
query += ",{";
if (cellData.ColumnInfo.Count == 1)
{
query += "{" + cellData.RowInfo[0].UniqueName + "}";
}
else
{
int temp = 0, j;
for (int i = 0; i < olapReport.SeriesElements.Count; i++)
{
if (olapReport.SeriesElements.List[i].ElementValue is DimensionElement)
{
for (j = temp; j < cellData.RowInfo.Count - 1; j++)
{
if (cellData.RowInfo[j].UniqueName.Contains((olapReport.SeriesElements.List[i].ElementValue as Syncfusion.OlapSilverlight.Reports.DimensionElement).Hierarchy.UniqueName) && (cellData.RowInfo[j + 1].UniqueName.Contains((olapReport.SeriesElements.List[i].ElementValue as Syncfusion.OlapSilverlight.Reports.DimensionElement).Hierarchy.UniqueName)))
{
++temp;
}
else
{
++temp;
break;
}
}
if (i < olapReport.SeriesElements.Count - 1)
{
query += "{" + cellData.RowInfo[j].UniqueName + "} * {";
}
else
{
query += "{" + cellData.RowInfo[j].UniqueName + "}";
}
}
}
}
query += "} on 1";
}
if (olapReport.SlicerElements.Count > 0)
{
AppendSlicerPart(olapReport, ref query, ref filter);
}
else
{
query += "from [" + olapReport.CurrentCubeName + "]";
}
}
else if (cellData != null && cellData.Measure != "" && olapReport.SeriesElements.Count != 0)
{
for (int i = 0; i < olapReport.SeriesElements.Count; i++)
{
if (olapReport.SeriesElements.List[i].ElementValue.ToString().Contains("MeasureElements"))
{
flag = true;
break;
}
}
if (flag)
{
if (cellData.ColumnInfo.Count >= 0)
{
query = "drillthrough Select{{";
if (cellData.ColumnInfo.Count == 1)
{
query += "{" + cellData.ColumnInfo[0].UniqueName + "}";
}
else
{
int temp = 0, j;
for (int i = 0; i < olapReport.CategoricalElements.Count; i++)
{
if (olapReport.CategoricalElements.List[i].ElementValue is DimensionElement)
{
for (j = temp; j < cellData.ColumnInfo.Count - 1; j++)
{
if (cellData.ColumnInfo[j].UniqueName.Contains((olapReport.CategoricalElements.List[i].ElementValue as Syncfusion.OlapSilverlight.Reports.DimensionElement).Hierarchy.UniqueName) && (cellData.ColumnInfo[j + 1].UniqueName.Contains((olapReport.CategoricalElements.List[i].ElementValue as Syncfusion.OlapSilverlight.Reports.DimensionElement).Hierarchy.UniqueName)))
{
++temp;
}
else
{
++temp;
break;
}
}
if (i < olapReport.CategoricalElements.Count - 1)
{
query += cellData.ColumnInfo[j].UniqueName + "} * {";
}
else
{
query += cellData.ColumnInfo[j].UniqueName + "}";
}
}
}
}
query += "} on 0";
}
query += ",{{[Measures].[" + cellData.Measure.ToString() + "]}";
if (cellData.RowInfo.Count >= 0)
{
if (cellData.RowInfo.Count == 1)
{
query += "*{" + cellData.RowInfo[0].UniqueName + "}";
}
else
{
int temp = 0, j;
for (int i = 0; i < olapReport.SeriesElements.Count; i++)
{
if (olapReport.SeriesElements.List[i].ElementValue is DimensionElement)
{
for (j = temp; j < cellData.RowInfo.Count - 1; j++)
{
if (cellData.RowInfo[j].UniqueName.Contains((olapReport.SeriesElements.List[i].ElementValue as Syncfusion.OlapSilverlight.Reports.DimensionElement).Hierarchy.UniqueName) && (cellData.RowInfo[j + 1].UniqueName.Contains((olapReport.SeriesElements.List[i].ElementValue as Syncfusion.OlapSilverlight.Reports.DimensionElement).Hierarchy.UniqueName)))
{
++temp;
}
else
{
++temp;
break;
}
}
query += "*{" + cellData.RowInfo[j].UniqueName + "}";
}
}
}
query += "} on 1";
}
if (olapReport.SlicerElements.Count > 0)
{
AppendSlicerPart(olapReport, ref query, ref filter);
}
else
{
query += "from [" + olapReport.CurrentCubeName + "]";
}
}
}
else if (cellData != null && cellData.RowInfo.Count != 0 && cellData.ColumnInfo.Count != 0)
{
if (cellData.ColumnInfo.Count >= 0)
{
query = "drillthrough Select{{";
if (cellData.ColumnInfo.Count == 1)
{
query += "{" + cellData.ColumnInfo[0].UniqueName + "}}";
}
else
{
int temp = 0, j;
for (int i = 0; i < olapReport.CategoricalElements.Count; i++)
{
if (olapReport.CategoricalElements.List[i].ElementValue is DimensionElement)
{
for (j = temp; j < cellData.ColumnInfo.Count - 1; j++)
{
if (cellData.ColumnInfo[j].UniqueName.Contains((olapReport.CategoricalElements.List[i].ElementValue as Syncfusion.OlapSilverlight.Reports.DimensionElement).Hierarchy.UniqueName) && (cellData.ColumnInfo[j + 1].UniqueName.Contains((olapReport.CategoricalElements.List[i].ElementValue as Syncfusion.OlapSilverlight.Reports.DimensionElement).Hierarchy.UniqueName)))
{
++temp;
}
else
{
++temp;
break;
}
}
if (i < olapReport.CategoricalElements.Count - 1)
{
query += cellData.ColumnInfo[j].UniqueName + "} * {";
}
else
{
query += cellData.ColumnInfo[j].UniqueName + "}";
}
}
}
}
query += "} on 0";
}
if (cellData.RowInfo.Count >= 0)
{
query += ",{{";
if (cellData.ColumnInfo.Count == 1)
{
query += "{" + cellData.RowInfo[0].UniqueName + "}}";
}
else
{
int temp = 0, j;
for (int i = 0; i < olapReport.SeriesElements.Count; i++)
{
if (olapReport.SeriesElements.List[i].ElementValue is DimensionElement)
{
for (j = temp; j < cellData.RowInfo.Count - 1; j++)
{
if (cellData.RowInfo[j].UniqueName.Contains((olapReport.SeriesElements.List[i].ElementValue as Syncfusion.OlapSilverlight.Reports.DimensionElement).Hierarchy.UniqueName) && (cellData.RowInfo[j + 1].UniqueName.Contains((olapReport.SeriesElements.List[i].ElementValue as Syncfusion.OlapSilverlight.Reports.DimensionElement).Hierarchy.UniqueName)))
{
++temp;
}
else
{
++temp;
break;
}
}
if (i < olapReport.SeriesElements.Count - 1)
{
query += cellData.RowInfo[j].UniqueName + "} * {";
}
else
{
query += cellData.RowInfo[j].UniqueName + "}";
}
}
}
}
query += "} on 1";
}
if (olapReport.SlicerElements.Count > 0)
{
AppendSlicerPart(olapReport, ref query, ref filter);
}
else
{
query += "from [" + olapReport.CurrentCubeName + "]";
}
}
}
else if (cellData != null && cellData.ColumnInfo.Count == 0 && cellData.RowInfo.Count == 0 && olapReport.SeriesElements.Count == 0 && olapReport.CategoricalElements.List[0].ElementValue.ElementName == null)
{
query = "drillthrough Select{[Measures].[" + cellData.Measure + "]} on 0 from [Adventure Works]";
}
OlapManager.Execute(query);
OlapManager.CellSetChanged += new CellSetChangedEventHandler(OlapManager_CellSetChanged);
gridChildWindow.Show();
}
}
private void OlapManager_CellSetChanged(object sender, CellSetChangedEventArgs e)
{
gridChildWindow.dataGrid1.ItemsSource = e.ResultSet;
}
private void AppendSlicerPart(OlapReport olapReport, ref string query, ref string filter)
{
query += " From (Select {({";
for (int i = 0; i < olapReport.SlicerElements.Count; i++)
{
if (olapReport.SlicerElements.List[i].ElementValue is MeasureElements)
{
query += (olapReport.SlicerElements.List[i].ElementValue as Syncfusion.OlapSilverlight.Reports.MeasureElements).Elements[0].UniqueName;
}
else if (olapReport.SlicerElements.List[i].ElementValue is DimensionElement)
{
if ((olapReport.SlicerElements.List[i].ElementValue as Syncfusion.OlapSilverlight.Reports.DimensionElement).Hierarchy.LevelElements[0].MemberElements.Count == 0)
{
query += (olapReport.SlicerElements.List[i].ElementValue as Syncfusion.OlapSilverlight.Reports.DimensionElement).Hierarchy.UniqueName;
}
else
{
for (int j = 0; j < (olapReport.SlicerElements.List[i].ElementValue as Syncfusion.OlapSilverlight.Reports.DimensionElement).Hierarchy.LevelElements[0].MemberElements.Count; j++)
{
filter = (olapReport.SlicerElements.List[i].ElementValue as Syncfusion.OlapSilverlight.Reports.DimensionElement).Hierarchy.LevelElements[0].MemberElements[j].UniqueName;
if (j != (olapReport.SlicerElements.List[i].ElementValue as Syncfusion.OlapSilverlight.Reports.DimensionElement).Hierarchy.LevelElements[0].MemberElements.Count - 1)
query += filter + ", ";
else
query += filter + "";
}
}
}
if (i != olapReport.SlicerElements.Count - 1)
query += "}*{";
}
query += "})} on 0 FROM [Adventure Works])";
}
void olapClient_Loaded(object sender, RoutedEventArgs e)
{
this.olapClient.IsCalculatedMembersEnabled = true;
this.olapClient.OlapGrid.ValueCellStyle.IsHyperlinkCell = true;
this.olapClient.OlapGrid.LinkClick += new Syncfusion.Silverlight.Grid.Olap.LinkLabelClickEventHander(OlapGrid_LinkClick);
}
Figure: OlapGrid in OlapClient with enabled hyperlink cells
Figure: GridDataControl with raw data has been shown in child window.
