Hi,
Our company(reportdev) wants to launch an OLAP analysis and reporting tool. Everything is going fine but we are now in need of a smart, intelligent and on the fly analytical powerfull pivot-grid that has capabilities of the followings to deliver our customer's needs satisfactorily:-
# Run-time pivoting
# Dril-down
# Export to excel, pdf etc.
# Formatted cell values incl. currency symbols, rounding upto n decimal places etc.
# Charting capabilities and so many more func.
We've tested lots of grids but Infragistics xamPivotGrid seems to be an ideal amongst them. I've downloaded and install the trial version of the grid(NetAdvantage_SilverlightDVWithSamplesAndHelp_20103.exe) and developing app using adomd connection followed by the sample CustomDataProvider .
Everything goes fine but,
Our customers want an option whether they would like to view custom-formatted cell data($#,##0.00;($#,##0.00)) by a check box click.
Also, there is a problem saying 'Message: Element is already the child of another element.' while exporting a large number of data rows(for more than 3 thousands) to excel but it works great for a small amount of rows.
Is it possible to fix them up, how?
Thnx,
- Sb.
The way to do custom formatted value is as follow:
1. Create new cell style as resource
<Style x:Key="ScaledCell" TargetType="ig:PivotCellControl">
<Setter Property="Template">
<Setter.Value>
<ControlTemplate TargetType="ig:PivotCellControl">
<Grid>
<Border x:Name="Root" BorderBrush="{TemplateBinding BorderBrush}" BorderThickness="{TemplateBinding BorderThickness}"
Background="{TemplateBinding Background}" />
<Grid.ColumnDefinitions>
<ColumnDefinition Width="*" />
</Grid.ColumnDefinitions>
<TextBlock Name="cellPresenter" Text="{Binding RelativeSource={RelativeSource TemplatedParent}, Path=Cell.Data, Converter={StaticResource CellValueFormat}}"
Grid.Column="1" VerticalAlignment="Center" HorizontalAlignment="Left" Margin="5" />
</Grid>
</ControlTemplate>
</Setter.Value>
</Setter>
</Style>
2. Add converter class to your project. In convert method apply your custom formatting logic.
using System.Windows.Data;
using Infragistics.Olap.Data;
public class FormatValueConverter : IValueConverter
{
#region IValueConverter Members
public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
ICell cell = value as ICell;
if (cell != null)
return string.Format("$ ## {0}", cell.Value);
}
return "";
public object ConvertBack(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
#endregion
3. Apply the new style to pivot cell
<ig:XamPivotGrid Grid.Column="0" Name="pivotGrid" DataSource="{StaticResource pivotGridDataSource}" CellStyle="{StaticResource ScaledCell}"/>
This should work for your case.
About excel export, which code do you use for exporting. Could you attach little sample for this issue.
Regards
Todor
Hello,
Your code works fine. Thanks a bunch for the code.
The export methods which I've used to export grid's data to excel are as follows:
private void SaveExport() { SaveFileDialog dialog = new SaveFileDialog { Filter = "Excel files|*.xls", DefaultExt = "xls" };
bool? showDialog = dialog.ShowDialog(); if (showDialog == true) { using (Stream exportStream = dialog.OpenFile()) { Workbook wBook = new Workbook(WorkbookFormat.Excel97To2003); Worksheet wSheet = wBook.Worksheets.Add("IS Imported Data"); this.PrepareExportToExcel(wBook, wSheet); wBook.Save(exportStream); exportStream.Close(); } } }
private void PrepareExportToExcel(Workbook wBook, Worksheet wSheet) { int iTopHH = 0; //Top Header Height if (this.chkColHead.IsChecked.Value) { foreach (PivotHeaderCell cell in this.xamPivotGrid.GridLayout.ColumnHeaderCells) { if (iTopHH < cell.Member.LevelDepth + cell.RowSpan) iTopHH = cell.Member.LevelDepth + cell.RowSpan; } }
int iLHW = 0; //Left Header Width if (this.chkRowHead.IsChecked.Value) { foreach (PivotHeaderCell cell in this.xamPivotGrid.GridLayout.RowHeaderCells) { if (iLHW < cell.Member.LevelDepth + cell.ColumnSpan) iLHW = cell.Member.LevelDepth + cell.ColumnSpan; } }
//Freeze header rows and columns if (this.chkColHead.IsChecked.Value || this.chkRowHead.IsChecked.Value) wSheet.DisplayOptions.PanesAreFrozen = true; if (this.chkRowHead.IsChecked.Value) wSheet.DisplayOptions.FrozenPaneSettings.FrozenRows = iTopHH; if (this.chkColHead.IsChecked.Value) wSheet.DisplayOptions.FrozenPaneSettings.FrozenColumns = iLHW;
//Set default width for the columns wSheet.DefaultColumnWidth = 3500;
int iCSpan, iCId, iRSpan, iRId; //ColumnSpan, ColumnID, RowSpan, RowId string sCellVal;
if (this.chkColHead.IsChecked.Value) { // Build Column Header foreach (PivotHeaderCell cell in this.xamPivotGrid.GridLayout.ColumnHeaderCells) { iCSpan = cell.ColumnSpan; iCId = this.xamPivotGrid.GridLayout.Columns.IndexOf(cell.Column as PivotDataColumn) + iLHW;
iRSpan = cell.RowSpan; iRId = cell.Member.LevelDepth;
sCellVal = cell.Member.Caption; if (cell.Member.IsTotal && cell.IsToggleVisible == false) sCellVal += " Total";
if (iCSpan > 1 || iRSpan > 1) wSheet.MergedCellsRegions.Add(iRId, iCId, iRId + iRSpan - 1, iCId + iCSpan - 1);
this.SetCellValue(wSheet.Rows[iRId].Cells[iCId], sCellVal, "TopHeader"); } }
if (this.chkRowHead.IsChecked.Value) { // Build Row Header foreach (PivotHeaderCell cell in this.xamPivotGrid.GridLayout.RowHeaderCells) { iCSpan = cell.ColumnSpan; iCId = cell.Member.LevelDepth;
iRSpan = cell.RowSpan; iRId = this.xamPivotGrid.GridLayout.Rows.IndexOf(cell.Row as PivotDataRow) + iTopHH;
this.SetCellValue(wSheet.Rows[iRId].Cells[iCId], sCellVal, "LeftHeader"); } }
// Build Data int iCR = 0; //Current Row foreach (PivotDataRow row in this.xamPivotGrid.GridLayout.Rows) { int iCC = 0; //Current Cell foreach (PivotCell cell in row.Cells) { string scCV; //Current Cell Value double dcCV; //Current Cell Value if (cell.Data != null) { scCV = (cell.Data as ICell).FormattedValue; scCV = (scCV == "") ? "0" : scCV; dcCV = double.Parse(scCV); } else dcCV = 0;
this.SetCellValue(wSheet.Rows[iCR + iTopHH].Cells[iCC + iLHW], dcCV, "Data"); iCC++; } iCR++; }
//Now, Adding the ColHeader Caption n RowHeader to xls wSheet.MergedCellsRegions.Add(0, 0, 0, 1); wSheet.Rows[0].Cells[0].Value = this.xamPivotGrid.DataSource.Columns[0].Caption; wSheet.Rows[0].Cells[0].CellFormat.Alignment = HorizontalCellAlignment.Right;
wSheet.MergedCellsRegions.Add(1, 0, 1, 1); wSheet.Rows[1].Cells[0].Value = this.xamPivotGrid.DataSource.Rows[0].Caption; wSheet.Rows[1].Cells[0].CellFormat.Alignment = HorizontalCellAlignment.Center; }
//Set the value and format each cell void SetCellValue(WorksheetCell cell, double dCellValue, string CellType) { cell.Value = dCellValue; cell.CellFormat.ShrinkToFit = ExcelDefaultableBoolean.True; cell.CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
if (CellType == "TopHeader") { cell.CellFormat.Alignment = HorizontalCellAlignment.Center; cell.CellFormat.FillPatternForegroundColor = new Color() { A = 255, R = 181, G = 212, B = 240 }; cell.CellFormat.FillPattern = FillPatternStyle.Solid; } else if (CellType == "LeftHeader") { cell.CellFormat.Alignment = HorizontalCellAlignment.Right; cell.CellFormat.FillPatternForegroundColor = new Color() { A = 255, R = 200, G = 212, B = 240 }; cell.CellFormat.FillPattern = FillPatternStyle.Solid; } else if (CellType == "Data") { cell.CellFormat.Alignment = HorizontalCellAlignment.Right; cell.CellFormat.FillPatternForegroundColor = new Color() { A = 255, R = 242, G = 244, B = 249 }; cell.CellFormat.FillPattern = FillPatternStyle.Solid; cell.CellFormat.FormatString = "$#,##0.00;[Red]$#,##0.00"; } else { MessageBox.Show("XPG_Cell_Type_Not_Located"); } }
//Set the value and format each cell void SetCellValue(WorksheetCell cell, string sCellValue, string CellType) { cell.Value = sCellValue; cell.CellFormat.ShrinkToFit = ExcelDefaultableBoolean.True; cell.CellFormat.VerticalAlignment = VerticalCellAlignment.Center;
if (CellType == "TopHeader") { cell.CellFormat.Alignment = HorizontalCellAlignment.Center; cell.CellFormat.FillPatternForegroundColor = new Color() { A = 255, R = 181, G = 212, B = 240 }; cell.CellFormat.FillPattern = FillPatternStyle.Solid; } else if (CellType == "LeftHeader") { cell.CellFormat.Alignment = HorizontalCellAlignment.Right; cell.CellFormat.FillPatternForegroundColor = new Color() { A = 255, R = 200, G = 212, B = 240 }; cell.CellFormat.FillPattern = FillPatternStyle.Solid; } else if (CellType == "Data") { cell.CellFormat.Alignment = HorizontalCellAlignment.Right; cell.CellFormat.FillPatternForegroundColor = new Color() { A = 255, R = 242, G = 244, B = 249 }; cell.CellFormat.FillPattern = FillPatternStyle.Solid; } else { MessageBox.Show("XPG_Cell_Type_Not_Located"); } }
Any suggestion?
int iTopHH = 0;if (columnHeadersCheckBox.IsChecked.Value){ iTopHH = this.pivotGrid.GridLayout.PrivateRowsForColumnPanel.Count;} int iLHW = 0;if (rowHeadersCheckBox.IsChecked.Value){ iLHW = this.pivotGrid.GridLayout.PrivateColumnsForRowPanel.Count;}
iRId = this.pivotGrid.GridLayout.PrivateRowsForColumnPanel.IndexOf(cell.Row as PivotHeaderRow);
iCId = this.pivotGrid.GridLayout.PrivateColumnsForRowPanel.IndexOf(cell.Column as PivotHeaderColumn);
Hi Todor,
Thanks for your quick reply. And yes, this code stuff fixes up the excel export issue clearly.
Now, everything looks perfect except the ConnectionString property of XmlaDataSource in the sample.
Looks like it is hard coded under the <UserControl.Resources> tag in the xmla user control(the MainPage.xaml) file. I've used the following piece of xaml codes in the MainPage.xaml to access my SSAS server where the OLAP database and my cube data located:
<UserControl.Resources> <olap:XmlaDataSource x:Key="adomdDataSource" Database="asCoCIPLive" Cube="cubCoCIPLiv" Rows="[Vw GL Account Dept].[Account Number].MEMBERS, [Vw GL Account Dept].[Dept Name].MEMBERS" Columns="[Vw GL Account Dept].[Period].MEMBERS" Measures="[Amount]">
<olap:XmlaDataSource.ConnectionSettings> <CustomDataProvider:AdomdConnectionSettings ConnectionString="Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=ssasCoCIPLive;Data Source=TECHVALLEY;" /> </olap:XmlaDataSource.ConnectionSettings> </olap:XmlaDataSource></UserControl.Resources>
Is there any way to handle the whole connection process dynamically in the code behind(i.e. under the MainPage.xaml.cs)?
Note that, I don't want to access my SSAS server db through the HTTP acceess(using msmdpump.dll) etc. as it always painfull to me as I need to configure the IIS server and some other stuffs for the case.
Infact, I would like to let my customers provide their database and server name arbitrarily in a separate page(server settings page) and there should be a mechanism in the MainPage's code behind to generate the ConnectionString dynamically using the provided server and the db to establish the connection. Is it possible to complete the job with the version of NetAdvantage_SilverlightDVWithSamplesAndHelp_20103.exe? How?
Thanks again for your co-operations.
Regards,
- Salam.
Hi Salam,
You can look at this post about creating data source with code behind.
Also to control connection string you can use follow code
xmlaDataSource.ConnectionSettings =
new AdomdConnectionSettings{ CatalogName="ww", ConnectionString="connstring"}