A Few Questions

We are looking at RS2005 as a possible reporting tool, and trying to figure
out a few things with not much luck, so I'll try asking here. The questions:
1. How can you hit multiple data sources within one data set?
We have cases where we want to take information from two different
databases, say a MS SQL and an Oracle, and join information from these two
into one dataset, is this possible and if so how?
2. How do you a new field within the report set?
Say in one of the fields (call it field x) being returned you want to create
another field (call it field y) and populate it with a certain set of
values. For example, if field x = "A" then we want to populate field y with
a value of 10. If field x = "F" then we want to populate field y with a
value of 15.
3. How do you deal with multiple selects in one statement. Example TYPE in
(10,20,30,70,90)?
In Reporting services you would have to use multiple OR statements. This
would be bad if we had a lot of other inclusions or exclusions.
ThanksOn Mar 16, 9:22 am, "Nobody" <trin...@.nobody.com> wrote:
> We are looking at RS2005 as a possible reporting tool, and trying to figure
> out a few things with not much luck, so I'll try asking here. The questions:
> 1. How can you hit multiple data sources within one data set?
> We have cases where we want to take information from two different
> databases, say a MS SQL and an Oracle, and join information from these two
> into one dataset, is this possible and if so how?
> 2. How do you a new field within the report set?
> Say in one of the fields (call it field x) being returned you want to create
> another field (call it field y) and populate it with a certain set of
> values. For example, if field x = "A" then we want to populate field y with
> a value of 10. If field x = "F" then we want to populate field y with a
> value of 15.
> 3. How do you deal with multiple selects in one statement. Example TYPE in
> (10,20,30,70,90)?
> In Reporting services you would have to use multiple OR statements. This
> would be bad if we had a lot of other inclusions or exclusions.
> Thanks
1. Refer to Amarnath's response on a previous post concerning this
question:
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/9dc8aa59b6f9271f/d54db9f92d839b88?lnk=gst&q=oracle+sql+server&rnum=2#d54db9f92d839b88
2. For field y, in Layout view, select field y, right-click it and
select 'Expression...' in the Edit Expression dialog, enter in either
of the following:
=switch(Fields!x.Value = "A", 10, Fields!x.Value = "F", 15, true, 0)
-or-
=iif(Fields!x.Value = "A", 10, 15)
3. This question is not very clear to me. So I'll do the best I can
here:
In the report query the SQL syntax should be: select ... from ...
where TYPE in (10,20,30,70,90)
In the report in general, a multi-select parameter can be used for the
TYPE options and use a temporary table, while loop (or cursor) and
keyword 'patindex' (in the report stored procedure/query) to parse the
values in the multi-select parameter that is returned. The results
should be put into a temp table and then 'select ... from ... where
TYPE in (select TYPE from #TempTable)' should do the trick.
Hope this is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Thanks, I'll pass that on to the person here that had those questions.
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1174103874.201065.41030@.n76g2000hsh.googlegroups.com...
> 1. Refer to Amarnath's response on a previous post concerning this
> question:
> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/9dc8aa59b6f9271f/d54db9f92d839b88?lnk=gst&q=oracle+sql+server&rnum=2#d54db9f92d839b88
> 2. For field y, in Layout view, select field y, right-click it and
> select 'Expression...' in the Edit Expression dialog, enter in either
> of the following:
> =switch(Fields!x.Value = "A", 10, Fields!x.Value = "F", 15, true, 0)
> -or-
> =iif(Fields!x.Value = "A", 10, 15)
> 3. This question is not very clear to me. So I'll do the best I can
> here:
> In the report query the SQL syntax should be: select ... from ...
> where TYPE in (10,20,30,70,90)
> In the report in general, a multi-select parameter can be used for the
> TYPE options and use a temporary table, while loop (or cursor) and
> keyword 'patindex' (in the report stored procedure/query) to parse the
> values in the multi-select parameter that is returned. The results
> should be put into a temp table and then 'select ... from ... where
> TYPE in (select TYPE from #TempTable)' should do the trick.
> Hope this is helpful.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>