Showing posts with label datasets. Show all posts
Showing posts with label datasets. Show all posts

Wednesday, March 28, 2012

Jump to different table within the same report

My report has 5 tables that are populated from 5 different datasets and each
table is seperated by a page break so that they get exported to different
worksheets in excel. My question is....is it possible to create a
link/bookmark that can jump from one table to another in the same report and
if it is possible will this functionality export to excel? For
example...when clicking a cell in worksheet1 it will jump to the first value
found in worksheet2. Does that make any sense?Yes, these are called bookmarks. They work in HTML and Excel. See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_interactive_v1_8j57.asp?frame=true.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Shoeman" <Shoeman@.discussions.microsoft.com> wrote in message
news:D7529257-E5D9-461F-BD99-2E9A2603963C@.microsoft.com...
> My report has 5 tables that are populated from 5 different datasets and
> each
> table is seperated by a page break so that they get exported to different
> worksheets in excel. My question is....is it possible to create a
> link/bookmark that can jump from one table to another in the same report
> and
> if it is possible will this functionality export to excel? For
> example...when clicking a cell in worksheet1 it will jump to the first
> value
> found in worksheet2. Does that make any sense?|||Thanks Brian,
That worked!
"Brian Welcker [MSFT]" wrote:
> Yes, these are called bookmarks. They work in HTML and Excel. See
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_interactive_v1_8j57.asp?frame=true.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Shoeman" <Shoeman@.discussions.microsoft.com> wrote in message
> news:D7529257-E5D9-461F-BD99-2E9A2603963C@.microsoft.com...
> > My report has 5 tables that are populated from 5 different datasets and
> > each
> > table is seperated by a page break so that they get exported to different
> > worksheets in excel. My question is....is it possible to create a
> > link/bookmark that can jump from one table to another in the same report
> > and
> > if it is possible will this functionality export to excel? For
> > example...when clicking a cell in worksheet1 it will jump to the first
> > value
> > found in worksheet2. Does that make any sense?
>
>

Monday, March 26, 2012

Juggling Parameters

I am reporting off a star-schema data warehouse. I have three datasets that I
use to populate parameters, and one master dataset that uses the values from
all three to populate the report. The way I have set up the master dataset
is:
SELECT *
FROM t_policy_info
WHERE month_key = @.month_key AND
paper_desc = @.paper_desc AND
unit_desc = @.unit_desc
Matching on non-key values is very time-consuming. For the @.paper_desc and
@.unit_desc datasets, I'm only pulling back the descriptions so that they
appear in the dropdowns at the top of the report.
How should I configure the datasets so that the master query matches
WHERE month_key = @.month_key AND
paper_key= @.paper_key AND
unit_key = @.unit_key ?
Should the paper and unit datasets also contain the key fields so that they
can be matched in the master dataset? How do I prevent the user from being
prompted for the keys (which they shouldn't)?
This is really a performance issue, and while I could make it work with
straight SQL, I'm not sure how to implement it with Reporting Services.
Thanks,
MikeHave you looked at the parametere Label Field/ Value Field inputs , one of
them is what is displayed to the user and other is what is passed to the
query.?
When you create your query for the main dataset along with the month also
pass the paper_key and unit_key parameters, as input by the user.
HTH
"Bassist695" <Bassist695@.discussions.microsoft.com> wrote in message
news:8549E9F8-0F83-40A6-9737-864C4C302045@.microsoft.com...
>I am reporting off a star-schema data warehouse. I have three datasets that
>I
> use to populate parameters, and one master dataset that uses the values
> from
> all three to populate the report. The way I have set up the master dataset
> is:
> SELECT *
> FROM t_policy_info
> WHERE month_key = @.month_key AND
> paper_desc = @.paper_desc AND
> unit_desc = @.unit_desc
> Matching on non-key values is very time-consuming. For the @.paper_desc and
> @.unit_desc datasets, I'm only pulling back the descriptions so that they
> appear in the dropdowns at the top of the report.
> How should I configure the datasets so that the master query matches
> WHERE month_key = @.month_key AND
> paper_key= @.paper_key AND
> unit_key = @.unit_key ?
> Should the paper and unit datasets also contain the key fields so that
> they
> can be matched in the master dataset? How do I prevent the user from being
> prompted for the keys (which they shouldn't)?
> This is really a performance issue, and while I could make it work with
> straight SQL, I'm not sure how to implement it with Reporting Services.
> Thanks,
> Mike

Wednesday, March 21, 2012

Joining two reporting services datasets

Hi All,
It is possibile to create a dataset that is a join of two other datasets
(the two datasets are of diverse origin: the first is OLAP the second is
relational)?
Example:
I have dataset 1 and 2 I want to create the third that is a join of the 1
and the 2.
Can anyone help me?
Thank you.if it's just a SQL command you could create a 3rd dataset?
"gdaquila" wrote:
> Hi All,
> It is possibile to create a dataset that is a join of two other datasets
> (the two datasets are of diverse origin: the first is OLAP the second is
> relational)?
> Example:
> I have dataset 1 and 2 I want to create the third that is a join of the 1
> and the 2.
> Can anyone help me?
> Thank you.
>
>|||You can not do this within RS. You either need to have a stored procedure
doing this (can use linked databases) or you need to use subreports.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"gdaquila" <gdaquila@.nospam.it> wrote in message
news:2vhku5F2lqt1fU1@.uni-berlin.de...
> Hi All,
> It is possibile to create a dataset that is a join of two other datasets
> (the two datasets are of diverse origin: the first is OLAP the second is
> relational)?
> Example:
> I have dataset 1 and 2 I want to create the third that is a join of the 1
> and the 2.
> Can anyone help me?
> Thank you.
>|||Hi,
Ok,
I will try with a linked server.
You think that I can follow the sample published by microsoft at
http://www.microsoft.com/downloads/details.aspx?FamilyID=f9b6e945-1f4c-4b7c-9c83-c6801f0576ff&DisplayLang=en
In this samples I have one regarding the use of linked server.
The last my question is: in this approach, the multidimensional data must be
retrieved via SQL Query not via MDX query it is correct?
Thank you!
G
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> ha scritto nel messaggio
news:eiHEqHDyEHA.3976@.TK2MSFTNGP09.phx.gbl...
> You can not do this within RS. You either need to have a stored procedure
> doing this (can use linked databases) or you need to use subreports.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "gdaquila" <gdaquila@.nospam.it> wrote in message
> news:2vhku5F2lqt1fU1@.uni-berlin.de...
>> Hi All,
>> It is possibile to create a dataset that is a join of two other datasets
>> (the two datasets are of diverse origin: the first is OLAP the second is
>> relational)?
>> Example:
>> I have dataset 1 and 2 I want to create the third that is a join of the 1
>> and the 2.
>> Can anyone help me?
>> Thank you.
>>
>|||Sorry, I don't have any experience with MDX queries.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"gdaquila" <gdaquila@.nospam.it> wrote in message
news:2vjgbgF2lorcjU1@.uni-berlin.de...
> Hi,
> Ok,
> I will try with a linked server.
> You think that I can follow the sample published by microsoft at
>
http://www.microsoft.com/downloads/details.aspx?FamilyID=f9b6e945-1f4c-4b7c-9c83-c6801f0576ff&DisplayLang=en
> In this samples I have one regarding the use of linked server.
> The last my question is: in this approach, the multidimensional data must
be
> retrieved via SQL Query not via MDX query it is correct?
> Thank you!
> G
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> ha scritto nel messaggio
> news:eiHEqHDyEHA.3976@.TK2MSFTNGP09.phx.gbl...
> > You can not do this within RS. You either need to have a stored
procedure
> > doing this (can use linked databases) or you need to use subreports.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "gdaquila" <gdaquila@.nospam.it> wrote in message
> > news:2vhku5F2lqt1fU1@.uni-berlin.de...
> >> Hi All,
> >> It is possibile to create a dataset that is a join of two other
datasets
> >> (the two datasets are of diverse origin: the first is OLAP the second
is
> >> relational)?
> >> Example:
> >> I have dataset 1 and 2 I want to create the third that is a join of the
1
> >> and the 2.
> >> Can anyone help me?
> >> Thank you.
> >>
> >>
> >
> >
>

Joining two datasets?

Hi All,

I have got two datasets. Some 'normal' rows with state values and an addtional dataset with the translation from state value to plain text (value / text pair).

A table object is attached to the first dataset, but I would like to show the plain text from the second dataset in a group header. So I'd like to do some kind of 'look up in another dataset'.

I wasn't able to find a function that could help me solve this problem. Does anyone has any ideas on this?

Kind regards.

Create 2 new parameters: Parameter1 will have all the values from DataSet2 (dataset with text/value paur) and Parameter2 will have all the text from DataSet2.

Write a function (VB.Net ot C#) in the report code (Report -> Report Properties -> Code tab) which will accept these parameters as object arrays and then store them in a shared variable.

Create a textbox in the beginning of the report which will call the above function by passing both the parameters. If you just specify the parameter (say Parameter!Parameter1.Value), it is considered as an array as it have more than one value and it will be received in the function as an object array.

Write another function (function2) in the code which will get the state id (or value) and then loop through the shared arrays (already stored) to find the corresponding text.

Call function2 from your table header to get the text for a given value.

Hope this helps. If more examples and explanation is needed, let me know.

Shyam

|||

Hello Shyam,

thanks a lot for your post. I think this a way to solve my problem.

But also think MS should think about this for future versions. I think it is very complicated (Crystal Reports makes it much easier for developers). It would also be very helpful if datasets could be accessed directly within custom funcation.

Kind regards, CLive76

|||

An alternative approach is to combine the two datasets already in the dataset query. Some options to consider:
? Linked Server functionality (see http://msdn2.microsoft.com/en-us/library/aa213283(SQL.80).aspx)
? OpenRowSet functionality to join data from another database server into the current query (http://msdn2.microsoft.com/en-us/library/ms190312.aspx, http://msdn2.microsoft.com/en-us/library/aa276850(SQL.80).aspx)

-- Robert

Joining Two Datasets in Reporting Services.

I am using SQL Reporting Services 2000.

Is there some way I can connect two datasets? Main issue is that I have to connect two tables from different databases and then display them in a report. Since these tables reside in different databases therefore I have one dataset for each table. While displaying the report I need to join both these datasets and display a report with data from both the tables.

Use linked server concept to join two databases.

Joining two datasets in a report

Good afternoon,
This is actually quite urgent and I'd really appreciate any helpful
responses.
Is it possible to join two datasets into a single report based on a specific
column in each dataset? That is, if I have dataset 'dspatient' and dataset
'dsvisits', I'd like to join the two separate datasets on patient_id which
is present in each dataset and display the results.
How might I accomplish this? I should also mention these datasets are
created by two different datasources.
Any help would be greatly appreciated.
Thank you,
JayThis is a multi-part message in MIME format.
--=_NextPart_000_003E_01C53E7B.9989C690
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
Jay,
Do both of the datasets live on the same server but just different
databases? I'm assuming that since you are using SQL Server 2000?
Mark
--Original Message--
From: Jason [mailto:jason@.grossmans.net]
Posted At: Friday, April 08, 2005 6:15 PM
Posted To: microsoft.public.sqlserver.reportingsvcs
Conversation: Joining two datasets in a report
Subject: Joining two datasets in a report
Good afternoon,
This is actually quite urgent and I'd really appreciate any helpful
responses.
Is it possible to join two datasets into a single report based on a
specific column in each dataset? That is, if I have dataset 'dspatient'
and dataset 'dsvisits', I'd like to join the two separate datasets on
patient_id which is present in each dataset and display the results.
How might I accomplish this? I should also mention these datasets are
created by two different datasources.
Any help would be greatly appreciated.
Thank you,
Jay
--=_NextPart_000_003E_01C53E7B.9989C690
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
&

Dataset clarification
Jay,
Do both of the datasets live on the same server but =just different databases? I'm assuming that since you are using =SQL Server 2000?
Mark
--Original Message--
From: Jason [mailto:jason@.grossmans.net] =Posted At: Friday, April 08, 2005 6:15 PM
Posted To: =microsoft.public.sqlserver.reportingsvcs
Conversation: Joining two datasets in a report
Subject: Joining two datasets in a report
Good afternoon,
This is actually quite urgent and I'd really =appreciate any helpful responses.
Is it possible to join two datasets into a single =report based on a specific column in each dataset? That is, if I =have dataset 'dspatient' and dataset 'dsvisits', I'd like to join the =two separate datasets on patient_id which is present in each dataset and =display the results.
How might I accomplish this? I should also =mention these datasets are created by two different datasources.
Any help would be greatly appreciated.
Thank you,
Jay


--=_NextPart_000_003E_01C53E7B.9989C690--|||This is a multi-part message in MIME format.
--=_NextPart_000_002E_01C53E77.5A06E9C0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Dataset clarificationOne database is on sqlserver 2000 and the other is =on a vms/vax box, but both datasets are currently in one report (I'm not =sure how I'd store a dataset in a database directly). So, basically I =can get two separate datasets into my report, but cannot make a join on =these datasets for the report.
Thanks!
"Mark Monica" <mmonica@.stf.com> wrote in message =news:%231Wvh0pPFHA.2932@.TK2MSFTNGP09.phx.gbl...
Jay,
Do both of the datasets live on the same server but just different =databases? I'm assuming that since you are using SQL Server 2000?
Mark
--Original Message-- From: Jason [mailto:jason@.grossmans.net] Posted At: Friday, April 08, 2005 6:15 PM Posted To: microsoft.public.sqlserver.reportingsvcs Conversation: Joining two datasets in a report Subject: Joining two datasets in a report
Good afternoon,
This is actually quite urgent and I'd really appreciate any helpful =responses.
Is it possible to join two datasets into a single report based on a =specific column in each dataset? That is, if I have dataset 'dspatient' =and dataset 'dsvisits', I'd like to join the two separate datasets on =patient_id which is present in each dataset and display the results.
How might I accomplish this? I should also mention these datasets are =created by two different datasources.
Any help would be greatly appreciated.
Thank you,
Jay --=_NextPart_000_002E_01C53E77.5A06E9C0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Dataset clarification
One database is on sqlserver 2000 and =the other is on a vms/vax box, but both datasets are currently in one report (I'm not =sure how I'd store a dataset in a database directly). So, basically I =can get two separate datasets into my report, but cannot make a join on these =datasets for the report.
Thanks!
"Mark Monica" wrote in =message news:%231Wvh0pPFHA.=2932@.TK2MSFTNGP09.phx.gbl...
Jay,
Do both of the datasets live on the same server but =just different databases? I'm assuming that since you are using SQL =Server 2000?
Mark
--Original Message-- From: Jason [mailto:jason@.grossmans.net] Posted At: Friday, April 08, 2005 6:15 =PM Posted To: =microsoft.public.sqlserver.reportingsvcs Conversation: Joining two datasets in a =report Subject: Joining two datasets in a report = Good afternoon,
This is actually quite urgent and I'd really =appreciate any helpful responses.
Is it possible to join two datasets into a single =report based on a specific column in each dataset? That is, if I have dataset = 'dspatient' and dataset 'dsvisits', I'd like to join the two separate =datasets on patient_id which is present in each dataset and display the results.
How might I accomplish this? I should also =mention these datasets are created by two different datasources.
Any help would be greatly appreciated.
Thank you,
Jay

--=_NextPart_000_002E_01C53E77.5A06E9C0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0071_01C53E82.4266EF80
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Dataset clarificationYou cannot joing two datasets together. You have =two choices, do this in a stored procdure using linked databases (be =careful or you can really have performance problems. You should use =openquery and not 4 part naming if using linked databases). Or you can =use subreports.
-- Bruce Loehle-Conger MVP SQL Server Reporting Services
"Jason" <jason@.grossmans.net> wrote in message =news:eOJtlmqPFHA.2132@.TK2MSFTNGP14.phx.gbl...
One database is on sqlserver 2000 and the other is on a vms/vax box, =but both datasets are currently in one report (I'm not sure how I'd =store a dataset in a database directly). So, basically I can get two =separate datasets into my report, but cannot make a join on these =datasets for the report.
Thanks!
"Mark Monica" <mmonica@.stf.com> wrote in message =news:%231Wvh0pPFHA.2932@.TK2MSFTNGP09.phx.gbl...
Jay,
Do both of the datasets live on the same server but just different =databases? I'm assuming that since you are using SQL Server 2000?
Mark
--Original Message-- From: Jason [mailto:jason@.grossmans.net] Posted At: Friday, April 08, 2005 6:15 PM Posted To: microsoft.public.sqlserver.reportingsvcs Conversation: Joining two datasets in a report Subject: Joining two datasets in a report
Good afternoon,
This is actually quite urgent and I'd really appreciate any helpful =responses.
Is it possible to join two datasets into a single report based on a =specific column in each dataset? That is, if I have dataset 'dspatient' =and dataset 'dsvisits', I'd like to join the two separate datasets on =patient_id which is present in each dataset and display the results.
How might I accomplish this? I should also mention these datasets =are created by two different datasources.
Any help would be greatly appreciated.
Thank you,
Jay --=_NextPart_000_0071_01C53E82.4266EF80
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Dataset clarification
You cannot joing two datasets together. =You have two choices, do this in a stored procdure using linked databases (be =careful or you can really have performance problems. You should use openquery and =not 4 part naming if using linked databases). Or you can use =subreports.
-- Bruce Loehle-Conger MVP =SQL Server Reporting Services
"Jason" wrote =in message news:eOJtlmqPFHA.2132=@.TK2MSFTNGP14.phx.gbl...
One database is on sqlserver 2000 and =the other is on a vms/vax box, but both datasets are currently in one report =(I'm not sure how I'd store a dataset in a database directly). So, =basically I can get two separate datasets into my report, but cannot make a join =on these datasets for the report.

Thanks!


"Mark Monica" wrote in =message news:%231Wvh0pPFHA.=2932@.TK2MSFTNGP09.phx.gbl...
Jay,
Do both of the datasets live on the same server =but just different databases? I'm assuming that since you are using SQL =Server 2000?
Mark
--Original Message-- From: Jason [mailto:jason@.grossmans.net] Posted At: Friday, April 08, 2005 6:15 =PM Posted To: =microsoft.public.sqlserver.reportingsvcs Conversation: Joining two datasets in a =report Subject: Joining two datasets in a report = Good afternoon,
This is actually quite urgent and I'd really =appreciate any helpful responses.
Is it possible to join two datasets into a single =report based on a specific column in each dataset? That is, if I have =dataset 'dspatient' and dataset 'dsvisits', I'd like to join the two =separate datasets on patient_id which is present in each dataset and display =the results.
How might I accomplish this? I should also =mention these datasets are created by two different datasources.
Any help would be greatly appreciated.
Thank you,
Jay =

--=_NextPart_000_0071_01C53E82.4266EF80--|||This is a multi-part message in MIME format.
--=_NextPart_000_0067_01C53E7C.A4688FF0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Dataset clarificationAlright, thanks. I'll work on the subreport option =hopefully.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message =news:eDZiswqPFHA.3144@.tk2msftngp13.phx.gbl...
You cannot joing two datasets together. You have two choices, do this =in a stored procdure using linked databases (be careful or you can =really have performance problems. You should use openquery and not 4 =part naming if using linked databases). Or you can use subreports.
-- Bruce Loehle-Conger MVP SQL Server Reporting Services
"Jason" <jason@.grossmans.net> wrote in message =news:eOJtlmqPFHA.2132@.TK2MSFTNGP14.phx.gbl...
One database is on sqlserver 2000 and the other is on a vms/vax box, =but both datasets are currently in one report (I'm not sure how I'd =store a dataset in a database directly). So, basically I can get two =separate datasets into my report, but cannot make a join on these =datasets for the report.
Thanks!
"Mark Monica" <mmonica@.stf.com> wrote in message =news:%231Wvh0pPFHA.2932@.TK2MSFTNGP09.phx.gbl...
Jay,
Do both of the datasets live on the same server but just different =databases? I'm assuming that since you are using SQL Server 2000?
Mark
--Original Message-- From: Jason [mailto:jason@.grossmans.net] Posted At: Friday, April 08, 2005 6:15 PM Posted To: microsoft.public.sqlserver.reportingsvcs Conversation: Joining two datasets in a report Subject: Joining two datasets in a report
Good afternoon,
This is actually quite urgent and I'd really appreciate any =helpful responses.
Is it possible to join two datasets into a single report based on =a specific column in each dataset? That is, if I have dataset ='dspatient' and dataset 'dsvisits', I'd like to join the two separate =datasets on patient_id which is present in each dataset and display the =results.
How might I accomplish this? I should also mention these datasets =are created by two different datasources.
Any help would be greatly appreciated.
Thank you,
Jay --=_NextPart_000_0067_01C53E7C.A4688FF0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Dataset clarification
Alright, thanks. I'll work on the =subreport option hopefully.
"Bruce L-C [MVP]" wrote in message news:eDZiswqPFHA.3144=@.tk2msftngp13.phx.gbl...
You cannot joing two datasets =together. You have two choices, do this in a stored procdure using linked databases (be =careful or you can really have performance problems. You should use openquery =and not 4 part naming if using linked databases). Or you can use subreports.

-- Bruce Loehle-Conger =MVP SQL Server Reporting Services

"Jason" =wrote in message news:eOJtlmqPFHA.2132=@.TK2MSFTNGP14.phx.gbl...
One database is on sqlserver 2000 =and the other is on a vms/vax box, but both datasets are currently in one report =(I'm not sure how I'd store a dataset in a database directly). So, =basically I can get two separate datasets into my report, but cannot make a join =on these datasets for the report.

Thanks!


"Mark Monica" wrote in =message news:%231Wvh0pPFHA.=2932@.TK2MSFTNGP09.phx.gbl...
Jay,
Do both of the datasets live on the same server =but just different databases? I'm assuming that since you are using =SQL Server 2000?
Mark
--Original Message-- From: Jason [mailto:jason@.grossmans.net] Posted At: Friday, April 08, 2005 6:15 =PM Posted To: microsoft.public.sqlserver.reportingsvcs Conversation: Joining two datasets in a report =Subject: Joining two datasets in a report
Good afternoon,
This is actually quite urgent and I'd really =appreciate any helpful responses.
Is it possible to join two datasets into a =single report based on a specific column in each dataset? That is, if I =have dataset 'dspatient' and dataset 'dsvisits', I'd like to join the =two separate datasets on patient_id which is present in each dataset =and display the results.
How might I accomplish this? I should also =mention these datasets are created by two different datasources. = Any help would be greatly appreciated. = Thank you,
Jay

--=_NextPart_000_0067_01C53E7C.A4688FF0--sql

Joining two DataSets

How do you join two datasets in reporting services... one from sql and another from oracle?

Thanks

Currently, datasets cannot be joined on the report. Some options you can explore:

1. Join the datasets at the database level by using the SQL Server linked servers feature.

2. Use a subreport for one of the datasets which can be synchronized with the master with parameters.

3. Use a SQL Server 2005 CLR stored procedure to fetch and join the ADO.NET datasets.

4. Write a custom data extension. This is the most intensive option which I will suggest as a last resort.

|||

Also, read about using the SSIS engine to create your combined dataset.

SSIS has easy and very powerful Transform options which may present you with a better option than writing the code yourself.

Have a look at the article here:

http://blogs.msdn.com/bimusings/archive/2006/07/19/using-a-parameterized-ssis-package-as-a-data-source-for-sql-reporting-services.aspx

Thank you,

LH

Joining Tables Across Multiple Datasets

I am trying to join two tables from two different datasets in SQL
Server 2005 Reporting Services. How would one go about doing this?
Since there are technically two spots to have SQL queries (one for
each dataset), how does one go about doing SQL? Is joining across
multiple datasets even possible? If so, are there clever workarounds?You cannot join datasets in RS. You either need to do this in a stored
procedure OR you should think about subreports. Usually what you need to do
can be accomplished with subreports. For a 1:1 or 1:M relationship
subreports will work for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<rdracer58@.gmail.com> wrote in message
news:1183746250.948449.38960@.o61g2000hsh.googlegroups.com...
>I am trying to join two tables from two different datasets in SQL
> Server 2005 Reporting Services. How would one go about doing this?
> Since there are technically two spots to have SQL queries (one for
> each dataset), how does one go about doing SQL? Is joining across
> multiple datasets even possible? If so, are there clever workarounds?
>sql

Monday, March 19, 2012

Joining of datasets supported in future version?

Rajeev,
Thanks for your reply. What I am after is being able to join a dataset
fron an SQL Server to one from a different data source, say Oracle.
Would I be right in saying that OpenRowset wouldn't help in this
instance?
PaulYou can do this using the linked server functionality in SQL Server 2000
SP3. Details are available on MSDN:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_12_9ooj.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul Taylor" <pjtaylor@.email.com> wrote in message
news:ce5uun$roq@.odbk17.prod.google.com...
> Rajeev,
> Thanks for your reply. What I am after is being able to join a dataset
> fron an SQL Server to one from a different data source, say Oracle.
> Would I be right in saying that OpenRowset wouldn't help in this
> instance?
> Paul
>

Joining of datasets supported in future version?

There are a number of posts in this group that have asked if datasets
can be joined which suggests that there is some requirement for this
functionality. We are currently comparing RS to Brio which allows
this.
Does anyone know if this feature will be introduced in a future
version?
Thanks,
PaulYou can join datasets using the OpenRowset feature of SQL Server.
--
Rajeev Karunakaran [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul Taylor" <pjtaylor@.email.com> wrote in message
news:3e0bce4c.0407260213.79edf12e@.posting.google.com...
> There are a number of posts in this group that have asked if datasets
> can be joined which suggests that there is some requirement for this
> functionality. We are currently comparing RS to Brio which allows
> this.
> Does anyone know if this feature will be introduced in a future
> version?
> Thanks,
> Paul|||It's a high-priority item on our wishlist for a future version, but we don't
have any specific plans for it right now (the high cost of implementation
makes it unlikely this will make it into SQL 2005).
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Paul Taylor" <pjtaylor@.email.com> wrote in message
news:3e0bce4c.0407260213.79edf12e@.posting.google.com...
> There are a number of posts in this group that have asked if datasets
> can be joined which suggests that there is some requirement for this
> functionality. We are currently comparing RS to Brio which allows
> this.
> Does anyone know if this feature will be introduced in a future
> version?
> Thanks,
> Paul

Monday, March 12, 2012

Joining different datasets with parameters

Hi all,

lets say i have one table with colour ids and colour names, and another table with broad-leafed trees and different colour id fields for root, leafs and trunk:

Table colours:
integer field colour_id, varchar field colour_name
Table trees:
varchar field tree_type, integer field leaf_colour_id,
integer field trunk_colour_id, integer field root_colour_id

one way to get the fields would be:

select T.tree_type, C1.colour_name as leaf_colour_name,
C2.colour_name as trunk_colour_name, C3.colour_name as root_colour_name
from trees as T
inner join colours as C1 on C1.colour_id = leaf_colour_id
inner join colours as C2 on C2.colour_id = trunk_colour_id
inner join colours as C3 on C3.colour_id = root_colour_id;

But i would like to do that with some kind of inline function like:
colourName := select colour_name from colours where colour_id = @.colourId;
and then:
select tree_type, colourName(leaf_colour_id), colourName(trunk_colour_id), colourName(root_colour_id) from trees;

Is that possible? Any comments welcome.

Thanks,
haraldIn Sql Server, you can create a User Defined Function (Scalar) which will produce this. It will make code more readable but will slow it down a bit.
See the templates in SQL Server, and this article.
It would be something like:
CREATE FUNCTION fnGetColourName(@.ColourID as int)
RETURNS int
as
BEGIN
DECLARE @.Value as int
SET @.Value = (SELECT MAX(Colour_name)
FROM Colours
WHERE colour_id = @.ColourID
END
RETURN @.Value
SELECT dbo.fnGetColourName(leaf_colour_id) as leafColour
FROM trees
Alternatively, you can use the Custom Code section of the report to create a function that will do the same thing, see examples from Bryant Likes's blog here.|||

Hi wavemash,

thanks for your reply, now my sql statement is more readable

Thanks,
harald

Joining DataSets with Hashtables

I have Fact data (Failures by Resource) from DataSource A, and
Dimension data (Owner of Resources) in DataSource B, where A and B are
different Oracle servers. Linked Servers are not an option for at
least 3 months, if ever. These sources return DataSets A and B.
I need to develop a report where I total the Failures from A "Grouped
By" the Owner of a Resource from B. Very simple lookup.
If I use the Report custom Code, I can push the Resource/Owner mapping
into a (Public global variable) Hashtable, and pull it out in the
Details of a table. Data is pushed using a Table using DataSet A with
Detail rows that call a SetData(ID,Owner) function with a non-Visible
Details row. Data is retrieved in a separate table based on DataSet B
using a GetData(ID) function.
But:
* GetData() only appears to function in Expressions in the Details of
a table, even though GetData() is referencing the Hashtable.
* I cannot sort a Table on the GetData() results, data comes back in
random order
* I cannot Group by the GetData() results, the field appears to be set
to the Owner of the first ID (not for each row in the DataSet!!)
* I can add a Calculated Field to DataSet A that calls SetData(), and
it appears to work (I have SetData return the Owner, so I can see the
function is called), but if I add a Calculated Field to DataSet B that
calls GetData(), all the field contents are Nothing.
I am running out of ideas here. Why is an action so common made so
difficult in SSRS?
-- Scott-- Report Properties -> Code Pane
--
Public Shared mapping As New System.Collections.Hashtable()
Public Function StoreValue( ByVal dataSetName As String, ByVal
fieldName as String, _
ByVal idName As String, ByVal fieldValue as Object ) As Object
Dim hashName As String
hashName = dataSetName & "." & fieldName & "." & idName
If Not mapping.ContainsKey( hashName ) Then
mapping.Add( hashName, fieldValue )
End If
Return fieldValue
End Function
Public Function GetValue( ByVal dataSetName As String, ByVal fieldName
as String, _
ByVal idName As String ) As Object
Dim hashName As String
hashName = dataSetName & "." & fieldName & "." & idName
If mapping.ContainsKey( hashName ) Then
Return mapping( hashName )
Else
Return Nothing
End If
End Function
-- Report Body, Table Object --
-- Table 1, Details Section --
DataSet dsNames
=Code.StoreValue( "dsNames", "ORGNAME", Fields!RESOURCEID.Value,
Fields!ORGNAME.Value )
-- Table 2, Details Section --
DataSet dsResources
=Code.GetValue( "dsNames", "ORGNAME", Fields!RESOURCEID.Value )

Joining datasets returned from stored procedures.

Hello,
I am restricted to using stored procedures for data access. I would like to
be able to create a "Text" Dataset named C which is a join of two "Stored
Procedure" data sets (sp1, and sp2). Any idea how I can do this. Thanks.Tyr this:
SELECT * FROM OPENQUERY(servername, 'sp_1')
union
SELECT * FROM OPENQUERY(servername, 'sp_2')
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jason Agee" <jason.agee@.tlc.state.tx.us> wrote in message
news:%23%23nvqx4YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I am restricted to using stored procedures for data access. I would like
to
> be able to create a "Text" Dataset named C which is a join of two "Stored
> Procedure" data sets (sp1, and sp2). Any idea how I can do this. Thanks.
>

Friday, March 9, 2012

JOIN two Datasets?

I would like to perform an inner join on two seperate datasets. One dataset
gets its data from SQL server, the other gets its data from a XML web
service.Can't do this. You might consider using a sub report. A sub report can be
put into the table object cells.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jeff Richardson" <BobcatRidge@.newsgroups.nospam> wrote in message
news:O63%23UQuxGHA.3464@.TK2MSFTNGP03.phx.gbl...
>I would like to perform an inner join on two seperate datasets. One
>dataset gets its data from SQL server, the other gets its data from a XML
>web service.
>|||Thanks.
I have not tried the sub-report route because I assumed that you would get
all rows from the parent dataset even if the sub-report contained no rows.
Is there a way to that the parent rows are only displayed when the
sub-report has rows?
Thanks
Jeff.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:u1qBZauxGHA.3456@.TK2MSFTNGP03.phx.gbl...
> Can't do this. You might consider using a sub report. A sub report can be
> put into the table object cells.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jeff Richardson" <BobcatRidge@.newsgroups.nospam> wrote in message
> news:O63%23UQuxGHA.3464@.TK2MSFTNGP03.phx.gbl...
>>I would like to perform an inner join on two seperate datasets. One
>>dataset gets its data from SQL server, the other gets its data from a XML
>>web service.
>|||Hi Jeff,
Although you could not control the visibility of a textbox with the
Subreport, you could add another dataset to control the visibility.
The dataset should be the same as the the one you use in the sub report.
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.