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]

"FONT-FAMILY: Georgia">"http://ramymahrous.wordpress.com/2009/04/20/building-aspnet-reporting-olap-based-application-part-3/rs-p3-4/" rel="attachment wp-att-89">"334" height="195" title="rs-p3-4" class="aligncenter size-full wp-image-89" alt="rs-p3-4" src="http://ramymahrous.wordpress.com/files/2009/04/rs-p3-4.jpg" complete="true" />

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