SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Connecting to a Reporting Services Model

There's always been some confusion about models in Reporting Services, many people ignore them thinking that they are only for use by Report Builder. Models can also be used by BI Design Studio and should be used for all the same reasons that make it useful in RB - it hides the joins from the person creating the report, you can 'pretty up' a model to have field names that are easier to understand on a report, and models even include a lot of cube like attributes that make reporting easier. Unfortuneately BIDS doesn't provide much help in connecting to the model, it just gives you a textbox and waits for you to type the connection string rather than offering the option of a builder. You can find details on how to do it at How to Create a Report Model Datasource.

For those that have never built a model it's essentially a super view. You add all the tables to the model that you need for reporting and then draw the relationships. It will add the foreign key relationships automatically, but you can override those or add in those that might have been omitted from the physical model. Once you save the view building the model is just a 10 step wizard away from being done. Give it a try sometime.


I'm Andy Warren, currently a SQL Server trainer with End to End Training. Over the past few years I've been a developer, DBA, and IT Director. I was one of the original founders of SQLServerCentral.com and helped grow that community from zero to about 300k members before deciding to move on to other ventures.


No comments.

Leave a Comment

Please register or log in to leave a comment.