Rdlc reads cube data in Analysis Services

In RDL of Microsoft report services, data can be queried from the cube of the report service by default. However, rdlc requires indirect access to the data in the cube.

Compared with RDL and rdlc, they are completely different routines. The difference between the two is definitely not as simple as a name difference of C. There is a big difference between the design details and the deployment method. In actual projects, the specific solution is determined based on different situations.

We recommend two access methods.

The first method is to link the Analysis Service database to the Data Engine service, and then write the MDX query logic to the stored procedure, at last, the top-level application indirectly accesses the analytic Service database by calling the stored procedure.

 

Another solution is to encapsulate the data source using methods. Since rdlc itself is in a C # Or VB.net project, rdlc supports a method of the project as a data source, so that you can call adomd.net in the method, then, the MDX result is returned to rdlc in the form of list.

For the specific implementation of the first method, refer to the link server operation. The second method is described here.

To install sqlserver, refer to here.

Http://www.cnblogs.com/aspnetx/archive/2012/04/13/2446479.html

Version 2012 is recommended here. If you don't consider the new features in 2012, you don't have to worry too much about whether the previous version can be migrated.

Download the sample data file.

Download from codeplex.

DW file:

Http://msftdbprodsamples.codeplex.com/downloads/get/165405

SSAs script file:

Http://msftdbprodsamples.codeplex.com/downloads/get/258486

Since the cube used in this article is taken from the adventure works sample library in the official Microsoft sqlserver example, while the SSAS database only provides project files, you need to download the DW file for SSAs to process at the same time.

It should be noted that the DW database is downloaded, not the OLTP database. The two databases differ greatly in structure and do not download the database incorrectly.

First, attach the downloaded database file to the Data Engine service.

 

Open the SSAS script file.

 

The vs2010 designer is opened. (Wait for a long time ...)

If you have installed vs2012, right-click it and choose vs2010.

 

Right-click the project and process the SSAS database.

 

There is no major difference between the processing process and the previous version.

 

Next, let's show the following MDX statement query results in rdlc.

Rdlc reads cube data in Analysis Services

 

Create an Asp.net webapplication project.

Add a page in the project to carry the report viewer control, and add a class to which the encapsulation method is used for rdlc reports. In addition, you must add an rdlc report definition file.

 

Specifically, whether it is RDL or rdlc in Asp.net or winformProgramCan be integrated through the report viewer control.

You also need to add a reference, adome.net, a class similar to ado.net. Different sqlserver s have different versions, for sqlserver2012, version 11. If it is installed by default, you can find the DLL to be referenced in the following directory.

C: \ Program Files \ Microsoft. NET \ adomd. Net \ 110

 

First, define a class. This class corresponds to the result returned by MDX. The following classes are defined for the aforementioned MDX statements.

Public class dataitem

{

Public String rowtitle {Get; set ;}

Public double value1 {Get; set ;}

Public double value2 {Get; set ;}

}

Then write the method, which will be used as the data source of the subsequent rdlc report.

Public static list <dataitem> getdata ()

{

List <dataitem> result = new list <dataitem> ();

Adomdconnection conn = new adomdconnection ();

Conn. connectionstring = "Integrated Security = sspi; persist Security info = true; initial catalog = AdventureWorksDW2012Multidimensional-EE; Data Source = .";

Conn. open ();

Stringbuilder sbmdx = new stringbuilder ();

Sbmdx. appendline ("");

Sbmdx. appendline ("member [measures]. [sale amount ratio] as '[measures]. [Internet sales amount]/([measures]. [Internet sales amount], [product]. [product categories]. [All]) ', format_string = '0. 00% '");

Sbmdx. appendline ("select ");

Sbmdx. appendline ("{[measures]. [Internet sales amount], [measures]. [sale amount ratio]}");

Sbmdx. appendline ("On 0 ,");

Sbmdx. appendline ("non empty [product]. [product categories]. [product]. members ");

Sbmdx. appendline ("On 1 ");

Sbmdx. appendline ("from [adventure works]");

Adomdcommand comm = new adomdcommand ();

Comm. Connection = conn;

Comm. commandtext = sbmdx. tostring ();

Adomddatareader DR = comm. executereader ();

While (dr. Read ())

{

Dataitem di = new dataitem ();

Di. rowtitle = Dr [0]. tostring () + "-" + Dr [1]. tostring () + "-" + Dr [2]. tostring ();

Di. value1 = double. parse (Dr [3]. tostring ());

Di. value2 = double. parse (Dr [4]. tostring ());

Result. Add (DI );

}

Return result;

}

}

Because the adomd.net object is used, you need to add the following reference:

Using Microsoft. analysisservices. adomdclient;

Open report. rdlc

 

Select "new"> "dataset" in report data. The preceding dataset attribute form is opened. In data source, you can find the name of the currently created program nameset space and select it, then you can see the method just created in avaliable dataset. Select it to view the structure of the dataset.

 

Next, we will briefly design this report.

 

Return to the default. aspx page and add the reportviewer control. In visual studo 2008 and later versions, you also need to add the script Manager Control.

 

Select a report from the project in the control. You can see that an objectdatasource control is added to the page. Visual Studio automatically fills in the data source control based on the data source in the report.

Finally, run the project directly to view the final report style. Unlike RDL, rdlc does not have the preview function. Therefore, you can only view the report results on the hosted page.

 

The MDX result is displayed.

Key points:

Rdlc can identify the methods in the project as its own data source.

On the page that hosts aspx, the class method is mapped to the data source definition of rdlc through objectdatasource.

Summary:

This method is more difficult than RDL or server connection method, but it also has some advantages from another perspective.

1. Reporting Services is not required.

2. complex logic can be encapsulated in C # code. It is easy and convenient for debugging to develop complex reports.

3. Not only MDX, but also Mining Model and DMX query.