Any advice to connect an SSAS cube with an excel pivot table using c #?

Question

Any advice to connect an SSAS cube with an excel pivot table using c #?

Navigation

#1 by (0 votes)

1

I am with a development project in which I have to connect by means of C # in Visual Studio a cube to an Excel pivot table, in which I can visualize the information lodged in the cube. I was wondering if anyone has done it or has any idea how to help me. What I need to do is very similar to the connection that can be made with the cube directly inside the excel document, only that it is by code in c # and I generate the excel with the cube data in pivot table. I attach images to make it clearer what I need to do. I can only use free tools or that are from Microsoft.

It is doing what you see in the image, but by code.

c# excel

asked by Fred 07.09.2016 в 01:29

source

1 answer

External exception C0000006 when starting Embarcadero Delphi 2010 Check existence of a directory

score 0 · Answer 1

You can use the office api to create the cube from code

Creating a PivotTable Programmatically

with the library COM Microsoft.Office.Interop.Excel , you can create the PivotCache

In the article connects the pivotable to a Sql Server database, but in your case it would be the same path since it is an external data source

Creating Addin Excel C # to consult Cube

just change how you define the connection string and the command

   string connection = @"OLEDB;Provider=MSOLAP.1; Location=xx;Connect Timeout=60; Initial Catalog=xx;Client Cache Size=25"; ;

   string command = "Nombrecubo";

   PivotCache pivotCache = wkbk.PivotCaches().Add(XlPivotTableSourceType.xlExternal, Type.Missing);

   pivotCache.Connection = connection;
   pivotCache.MaintainConnection = true;
   pivotCache.CommandType = XlCmdType.xlCmdCube
   pivotCache.CommandText = command;

   PivotTables pvtTables = (PivotTables)sheet.PivotTables(Type.Missing);

   PivotTable pvt = pvtTables.Add(pivotCache, sheet.Cells[8,3], "PivotTable1", Type.Missing, Type.Missing);