CODEWITHSUNDEEP
asp.netsql-server-2005reporting-servicesssasolap
Luka Milani
Luka Milani

Reputation: 1541

Display an OLAP SQL Server in a web page?

I have this scenario:

WServer 2008, SQL Server 2005, MS Analysis services (SSAS), ISS7 and a OLAP CUBE.

I need to embed the OLAP result in a webpage in an existing site.

I don't need any selection of dimension, no any drill down, no any 3 dimension, just a passive result of a preset MDX query.

Below here an example of MDX query used: (Sell statistic for an agent in a period)

SELECT NON EMPTY { [Measures].[Valore] } ON COLUMNS,
    NON EMPTY { ([Prodotti].[Top Marca].[Top Marca].ALLMEMBERS * [Calendario].[Anno -  Mese].[Mese].ALLMEMBERS * [Agenti].[Cod Agente].[Cod Agente].ALLMEMBERS ) }
    DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [Calendario].[Anno].&[2012] } ) ON COLUMNS 
    FROM ( SELECT ( { [Agenti].[Vw Agenti].&[005] } ) ON COLUMNS FROM [Vendite])) 
    CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Below an easy result that i need to show in the website:

olap result

I tried to use Report Viewer but seem I can't due this error:

Remote report processing requires Microsoft SQL Server 2008 Reporting Services or later

and I can't install SQL Server 2008 so I need an alternative solution.

I tried some open source but they need install JAVA and more, most also not connect to SQL Server or required a different web server.

I tried also use a TSQL query but it return only 1 column for the months when in real i need many columns as months calculated

NOTE: the users are many with many different operating system and browser as iPad, Mac, Windows, Linux, ... so the result must be an HTML output

So my question is:

What is the best way to develop a simple webpage (asp or aspx) (server side or client side) to query the cube to get a simple result (must be HTML) as show above, without any selection by the user ?

Or what didn't I understand at all with this scenario ?

Thanks in advance for who can help me!

Upvotes: 5

Views: 4352

Answers (2)

Diego
Diego

Reputation: 36156

Well, what you are showing there is the result of an SSRS report and you want to access it from your application, right?

There are basically 3 ways you can interact with SSRS from your application: The Report Server Web Service (also known as SOAP, which is how the Report Manager is built), URL Access (plain url of the report on the report server) and the ReportViewer Controls.

When using the ReportViewer Controls, you can use the Local Processing Mode or Remote Processing Mode. The Remote processing mode is when the control retrieves a fully processed report from an SSRS server. So, as the name says, the processing is Remote.

As I understand from your question, you don't have a SSRS instance available to connect to, but you have your report. This is the scenario where you would use the local processing mode. On this type of processing mode, reports are stored locally with a .rdlc file extension and the control opens a report definition, processes it, and then renders the report in the view area.

You also need to add the report file to the VS project and there are some minor restrictions like reports can only be displayed using PDF, excel and image formats.

Upvotes: 4

MartinDotNet
MartinDotNet

Reputation: 2530

From what you are asking, there is no need for the ReportViewer controls etc.

There is ADOMD.NET (instead of standard ADO.NET) which should allow you to get all the data you want just using a static MDX query. The only part of this that you need to consider is how to properly parse the result object to get the HTML Table in the format you want.

This would be a much lighter weight solution that using things like the report viewer.

I don't have any examples on how to parse it as it's something I'm currently working on, but from what I can see so far it's not hard, and it definitely includes all the information you require.

In terms of accessing the cube, you can either connect directly using TCP/IP (seems that is available as you mentioned you doing a TSQL query), or you could setup MSMDPUMP access.

This would not provide any kind of interactivity, but you said that isn't an issue.

I'll try to update this with how to parse the information from an ADOMD.NET result set at some point when I know how.

Upvotes: 0

Related Questions