Using a Report Builder Model as a Report Designer data source in SQL 2005

New in CTP16 / SQL Server 2005 Community Tech Preview (September) — If you’ve spent a lot of time building the perfect model for use by Report Builder, you may want to use it as a data source for any reports you build with VS Report Designer, too.

Well, now you can. If you explore the Data Source designer in the September Community Tech Preview, you should see “Report Server Model” show up in the Type combo-box (where you select Microsoft SQL Server, OLEDB, ODBC, etc.) . After you select this value, use the Connection String text box to specify the location of your model on the report server. The connect string should be made up of two parts: the first part is the location of your server (server=http://yourserver/reportserver), then a semi-colon, and then the path to your model (datasource=/SomeFolder/SomeModelName).

Here’s an example:


Add your credentials information, and you’re done. If you happen to be creating the data source as the result of choosing <New Dataset> from the Dataset combo-box, leave the Query String blank…

Once you click OK, you’ll get a very cool little UI which allows you to drag and drop entities of your model into a design surface. I think this is *way cool*.

Comments (41)

  1. Hello Russell,

    This is awesome news; I can’t wait to try it. This is the one thing I thought was missing in RTM. Does this mean the report designer reports based on a report model now support Report Builder drill through functionality?

    BTW Cool Blog


  2. Hello Russell,

    Just tried this out in RTM, and of course it works! It’s a great feature for building complex reports and reusing report models.


  3. Agreed, it takes so long to tweak your model (getting it juuust right), that it’s pity not to use it….

  4. Amar Deo says:

    Guys, you have solved my problem & saved my life too….

    I was looking for connection string format when Report Model used as data source.

    Thanks Very Much..

  5. Wendy says:

    I created a Report Model. I tried to run it in the Report Builder. I got the following error:


    For more information about this error navigate to the report server on the local server machine, or enable remote errors


    Cannot create a connection to data source ‘dataSource1’.


    An error has occurred during report processing.


    The dataSource name I have is not dataSource1.

    Any idea to get the Report Builder runs????

    Thank so much..

  6. Hi Wendy —

    Your Report Model relies on a Data Source View, and the Data Source View (DSV) in turn relies on a data source. Did you by any chance based your data source view on a data source named "dataSource1" and then later change the name of the dataSource1 to something else? If so, the DSV isn’t "smart" enough to know you changed the name of the data source. If this is your problem (not sure it is), you’d need to open the .dsv in a text editor and fix up the name of your data source, then re-publish your model to the server.

    Hope this helps.

  7. Jeremy says:

    I tried this, but when I go to create a dataset, I assume you select commandtype tabledirect to make the drag and drop interface to come up.  I drag some columns but when I go to run it, it gives me an error "commandtype "tabledirect" is not supported. (Microsoft.ReportingServices.DataExtensions)"  Am I missing something?  Is this then intended way to do this?

  8. Lenny says:

    I have a similar issue. Did anybody know how to solve it?

    Thank you


  9. Are you using Windows auth on the connection both TO the model and IN the model’s data source as well? If this is the case, you might have a problem with Kerberos double-hop. Instead, can you try hard-coding the credentials & password?

  10. johnny says:

    Can someone help me please ?

    After I created a data source in designer based on report model, I can see the source columns and drog&drop them in the query builder. But after I click on the "execute !" button, following error is shown:

    An error ocured while executing the query. Failed to execute query. (Microsoft.ReportingServices.DataExtensions).

    I´ve tried Windows integrated auth, credentials & password too, both with the same results…hm ?

    Any idea would be appreciated…

  11. Generally, this points to an error in the model itself. If you create a brand new model and do the same thing, does it work?

  12. johhny says:

    No, unfortunatelly it doesn´t work for any model I create…the same error message comes.

  13. Peter Rogers says:

    I am seeing the same error:

    For more information about this error navigate to the report server on the local server machine, or enable remote errors


    Cannot create a connection to data source ‘dataSource1’.


    An error has occurred during report processing.

  14. Pamela says:

    I have the same error…. the connections are correct, all is correct, i can use the model by report builder but i can’t see the results on report services.

  15. Can any of you folks open up the smdl (model) document in notepad and see if the following tags are missing?


    <CustomProperty Name="qd:PerspectiveID">

    <Value xsi:type="xsd:string"></Value&gt;



    Also, does this happen against ANY model, or only models which are generated against SSAS cubes?

  16. NamoGuy says:

    I have done all of that. and still get the same message.  If it helps, the only way I was able to make mine go away, was to quite trying to run Report Services from a seperate machine.  I have to RS Server each with there own Databases. When I make a datasouce on Box 1 and point to the Database on Box 2 I get this error every time.  When you open them in Notepad the datasource1 string is no place to be found.   If I deploy the exact same Model to the server where the data is stored and open report builder and drop a field on the report it runs pervectly.  I guess I could create a few on Box 1 that selects the view on Box two and all might be happy.  If I find this site again, I will update you if that worked any better.

  17. Tammy Moisan says:

    This is typically due to a security issue.  Check your data source and make sure the account has the correct permisssions.

  18. Martin says:

    We have a deployed DEV copy of Report Model on SSRS.  Wanted to change to point to QA copy of dbase on same box, so I edited connection string of DataSource (named GYPDM) via SQL Mgt Studio.  Now we get the error as others above.  Changed back to original values, get same error still.  HELP.


    Cannot create a connection to data source ‘dataSource1’.


  19. Did you rebuild the model?

  20. Julie says:

    Can anyone comment on performance using a Report Model as the data source for reports created in Report Designer vs. using SQL Server Stored Procedures?

  21. I did some very quick and dirty testing and didn’t see a big difference — but I was using relatively small datasets (< 1M rows on SQL). If you’re interested in this, create the same report using a sproc & using a report model…Execute both, then see how long you sit in the data stage of report processing by reviewing the ExectionLog table inside the ReportServer database. Doing so will give you an "exact" answer to your question.

  22. joe says:

    Has anyone been able to resolve this issue?

    Connecting to and querying a model in report designer?

  23. Vincent says:

    Hi All,

    I have used Report Builder to enable user to do Ad-Hoc Report that has been great so far, but I have unique requirement for below table/view:

    Month           Headcount


    January-06      45

    February-06     41

    March-06        43

    April-06        42

    May-06          38

    June-06         30

    July-06         50


    Dec-06          42

    Above table represents how many employees, one company/department has during Month column. In January-06, there is 45 employees, February-06, there is 41 employees, and so on.

    If user choose to show Headcount attribute, Report Builder should display each month headcount. The current problem is on the aggregate. for example, report should show Headcount for Quarter 1 as 43, which is headcount for March-06 (how many employees I have at the end of Quarter 1), Quarter 2 should display 30, which is headcount for June-06, and for Year of 2006 should be 42 (at Dec-06). There will be more complexity: if now is still in the month of November 06 and no data for December yet, report should show headcount of November 06 as headcount for Year of 2006.

    I don’t think default available aggregates which are: Total, Average, Max, Min, able to do this.

    Q1, correct value should be 40 (March-06)

       If I use Total Aggregate, it will be 99.

       If I use Average, it will be 24.75

       If I use Max, it will be 45.

       If I use Min, it will be 41.

    I need custom aggregate that show number from last record in the dataset. Is there any possible way how to do this? Any input is appreciated.

    Thank you.

  24. I assume you’re dealing with a relational database and not a cube (where this would be relatively easy)…If this is the case, why not create a view which does the calculations and then just expose the view as an entity in your model?

  25. Vincent says:

    Thank you for the answer. Yes, this is just a relational database. I am not sure how I can do it by creating View. The problem is on the aggregate level, where Report Builder can do a drill down, for example yearly to quarterly to monthly. On the level of Yearly and Quarterly, Report Builder will aggregate the data using default aggregate (Sum,Avg,Max,Min). I am not sure how to do another aggregate, for example LastRow. There are already other attributes, besides Headcount, that users need to compare each other. Do you think it’s possible by creating Views? I already cracked my head how to do it, days and nights :(.

    Is it possible to highlight possibilities by doing Cube? Can this cube be accessible from Report Builder? My users have already used and liked the simplicity of doing Ad Hoc Report using Report Builder.

  26. There are lots of functions built into Analysis Services to help you deal with non-additive measures (which is what you’re dealing with). For example, you could return the "last child" of your quarter, which would be 42 with MDX:

    SELECT [Date].[Fiscal].[Fiscal Quarter].[Q1 FY 2006].LastChild ON 0

    FROM [Some Cube]

    You’ll find that building a cube feels very similar to creating a report model – And you can build report models against cubes, as well so they can be leveraged via Report Builder.

    Hope this helps!

  27. alexkreyn says:

    I have probably stupid question but how to add credential?

    here is my connection string:


    When I click OK I got msg box: Add credentials with the username and password…What is wrong here?????

  28. Alekreyn says:

    I have probably stupid question but how to add credential?

    here is my connection string:

    server=http://localhost/reportserver semi coma datasource=/Models/AdventureWorksDWModel semi coma username=Myusername semi coma password=MyPassword;

    When I click OK I got msg box: Add credentials with the username and password…What is wrong here?????

  29. Alkeyreyn, you don’t add crdentials in the connectstring, you add them in the standard credentials tab.

  30. alexkreyn says:

    thanks for this but i still feel myselef as an idiot

    Model is based on datasourceview…To get datasourceview you need to know login and password for the db…Then you create model…This part is done without any problems.

    Suppose, I am a client user and wsnt to create hoc-up reports using Report designer(not report builder). I really should not know db user name and password…I just need to know the name of the model which is produced already for me by sql server developer…Does he has to create credentials also somehow for this model and tell me that credentials? I really comfused… Thanks for ypor answer

  31. alexkreyn says:

    Actually I created new shared datasource choosing no credentials option from credentials tab…But later when I try to create new dataset  for the report I ‘ve got msgbox: A connection is not valid set and test connection string…I used connection string above

  32. Alekreyn says:

    OK… please help me…Here what I have

    CREATE CREDENTIAL credentialusers  WITH IDENTITY = MyServerAlex’, SECRET = ‘CredentialPassw’




    ALTER LOGIN mary WITH CREDENTIAL = credentialusers


    After I execute all of those…What should I use for my credentials???? I tried credentialusers  with CredentialPassw and was not successfull…Could somebody explain me why? please…

  33. OK, just so we’re on the same page…you want to do TWO things, right? ONE: Use a model as a datasource in a Report Designer / Report Builder Report. TWO: Set credentials that the model should use to go get data.

    For TWO, you type in the credentials in the data source used by the DSV in your model project. You do this by clicking EDIT in the Data Source Designer.

    For ONE, you include the server name and the path to the model, ala server=http://localhost/reportserver;datasource=/Models/AdventureWorksDWModel

    You cannot, however pass a username & password on this string. We will use the value you already specified in TWO. If the value needs to be dynamic, use Windows Auth instead of hardcoding a SQL userID and Password when you setup the model data source. Hope this helps

  34. TrietHo says:


    I am interesting with your article but I have another solution. Can we use a Report Builder Model as a Report Designer data source in SSRS integrated mode?

    For example: I have a sharepoint site: "http://localhost/ReportCenter&quot;, and the model is placed at "http://localhost/ReportCenter/Data Connections/myModel.smdl". I don’t know how to compose a connection string for the data source in VS.NET report designer.

    Thank you very much for your help,


  35. Triet, I’ve never tried it, but you should be able to get this working by pointing FIRST to your Report Server installation (in the example below, lets say it called SSRSBox, listening on port 8080) and then tell the SSRS instance where it can find the model itself on the MOSSBox

    http://SSRSBOX:8080/reportserver?http://mossBox/ReportCenter/Data Connections/myModel.smdl

  36. mlucania says:


    Yes, you can use the report model as a data source within BIDS in SSRS integrated mode. Create the shared data source of type "Report Server Model". Use the following connection string to connect to the report model within your SharePoint Lib.

    server=http://<servername>/<site>/<documentLib>; Datasource=http://<servername>/<site>/<documentLib>/<folder>/MODEL.smdl

    **Important, you’ll need the fully qualified URL to the model including the .smdl extension

    For your example your connection string should read the following:

    server=http://localhost/ReportCenter/Data Connection; Datasource=http://localhost/ReportCenter/Data Connections/myModel.smdl

  37. Sandy says:


    I generated a report model and I tried to generate an ad hoc report………..where in I am getting the following error

    Cannot run this report

    The expression expr1 contains a function ‘in’ with an invalid argument ‘2’: literal without a path is required.

    Can anybody suggest me a solution

  38. Raj Bhachoo says:

    Problem: (this error appears)

    Cannot create a connection to data source ‘dataSource1’.


    1: Login to the webserver (http://localhost/Reports)

    2: Navigate to: Home > Data Sources >   Properties  > General

    3: In the "Connection string:" box: REMOVE the ‘PROVIDER’ STRING and type in the following – "Data Source=localhost;Initial Catalog=<YOUR DATASOURCE_NAME>

    4: select "Windows integrated security" radio button

    5: your Report Builder should now run.

  39. Stephen says:

    I’m trying to use the Report Model as a data source in Report Designer/BIDS (SSRS 2005).  We are running SSRS in Integrated Mode.  I have read the messages above about how to set the connection string but I am still experiencing an error.

    We have a SSAS cube, a SSAS data connection in a Data Connections folder in a SharePoint Library.  This data connection includes the credentials for accessing the cube as windows credentials.

    From this data connection we have built a Report Model.  We can use this report model to build ad-hoc reports in Report Builder and it all seems to work fine.

    We would like to use the report model as a data source for reports built using BIDS/Visual Studio.  In the BIDS project we created the data souce with a connection string like:

    "server=http://server:port/site/library; datasource=http://server:port/site/library/model.smdl&quot;

    On the credentials tab we would really like to select the integrated security option.  We have tried each one in turn.  We can create the shared data source in BIDS but as soon as we try to use it in a report, we get a message stating:

    “A connection cannot be made to the database.

    Set and test the connection string.

    User not authorised”

    Any ideas what we’re doing wrong would be very helpful.



  40. Santhosh says:

    Everything is fine, I am using ReportBuilder and Report Manager to create reports with ReportModel as Datasource. The problem lies when I am trying to create large set of Reports like columns of 120~ and rows of 50k~… Please help me if you can