Datasets

When using datasets in reports, is it possible to reuse them in other
reports? For example a generic dataset that returns all employee names to
use in parameters. Another report needs the same parameter and we need to
create another dataset. Is there some mechanism to share the datasets
across reports?
This is regarding Reporting Services on SQL 2000.
ThanksCreate a sproc with parms?
--
William Stacey [MVP]
"isicoder" <isicoder@.newsgroups.nospam> wrote in message
news:OxiAWUqIGHA.1388@.TK2MSFTNGP11.phx.gbl...
| When using datasets in reports, is it possible to reuse them in other
| reports? For example a generic dataset that returns all employee names to
| use in parameters. Another report needs the same parameter and we need to
| create another dataset. Is there some mechanism to share the datasets
| across reports?
|
| This is regarding Reporting Services on SQL 2000.
|
| Thanks
|
||||Yes we do have a stored proc that has params. I was wondering about the
dataset within the report itself? Having two reports that share the same
proc (via the dataset) to get a lookup is one way (and possibly the only
way) to do it, but what about sharing the same dataset within the report
designer? I don't think MS SQL reporting services is set up to do this but
I'd like to be wrong to minimize the amount of coding I need to do for each
report.
As I'm getting up and running, I imagine one day we will be generating some
or all of our reports from code and generating the RDL on the fly. As this
question pertains to SQL Reporting 2000 we are a bit behind the times but it
would be nice to know for either version (SQL 2000 or SQL 2005) as we will
one day migrate to 2005.
Thanks
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:OX5J5tqIGHA.2320@.TK2MSFTNGP11.phx.gbl...
> Create a sproc with parms?
> --
> William Stacey [MVP]
> "isicoder" <isicoder@.newsgroups.nospam> wrote in message
> news:OxiAWUqIGHA.1388@.TK2MSFTNGP11.phx.gbl...
> | When using datasets in reports, is it possible to reuse them in other
> | reports? For example a generic dataset that returns all employee names
> to
> | use in parameters. Another report needs the same parameter and we need
> to
> | create another dataset. Is there some mechanism to share the datasets
> | across reports?
> |
> | This is regarding Reporting Services on SQL 2000.
> |
> | Thanks
> |
> |
>|||From what I've found, you can't reuse datasets, unfortunately. You're not
the first one to ask...
Stored procedures are the best way to go, but unfortunately you can't have a
list reusable datasets. That would have been great...
Kaisa M. Lindahl
"isicoder" <isicoder@.newsgroups.nospam> wrote in message
news:%23M28JR5IGHA.3224@.TK2MSFTNGP09.phx.gbl...
> Yes we do have a stored proc that has params. I was wondering about the
> dataset within the report itself? Having two reports that share the same
> proc (via the dataset) to get a lookup is one way (and possibly the only
> way) to do it, but what about sharing the same dataset within the report
> designer? I don't think MS SQL reporting services is set up to do this
> but I'd like to be wrong to minimize the amount of coding I need to do for
> each report.
> As I'm getting up and running, I imagine one day we will be generating
> some or all of our reports from code and generating the RDL on the fly.
> As this question pertains to SQL Reporting 2000 we are a bit behind the
> times but it would be nice to know for either version (SQL 2000 or SQL
> 2005) as we will one day migrate to 2005.
> Thanks
>
> "William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
> news:OX5J5tqIGHA.2320@.TK2MSFTNGP11.phx.gbl...
>> Create a sproc with parms?
>> --
>> William Stacey [MVP]
>> "isicoder" <isicoder@.newsgroups.nospam> wrote in message
>> news:OxiAWUqIGHA.1388@.TK2MSFTNGP11.phx.gbl...
>> | When using datasets in reports, is it possible to reuse them in other
>> | reports? For example a generic dataset that returns all employee names
>> to
>> | use in parameters. Another report needs the same parameter and we need
>> to
>> | create another dataset. Is there some mechanism to share the datasets
>> | across reports?
>> |
>> | This is regarding Reporting Services on SQL 2000.
>> |
>> | Thanks
>> |
>> |
>>
>

DataSet Accessing more than one database

Is there a way we can create a dataset that extracts data from more than one
database?
Example:
SELECT d.field1, u.field1
FROM Databse1.dbo.tbl_Test d, Databse2.dbo.tbl_User u
WHERE d.ID= u.IDyou could create a VIEW in database1 that does a select * from
database2.dbo.tbl_user
that way from the report server point of view they both come from the
same database
NJ wrote:
> Is there a way we can create a dataset that extracts data from more than one
> database?
> Example:
> SELECT d.field1, u.field1
> FROM Databse1.dbo.tbl_Test d, Databse2.dbo.tbl_User u
> WHERE d.ID= u.ID

DataSet . . .?

For the my report in dataset i am using union. For example:
select * from tblOne
Union All
select * from tblTwo
Union All
select * from tblThree
Union All
select * from tblFour
But when i view the report it shows in different group order
select * from tblOne
Union All
select * from tblTwo
Union All
select * from tblFour
Union All
select * from tblThree
What do i need to do, so the way i wrote the code that i would like to see
groups?
Each select statement has column with group name.
Thanks.something like: (?)
select 'one' as GroupName, * from tblOne
Union All
select 'two' as GroupName, * from tblTwo
Union All
select 'four' as GroupName, * from tblFour
Union All
select 'three' as GroupName, * from tblThree
"GGill" <GGill@.discussions.microsoft.com> wrote in message
news:822E4C84-A22B-456C-AA23-3A14C122197D@.microsoft.com...
> For the my report in dataset i am using union. For example:
> select * from tblOne
> Union All
> select * from tblTwo
> Union All
> select * from tblThree
> Union All
> select * from tblFour
> But when i view the report it shows in different group order
> select * from tblOne
> Union All
> select * from tblTwo
> Union All
> select * from tblFour
> Union All
> select * from tblThree
> What do i need to do, so the way i wrote the code that i would like to see
> groups?
> Each select statement has column with group name.
> Thanks.|||I do not have GroupName column with one, two . . .
What i am looking for is show data the way i wrote the code. Each select, in
this case i have four has product name. So i would like to show way is in
dataset.
"Jeje" wrote:
> something like: (?)
> select 'one' as GroupName, * from tblOne
> Union All
> select 'two' as GroupName, * from tblTwo
> Union All
> select 'four' as GroupName, * from tblFour
> Union All
> select 'three' as GroupName, * from tblThree
>
> "GGill" <GGill@.discussions.microsoft.com> wrote in message
> news:822E4C84-A22B-456C-AA23-3A14C122197D@.microsoft.com...
> > For the my report in dataset i am using union. For example:
> >
> > select * from tblOne
> > Union All
> > select * from tblTwo
> > Union All
> > select * from tblThree
> > Union All
> > select * from tblFour
> >
> > But when i view the report it shows in different group order
> >
> > select * from tblOne
> > Union All
> > select * from tblTwo
> > Union All
> > select * from tblFour
> > Union All
> > select * from tblThree
> >
> > What do i need to do, so the way i wrote the code that i would like to see
> > groups?
> > Each select statement has column with group name.
> >
> > Thanks.
>|||GGill,
I think it is obvious that 'the way you wrote the code' is not going to
give you the answer you want. What JeJe was suggesting was to simply add a
user defined expression to each SELECT statement to make it sort the way you
want. Such as :
SELECT '1' as SortOrder, table1.* FROM table1
UNION ALL
SELECT '2' as SortOrder, table2.* FROM table2
UNION ALL
SELECT '3' as SortOrder, table3.* FROM table3
ORDER BY SortOrder
the field "SortOrder" doesn't need to be in your table..it is an expression.
Michael C
"GGill" wrote:
> I do not have GroupName column with one, two . . .
> What i am looking for is show data the way i wrote the code. Each select, in
> this case i have four has product name. So i would like to show way is in
> dataset.
> "Jeje" wrote:
> > something like: (?)
> > select 'one' as GroupName, * from tblOne
> > Union All
> > select 'two' as GroupName, * from tblTwo
> > Union All
> > select 'four' as GroupName, * from tblFour
> > Union All
> > select 'three' as GroupName, * from tblThree
> >
> >
> > "GGill" <GGill@.discussions.microsoft.com> wrote in message
> > news:822E4C84-A22B-456C-AA23-3A14C122197D@.microsoft.com...
> > > For the my report in dataset i am using union. For example:
> > >
> > > select * from tblOne
> > > Union All
> > > select * from tblTwo
> > > Union All
> > > select * from tblThree
> > > Union All
> > > select * from tblFour
> > >
> > > But when i view the report it shows in different group order
> > >
> > > select * from tblOne
> > > Union All
> > > select * from tblTwo
> > > Union All
> > > select * from tblFour
> > > Union All
> > > select * from tblThree
> > >
> > > What do i need to do, so the way i wrote the code that i would like to see
> > > groups?
> > > Each select statement has column with group name.
> > >
> > > Thanks.
> >

Datagram message

My app was working fine (no change in the last couple of years) until
recently.
Here is an example of my XML -
<root
xmlns:updg="urn:schemas-microsoft-com:xml-updategram"><updg:sync><updg:before><ProbePart
VFPID="28724"/></updg:before><updg:after><ProbePart
ReconSell="5520"/></updg:after></updg:sync></root>
which results in the Response -
<H3>ERROR: 400.100 Bad Request</H3><b>HResult:</b>
0x80004005<br><b>Source:</b> Microsoft SQL isapi
extension<br><b>Description:</b> Query not specified<br>
Not sure where to look for resolution - any pointers would be appreciated.
Could you please provide some more information? Like what your setup is and
a simple complete repro?
Thanks
Michael
"howard" <howard@.discussions.microsoft.com> wrote in message
news:1B14174A-7CC0-4B78-BB8E-15EC906834BE@.microsoft.com...
> My app was working fine (no change in the last couple of years) until
> recently.
> Here is an example of my XML -
> <root
> xmlns:updg="urn:schemas-microsoft-com:xml-updategram"><updg:sync><updg:before><ProbePart
> VFPID="28724"/></updg:before><updg:after><ProbePart
> ReconSell="5520"/></updg:after></updg:sync></root>
> which results in the Response -
> <H3>ERROR: 400.100 Bad Request</H3><b>HResult:</b>
> 0x80004005<br><b>Source:</b> Microsoft SQL isapi
> extension<br><b>Description:</b> Query not specified<br>
> Not sure where to look for resolution - any pointers would be appreciated.
>
>

Dataflow where sourcecolumns vary ?

Is it possible to set up at dataflow with for example an excel source that points to a sheet with varying number of colums.

In the file there is some information about number of columns. But the question is here how you use that information to define the columns in the dataflow ?

The columns in the data-flow need to be known at design-time so by and large you cannot chage things at runtime.

There is a workaround though - you can change a package that you are about to execute using the execute package task. Here's how courtesy of Kirk Haselden: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/12/31/17731.aspx

-Jamie

database: mssql

I am conducting a database design and I managed to reach the 3rd normal form. My question is, if i change my primary key, for example '0001' to '0002', is it possible to propagate the change/update to the other linked table.
if you require more info, please let me know. Thank you in advance.If I understand correctly then yes -
If you have 2 tables:
Customer - With Field CustomerId
And Order with Field OrderId and CustomerId
And you want to change the CustomerId Field then you would do the following:
SET IDENTITY_INSERT [dbo].[Customer] ON
GO
ALTER TABLE [dbo].[Order] NOCHECK CONSTRAINT ALL
GO
-- Add a column to hold the old ID
ALTER TABLE CUSTOMER WITH CHECK ADD OldID INT
GO
UPDATE CUSTOMER SET OldID = CustomerId
GO
-- Add Code to Change CustomerId Here
UPDATE O
Set O.CustomerId = C.CustomerId
From Order O
INNER JOIN Customer C
ON O.CustomerId = C.OldId
GO
SET IDENTITY_INSERT [dbo].[Customer] OFF
GO
ALTER TABLE [dbo].[Order] CHECK CONSTRAINT ALL
GO
|||

You can use declarative referential integrity and cascade updates popagate the changes. Sample code below.

Regards

set nocount on
go

use tempdb
go

create table parent(p int constraint p_pk primary key)
go

create table child(
c int constraint c_pk primary key
, p int constraint c_fk foreign key references parent(p) on update cascade
)
go

insert into parent values (1)
go
insert into child values (1,1)
go

update parent set p = 2 where p = 1
go

select * from child
go

drop table child
go
drop table parent
go

|||Hi,
In addition to that. If your using sql2k you could just create a diagram and link the tables that you want in Enterprise Manager...
cheers,
Paul June A. Domag