Building ASP.NET Reporting OLAP-Based Application Part-3

In the previous two posts building the Cube and building the Report, we discovered how BIDS helps in developing BI applications in robust, managed and organized way. I believe if you have few BI concepts you can do this walkthrough in 10 minutes.

Let’s come to the last layer which I see it’s the presentation layer. One question may come to your mind why I build ASP.NET application over Reporting Service? Why I don’t give the end user Reporting Service Server URL. Because of security? NO. Reporting Service Server can manage different types of security which doesn’t put headache on your development team but what if your end user need UI layer say in Silverlight? How can you embed in the Reporting Service. We have ReportViewer Windows\Web control which provides a very rich of functionality to View Reporting Service (Local and Server) Reports. In our walkthrough we use Server Report.

Let’s open our previous solution and add a new ASP.NET web application project: InternetSalesWebsite

Default.aspx page open in source view, switch to Design view.

From ToolBox->Reporting-> drag MicrosoftReportView control and drop it on the page drag also a Button then close the ToolBox because it slow down VS IDE. (Thanks to Taha Amin how helped me in this)

So from ToolBox drag two CheckBoxList, one for product dimension and one for promotion dimension

For the first CheckBoxList ProductList we need to load Products dimension data into it. Press on the smart arrow then choose data source; select data source select a new data source a new dialog opens, Choose data source type: Database and give the SqlDataSource ID: ProductsDataSource then OK, a new dialog to choose Data Connection, press on New Connection; Data source press change -> ; Data Provider select .NET framework Data Provider For OLE DB then OK; Add connection dialog opens in OLE DB Provider select Microsoft OLE DB Provider For Analysis Services 9.0; Server or file name enter the Analysis Service Server name in my case here “ramymahrous-lap”; Use Windows NT Integrated Security; Intila Catalog “AdventureWorksInternetSalesCube”; Test Connection if succeeded press OK

Then Next, save this connection as “InternetSalesConnectionString”; next; Configure Data Source dialog opens; select Specify a Custom SQL Statement or stored procedure; next; error message appears ignore it and press OK; write this MDX script which get Products data

WITH

MEMBER [Measures].[ParameterCaption]

AS '[Dim Product].[English Product Name].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER [Measures].[ParameterValue]

AS '[Dim Product].[English Product Name].CURRENTMEMBER.UNIQUENAME'

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue]}

ON COLUMNS , [Dim Product].[English Product Name].ALLMEMBERS

ON ROWS

FROM [Adventure Works DW]

Next; Test Query; if it gets data press finish otherwise tell me what you got.

We return back to “Choose Data Source” dialog; Select a data source: ProductsDataSource; Select a data field to display in the CheckBoxList: [Measures].[ParameterCaption]; Select a data field for the value of the CheckBoxList: [Measures].[ParameterValue]; OK

Repeat that with Promotion CheckBoxList but you won’t configure the connection again just select “IntertnetSalesConnectionString” from connections and the MDX script would be like that

WITH

MEMBER [Measures].[ParameterCaption]

AS '[Dim Promotion].[English Promotion Name].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER [Measures].[ParameterValue]

AS '[Dim Promotion].[English Promotion Name].CURRENTMEMBER.UNIQUENAME'

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue]}

ON COLUMNS , [Dim Promotion].[English Promotion Name].ALLMEMBERS

ON ROWS

FROM [Adventure Works DW]

Double click on the button to enter its click event handler to write some code to call the report and pass the parameters value to it.

protected void Button1_Click(object sender, EventArgs e)

{

ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote; //work on report resides in the server not local

ReportViewer1.ShowParameterPrompts = false; //hide parameters area and we will pass it through our controls

ReportViewer1.ServerReport.ReportServerUrl = new Uri("http://ramymahrous-lap/Reportserver_SQLSERVER2008/"); //Report Server URL not Report Server Manager URL

ReportViewer1.ServerReport.ReportPath = "/InternetSaleCount_ByProduct_ByPromotion";//don't put report extension ".rdl"

//using Reporing Service we know InternetSalesReport needs 2 paramters

//DimProductEnglishProductName

//DimPromotionEnglishPromotionName

List parametersList =

new List();

parametersList.Add(GetParameterValue(PromotionList, "DimPromotionEnglishPromotionName"));//fills DimPromotionEnglishPromotionName with selected values user selected

parametersList.Add(GetParameterValue(ProductsList, "DimProductEnglishProductName"));//fills DimProductEnglishProductName with selected values user selected




ReportViewer1.ServerReport.SetParameters(parametersList); //set report paramters with values

ReportViewer1.ServerReport.Refresh(); //display the report

}

///

/// Gets every Report parameters value(s)

///

///

Which contains parameter values ///

Parameter Name /// Report Parameter

Microsoft.Reporting.WebForms.ReportParameter GetParameterValue(CheckBoxList checkListBox,

string parameterName)

{

List parameterValues = new List();




foreach (ListItem li in checkListBox.Items)

{

if (li.Selected)

{

if (li.Text == "All")

{

parameterValues.Add(li.Value);

break; //no need to go through to know if user selected another value.

}

else

parameterValues.Add(li.Value);

}

}

return new Microsoft.Reporting.WebForms.ReportParameter(parameterName, parameterValues.ToArray(),

true);

}

Press control + F5, to build and view your web application, it should work like that.

We have done.

Some important notice you should be aware of

1- I didn’t develop data tier, I just filled the CheckBoxList controls with data directly

2- To apply our infrastructure architecture First Part you need to have a user have permission to access Reporting Server Server and modify the above code to add some lines

ReportViewer1.ServerReport.ReportServerCredentials =

new ReportServerCredentials(Username, password, domain);

you’ll find ReportServerCredentials class attached in the demo.

This class developed by someone I don’t remember who is or the site.

3- Please if you’ve any question comment or contact me on ramyamahrous@hotmail.com

Full article in .docx format: reporting-service-via-asp

Walkthrough demo: http://cid-3e2288e7a8e55f56.skydrive.live.com/self.aspx/Public%20folder/Reporting%20Service%20via%20ASP.zip