Reg: Exporting to CSV Format

Hi,
Is there any facility to restrict the dataset not to display the textbox
more than once when exporting to CSV format,when textbox value displays group
value(text).
Many ThanksPlease ignore this one.
prasad
"VP" wrote:
> Hi,
> Is there any facility to restrict the dataset not to display the textbox
> more than once when exporting to CSV format,when textbox value displays group
> value(text).
> Many Thanks
>
>
>|||Hi,
I want to create a CSV or comma delimited file from four MSSQL databases
running on one server. Can anyone assist me?
"VP" wrote:
> Hi,
> Is there any facility to restrict the dataset not to display the textbox
> more than once when exporting to CSV format,when textbox value displays group
> value(text).
> Many Thanks
>
>
>

refresh fields button does not work?

I have a dataset that calls a stored procedure. it's command type is set to
stored procedure.
The stored procedure returns 1 result set from a temp table (like select *
from #temp). In sql query analyzer it only returns 1 grid so I know it's not
returning multiple result sets.
when I hit the ! button I fill out my parameters and run the stored
procedure. It returns all the columns with some data. good. but the only
field I see is one called ID of type database field.
I hit the refresh fields button and nothing changes. The dataset is
returning 1 result set with the columns and data. WhyOWhy are the fields not
being filled out?What backend are you going against (SQL Server, OLEDB, ODBC?).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
news:1D8DF4D7-56D3-4A9A-923B-63A7F82CA886@.microsoft.com...
> I have a dataset that calls a stored procedure. it's command type is set
to
> stored procedure.
> The stored procedure returns 1 result set from a temp table (like select *
> from #temp). In sql query analyzer it only returns 1 grid so I know it's
not
> returning multiple result sets.
> when I hit the ! button I fill out my parameters and run the stored
> procedure. It returns all the columns with some data. good. but the only
> field I see is one called ID of type database field.
> I hit the refresh fields button and nothing changes. The dataset is
> returning 1 result set with the columns and data. WhyOWhy are the fields
not
> being filled out?|||I figured it out!
in my stored procedure I was doing this
create table [#whatever]
select * from #whatever
this runs fine from sql query analyzer but does not return a list of fields
in report designer gui.
but changing my stored procedure to
create table [#whatever]
select * from [#whatever]
fixed the problem. I now get the entire list of fields in the gui.
I'm not sure if I would call that a bug or not but it sure was anoying
trying to figure it out over the last several hours.
"Bruce L-C [MVP]" wrote:
> What backend are you going against (SQL Server, OLEDB, ODBC?).
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
> news:1D8DF4D7-56D3-4A9A-923B-63A7F82CA886@.microsoft.com...
> > I have a dataset that calls a stored procedure. it's command type is set
> to
> > stored procedure.
> >
> > The stored procedure returns 1 result set from a temp table (like select *
> > from #temp). In sql query analyzer it only returns 1 grid so I know it's
> not
> > returning multiple result sets.
> >
> > when I hit the ! button I fill out my parameters and run the stored
> > procedure. It returns all the columns with some data. good. but the only
> > field I see is one called ID of type database field.
> >
> > I hit the refresh fields button and nothing changes. The dataset is
> > returning 1 result set with the columns and data. WhyOWhy are the fields
> not
> > being filled out?
>
>|||Hmmm, you must have had some special characters in it. I create temporary
tables and do a select * from it without have to put [] around it. I'll
remember that though since from time to time I have been unable to help
people who do not get the field list. Learn something new every day.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
news:6D9998C0-B7E2-4900-A800-B93504EF8EF6@.microsoft.com...
> I figured it out!
> in my stored procedure I was doing this
> create table [#whatever]
> select * from #whatever
> this runs fine from sql query analyzer but does not return a list of
fields
> in report designer gui.
> but changing my stored procedure to
> create table [#whatever]
> select * from [#whatever]
> fixed the problem. I now get the entire list of fields in the gui.
> I'm not sure if I would call that a bug or not but it sure was anoying
> trying to figure it out over the last several hours.
>
> "Bruce L-C [MVP]" wrote:
> > What backend are you going against (SQL Server, OLEDB, ODBC?).
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
> > news:1D8DF4D7-56D3-4A9A-923B-63A7F82CA886@.microsoft.com...
> > > I have a dataset that calls a stored procedure. it's command type is
set
> > to
> > > stored procedure.
> > >
> > > The stored procedure returns 1 result set from a temp table (like
select *
> > > from #temp). In sql query analyzer it only returns 1 grid so I know
it's
> > not
> > > returning multiple result sets.
> > >
> > > when I hit the ! button I fill out my parameters and run the stored
> > > procedure. It returns all the columns with some data. good. but the
only
> > > field I see is one called ID of type database field.
> > >
> > > I hit the refresh fields button and nothing changes. The dataset is
> > > returning 1 result set with the columns and data. WhyOWhy are the
fields
> > not
> > > being filled out?
> >
> >
> >|||all right I take it back after more testing I can reproduce this behavior but
I was not aware it worked like this.
forget what I said before it was just a fluke I must have made a mistake
while testing it.
here is the reproducable behavior.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE
dbo.whatever
@.param_me int
as
-- end results temp table
create table [#whatever]
(
[key] int not null,
)
if (@.param_me is null)
begin
select 'how did you get here?'
end
else
begin
insert into [#whatever]([key])values(1)
insert into [#whatever]([key])values(2)
insert into [#whatever]([key])values(3)
end
select * from [#whatever]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
create a dataset that calls whatever passing a param value
it looks like even though the select 'how did you get here?' never gets run
it somehow gets returned as the first result set?
bruce could you give this a whirl and see if you see the same result? I
would appreciate it thanks.
"Bruce L-C [MVP]" wrote:
> Hmmm, you must have had some special characters in it. I create temporary
> tables and do a select * from it without have to put [] around it. I'll
> remember that though since from time to time I have been unable to help
> people who do not get the field list. Learn something new every day.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
> news:6D9998C0-B7E2-4900-A800-B93504EF8EF6@.microsoft.com...
> > I figured it out!
> >
> > in my stored procedure I was doing this
> > create table [#whatever]
> > select * from #whatever
> >
> > this runs fine from sql query analyzer but does not return a list of
> fields
> > in report designer gui.
> >
> > but changing my stored procedure to
> > create table [#whatever]
> > select * from [#whatever]
> >
> > fixed the problem. I now get the entire list of fields in the gui.
> >
> > I'm not sure if I would call that a bug or not but it sure was anoying
> > trying to figure it out over the last several hours.
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > What backend are you going against (SQL Server, OLEDB, ODBC?).
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
> > > news:1D8DF4D7-56D3-4A9A-923B-63A7F82CA886@.microsoft.com...
> > > > I have a dataset that calls a stored procedure. it's command type is
> set
> > > to
> > > > stored procedure.
> > > >
> > > > The stored procedure returns 1 result set from a temp table (like
> select *
> > > > from #temp). In sql query analyzer it only returns 1 grid so I know
> it's
> > > not
> > > > returning multiple result sets.
> > > >
> > > > when I hit the ! button I fill out my parameters and run the stored
> > > > procedure. It returns all the columns with some data. good. but the
> only
> > > > field I see is one called ID of type database field.
> > > >
> > > > I hit the refresh fields button and nothing changes. The dataset is
> > > > returning 1 result set with the columns and data. WhyOWhy are the
> fields
> > > not
> > > > being filled out?
> > >
> > >
> > >
>
>|||I think what is happening is that it is considering the first select to be
the first return result. You could temporarily remove that part, get your
field list and design your report and then put it back in.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
news:BE164DC8-D6C8-4601-8C3B-E10B1B07B6F3@.microsoft.com...
> all right I take it back after more testing I can reproduce this behavior
> but
> I was not aware it worked like this.
> forget what I said before it was just a fluke I must have made a mistake
> while testing it.
> here is the reproducable behavior.
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> CREATE PROCEDURE
> dbo.whatever
> @.param_me int
> as
> -- end results temp table
> create table [#whatever]
> (
> [key] int not null,
> )
> if (@.param_me is null)
> begin
> select 'how did you get here?'
> end
> else
> begin
> insert into [#whatever]([key])values(1)
> insert into [#whatever]([key])values(2)
> insert into [#whatever]([key])values(3)
> end
> select * from [#whatever]
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> create a dataset that calls whatever passing a param value
> it looks like even though the select 'how did you get here?' never gets
> run
> it somehow gets returned as the first result set?
> bruce could you give this a whirl and see if you see the same result? I
> would appreciate it thanks.
>
> "Bruce L-C [MVP]" wrote:
>> Hmmm, you must have had some special characters in it. I create temporary
>> tables and do a select * from it without have to put [] around it. I'll
>> remember that though since from time to time I have been unable to help
>> people who do not get the field list. Learn something new every day.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
>> news:6D9998C0-B7E2-4900-A800-B93504EF8EF6@.microsoft.com...
>> > I figured it out!
>> >
>> > in my stored procedure I was doing this
>> > create table [#whatever]
>> > select * from #whatever
>> >
>> > this runs fine from sql query analyzer but does not return a list of
>> fields
>> > in report designer gui.
>> >
>> > but changing my stored procedure to
>> > create table [#whatever]
>> > select * from [#whatever]
>> >
>> > fixed the problem. I now get the entire list of fields in the gui.
>> >
>> > I'm not sure if I would call that a bug or not but it sure was anoying
>> > trying to figure it out over the last several hours.
>> >
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> > > What backend are you going against (SQL Server, OLEDB, ODBC?).
>> > >
>> > > --
>> > > Bruce Loehle-Conger
>> > > MVP SQL Server Reporting Services
>> > >
>> > > "letuce dance" <letucedance@.discussions.microsoft.com> wrote in
>> > > message
>> > > news:1D8DF4D7-56D3-4A9A-923B-63A7F82CA886@.microsoft.com...
>> > > > I have a dataset that calls a stored procedure. it's command type
>> > > > is
>> set
>> > > to
>> > > > stored procedure.
>> > > >
>> > > > The stored procedure returns 1 result set from a temp table (like
>> select *
>> > > > from #temp). In sql query analyzer it only returns 1 grid so I
>> > > > know
>> it's
>> > > not
>> > > > returning multiple result sets.
>> > > >
>> > > > when I hit the ! button I fill out my parameters and run the stored
>> > > > procedure. It returns all the columns with some data. good. but
>> > > > the
>> only
>> > > > field I see is one called ID of type database field.
>> > > >
>> > > > I hit the refresh fields button and nothing changes. The dataset
>> > > > is
>> > > > returning 1 result set with the columns and data. WhyOWhy are the
>> fields
>> > > not
>> > > > being filled out?
>> > >
>> > >
>> > >
>>|||Yes I think thatâ's become obvious; the first result set that is tripping the
refresh fields button up. Because if I give the select 'how did you get
here?' as error_column. I see error_column in the list of fields, even
though that result set did not get returned and the data for the other result
set is really being displayed.
It's just confusing. All of the tools I'm using query analyzer, query
designer don't return this result set when I run the stored procedure with a
param value other than null.
Whatever the refresh fields button does it's not refresh fields, it's should
be called "return first potential result field list regardless of what
columns are really returned"
It's not a show stopper there are lots of easy work a rounds. It just sucks
using a version 1 product with so many of these little time wasters. But I
already know you think rs is super duper, I don't think so just yet.
try using rs.exe to create a datasource, publish a report and set the
published report datasource in your rs.exe script. It does not work I opened
up a support case 2 weeks ago with microsoft and they are still trying to
figure out why it does not work. lots of little time wasters.
ok I'm done complaining now.
"Bruce L-C [MVP]" wrote:
> I think what is happening is that it is considering the first select to be
> the first return result. You could temporarily remove that part, get your
> field list and design your report and then put it back in.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
> news:BE164DC8-D6C8-4601-8C3B-E10B1B07B6F3@.microsoft.com...
> > all right I take it back after more testing I can reproduce this behavior
> > but
> > I was not aware it worked like this.
> >
> > forget what I said before it was just a fluke I must have made a mistake
> > while testing it.
> >
> > here is the reproducable behavior.
> >
> > SET QUOTED_IDENTIFIER OFF
> > GO
> > SET ANSI_NULLS ON
> > GO
> >
> > CREATE PROCEDURE
> > dbo.whatever
> > @.param_me int
> >
> > as
> >
> > -- end results temp table
> > create table [#whatever]
> > (
> > [key] int not null,
> > )
> >
> > if (@.param_me is null)
> > begin
> > select 'how did you get here?'
> > end
> > else
> > begin
> > insert into [#whatever]([key])values(1)
> > insert into [#whatever]([key])values(2)
> > insert into [#whatever]([key])values(3)
> > end
> > select * from [#whatever]
> >
> > GO
> > SET QUOTED_IDENTIFIER OFF
> > GO
> > SET ANSI_NULLS ON
> > GO
> >
> > create a dataset that calls whatever passing a param value
> >
> > it looks like even though the select 'how did you get here?' never gets
> > run
> > it somehow gets returned as the first result set?
> >
> > bruce could you give this a whirl and see if you see the same result? I
> > would appreciate it thanks.
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Hmmm, you must have had some special characters in it. I create temporary
> >> tables and do a select * from it without have to put [] around it. I'll
> >> remember that though since from time to time I have been unable to help
> >> people who do not get the field list. Learn something new every day.
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
> >> news:6D9998C0-B7E2-4900-A800-B93504EF8EF6@.microsoft.com...
> >> > I figured it out!
> >> >
> >> > in my stored procedure I was doing this
> >> > create table [#whatever]
> >> > select * from #whatever
> >> >
> >> > this runs fine from sql query analyzer but does not return a list of
> >> fields
> >> > in report designer gui.
> >> >
> >> > but changing my stored procedure to
> >> > create table [#whatever]
> >> > select * from [#whatever]
> >> >
> >> > fixed the problem. I now get the entire list of fields in the gui.
> >> >
> >> > I'm not sure if I would call that a bug or not but it sure was anoying
> >> > trying to figure it out over the last several hours.
> >> >
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> > > What backend are you going against (SQL Server, OLEDB, ODBC?).
> >> > >
> >> > > --
> >> > > Bruce Loehle-Conger
> >> > > MVP SQL Server Reporting Services
> >> > >
> >> > > "letuce dance" <letucedance@.discussions.microsoft.com> wrote in
> >> > > message
> >> > > news:1D8DF4D7-56D3-4A9A-923B-63A7F82CA886@.microsoft.com...
> >> > > > I have a dataset that calls a stored procedure. it's command type
> >> > > > is
> >> set
> >> > > to
> >> > > > stored procedure.
> >> > > >
> >> > > > The stored procedure returns 1 result set from a temp table (like
> >> select *
> >> > > > from #temp). In sql query analyzer it only returns 1 grid so I
> >> > > > know
> >> it's
> >> > > not
> >> > > > returning multiple result sets.
> >> > > >
> >> > > > when I hit the ! button I fill out my parameters and run the stored
> >> > > > procedure. It returns all the columns with some data. good. but
> >> > > > the
> >> only
> >> > > > field I see is one called ID of type database field.
> >> > > >
> >> > > > I hit the refresh fields button and nothing changes. The dataset
> >> > > > is
> >> > > > returning 1 result set with the columns and data. WhyOWhy are the
> >> fields
> >> > > not
> >> > > > being filled out?
> >> > >
> >> > >
> >> > >
> >>
> >>
> >>
>
>|||Glad you got it to work out. Yes I am a supporter of RS but I don't deny
there are things that need to get better (it is version 1). That said, I
think any product or development work has these types of frustrations. I
just spent a day mucking around with linked databases and finally found that
the issue comes down to the oledb/odbc provider for Sybase. So, day wasted
and figure out some other way to do it. I just try to get people to be able
to get done what they are trying to do.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
news:D3C917F1-06B6-4B03-92DC-7D6D261EFAC4@.microsoft.com...
> Yes I think that's become obvious; the first result set that is tripping
the
> refresh fields button up. Because if I give the select 'how did you get
> here?' as error_column. I see error_column in the list of fields, even
> though that result set did not get returned and the data for the other
result
> set is really being displayed.
> It's just confusing. All of the tools I'm using query analyzer, query
> designer don't return this result set when I run the stored procedure with
a
> param value other than null.
> Whatever the refresh fields button does it's not refresh fields, it's
should
> be called "return first potential result field list regardless of what
> columns are really returned"
> It's not a show stopper there are lots of easy work a rounds. It just
sucks
> using a version 1 product with so many of these little time wasters. But
I
> already know you think rs is super duper, I don't think so just yet.
> try using rs.exe to create a datasource, publish a report and set the
> published report datasource in your rs.exe script. It does not work I
opened
> up a support case 2 weeks ago with microsoft and they are still trying to
> figure out why it does not work. lots of little time wasters.
> ok I'm done complaining now.
>
> "Bruce L-C [MVP]" wrote:
> > I think what is happening is that it is considering the first select to
be
> > the first return result. You could temporarily remove that part, get
your
> > field list and design your report and then put it back in.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
> > news:BE164DC8-D6C8-4601-8C3B-E10B1B07B6F3@.microsoft.com...
> > > all right I take it back after more testing I can reproduce this
behavior
> > > but
> > > I was not aware it worked like this.
> > >
> > > forget what I said before it was just a fluke I must have made a
mistake
> > > while testing it.
> > >
> > > here is the reproducable behavior.
> > >
> > > SET QUOTED_IDENTIFIER OFF
> > > GO
> > > SET ANSI_NULLS ON
> > > GO
> > >
> > > CREATE PROCEDURE
> > > dbo.whatever
> > > @.param_me int
> > >
> > > as
> > >
> > > -- end results temp table
> > > create table [#whatever]
> > > (
> > > [key] int not null,
> > > )
> > >
> > > if (@.param_me is null)
> > > begin
> > > select 'how did you get here?'
> > > end
> > > else
> > > begin
> > > insert into [#whatever]([key])values(1)
> > > insert into [#whatever]([key])values(2)
> > > insert into [#whatever]([key])values(3)
> > > end
> > > select * from [#whatever]
> > >
> > > GO
> > > SET QUOTED_IDENTIFIER OFF
> > > GO
> > > SET ANSI_NULLS ON
> > > GO
> > >
> > > create a dataset that calls whatever passing a param value
> > >
> > > it looks like even though the select 'how did you get here?' never
gets
> > > run
> > > it somehow gets returned as the first result set?
> > >
> > > bruce could you give this a whirl and see if you see the same result?
I
> > > would appreciate it thanks.
> > >
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > >> Hmmm, you must have had some special characters in it. I create
temporary
> > >> tables and do a select * from it without have to put [] around it.
I'll
> > >> remember that though since from time to time I have been unable to
help
> > >> people who do not get the field list. Learn something new every day.
> > >>
> > >> --
> > >> Bruce Loehle-Conger
> > >> MVP SQL Server Reporting Services
> > >>
> > >> "letuce dance" <letucedance@.discussions.microsoft.com> wrote in
message
> > >> news:6D9998C0-B7E2-4900-A800-B93504EF8EF6@.microsoft.com...
> > >> > I figured it out!
> > >> >
> > >> > in my stored procedure I was doing this
> > >> > create table [#whatever]
> > >> > select * from #whatever
> > >> >
> > >> > this runs fine from sql query analyzer but does not return a list
of
> > >> fields
> > >> > in report designer gui.
> > >> >
> > >> > but changing my stored procedure to
> > >> > create table [#whatever]
> > >> > select * from [#whatever]
> > >> >
> > >> > fixed the problem. I now get the entire list of fields in the gui.
> > >> >
> > >> > I'm not sure if I would call that a bug or not but it sure was
anoying
> > >> > trying to figure it out over the last several hours.
> > >> >
> > >> >
> > >> > "Bruce L-C [MVP]" wrote:
> > >> >
> > >> > > What backend are you going against (SQL Server, OLEDB, ODBC?).
> > >> > >
> > >> > > --
> > >> > > Bruce Loehle-Conger
> > >> > > MVP SQL Server Reporting Services
> > >> > >
> > >> > > "letuce dance" <letucedance@.discussions.microsoft.com> wrote in
> > >> > > message
> > >> > > news:1D8DF4D7-56D3-4A9A-923B-63A7F82CA886@.microsoft.com...
> > >> > > > I have a dataset that calls a stored procedure. it's command
type
> > >> > > > is
> > >> set
> > >> > > to
> > >> > > > stored procedure.
> > >> > > >
> > >> > > > The stored procedure returns 1 result set from a temp table
(like
> > >> select *
> > >> > > > from #temp). In sql query analyzer it only returns 1 grid so I
> > >> > > > know
> > >> it's
> > >> > > not
> > >> > > > returning multiple result sets.
> > >> > > >
> > >> > > > when I hit the ! button I fill out my parameters and run the
stored
> > >> > > > procedure. It returns all the columns with some data. good.
but
> > >> > > > the
> > >> only
> > >> > > > field I see is one called ID of type database field.
> > >> > > >
> > >> > > > I hit the refresh fields button and nothing changes. The
dataset
> > >> > > > is
> > >> > > > returning 1 result set with the columns and data. WhyOWhy are
the
> > >> fields
> > >> > > not
> > >> > > > being filled out?
> > >> > >
> > >> > >
> > >> > >
> > >>
> > >>
> > >>
> >
> >
> >

Referencing the temporary Dataset in Lookup control SQL

Does anyone know how to reference the ongoing dataset within the dataflow in sql?

So here is the scenario I have.

1) OLE DB SOURCE
2) Lookup a value and that column gets added to the dataset
3) Lookup another value but this time I would rather code the sql rather than select a referencing table. How do I reference the current dataset?

Thanks,
ChestonI'm not clear on what you are trying to do.

You can use SQL in a lookup transformation. You can even build a lookup table with literal values in SQL in that lookup transformation without hitting a table.|||Let me see if I can be more clear on what I am trying to do.

Table 1 - Ole DB source control
Lookup 1 - Reference another table and based on keys add a foreign key
Lookup 2 - I wish to perform a sql statement to update the newly added foreign key from lookup 1 to a default value if it is null. How do I reference the dataset?

So in the Lookup 2 I select SQL query:
Update dataset set foreign key = 0 where foreign key is null

Then continue on with the dataset with more lookups/transformations.

Does this help?

Cheston|||Use a derived column transformation to set that column to 0 if it is null.

Referencing field name from different dataset?

Is it possible to use one dataset to reference fields from another dataset
for Reporting Services?
Here's the example for ASP:
Dataset 1:
SELECT CUST_ID, EMP_ID, SALES_ID
FROM CUSTOMER, EMPLOYEE, SALES
WHERE CUSTOMER.CUST_ID = '3232'
ORDER BY CUST_ID
The second query will display the Sales_Amount base on the object from the
first query.
Dataset 2:
SELECT TOP 1 SALES_AMOUNT
FROM CUSTOMER, EMPLOYEE, SALES
WHERE CUSTOMER.CUST_ID = '"&CUST_ID&"'
AND EMPLOYEE.EMP_ID = '"&EMP_ID&"'
AND SALES.ID = '"&SALES_ID&"'
I have a number of reports, migrating from ASP to Reporting Services, and
was wondering if this is possible with RS.
Use the first dataset as is and second dataset. I was thinking about
referencing it like this:
=IIF((Fields!Cust_ID.Value & "Cust_ID").Value AND (Fields!Emp_ID.Value &
"Emp_ID").Value AND (Fields!ID.Value & "Sales_ID").Value),
Fields!Sales_Amount.Value, "N/A")
Intended output:
CustID EmpID SalesID SalesAmount
1 2 3232 $345.00
2 2 3643 $223.00
3 6 8772 $1234.54
4 6 N/A
So Dataset 2 will check the fields and see if there's a match, if it does
then output Sales_Amount base on the output fields. Please advise on what to
do or suggestions.
Thanks.You can not do it the way you are envisioning it but it can be done. The
reason you can't do it the way you are thinking is because RS does not joing
datasets. You can have multiple datasets but they are independent of one
another. What you can do is have two reports. A main and a subreport (a
subreport is a normal report with a parameter). In your case the subreport
would have three parameters. Design the first report, then add a column to
the right of the ones you have (right mouse click, add column). Drop your
subreport into the column. Set the parameters to the fields (right mouse
click on the subreport to do this).
So, to do this make sure you understand report parameters, query parameters,
subreports. It will all fall together for you once you understand those
three things.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"chang" <chang@.discussions.microsoft.com> wrote in message
news:4E26F8E7-F8DB-4177-A967-5FF1178B71FB@.microsoft.com...
> Is it possible to use one dataset to reference fields from another dataset
> for Reporting Services?
> Here's the example for ASP:
> Dataset 1:
> SELECT CUST_ID, EMP_ID, SALES_ID
> FROM CUSTOMER, EMPLOYEE, SALES
> WHERE CUSTOMER.CUST_ID = '3232'
> ORDER BY CUST_ID
> The second query will display the Sales_Amount base on the object from the
> first query.
> Dataset 2:
> SELECT TOP 1 SALES_AMOUNT
> FROM CUSTOMER, EMPLOYEE, SALES
> WHERE CUSTOMER.CUST_ID = '"&CUST_ID&"'
> AND EMPLOYEE.EMP_ID = '"&EMP_ID&"'
> AND SALES.ID = '"&SALES_ID&"'
> I have a number of reports, migrating from ASP to Reporting Services, and
> was wondering if this is possible with RS.
> Use the first dataset as is and second dataset. I was thinking about
> referencing it like this:
> =IIF((Fields!Cust_ID.Value & "Cust_ID").Value AND (Fields!Emp_ID.Value &
> "Emp_ID").Value AND (Fields!ID.Value & "Sales_ID").Value),
> Fields!Sales_Amount.Value, "N/A")
> Intended output:
> CustID EmpID SalesID SalesAmount
> 1 2 3232 $345.00
> 2 2 3643 $223.00
> 3 6 8772 $1234.54
> 4 6 N/A
>
> So Dataset 2 will check the fields and see if there's a match, if it does
> then output Sales_Amount base on the output fields. Please advise on what
to
> do or suggestions.
> Thanks.|||Thank you Bruce. I think what you suggested might be what I need. I've been
pondering this for several weeks now and can't seem to get it to work.
For the subreport, do I need to create another report for that or just the
second dataset?
"Bruce L-C [MVP]" wrote:
> You can not do it the way you are envisioning it but it can be done. The
> reason you can't do it the way you are thinking is because RS does not joing
> datasets. You can have multiple datasets but they are independent of one
> another. What you can do is have two reports. A main and a subreport (a
> subreport is a normal report with a parameter). In your case the subreport
> would have three parameters. Design the first report, then add a column to
> the right of the ones you have (right mouse click, add column). Drop your
> subreport into the column. Set the parameters to the fields (right mouse
> click on the subreport to do this).
> So, to do this make sure you understand report parameters, query parameters,
> subreports. It will all fall together for you once you understand those
> three things.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "chang" <chang@.discussions.microsoft.com> wrote in message
> news:4E26F8E7-F8DB-4177-A967-5FF1178B71FB@.microsoft.com...
> > Is it possible to use one dataset to reference fields from another dataset
> > for Reporting Services?
> >
> > Here's the example for ASP:
> >
> > Dataset 1:
> >
> > SELECT CUST_ID, EMP_ID, SALES_ID
> > FROM CUSTOMER, EMPLOYEE, SALES
> > WHERE CUSTOMER.CUST_ID = '3232'
> > ORDER BY CUST_ID
> >
> > The second query will display the Sales_Amount base on the object from the
> > first query.
> >
> > Dataset 2:
> > SELECT TOP 1 SALES_AMOUNT
> > FROM CUSTOMER, EMPLOYEE, SALES
> > WHERE CUSTOMER.CUST_ID = '"&CUST_ID&"'
> > AND EMPLOYEE.EMP_ID = '"&EMP_ID&"'
> > AND SALES.ID = '"&SALES_ID&"'
> >
> > I have a number of reports, migrating from ASP to Reporting Services, and
> > was wondering if this is possible with RS.
> >
> > Use the first dataset as is and second dataset. I was thinking about
> > referencing it like this:
> >
> > =IIF((Fields!Cust_ID.Value & "Cust_ID").Value AND (Fields!Emp_ID.Value &
> > "Emp_ID").Value AND (Fields!ID.Value & "Sales_ID").Value),
> > Fields!Sales_Amount.Value, "N/A")
> >
> > Intended output:
> > CustID EmpID SalesID SalesAmount
> > 1 2 3232 $345.00
> > 2 2 3643 $223.00
> > 3 6 8772 $1234.54
> > 4 6 N/A
> >
> >
> > So Dataset 2 will check the fields and see if there's a match, if it does
> > then output Sales_Amount base on the output fields. Please advise on what
> to
> > do or suggestions.
> >
> > Thanks.
>
>|||A subreport is just a standard report. Create it by itself and test it out.
Since you want to embed the subreport you should make it very simple. Then
you just drag and drop it into an empty column in a table. Then right click
on it and set the parameters. So, the second dataset is only in the
subreport. You main report only has the one dataset. In certain cases
subreports are really the only way to solve the problem and it can be very
clean way to do so.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"chang" <chang@.discussions.microsoft.com> wrote in message
news:7D5B2A51-394C-4542-8B1A-B47D11BD4305@.microsoft.com...
> Thank you Bruce. I think what you suggested might be what I need. I've
been
> pondering this for several weeks now and can't seem to get it to work.
> For the subreport, do I need to create another report for that or just the
> second dataset?
> "Bruce L-C [MVP]" wrote:
> > You can not do it the way you are envisioning it but it can be done. The
> > reason you can't do it the way you are thinking is because RS does not
joing
> > datasets. You can have multiple datasets but they are independent of one
> > another. What you can do is have two reports. A main and a subreport (a
> > subreport is a normal report with a parameter). In your case the
subreport
> > would have three parameters. Design the first report, then add a column
to
> > the right of the ones you have (right mouse click, add column). Drop
your
> > subreport into the column. Set the parameters to the fields (right mouse
> > click on the subreport to do this).
> >
> > So, to do this make sure you understand report parameters, query
parameters,
> > subreports. It will all fall together for you once you understand those
> > three things.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "chang" <chang@.discussions.microsoft.com> wrote in message
> > news:4E26F8E7-F8DB-4177-A967-5FF1178B71FB@.microsoft.com...
> > > Is it possible to use one dataset to reference fields from another
dataset
> > > for Reporting Services?
> > >
> > > Here's the example for ASP:
> > >
> > > Dataset 1:
> > >
> > > SELECT CUST_ID, EMP_ID, SALES_ID
> > > FROM CUSTOMER, EMPLOYEE, SALES
> > > WHERE CUSTOMER.CUST_ID = '3232'
> > > ORDER BY CUST_ID
> > >
> > > The second query will display the Sales_Amount base on the object from
the
> > > first query.
> > >
> > > Dataset 2:
> > > SELECT TOP 1 SALES_AMOUNT
> > > FROM CUSTOMER, EMPLOYEE, SALES
> > > WHERE CUSTOMER.CUST_ID = '"&CUST_ID&"'
> > > AND EMPLOYEE.EMP_ID = '"&EMP_ID&"'
> > > AND SALES.ID = '"&SALES_ID&"'
> > >
> > > I have a number of reports, migrating from ASP to Reporting Services,
and
> > > was wondering if this is possible with RS.
> > >
> > > Use the first dataset as is and second dataset. I was thinking about
> > > referencing it like this:
> > >
> > > =IIF((Fields!Cust_ID.Value & "Cust_ID").Value AND (Fields!Emp_ID.Value
&
> > > "Emp_ID").Value AND (Fields!ID.Value & "Sales_ID").Value),
> > > Fields!Sales_Amount.Value, "N/A")
> > >
> > > Intended output:
> > > CustID EmpID SalesID SalesAmount
> > > 1 2 3232 $345.00
> > > 2 2 3643 $223.00
> > > 3 6 8772 $1234.54
> > > 4 6 N/A
> > >
> > >
> > > So Dataset 2 will check the fields and see if there's a match, if it
does
> > > then output Sales_Amount base on the output fields. Please advise on
what
> > to
> > > do or suggestions.
> > >
> > > Thanks.
> >
> >
> >|||Thank you Bruce. That was most helpful. I was able to get it working.
Should've post it here weeks ago and got this resolve, but I was trying to
work on my own. This only took me 30 minute to get it working instead of
what took me 2 weeks.
Thanks again.
"Bruce L-C [MVP]" wrote:
> A subreport is just a standard report. Create it by itself and test it out.
> Since you want to embed the subreport you should make it very simple. Then
> you just drag and drop it into an empty column in a table. Then right click
> on it and set the parameters. So, the second dataset is only in the
> subreport. You main report only has the one dataset. In certain cases
> subreports are really the only way to solve the problem and it can be very
> clean way to do so.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "chang" <chang@.discussions.microsoft.com> wrote in message
> news:7D5B2A51-394C-4542-8B1A-B47D11BD4305@.microsoft.com...
> > Thank you Bruce. I think what you suggested might be what I need. I've
> been
> > pondering this for several weeks now and can't seem to get it to work.
> >
> > For the subreport, do I need to create another report for that or just the
> > second dataset?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > You can not do it the way you are envisioning it but it can be done. The
> > > reason you can't do it the way you are thinking is because RS does not
> joing
> > > datasets. You can have multiple datasets but they are independent of one
> > > another. What you can do is have two reports. A main and a subreport (a
> > > subreport is a normal report with a parameter). In your case the
> subreport
> > > would have three parameters. Design the first report, then add a column
> to
> > > the right of the ones you have (right mouse click, add column). Drop
> your
> > > subreport into the column. Set the parameters to the fields (right mouse
> > > click on the subreport to do this).
> > >
> > > So, to do this make sure you understand report parameters, query
> parameters,
> > > subreports. It will all fall together for you once you understand those
> > > three things.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > >
> > > "chang" <chang@.discussions.microsoft.com> wrote in message
> > > news:4E26F8E7-F8DB-4177-A967-5FF1178B71FB@.microsoft.com...
> > > > Is it possible to use one dataset to reference fields from another
> dataset
> > > > for Reporting Services?
> > > >
> > > > Here's the example for ASP:
> > > >
> > > > Dataset 1:
> > > >
> > > > SELECT CUST_ID, EMP_ID, SALES_ID
> > > > FROM CUSTOMER, EMPLOYEE, SALES
> > > > WHERE CUSTOMER.CUST_ID = '3232'
> > > > ORDER BY CUST_ID
> > > >
> > > > The second query will display the Sales_Amount base on the object from
> the
> > > > first query.
> > > >
> > > > Dataset 2:
> > > > SELECT TOP 1 SALES_AMOUNT
> > > > FROM CUSTOMER, EMPLOYEE, SALES
> > > > WHERE CUSTOMER.CUST_ID = '"&CUST_ID&"'
> > > > AND EMPLOYEE.EMP_ID = '"&EMP_ID&"'
> > > > AND SALES.ID = '"&SALES_ID&"'
> > > >
> > > > I have a number of reports, migrating from ASP to Reporting Services,
> and
> > > > was wondering if this is possible with RS.
> > > >
> > > > Use the first dataset as is and second dataset. I was thinking about
> > > > referencing it like this:
> > > >
> > > > =IIF((Fields!Cust_ID.Value & "Cust_ID").Value AND (Fields!Emp_ID.Value
> &
> > > > "Emp_ID").Value AND (Fields!ID.Value & "Sales_ID").Value),
> > > > Fields!Sales_Amount.Value, "N/A")
> > > >
> > > > Intended output:
> > > > CustID EmpID SalesID SalesAmount
> > > > 1 2 3232 $345.00
> > > > 2 2 3643 $223.00
> > > > 3 6 8772 $1234.54
> > > > 4 6 N/A
> > > >
> > > >
> > > > So Dataset 2 will check the fields and see if there's a match, if it
> does
> > > > then output Sales_Amount base on the output fields. Please advise on
> what
> > > to
> > > > do or suggestions.
> > > >
> > > > Thanks.
> > >
> > >
> > >
>
>

Reference to a second dataset in a second matrix within one report

Hello Everyone,
I am attempting to create a report that contains two matrices. Each matrix will use it's own dataset. My problem is on the second matrix...I am unable to fiture out how to code the Value Property to refer to the field without First or Sum. When I drag and drop the fields into the cells, they appear like this;
=First(Fields!ReportRow.Value, "Claims")
or
=Sum(Fields!ReportRowSeq.Value, "Claims")
Is there any way I can get the field only without an aggregate?
Something like;
=Fields!ReportRowSeq.Value, "Claims"?
Please help, I am new to Reporting Services and appreciate any help you can offer!
Thank You, Tin
From http://www.developmentnow.com/g/115_2007_2_0_0_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comOn Feb 23, 4:43 pm, Tina Wolf<Tina_W...@.countrywide.com> wrote:
> Hello Everyone,
> I am attempting to create a report that contains two matrices. Each matrix will use it's own dataset. My problem is on the second matrix...I am unable to fiture out how to code the Value Property to refer to the field without First or Sum. When I drag and drop the fields into the cells, they appear like this;
> =First(Fields!ReportRow.Value, "Claims")
> or
> =Sum(Fields!ReportRowSeq.Value, "Claims")
> Is there any way I can get the field only without an aggregate?
> Something like;
> =Fields!ReportRowSeq.Value, "Claims"?
> Please help, I am new to Reporting Services and appreciate any help you can offer!
> Thank You, Tina
> Fromhttp://www.developmentnow.com/g/115_2007_2_0_0_0/sql-server-reporting...
> Posted via DevelopmentNow.com Groupshttp://www.developmentnow.com
You should be able to just use: 'Fields!ReportRowSeq.Value' as long as
the dataset 'Claims' is used as the main one in the matrix, it should
work out fine. If you get unexpected results, you will need to re-
evaluate the query you are using for the matrix. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||In a matrix, the content cells are aggregates, grouped by the row and column
headers. That is why the detail cell contents want to be sum, or first or
max...
I don't think it would work any other way.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Tina Wolf" wrote:
> Hello Everyone,
> I am attempting to create a report that contains two matrices. Each matrix will use it's own dataset. My problem is on the second matrix...I am unable to fiture out how to code the Value Property to refer to the field without First or Sum. When I drag and drop the fields into the cells, they appear like this;
> =First(Fields!ReportRow.Value, "Claims")
> or
> =Sum(Fields!ReportRowSeq.Value, "Claims")
> Is there any way I can get the field only without an aggregate?
> Something like;
> =Fields!ReportRowSeq.Value, "Claims"?
> Please help, I am new to Reporting Services and appreciate any help you can offer!
> Thank You, Tina
> From http://www.developmentnow.com/g/115_2007_2_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
>|||Thank you! I have now changed my code to use only one dataset. This has brought up another issue for me. I will ultimately have 5 matrices on this report and will have all the data within one dataset, but each matrix needs to use only it's data. How do I code an expression to pull only specific data from the dataset for each matrix
From http://www.developmentnow.com/groups/viewthread.aspx?newsgroupid=115&threadid=93631
Posted via DevelopmentNow.com Group
http://www.developmentnow.com|||On Feb 26, 4:36 pm, Tina Wolf<tina_w...@.countrywide.com> wrote:
> Thank you! I have now changed my code to use only one dataset. This has brought up another issue for me. I will ultimately have 5 matrices on this report and will have all the data within one dataset, but each matrix needs to use only it's data. How do I code an expression to pull only specific data from the dataset for each matrix?
> Fromhttp://www.developmentnow.com/groups/viewthread.aspx?newsgroupid=115&...
> Posted via DevelopmentNow.com Groupshttp://www.developmentnow.com
If there is a distinguishing factor within the single dataset, you
should be able to use a different filter for each matrix. Of course,
you could always use separate datasets as well, since you can
reference datasets outside of the location that they are used (in the
same report).
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Thank you, Enrique!
The filter works perfectly! I really appreciate your help!
Regards, Tin
From http://www.developmentnow.com/groups/viewthread.aspx?newsgroupid=115&threadid=93631
Posted via DevelopmentNow.com Group
http://www.developmentnow.com

Reference the reports DataSet from Code

Hi All,

From my reports code I would like to do some things with the reports DataSet. Is it possible to reference this object from within the report code?

Thanks,
Eric

http://msdn2.microsoft.com/en-us/library/ms157274.aspx

Reference Empty Data Set

I have a dataset that sometimes is empty. I have it as a detail row in a table on the report, but since the dataset is null the row does not appear. What I would like to do is have it say "No results" if there are no results, otherwise have it show the results.

I tried to add a textbox and reference the textbox on the table, but it was a no go.

Here is what I had in my textbox

<code>
=code.noRows(ReportItems!table8!textbox30)
</code>

and this is what I was using in the code

<code>
Function noRows(byVal result) as string
if result = "" then
noRows = "NO Results"
else
noRows = result
end if
end function
</code>

Can anyone assist me with how to do this?

Thanks

Try this:

Data regions (matrix, table, list, chart) have a so-called NoRows property. If set, this is the message to display in a textbox (that replaces the data region when no rows of data are available).

Note: the NoRows property is available in the VS properties window.

-- Robert

|||When I use that my header disappears. Is there a way around this?
I need the "No Results" to appear on the detail line and the header to stay regardless.

Thanks

|||

No. If you use the NoRows message property, it will replace the entire table.

-- Robert

Reducing 5 values to 1 value

I am trying to take values that exist in a single column and create a column
elsewhere that contains comma separated values of that dataset.
The '5' is arbitrary...
For example,
Greg
John
Bill
Carol
Judy
Julie
Would look like
Greg, John, Bill, Carol, Judy, Julie
Appreciate in advance any thoughts....
GregA way, maybe not the best way
DECLARE @.name varchar(512)
DECLARE @.list varchar(512)
SET @.name = ''
SET @.list = ''
DECLARE names_cursor STATIC FORWARD_ONLY
FOR
SELECT DISTINCT [name] FROM [YourTable]
OPEN names_cursor
FETCH NEXT FROM names_cursor INTO @.name
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
SET @.list = @.list + @.name
FETCH NEXT FROM names_cursor INTO @.name
END
INSERT INTO [OtherTable] VALUES (@.list)
CLOSE names_cursor
DEALLOCATE names_cursor
Look at
http://www.microsoft.com/technet/co...r />
931d-e02a
cbb88505
for a trigger that I wrote that would keep those two tables syncrhonized.
"Greg" wrote:

> I am trying to take values that exist in a single column and create a colu
mn
> elsewhere that contains comma separated values of that dataset.
> The '5' is arbitrary...
> For example,
> Greg
> John
> Bill
> Carol
> Judy
> Julie
> Would look like
> Greg, John, Bill, Carol, Judy, Julie
> Appreciate in advance any thoughts....
> Greg
>|||Ahh I asked this one the other day...
See http://www.aspfaq.com/show.asp?id=2529
Or see this post..
http://groups.google.co.uk/group/mi...7f3f3d7de2dd4fa
Barry