SharePoint and SQL Server Reporting Services - Enable End users - The Report Model

Posted on 12/23/2007 @ 1:34 AM in #SharePoint by | Feedback | 10985 views

  1. SharePoint and Reporting Services - Introduction
  2. SharePoint and Reporting Services - Installation and Setup.
  3. SharePoint and Reporting Services - Setup your database.
  4. SharePoint and Reporting Services - Authoring a simple report.
  5. 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?

  1. Ping a developer (who is already pretty damned busy).
  2. Explain the developer what they want.
  3. Tell the developer that if he doesn't produce the report, Don Corleone will kill their children.
  4. Developer stays late to develop that report.
  5. Project manager insists on changed requirements, and wants to hit the end users with a change order.
  6. Developer produces a hotch potched report
  7. The software development lead bangs on the developers head because the report was produced at the cost of something else.
  8. 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!!

Sound off but keep it civil:

Older comments..

On 12/27/2007 1:13:31 PM dongov said ..
Are you planning to update the SharePoint DVD with SQL Server Reporting Services stuff coverage?

On 12/27/2007 2:54:44 PM Sahil Malik said ..
Dongov -

Hmm .. that is an interesting idea.

Let me discuss it with Carl.


On 12/27/2007 7:47:40 PM Sahil Malik said ..
Dongov - Just chatted with Carl. I think we will do a DNRTV on SSRS + SP, so the content will be available, basically for free.

On 1/2/2008 1:45:53 AM DonGov said ..
Sahil, I bought the SharePoint DVD. the DVD ROCKS!!! However I would appreciate if we can get the source code with it.

On 1/2/2008 2:34:41 AM Sahil Malik said ..

Glad you like the DVD. The source code was supposed to be available, so I am surprised to hear that you don't have it. I'll follow it up.

Meanwhile, can you hit the "Contact" button on my site, include your email address, and I'll email you the source code?



On 5/11/2009 10:27:07 AM kots said ..
thanks It works very well .. Good Articale.

On 6/10/2009 2:13:07 PM K-Dog said ..
Awesome article. Thank you for putting it together.

On 12/16/2010 8:54:13 PM Shane Schmidt said ..

Would love to know where we can get the DVD on sharepoint

Shane Schmidt

Senior DWH consultant


On 3/9/2013 9:29:49 AM Frank said ..
In the paragraph "Using the Report Model", somehow I didn't get the reporting context items, like "Load in Report Builder" and others.

I found that I didn't have the "Report Server Intergration Feature" Activated.

In the topsite goto: site action - site settings - site collection administration - site collection features

and activate Report Server Integration Features.

Hereafter the context sensitive menu items will be there.