- SharePoint and Reporting Services - Introduction
- SharePoint and Reporting Services - Installation and Setup.
- SharePoint and Reporting Services - Setup your database.
- SharePoint and Reporting Services - Authoring a simple report.
- SharePoint and Reporting Services - Enabling end users - the report model.
Okay, now that you have setup SSRS + MOSS on your farm, and have setup a database, and have written a simple report, I'd recommend playing with it a bit more and creating charts, drill down reports etc.
Once you feel fairly comfortable with that - read on.
Okay so, based on the simple report you wrote, your users know that you have so many customers. Great! Now they want to know, what orders did the customers place. What do they do?
- Ping a developer (who is already pretty damned busy).
- Explain the developer what they want.
- Tell the developer that if he doesn't produce the report, Don Corleone will kill their children.
- Developer stays late to develop that report.
- Project manager insists on changed requirements, and wants to hit the end users with a change order.
- Developer produces a hotch potched report
- The software development lead bangs on the developers head because the report was produced at the cost of something else.
- The end business user gets an incorrect hotch potched report.
The above is typical, but so far from right. The right would have been, the end user should have been able to author atleast simple reports himself.
And that is where building a cube, and a report model comes in.
Use the following steps:
#1: Create a new report model project in the BI studio. Call it "NorthwindModel".
#2: Create a new data source as before.
#3: Create a new Data source view. A data source view is .. adding the relevant tables, relationships etc. Create a new DataSourceView of name "NorthwindCustomerOrders", and add the Customers and Orders tables.
#4: Under the Report Models folder, add a new Report Model.
- Choose to update model statistics
- Give it a name "NorthwindCustomersOrdersModel", and hit "Run"to create a new report model.
Deploy as before.
Using the Report Model
This is where things get interesting. Assuming the whole thing is nicely deployed to a production environment, the idea is, the end users can now author their own reports - using this model that you deployed.
The question is "how" :-). The answer is, using a ClickOnce application that is a part of SharePoint.
#1. On the context sensitive menu for the NorthWindCustomersOrderModel in the "Reports" document library, choose "Load in Report Builder", by doing so, your friendly neighborhood click once launcher launches -
#2. And before you can say, whippityzippityzoo, the Microsoft Report Builder - or BI Studio Lite ;-) as I like to call it, launches as shown below -
#3. Great, now the end business user can author his own report as shown below -
#4. The end user can now hit the "Save" button to directly save the report in the document library, call it "NW Customers".
Running the Report:
Now you could have run the report in the webpart, but you can view how to do that here. I am simply going to click on the report created by the end user in the document library, to run it inside sharepoint.
As shown below, the report loads up in the browser -
Now obviously the next question the business user will ask is .. "Can I get details of a particular order?".
The answer is "Yes". Since the Data Source View contained the order structure information, the user can now simply click on any of the order column value, to view details of the order as shown below -
Now IMAGINE, if the above was being driven by a typical Datawarehouse cube, the end user would be able to splice and slice the data however he wanted - without bothering the developer, or annoying the project manager.
Win win situation y'all!!