次の方法で共有

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=https://yourserver/reportserver), then a semi-colon, and then the path to your model (datasource=/SomeFolder/SomeModelName).

Here's an example:

server=https://MyReportServer/reportserver;datasource=/Models/AdventureWorks

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

  • Anonymous
    November 02, 2005
    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

    Myles

  • Anonymous
    November 02, 2005
    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.

    Myles

  • Anonymous
    November 04, 2005
    Agreed, it takes so long to tweak your model (getting it juuust right), that it's pity not to use it....

  • Anonymous
    December 06, 2005
    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..

  • Anonymous
    February 01, 2006
    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..

  • Anonymous
    February 01, 2006
    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.

  • Anonymous
    February 15, 2006
    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?

  • Anonymous
    May 30, 2006
    I have a similar issue. Did anybody know how to solve it?
    Thank you
    Lenny

  • Anonymous
    June 06, 2006
    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?

  • Anonymous
    June 27, 2006
    The comment has been removed

  • Anonymous
    June 28, 2006
    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?

  • Anonymous
    June 30, 2006
    No, unfortunatelly it doesn´t work for any model I create...the same error message comes.

  • Anonymous
    July 12, 2006
    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.

  • Anonymous
    July 12, 2006
    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.

  • Anonymous
    July 17, 2006
    Can any of you folks open up the smdl (model) document in notepad and see if the following tags are missing?

    <CustomProperties>
    <CustomProperty Name="qd:PerspectiveID">
    <Value xsi:type="xsd:string">http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling/udmmodeling:Test_Cube</Value>
    </CustomProperty>
    </CustomProperties>

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

  • Anonymous
    September 10, 2006
    The comment has been removed

  • Anonymous
    September 14, 2006
    This is typically due to a security issue.  Check your data source and make sure the account has the correct permisssions.

  • Anonymous
    September 27, 2006
    The comment has been removed

  • Anonymous
    October 12, 2006
    Did you rebuild the model?

  • Anonymous
    October 16, 2006
    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?

  • Anonymous
    October 17, 2006
    The comment has been removed

  • Anonymous
    June 15, 2007
    Has anyone been able to resolve this issue?Connecting to and querying a model in report designer?

  • Anonymous
    June 18, 2007
    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      45February-06     41March-06        43April-06        42May-06          38June-06         30July-06         50(etc...)Dec-06          42Above 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.

  • Anonymous
    June 18, 2007
    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?

  • Anonymous
    June 19, 2007
    The comment has been removed

  • Anonymous
    June 21, 2007
    The comment has been removed

  • Anonymous
    September 21, 2007
    The comment has been removed

  • Anonymous
    September 21, 2007
    The comment has been removed

  • Anonymous
    September 21, 2007
    Alkeyreyn, you don't add crdentials in the connectstring, you add them in the standard credentials tab.

  • Anonymous
    September 24, 2007
    thanks for this but i still feel myselef as an idiotModel 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

  • Anonymous
    September 24, 2007
    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

  • Anonymous
    September 24, 2007
    The comment has been removed

  • Anonymous
    September 27, 2007
    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/AdventureWorksDWModelYou 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

  • Anonymous
    October 17, 2007
    Hello,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", 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,Triet

  • Anonymous
    October 18, 2007
    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 MOSSBoxhttp://SSRSBOX:8080/reportserver?http://mossBox/ReportCenter/Data Connections/myModel.smdl

  • Anonymous
    January 25, 2008
    Triet,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 extensionFor your example your connection string should read the following:server=http://localhost/ReportCenter/Data Connection; Datasource=http://localhost/ReportCenter/Data Connections/myModel.smdl

  • Anonymous
    April 28, 2008
    The comment has been removed

  • Anonymous
    October 14, 2008
    The comment has been removed

  • Anonymous
    February 10, 2009
    PingBack from http://business-inteligence.kdejonge.net/using-a-report-model-from-sharepoint-in-report-designer-bids/

  • Anonymous
    March 22, 2010
    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" 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. Thanks Stephen

  • Anonymous
    May 19, 2013
    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