Report Model (Data Source View) issue

We've built an oltp that uses nothing but synonyms that reach out to various DB's in the organization thru linked servers.

The issue that we are having is Data Source Views can only see tables and views. Is it possible to use synonyms? If not what would be a work around.

Please advise. Thanks

Steve

I use linked servers in reports (actually, let's be honest, I use linked servers pretty much ALL THE TIME ).

I haven't done this with Report Models that I can recall. But I am pretty sure that what works for me would work in Report Models.

There are a couple of things you can do as a workaround:

1. If you don't have other uses for the synonyms, eliminate the synonym from the equation entirely and just use fully-qualified syntax in views, for example (and of course you don't have to use * here!) :

CREATE VIEW vw_Synonym1 AS SELECT * FROM MyLinkedServer.MyDB1.dbo.MyTable

2. I'm not entirely sure that the models can't use synonyms! It's likely that they can but the wizards and interfaces that you're given in the designer cannot. If I'm right, you just need to write something to handle the model XML directly to get what you want.

I am not going to mess with this to test it out, because I don't spend a lot of time with report models, but start with the XSD (which must be published) and figure out whether the appropriate values should go. Then alter a copy of an existing model that you have to test -- I bet it works. If you want to go this route and get stuck on the details, holler. <g>

3. If you do have other uses for the synonyms, as apparently you do, and if you don't want to mess with the model xml, you can create a view that represents each synonym in a one-to-one relationship. IOW, pretty much CREATE VIEW vw_Synonym1 AS SELECT * FROM Synonym1. Again I know that * is evil but you get the picture <s>. Extend this properly as follows:

You can avoid the evil "*" by interrogating the properties of the thing underneath. You can automate all current synonyms to create new views (and leverage the interrogation code you just wrote) by examining SELECT * FROM sys.synonyms and writing code that works through the list. You can automate all future synonyms you create to do the same thing automatically by using a database trigger. Here is a sketch:

Report item not linked to a dataset

I have a need to add a text box to a report. The text box will have an
expression that I need to have that captues the value of a field from a
second data set associated with the report. I add a text box but in the edit
expresssion process I can't access any fields (from any data set) and i get
the message Report item not linked to a dataset. I'm not trying to add a new
table or anything like that. I just want to be able to grab that field value
and disply.
--
Thanks, JimOn Sep 21, 11:38 am, Jim B <J...@.lightning.com> wrote:
> I have a need to add a text box to a report. The text box will have an
> expression that I need to have that captues the value of a field from a
> second data set associated with the report. I add a text box but in the edit
> expresssion process I can't access any fields (from any data set) and i get
> the message Report item not linked to a dataset. I'm not trying to add a new
> table or anything like that. I just want to be able to grab that field value
> and disply.
> --
> Thanks, Jim
If I'm understanding you correctly, you should be able to reference a
dataset via an aggregate expression. Something like this should work.
=Max(Fields!SomeFieldName.Value, "SomeDatasetName")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||That's exactly what I needed to know. Thanks for your response to my question.
--
Thanks, Jim
"EMartinez" wrote:
> On Sep 21, 11:38 am, Jim B <J...@.lightning.com> wrote:
> > I have a need to add a text box to a report. The text box will have an
> > expression that I need to have that captues the value of a field from a
> > second data set associated with the report. I add a text box but in the edit
> > expresssion process I can't access any fields (from any data set) and i get
> > the message Report item not linked to a dataset. I'm not trying to add a new
> > table or anything like that. I just want to be able to grab that field value
> > and disply.
> > --
> > Thanks, Jim
>
> If I'm understanding you correctly, you should be able to reference a
> dataset via an aggregate expression. Something like this should work.
> =Max(Fields!SomeFieldName.Value, "SomeDatasetName")
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Sep 23, 9:20 am, Jim B <J...@.lightning.com> wrote:
> That's exactly what I needed to know. Thanks for your response to my question.
> --
> Thanks, Jim
> "EMartinez" wrote:
> > On Sep 21, 11:38 am, Jim B <J...@.lightning.com> wrote:
> > > I have a need to add a text box to a report. The text box will have an
> > > expression that I need to have that captues the value of a field from a
> > > second data set associated with the report. I add a text box but in the edit
> > > expresssion process I can't access any fields (from any data set) and i get
> > > the message Report item not linked to a dataset. I'm not trying to add a new
> > > table or anything like that. I just want to be able to grab that field value
> > > and disply.
> > > --
> > > Thanks, Jim
> > If I'm understanding you correctly, you should be able to reference a
> > dataset via an aggregate expression. Something like this should work.
> > =Max(Fields!SomeFieldName.Value, "SomeDatasetName")
> > Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Glad I could help.
Regards,
Enrique Martinez
Sr. Software Consultant

report item not linked to a dataset

Hi,
I'm using SQLServer 2000 and Visual Studio 2003.
Today all the sudden in Visual Studio the field list pane is empty for all
reports I have created. When I try to ad a field using Expressions, I receive
the message "report item not linked to a dataset". As a source for the
dataset I use a storede procedure.
The reports can still be used. I just cannot properly edit them anymore.
When l look at the xml code, the fields are in the report.
--
JanHi Jan
Have you tried right-clicking on the table in the report, going to properties,
then linking it to a dataset manually?
I've never had reports suddenly lose their datasets like that, but there are
often times when I add a table to a report and it isn't automatically linked
to a dataset.
Jan Hussaarts wrote:
>Hi,
>I'm using SQLServer 2000 and Visual Studio 2003.
>Today all the sudden in Visual Studio the field list pane is empty for all
>reports I have created. When I try to ad a field using Expressions, I receive
>the message "report item not linked to a dataset". As a source for the
>dataset I use a storede procedure.
>The reports can still be used. I just cannot properly edit them anymore.
>When l look at the xml code, the fields are in the report.|||The point is that normally there is a Fields pane on the left of the screen.
Add a table and you can drop a Field from the Fields pane on a cell in the
table. The field pane remains empty after manually linking the table to the
dataset.|||Have you checked your dataset to make sure it can still access the data?
If there is no dataset, or if you don't have access to the data for whatever
reason (maybe your user account doesn't have authority to view the data) then
the fields pane will remain empty, since RS can't actually see the fields
returned by the dataset query.
Jan Hussaarts wrote:
>The point is that normally there is a Fields pane on the left of the screen.
>Add a table and you can drop a Field from the Fields pane on a cell in the
>table. The field pane remains empty after manually linking the table to the
>dataset.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1|||When I look at de rdl version of the report, under < Datasets> the dataset
and all the fields that are output of the stored procedure are there. So,
access rights are ok. It is just the Fields pane that remains empty.
I'm thinking of uninstalling and installing RS. I think that might solve the
problem. It has taken a lot of my time up till now.|||unable to add a field in report viewer which is not selected in datase
From http://www.developmentnow.com/g/115_2005_9_0_0_598578/report-item-not-linked-to-a-dataset.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com|||unable to add a field in report viewer which is not selected in datase
From http://www.developmentnow.com/g/115_2005_9_0_0_598578/report-item-not-linked-to-a-dataset.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com|||In the future post your problem. Don't expect us to follow a link. I almost
just ignored this.
Go into the dataset that is no longer showing the field list and click on
the refresh fields button (it is to the right of the ...). See if that
brings back the field list.
This sometimes occurs with stored procedures. If it does not bring back the
list then it is possible that somebody has modified that stored procedure in
such a way that it causes a problem.
First see if this fixes your problem.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sunil tripathi" <sunil137_bpl@.yahoo.com> wrote in message
news:cbbcde3f-6750-4e12-bc8c-9a0d62474c18@.developmentnow.com...
> unable to add a field in report viewer which is not selected in dataset
> From
> http://www.developmentnow.com/g/115_2005_9_0_0_598578/report-item-not-linked-to-a-dataset.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com