How to Connect to Analysis Services from Client Management Studio?

From my client machine, I have no trouble accessing the Database Engine of my 2005 server in SQL Server Management Studio since I can use SQL Server Authentication. However, Analysis Services (at least how my server is configured right now) only allows Windows Authentication and I can connect. For what it's worth, I can't connect to the Database Engine either on my client using Windows Authentication.

What do I need to do to connect to Analysis Services from my client Management Studio?Like AS2K (and in fact all previous versions of OLAP and Analysis Services), we only support trusted connections from users to AS (both for queries and for administration/operators).

When Analysis Services itself connects to the backend RDBMS, that can be either trusted or SQL logins.

_-_-_ Dave|||

Dave Wickert wrote:

Like AS2K (and in fact all previous versions of OLAP and Analysis Services), we only support trusted connections from users to AS (both for queries and for administration/operators).

When Analysis Services itself connects to the backend RDBMS, that can be either trusted or SQL logins.

_-_-_ Dave

Dave, thanks for the reply. So how do I enable "trusted" logins for AS, if that's possible that is?|||I figured it out.
I had to make an exception for port 2383 (or whatever the default port is for Analysis Services) in Windows Firewall on the Analysis Services server.|||

Dear Dave ,

I am slightly less experienced , and wonder if you could answer some questions for me .

I am running MsSQL2005 and AS on a server .I have built cubes in server .Have had great difficulty in browsing the cube from any remote machine .

Initial problems were associated with the AS server not being able to authenticate the client .

I then put the server and client into the same domain .

I registered an existing user in the domain , into the server users , as member of the various sql groups (olap and other ) .

In the server (AS) user groups , should there be an OLAP administrator group.? I see only an OLAP user group ?

I also granted this domain user login to the sqlserver .

The client is now able to connect to the sqlserver and the Analysis server , using "windows authentication" with one catch .(Previously the cliebnt could connect to the sqlserver using username -pwd authentication .

There are no AS databases listed , no cubes shown etc . The client also has no authority to create a new database in the analysis server .

I am just wondering , what are all the prerequisites to get the client to be able to view the analysis service cubes ?

How can one verify that you have correctly setup the windows authentication required for AS , while you are logged into the server and sql manager ?

What is the correct setting for datasource property ?

Sorry for making such a long story .

any comments appreciated

How to Connect to Analysis Services from Client Management Studio?

From my client machine, I have no trouble accessing the Database Engine of my 2005 server in SQL Server Management Studio since I can use SQL Server Authentication. However, Analysis Services (at least how my server is configured right now) only allows Windows Authentication and I can connect. For what it's worth, I can't connect to the Database Engine either on my client using Windows Authentication.

What do I need to do to connect to Analysis Services from my client Management Studio?
Like AS2K (and in fact all previous versions of OLAP and Analysis Services), we only support trusted connections from users to AS (both for queries and for administration/operators).

When Analysis Services itself connects to the backend RDBMS, that can be either trusted or SQL logins.

_-_-_ Dave|||

Dave Wickert wrote:

Like AS2K (and in fact all previous versions of OLAP and Analysis Services), we only support trusted connections from users to AS (both for queries and for administration/operators).

When Analysis Services itself connects to the backend RDBMS, that can be either trusted or SQL logins.

_-_-_ Dave

Dave, thanks for the reply. So how do I enable "trusted" logins for AS, if that's possible that is?
|||I figured it out.
I had to make an exception for port 2383 (or whatever the default port is for Analysis Services) in Windows Firewall on the Analysis Services server.
|||

Dear Dave ,

I am slightly less experienced , and wonder if you could answer some questions for me .

I am running MsSQL2005 and AS on a server .I have built cubes in server .Have had great difficulty in browsing the cube from any remote machine .

Initial problems were associated with the AS server not being able to authenticate the client .

I then put the server and client into the same domain .

I registered an existing user in the domain , into the server users , as member of the various sql groups (olap and other ) .

In the server (AS) user groups , should there be an OLAP administrator group.? I see only an OLAP user group ?

I also granted this domain user login to the sqlserver .

The client is now able to connect to the sqlserver and the Analysis server , using "windows authentication" with one catch .(Previously the cliebnt could connect to the sqlserver using username -pwd authentication .

There are no AS databases listed , no cubes shown etc . The client also has no authority to create a new database in the analysis server .

I am just wondering , what are all the prerequisites to get the client to be able to view the analysis service cubes ?

How can one verify that you have correctly setup the windows authentication required for AS , while you are logged into the server and sql manager ?

What is the correct setting for datasource property ?

Sorry for making such a long story .

any comments appreciated

How to connect to a DB multiple times through page

Hey all,

I am still pretty new to all of this and I am having problems accessing a the same DB twice in my page. I want to pull information once in one spot, but then pull different information in a different spot on the page. Anyway, in the first spot, I have the following code:

Dim conStringAsString = WebConfigurationManager.ConnectionStrings("DataConn").ConnectionString

Dim conAsNew SqlConnection(conString)

Dim cmdAsNew SqlCommand("Select Team1.TeamID as Team1ID, Team1.TeamName as HomeTeam, Team2.TeamID as Team2ID, Team2.TeamName as AwayTeam, Football_Schedule.ScheduleID as ScheduleID from (Select * from Football_Teams) as Team1, (Select * from Football_Teams) as Team2, Football_Schedule where Football_schedule.team1 = Team1.teamid AND Football_Schedule.team2 = Team2.teamid order by Football_Schedule.WeekNum, Team1.TeamName, Team2.Teamname", con)Using con

con.Open()

Dim RSAs SqlDataReader = cmd.ExecuteReader()

While RS.Read()

blah blah blah

End While

End Using

Then in my other spot on the page I have the following:

Dim cmdresultsAsNew SqlCommand("Select Users.Firstname, sum(PointsID) as TotalPoints from Football_Input, Football_Schedule, Users where Football_Input.TeamID = Football_Schedule.winID and users.userid = Football_Input.UserID Group by Users.firstname")

Using con

con.Open()

Dim RDAs SqlDataReader = cmdresults.ExecuteReader()

While RD.Read()%>

<tr>

<td>

<%=RD%>

</td>

</tr>

<%EndWhile

EndUsing

When I try to execute I get this error, "ExecuteReader: Connection property has not been initialized." on the following line, "

RDAs SqlDataReader = cmdresults.ExecuteReader()" Any ideas? If possible a little explanation on how multiple connections to the same database work would be nice just for future reference.

Thanks in advance!!,

Chris

I think you are not using separate file for the code behind.

fischecp:

Dim cmdAsNew SqlCommand("Select Team1.TeamID as Team1ID, Team1.TeamName as HomeTeam, Team2.TeamID as Team2ID, Team2.TeamName as AwayTeam, Football_Schedule.ScheduleID as ScheduleID from (Select * from Football_Teams) as Team1, (Select * from Football_Teams) as Team2, Football_Schedule where Football_schedule.team1 = Team1.teamid AND Football_Schedule.team2 = Team2.teamid order by Football_Schedule.WeekNum, Team1.TeamName, Team2.Teamname", con)

fischecp:

Dim cmdresultsAsNew SqlCommand("Select Users.Firstname, sum(PointsID) as TotalPoints from Football_Input, Football_Schedule, Users where Football_Input.TeamID = Football_Schedule.winID and users.userid = Football_Input.UserID Group by Users.firstname")

Did you observe that you haven't provided the connection for the second command ?

Even if you would have done so, unless you close the previous datareader you'll get another error when trying to bind the second datareader stating you already have a reader attached to this connection. One more thing, you're trying to open the same connection again in your second code. This will also result in an error stating the connection is already open.

Now, my advices to you are:

If possible, use code behind files. This way you will be able to differentiate your UI and Code.

In the code behind write small functions for the tasks you've mentioned above. Create a connection object in each of the function and dispose it before the code leaves the function.

You can use data binding techniques to bind the data returned in your datareader to any of the UI controls.

Hope this will help.

|||

fischecp:

Dim cmdresultsAsNew SqlCommand("Select Users.Firstname, sum(PointsID) as TotalPoints from Football_Input, Football_Schedule, Users where Football_Input.TeamID = Football_Schedule.winID and users.userid = Football_Input.UserID Group by Users.firstname")

i think you forgot to give "con" as SqlConnection in New SqlCommand arguments... as..

Dim cmdresultsAsNew SqlCommand("SelectUsers.Firstname, sum(PointsID) as TotalPoints from Football_Input,Football_Schedule, Users where Football_Input.TeamID =Football_Schedule.winID and users.userid = Football_Input.UserID Groupby Users.firstname",con)

also make sure you close the connection each time after operation completes.. and re-open for a new operation..

Good Luck./.

|||

Ok, I appreciate the help. At least my page runs without errors, however! Now I get the page to load but in place of the data from the database is, "System.Data.SqlClient.SqlDataReader" in every line where the information from the database should be displaying.

Here is my code that I fixed (At least I think I fixed haha)

con =New SqlConnection(conString)

Dim cmdresultsAsNew SqlCommand("Select Users.Firstname, sum(PointsID) as TotalPoints from Football_Input, Football_Schedule, Users where Football_Input.TeamID = Football_Schedule.winID and users.userid = Football_Input.UserID Group by Users.firstname", con)

Using con

con.Open()

Dim RDAs SqlDataReader = cmdresults.ExecuteReader()

While RD.Read()%>

<tr>

<td>

<%=RD%>

</td>

</tr>

<%EndWhile

RD.close()

con.close()

EndUsing

I also closed out my previous connection and datareader like you guys mentioned. I'll post the code for that just incase. And I will use functions to clean it all up. I just would like to see how it works first =)

Dim conStringAsString = WebConfigurationManager.ConnectionStrings("DataConn").ConnectionString

Dim conAsNew SqlConnection(conString)

Dim cmdAsNew SqlCommand("Select Team1.TeamID as Team1ID, Team1.TeamName as HomeTeam, Team2.TeamID as Team2ID, Team2.TeamName as AwayTeam, Football_Schedule.ScheduleID as ScheduleID from (Select * from Football_Teams) as Team1, (Select * from Football_Teams) as Team2, Football_Schedule where Football_schedule.team1 = Team1.teamid AND Football_Schedule.team2 = Team2.teamid order by Football_Schedule.WeekNum, Team1.TeamName, Team2.Teamname", con)Using con

con.Open()

Dim RSAs SqlDataReader = cmd.ExecuteReader()

While RS.Read()

EndWhile

con.Close()

RS.Close()

EndUsing

Any Ideas? Thanks again guys,

Chris

|||

<%=RD%>

You probably want to specify which field you are trying to display on the page. Like:

<%= RD.Item(0) %>

Or "columnname" instead of index.

|||

haha wow, I have had my head so deep into this I didn't even stop to think that. Sometimes you just need to take a step back =)

Thanks again guys! you solved everything!

sql

how to combine the data as one report (rdl) from accessing two data sources (ODBC)

Hi,
Does any one know how to combine the data as one report (rdl) from accessing
two data sources (ODBC) without using integration services?
any extend method? as I know one rdl should be able to connect to one data
source only. pleaae help & advise
For example:
SQL2005:
source 1: mssql 2000: employee table: employee id, employee name
source 2: Oracle: employee address table: employee id, employee address
I want join get the data from two sources such that a rdl report shows
(employee id, employee name, employee address). Of-course my real case is
quite complexOn Oct 8, 7:53 pm, "frankie lo" <frankiec...@.hotmail.com> wrote:
> Hi,
> Does any one know how to combine the data as one report (rdl) from accessing
> two data sources (ODBC) without using integration services?
> any extend method? as I know one rdl should be able to connect to one data
> source only. pleaae help & advise
> For example:
> SQL2005:
> source 1: mssql 2000: employee table: employee id, employee name
> source 2: Oracle: employee address table: employee id, employee address
> I want join get the data from two sources such that a rdl report shows
> (employee id, employee name, employee address). Of-course my real case is
> quite complex
I'm not sure if this will help, but you might try using a subreport
for a different datasource. That way a secondary report can have it's
own datasource and it can be included in the main report by way of a
subreport control. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks Enrique
But can I use source 1's employee id list as pararmeter for source 2?
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1191892063.275037.327290@.g4g2000hsf.googlegroups.com...
> On Oct 8, 7:53 pm, "frankie lo" <frankiec...@.hotmail.com> wrote:
>> Hi,
>> Does any one know how to combine the data as one report (rdl) from
>> accessing
>> two data sources (ODBC) without using integration services?
>> any extend method? as I know one rdl should be able to connect to one
>> data
>> source only. pleaae help & advise
>> For example:
>> SQL2005:
>> source 1: mssql 2000: employee table: employee id, employee name
>> source 2: Oracle: employee address table: employee id, employee address
>> I want join get the data from two sources such that a rdl report shows
>> (employee id, employee name, employee address). Of-course my real case is
>> quite complex
>
> I'm not sure if this will help, but you might try using a subreport
> for a different datasource. That way a secondary report can have it's
> own datasource and it can be included in the main report by way of a
> subreport control. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||First, just to clear up a misconception you have. A report can have multiple
datasources. What it cannot do is join those data sources. For instance,
let's say you have a parameter on a report. One data source returns a single
record. You could put that in a table or list or whatever. Next below that
you want a bunch of detail. For that you have a second dataset to a
different datasource. No problem whatsoever. However if you want master
detail of the sense that you have multiple masters and for each master
record you want to show detail then that cannot be done with a single
report. You have to use subreports. Master-detail reports are exactly for
what subreports are designed.
You need to understand two things about subreports. First, subreports are
just a report. Like all reports you can have report parameters (and it would
be a rare subreport that didn't). Design and test your subreport stand alone
and make sure it works.
The second thing to understand is the act of embedding and hooking up the
subreport. I just drag and drop the report previously created and tested
onto my main report. You can drag it onto a list. You can drag it onto a
cell of a table control. I do both. Then right mouse click onto the
subreport and map your parameters. You can map your parameters to a field of
a dataset, you can map them to a report parameter on the main report, you
can map them to an expression.
Subreports are very important in RS.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"frankie lo" <frankiecblo@.hotmail.com> wrote in message
news:OVq07ohCIHA.464@.TK2MSFTNGP02.phx.gbl...
> Thanks Enrique
> But can I use source 1's employee id list as pararmeter for source 2?
>
> "EMartinez" <emartinez.pr1@.gmail.com> wrote in message
> news:1191892063.275037.327290@.g4g2000hsf.googlegroups.com...
>> On Oct 8, 7:53 pm, "frankie lo" <frankiec...@.hotmail.com> wrote:
>> Hi,
>> Does any one know how to combine the data as one report (rdl) from
>> accessing
>> two data sources (ODBC) without using integration services?
>> any extend method? as I know one rdl should be able to connect to one
>> data
>> source only. pleaae help & advise
>> For example:
>> SQL2005:
>> source 1: mssql 2000: employee table: employee id, employee name
>> source 2: Oracle: employee address table: employee id, employee address
>> I want join get the data from two sources such that a rdl report shows
>> (employee id, employee name, employee address). Of-course my real case
>> is
>> quite complex
>>
>> I'm not sure if this will help, but you might try using a subreport
>> for a different datasource. That way a secondary report can have it's
>> own datasource and it can be included in the main report by way of a
>> subreport control. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>|||Hi Bruce,
Thanks. it becomes clear that i think I am able to do & try. You are very
cool. Thanks a lot.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:us0p%23inCIHA.3884@.TK2MSFTNGP05.phx.gbl...
> First, just to clear up a misconception you have. A report can have
> multiple datasources. What it cannot do is join those data sources. For
> instance, let's say you have a parameter on a report. One data source
> returns a single record. You could put that in a table or list or
> whatever. Next below that you want a bunch of detail. For that you have a
> second dataset to a different datasource. No problem whatsoever. However
> if you want master detail of the sense that you have multiple masters and
> for each master record you want to show detail then that cannot be done
> with a single report. You have to use subreports. Master-detail reports
> are exactly for what subreports are designed.
> You need to understand two things about subreports. First, subreports are
> just a report. Like all reports you can have report parameters (and it
> would be a rare subreport that didn't). Design and test your subreport
> stand alone and make sure it works.
> The second thing to understand is the act of embedding and hooking up the
> subreport. I just drag and drop the report previously created and tested
> onto my main report. You can drag it onto a list. You can drag it onto a
> cell of a table control. I do both. Then right mouse click onto the
> subreport and map your parameters. You can map your parameters to a field
> of a dataset, you can map them to a report parameter on the main report,
> you can map them to an expression.
> Subreports are very important in RS.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
> news:OVq07ohCIHA.464@.TK2MSFTNGP02.phx.gbl...
>> Thanks Enrique
>> But can I use source 1's employee id list as pararmeter for source 2?
>>
>> "EMartinez" <emartinez.pr1@.gmail.com> wrote in message
>> news:1191892063.275037.327290@.g4g2000hsf.googlegroups.com...
>> On Oct 8, 7:53 pm, "frankie lo" <frankiec...@.hotmail.com> wrote:
>> Hi,
>> Does any one know how to combine the data as one report (rdl) from
>> accessing
>> two data sources (ODBC) without using integration services?
>> any extend method? as I know one rdl should be able to connect to one
>> data
>> source only. pleaae help & advise
>> For example:
>> SQL2005:
>> source 1: mssql 2000: employee table: employee id, employee name
>> source 2: Oracle: employee address table: employee id, employee
>> address
>> I want join get the data from two sources such that a rdl report shows
>> (employee id, employee name, employee address). Of-course my real case
>> is
>> quite complex
>>
>> I'm not sure if this will help, but you might try using a subreport
>> for a different datasource. That way a secondary report can have it's
>> own datasource and it can be included in the main report by way of a
>> subreport control. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>>
>|||On Oct 9, 7:37 pm, "frankie lo" <frankiec...@.hotmail.com> wrote:
> Hi Bruce,
> Thanks. it becomes clear that i think I am able to do & try. You are very
> cool. Thanks a lot.
> "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com> wrote in messagenews:us0p%23inCIHA.3884@.TK2MSFTNGP05.phx.gbl...
> > First, just to clear up a misconception you have. A report can have
> > multiple datasources. What it cannot do is join those data sources. For
> > instance, let's say you have a parameter on a report. One data source
> > returns a single record. You could put that in a table or list or
> > whatever. Next below that you want a bunch of detail. For that you have a
> > second dataset to a different datasource. No problem whatsoever. However
> > if you want master detail of the sense that you have multiple masters and
> > for each master record you want to show detail then that cannot be done
> > with a single report. You have to use subreports. Master-detail reports
> > are exactly for what subreports are designed.
> > You need to understand two things about subreports. First, subreports are
> > just a report. Like all reports you can have report parameters (and it
> > would be a rare subreport that didn't). Design and test your subreport
> > stand alone and make sure it works.
> > The second thing to understand is the act of embedding and hooking up the
> > subreport. I just drag and drop the report previously created and tested
> > onto my main report. You can drag it onto a list. You can drag it onto a
> > cell of a table control. I do both. Then right mouse click onto the
> > subreport and map your parameters. You can map your parameters to a field
> > of a dataset, you can map them to a report parameter on the main report,
> > you can map them to an expression.
> > Subreports are very important in RS.
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> > "frankie lo" <frankiec...@.hotmail.com> wrote in message
> >news:OVq07ohCIHA.464@.TK2MSFTNGP02.phx.gbl...
> >> Thanks Enrique
> >> But can I use source 1's employee id list as pararmeter for source 2?
> >> "EMartinez" <emartinez...@.gmail.com> wrote in message
> >>news:1191892063.275037.327290@.g4g2000hsf.googlegroups.com...
> >> On Oct 8, 7:53 pm, "frankie lo" <frankiec...@.hotmail.com> wrote:
> >> Hi,
> >> Does any one know how to combine the data as one report (rdl) from
> >> accessing
> >> two data sources (ODBC) without using integration services?
> >> any extend method? as I know one rdl should be able to connect to one
> >> data
> >> source only. pleaae help & advise
> >> For example:
> >> SQL2005:
> >> source 1: mssql 2000: employee table: employee id, employee name
> >> source 2: Oracle: employee address table: employee id, employee
> >> address
> >> I want join get the data from two sources such that a rdl report shows
> >> (employee id, employee name, employee address). Of-course my real case
> >> is
> >> quite complex
> >> I'm not sure if this will help, but you might try using a subreport
> >> for a different datasource. That way a secondary report can have it's
> >> own datasource and it can be included in the main report by way of a
> >> subreport control. Hope this helps.
> >> Regards,
> >> Enrique Martinez
> >> Sr. Software Consultant
You're welcome. Also, one thing that Bruce did not mention. If you
want to reference a single value from one dataset (i.e., ds1) in
another (i.e., ds2) you can reference it via an aggregate expression
similar to the following:
=Max(Fields!employee_id.Value, "ds1")
Also, you could try having one of the datasets used as a hidden multi-
select parameter and then reference it from the other dataset via an
expression:
=Parameters!employee_id.Value
Hope this helps further clarify things for you.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi Enrique,
Thanks for your suggestion, I will try
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1191980383.493910.75050@.g4g2000hsf.googlegroups.com...
> On Oct 9, 7:37 pm, "frankie lo" <frankiec...@.hotmail.com> wrote:
>> Hi Bruce,
>> Thanks. it becomes clear that i think I am able to do & try. You are very
>> cool. Thanks a lot.
>> "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com> wrote in
>> messagenews:us0p%23inCIHA.3884@.TK2MSFTNGP05.phx.gbl...
>> > First, just to clear up a misconception you have. A report can have
>> > multiple datasources. What it cannot do is join those data sources. For
>> > instance, let's say you have a parameter on a report. One data source
>> > returns a single record. You could put that in a table or list or
>> > whatever. Next below that you want a bunch of detail. For that you have
>> > a
>> > second dataset to a different datasource. No problem whatsoever.
>> > However
>> > if you want master detail of the sense that you have multiple masters
>> > and
>> > for each master record you want to show detail then that cannot be done
>> > with a single report. You have to use subreports. Master-detail reports
>> > are exactly for what subreports are designed.
>> > You need to understand two things about subreports. First, subreports
>> > are
>> > just a report. Like all reports you can have report parameters (and it
>> > would be a rare subreport that didn't). Design and test your subreport
>> > stand alone and make sure it works.
>> > The second thing to understand is the act of embedding and hooking up
>> > the
>> > subreport. I just drag and drop the report previously created and
>> > tested
>> > onto my main report. You can drag it onto a list. You can drag it onto
>> > a
>> > cell of a table control. I do both. Then right mouse click onto the
>> > subreport and map your parameters. You can map your parameters to a
>> > field
>> > of a dataset, you can map them to a report parameter on the main
>> > report,
>> > you can map them to an expression.
>> > Subreports are very important in RS.
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> > "frankie lo" <frankiec...@.hotmail.com> wrote in message
>> >news:OVq07ohCIHA.464@.TK2MSFTNGP02.phx.gbl...
>> >> Thanks Enrique
>> >> But can I use source 1's employee id list as pararmeter for source 2?
>> >> "EMartinez" <emartinez...@.gmail.com> wrote in message
>> >>news:1191892063.275037.327290@.g4g2000hsf.googlegroups.com...
>> >> On Oct 8, 7:53 pm, "frankie lo" <frankiec...@.hotmail.com> wrote:
>> >> Hi,
>> >> Does any one know how to combine the data as one report (rdl) from
>> >> accessing
>> >> two data sources (ODBC) without using integration services?
>> >> any extend method? as I know one rdl should be able to connect to
>> >> one
>> >> data
>> >> source only. pleaae help & advise
>> >> For example:
>> >> SQL2005:
>> >> source 1: mssql 2000: employee table: employee id, employee name
>> >> source 2: Oracle: employee address table: employee id, employee
>> >> address
>> >> I want join get the data from two sources such that a rdl report
>> >> shows
>> >> (employee id, employee name, employee address). Of-course my real
>> >> case
>> >> is
>> >> quite complex
>> >> I'm not sure if this will help, but you might try using a subreport
>> >> for a different datasource. That way a secondary report can have it's
>> >> own datasource and it can be included in the main report by way of a
>> >> subreport control. Hope this helps.
>> >> Regards,
>> >> Enrique Martinez
>> >> Sr. Software Consultant
>
> You're welcome. Also, one thing that Bruce did not mention. If you
> want to reference a single value from one dataset (i.e., ds1) in
> another (i.e., ds2) you can reference it via an aggregate expression
> similar to the following:
> =Max(Fields!employee_id.Value, "ds1")
> Also, you could try having one of the datasets used as a hidden multi-
> select parameter and then reference it from the other dataset via an
> expression:
> =Parameters!employee_id.Value
> Hope this helps further clarify things for you.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Oct 10, 1:37 am, "frankie lo" <frankiec...@.hotmail.com> wrote:
> Hi Enrique,
> Thanks for your suggestion, I will try
> "EMartinez" <emartinez...@.gmail.com> wrote in message
> news:1191980383.493910.75050@.g4g2000hsf.googlegroups.com...
> > On Oct 9, 7:37 pm, "frankie lo" <frankiec...@.hotmail.com> wrote:
> >> Hi Bruce,
> >> Thanks. it becomes clear that i think I am able to do & try. You are very
> >> cool. Thanks a lot.
> >> "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com> wrote in
> >> messagenews:us0p%23inCIHA.3884@.TK2MSFTNGP05.phx.gbl...
> >> > First, just to clear up a misconception you have. A report can have
> >> > multiple datasources. What it cannot do is join those data sources. For
> >> > instance, let's say you have a parameter on a report. One data source
> >> > returns a single record. You could put that in a table or list or
> >> > whatever. Next below that you want a bunch of detail. For that you have
> >> > a
> >> > second dataset to a different datasource. No problem whatsoever.
> >> > However
> >> > if you want master detail of the sense that you have multiple masters
> >> > and
> >> > for each master record you want to show detail then that cannot be done
> >> > with a single report. You have to use subreports. Master-detail reports
> >> > are exactly for what subreports are designed.
> >> > You need to understand two things about subreports. First, subreports
> >> > are
> >> > just a report. Like all reports you can have report parameters (and it
> >> > would be a rare subreport that didn't). Design and test your subreport
> >> > stand alone and make sure it works.
> >> > The second thing to understand is the act of embedding and hooking up
> >> > the
> >> > subreport. I just drag and drop the report previously created and
> >> > tested
> >> > onto my main report. You can drag it onto a list. You can drag it onto
> >> > a
> >> > cell of a table control. I do both. Then right mouse click onto the
> >> > subreport and map your parameters. You can map your parameters to a
> >> > field
> >> > of a dataset, you can map them to a report parameter on the main
> >> > report,
> >> > you can map them to an expression.
> >> > Subreports are very important in RS.
> >> > --
> >> > Bruce Loehle-Conger
> >> > MVP SQL Server Reporting Services
> >> > "frankie lo" <frankiec...@.hotmail.com> wrote in message
> >> >news:OVq07ohCIHA.464@.TK2MSFTNGP02.phx.gbl...
> >> >> Thanks Enrique
> >> >> But can I use source 1's employee id list as pararmeter for source 2?
> >> >> "EMartinez" <emartinez...@.gmail.com> wrote in message
> >> >>news:1191892063.275037.327290@.g4g2000hsf.googlegroups.com...
> >> >> On Oct 8, 7:53 pm, "frankie lo" <frankiec...@.hotmail.com> wrote:
> >> >> Hi,
> >> >> Does any one know how to combine the data as one report (rdl) from
> >> >> accessing
> >> >> two data sources (ODBC) without using integration services?
> >> >> any extend method? as I know one rdl should be able to connect to
> >> >> one
> >> >> data
> >> >> source only. pleaae help & advise
> >> >> For example:
> >> >> SQL2005:
> >> >> source 1: mssql 2000: employee table: employee id, employee name
> >> >> source 2: Oracle: employee address table: employee id, employee
> >> >> address
> >> >> I want join get the data from two sources such that a rdl report
> >> >> shows
> >> >> (employee id, employee name, employee address). Of-course my real
> >> >> case
> >> >> is
> >> >> quite complex
> >> >> I'm not sure if this will help, but you might try using a subreport
> >> >> for a different datasource. That way a secondary report can have it's
> >> >> own datasource and it can be included in the main report by way of a
> >> >> subreport control. Hope this helps.
> >> >> Regards,
> >> >> Enrique Martinez
> >> >> Sr. Software Consultant
> > You're welcome. Also, one thing that Bruce did not mention. If you
> > want to reference a single value from one dataset (i.e., ds1) in
> > another (i.e., ds2) you can reference it via an aggregate expression
> > similar to the following:
> > =Max(Fields!employee_id.Value, "ds1")
> > Also, you could try having one of the datasets used as a hidden multi-
> > select parameter and then reference it from the other dataset via an
> > expression:
> > =Parameters!employee_id.Value
> > Hope this helps further clarify things for you.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Let me know if I can be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant