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. 

rs-p3-1

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)

rs-p3-2

rs-p3-3

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]

rs-p3-4rs-p3-5rs-p3-6Next; 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.

rs-p3-7

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

8 Responses to Building ASP.NET Reporting OLAP-Based Application Part-3

  1. […] 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 WindowsWeb 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. More on https://ramymahrous.wordpress.com/2009/04/20/building-aspnet-reporting-olap-based-application-part-3/ Tags: ASP.NET, Microsoft, BI, SSRS, SSAS, Walkthrough […]

  2. Noha Mahmoud says:

    Great & nice work, actually it can help in my day time job🙂.Keep it up (Y)!

  3. Nasim Rizwee says:

    very good and well documented note to start OLAP with ASP.Net. If you have experience to use Oracle OLAP with ASP.Net application, please let me know or you can forward me some reference link.

    Thanks a lot.

  4. Lee2010 says:

    Nice blog! It’s really useful for everyone…Thanks!
    I think some books useful for starters and developers. Here list:

    1. LINQ For Dummies
    2. WPF Programmers Reference Windows Presentation Foundation with C Sharp 2010 and dot NET 4
    3. Teach Yourself WPF in 24 Hours
    4. Beginning Microsoft SQL Server 2008 Programming
    5. Pro Full-Text Search in SQL Server 2008
    6. WPF Recipes in C Sharp 2008 A Problem Solution Approach
    7. WPF Control Development Unleashed
    8. Web Design and Marketing Solutions for Business WebSites
    9. SQL Injection Attacks and Defense
    10. Illustrated WPF

    You can buy them or download free in http://www.latestebook.com. I hope you will get more useful infomation from them.

  5. […] Building ASP.NET Reporting OLAP-Based Application Part-3 April 2009 6 comments […]

  6. I am very grateful to one of my clients for telling me about this site, and the availability of this inspiration and these resources. I have been on the spiritual path for many years, and now help others on the path. I am delighted to have a way to connect with others who are new to the journey, but also delighted to have the wisdom available to me of others further along on the path. Thank you for making this available to us.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: