Pivot Tables: OLAP Support

Aspose.Cells Product Family
1

Hi there,


We’ve recently bought a license to Aspose.Cells, an amazing product. However, I’ve not been able to find a way to integrate data from OLAP Cubes into the Pivot Table implementation. Is this supported? If so, can you please point me to a usage document?

Thanks!
2

Hi,

Thanks for your posting and using Aspose.Cells.

Currently, you can create Pivot Tables using Aspose.Cells API. Please see this documentation article for your reference.

(Insert Pivot Table|Documentation)

However, your requested feature may not be supported. Could you please provide us Microsoft Excel steps performed to integrate data from OLAP Cubes into the Pivot Table? It will give us clear picture of your requirements and we will investigate if this feature can be performed using Aspose.Cells API or if there is any workaround.

Also, we will investigate if such a feature can be implemented in our future versions.

Thanks for your cooperation

3

Hi Shakeel,


Thanks for a prompt response. The following link explains in detail how to use a pivot table to query data from an OLAP database.
https://support.office.com/en-in/article/Overview-of-Online-Analytical-Processing-OLAP-15d2cdde-f70b-4277-b009-ed732b75fdd6

This is a very critical feature for us, as it enables fetching data as-needed from the database. It would be great if you can provide a workaround, or add this feature to your product on priority.

Thanks.

4

And this link contains the code to implement the same using Microsoft.Office.Interop.Excel:
http://blogs.msdn.com/b/andreww/archive/2008/07/25/creating-a-pivottable-programmatically.aspx

Hope this helps.

(Copying the code from the link here)

Excel.PivotCache pivotCache = this.Application.ActiveWorkbook.PivotCaches().Add(
Excel.XlPivotTableSourceType.xlExternal, missing);

pivotCache.Connection = connection;

pivotCache.MaintainConnection = true;

pivotCache.CommandText = **“CubeName”**;

pivotCache.CommandType = **Excel.XlCmdType.xlCmdCube**;

I can then add a new PivotTable to the worksheet, based on the PivotCache I’ve just configured:

Excel.Worksheet sheet = (Excel.Worksheet)this.Application.ActiveSheet;

Excel.PivotTables pivotTables = (Excel.PivotTables)sheet.PivotTables(missing);

Excel.PivotTable pivotTable = pivotTables.Add(pivotCache, this.Application.ActiveCell, “PivotTable1”, missing, missing);

pivotTable.SmallGrid = false;

pivotTable.ShowTableStyleRowStripes = true;

pivotTable.TableStyle2 = “PivotStyleLight1”;

Set up the SalesTerritory field as the page field, and FullName as the row field:

Excel.PivotField pageField = (Excel.PivotField)pivotTable.PivotFields(“SalesTerritory”);

pageField.Orientation = Excel.XlPivotFieldOrientation.xlPageField;

Excel.PivotField rowField = (Excel.PivotField)pivotTable.PivotFields(“FullName”);

rowField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
5

Hi,

Thanks for your posting and using Aspose.Cells.

Aspose.Cells cannot fetch the data from database. However, you can fetch the data from your database in a datatable and then import the datatable into your worksheet.

Please see the following documentation article that explains how to import data into worksheet using Aspose.Cells.

( Import Data into Worksheet|Documentation )

Please go through it and it should fit your needs. Let us know your feedback.

6

I’m afraid this approach will not work for us as we have a lot of data, and need to query it on need basis, as user drills down using the pivot table.


Can you run the code snippet in my previous reply by your development team to see if it can be incorporated in your library?
7

Hi,

Thanks for your posting and considering Aspose.Cells.

We will look into this feature. Could you please provide us some simple Excel file having Pivot Table with OLAP support for our investigation. It will help us analyze this feature and we will see if we could implement it or not.

Thanks for your cooperation.

8

Hey Shakeel,

The following link (together with the sample code in one of my replies above) should give you a fair idea about this feature.


`https://support.office.com/en-sg/article/Demo-Explore-Adventure-Works-in-Excel-by-using-an-OLAP-PivotTable-report-082ebb8b-8fcb-409d-a6c2-22120194c4db`

Thanks.
9

Hi,

Thanks for your posting and using Aspose.Cells.

We have logged this issue in our database to investigate its feasibility. We will evaluate your requirements and see if we could implement it or not. Once, there is some update for your regarding this issue, we will let you know asap.

This issue has been logged as

  • CELLSNET-43537 - Pivot Tables: OLAP Support

10

Hello,

I am trying to refresh PivotTables that are created from Microsoft Analysis Service connection.
Code used is below, Exception occurs:"Data Source is not supported"
Does Aspose.Cells v 17.1 support Excel files with external connections to Analysis Service?
Thanks much
K

public static void RefreshExcelFile(string file)
{
var workbook = new Aspose.Cells.Workbook(file);
var worksheet = workbook.Worksheets[0];
foreach (var pt in worksheet.PivotTables)
{
pt.RefreshData();
// pt.CalculateData();
}
}
11
Hi,

Thanks for your posting and using Aspose.Cells.

Aspose.Cells only allows you to modify the internal properties of connection string. But it cannot fetch data from database. You should fetch data from database yourself using ADO.NET and import it into your worksheet and then refresh the pivot table. With Aspose.Cells, you can also change the data source e.g. A1:B10 to A1:C10 and then refresh it.

So please write your own code to fetch data from database and import the data into worksheet using Aspose.Cells and then change data source of your pivot table as per your imported data range and refresh the pivot table using Aspose.Cells.