|
|
You're in the
sql server reporting services
group:Is it possible to link two datasets together ?
sql server reporting services:
are different tables. In my opinion a union is isn't a proper way to do this. Anyway... My central idea behind my question is: If I could solve this problem, I would be more flexible in using data for several reports. For example, I 've got one special view for clients (supposing there are more tables to build this view) , a second special view for sales and many more views for other matters. In every report where I now have to use client-data, I can take the same view. Otherwise I have to change every select statement for every special report if anything changes... [quoted text, click to view] >> Its a thing of strategy, do you see ? jack "Jan Pieter Posthuma" <jan-pieterp.at.avanade.com> schrieb im Newsbeitrag news:BF29CB19-BD2C-4218-A9AE-F970A4D6E011@microsoft.com... [quoted text, click to view] > Hi, > > It isn't possiple to link two datasets together or use more than one > dataset > on a control. > > But what about one dataset which unions both sql queries? And then you can > use a filter on a control to get only data from a single table. > > e.g. the dataset: > select *, 'Titles' as TableName from pubs.dbo.titles > UNION > select *, 'Sales' as TableName from pubs.dbo.sales > > and as filter to get titles data: > =Fields!TableName.Value='Titles' > > Be sure that both queries you use to union have the same column names, so > don't use the *. > > -- > | Jan Pieter Posthuma > -- > > > "Jack" wrote: > >> Saludos ! >> >> given two datasets: >> >> -------------------------------------------- >> dataset1: select * from pubs.dbo.titles >> dataset2: select * from pubs.dbo.sales >> -------------------------------------------- >> >> My question now is, how can i establish a relation between this two >> datasets >> ? And where (data register, layout register) ? >> Or only with Subreports ? >> >> thanks for any suggestion, hint.... >> >> Jack >> >> >>
Hi,
It isn't possiple to link two datasets together or use more than one dataset on a control. But what about one dataset which unions both sql queries? And then you can use a filter on a control to get only data from a single table. e.g. the dataset: select *, 'Titles' as TableName from pubs.dbo.titles UNION select *, 'Sales' as TableName from pubs.dbo.sales and as filter to get titles data: =Fields!TableName.Value='Titles' Be sure that both queries you use to union have the same column names, so don't use the *. -- | Jan Pieter Posthuma -- [quoted text, click to view] "Jack" wrote:
> Saludos ! > > given two datasets: > > -------------------------------------------- > dataset1: select * from pubs.dbo.titles > dataset2: select * from pubs.dbo.sales > -------------------------------------------- > > My question now is, how can i establish a relation between this two datasets > ? And where (data register, layout register) ? > Or only with Subreports ? > > thanks for any suggestion, hint.... > > Jack > >
Saludos !
given two datasets: -------------------------------------------- dataset1: select * from pubs.dbo.titles dataset2: select * from pubs.dbo.sales -------------------------------------------- My question now is, how can i establish a relation between this two datasets ? And where (data register, layout register) ? Or only with Subreports ? thanks for any suggestion, hint.... Jack
[quoted text, click to view] > My central idea behind my question is: If I could solve this problem, I > would be more flexible in using data for several reports. For example, I 've > got one special view for clients (supposing there are more tables to build > this view) , a second special view for sales and many more views for other > matters. In every report where I now have to use client-data, I can take the > same view. Otherwise I have to change every select statement for every > special report if anything changes... Not sure this answers your question but... Use a stored procedure to provide your data, then this stored procedure can be re-used for all reports. And only has to be edited once for all reports. sarah
You're right. thats the way we all doing it for years. But now for several
reasons we would like to use views instead of procs... so our customers are able to easily create their own reports and links between their required tables. without any knowledge of the underlying structure. Jack <sarah.abnett@jarvis-uk.com> schrieb im Newsbeitrag news:1119879460.100655.234790@g44g2000cwa.googlegroups.com... [quoted text, click to view] > > >> My central idea behind my question is: If I could solve this problem, I >> would be more flexible in using data for several reports. For example, I >> 've >> got one special view for clients (supposing there are more tables to >> build >> this view) , a second special view for sales and many more views for >> other >> matters. In every report where I now have to use client-data, I can take >> the >> same view. Otherwise I have to change every select statement for every >> special report if anything changes... > > Not sure this answers your question but... > Use a stored procedure to provide your data, then this stored procedure > can be re-used for all reports. And only has to be edited once for all > reports. > sarah >
Hi,
First sorry for my late reply, but I must say your last reply explained your needs perfectly. What you can try is to create a datasource with integrated authentication turned on. This would force SRS to use the users credentials to log in to the database. In the database views (to which the users only have read rights) you can create a user specified select statement by filtering with the SQL function SYSTEM_USER (SUSER_SNAME). Unfortunaly you can only use select statements in views. So if your database structure can't cope with this kind of behaviour, you can only fall back to stored procedures. Hope this would help you further. Feel free to ask for more information. -- | Jan Pieter Posthuma -- [quoted text, click to view] "Jack" wrote:
> You're right. thats the way we all doing it for years. But now for several > reasons we would like to use views instead of procs... so our customers are > able to easily create their own reports and links between their required > tables. without any knowledge of the underlying structure. > > Jack > > <sarah.abnett@jarvis-uk.com> schrieb im Newsbeitrag > news:1119879460.100655.234790@g44g2000cwa.googlegroups.com... > > > > > >> My central idea behind my question is: If I could solve this problem, I > >> would be more flexible in using data for several reports. For example, I > >> 've > >> got one special view for clients (supposing there are more tables to > >> build > >> this view) , a second special view for sales and many more views for > >> other > >> matters. In every report where I now have to use client-data, I can take > >> the > >> same view. Otherwise I have to change every select statement for every > >> special report if anything changes... > > > > Not sure this answers your question but... > > Use a stored procedure to provide your data, then this stored procedure > > can be re-used for all reports. And only has to be edited once for all > > reports. > > sarah > > > >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |