Is it possible to get a sub total for a Top N filter?
I've got a group that contains that contains a top N filter.
This filters properly for example, I see only 3 rows per group.
However, I cannot get a sub total for only those 3 rows. It calculates
all
of the rows with the main group.
How would I get the sum for only those 3 rows that are being displayed
on
the report.hi,
keep a visibility condition for other subtotals. i mean to say to
select each three records you would have taken mod ,right? take
absolute value now for not showing other totals
some thing like this
Rownumber(Isnothing)\3<2 such that it will be invisible for that row
Thank you
Raj Deep.A
Showing posts with label group. Show all posts
Showing posts with label group. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
Get 1st Value in Group
I have a grouping view and I want to be able to get the 1st value in a
column. In Access I would use First(colname) but I couldn't fing anything
similar in BOL. Thanks.
DavidIn SQL Server there is no direct equivalent of the Access FIRST and
LAST
functions. A table in SQL is not logically ordered so there is no first
or
last row - you have to specify the column/expression that determines
which
rows you want.
Here's an example taken from the Pubs database giving the first and
last
title based on the MIN and MAX values of Title_id.
SELECT
(SELECT title
FROM Titles
WHERE title_id
= (SELECT MIN(title_id)
FROM Titles)) AS First_Title,
(SELECT title
FROM Titles
WHERE title_id
= (SELECT MAX(title_id)
FROM Titles)) AS Last_Title
A common requirement is to extract a single row of a group based on
some
criteria. The query below retrieves values from the "Last" (maximum
title_id) row for each Type in the Titles table.
SELECT T1.type, T1.title_id, T1.title, T1.price
FROM titles AS T1
JOIN
(SELECT MAX(title_id) AS title_id
FROM titles
GROUP BY type) AS T2
ON T1.title_id = T2.title_id
If you need to retrieve the first or last rows based on the order in
which
they were entered into a table then you need to add a DATETIME column
to the
table to record the entry date:
date_created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
David Portas
SQL Server MVP
--|||FIRST has no meaning in a relational database. Maybe you are looking for
MIN or MAX. Or, if you show your table structure, sample data and desired
results (see http://www.aspfaq.com/5006), someone can give you more explicit
help.
http://www.aspfaq.com/
(Reverse address to reply.)
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:OYJV5aSGFHA.428@.TK2MSFTNGP15.phx.gbl...
> I have a grouping view and I want to be able to get the 1st value in a
> column. In Access I would use First(colname) but I couldn't fing anything
> similar in BOL. Thanks.
> David
>|||David C wrote:
> I have a grouping view and I want to be able to get the 1st value in a
> column. In Access I would use First(colname) but I couldn't fing
> anything similar in BOL. Thanks.
> David
What do you mean "First value in a column"?
A column only contains a single value. Are you dealing with some sort of
delimited set of values stored in a single column? If so, you can use
CHARINDEX() function to look for the delimiter you use to delimit the
"words" and then use SUBSTRING() to pull it out.
Probably better to do this on the client, though.
Can you post your DDL and example data.
David Gugick
Imceda Software
www.imceda.com|||This is what I am using.
SELECT TOP 100 PERCENT PersonID, MIN(DISTINCT HistoryText) AS
FirstOfHistoryText
FROM dbo.HistoryDates
WHERE (DateCode = N'H')
GROUP BY PersonID
ORDER BY MAX(HistoryDate) DESC
I thought that it would give me the HistoryText value for the PersonID
for the latest HistoryDate with DateCode = 'H' for that PersonID.
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Try:
SELECT personid, historytext
FROM dbo.HistoryDates AS H
WHERE datecode = N'H'
AND historydate =
(SELECT MAX(historydate)
FROM dbo.HistoryDates
WHERE personid = H.personid)
However, if you have duplicate values in the HistoryDate column that
might return more than one row for a given PersonId. Maybe that's not
what you want, in which case you might do:
SELECT personid, MIN(historytext)
FROM dbo.HistoryDates AS H
WHERE datecode = N'H'
AND historydate =
(SELECT MAX(historydate)
FROM dbo.HistoryDates
WHERE personid = H.personid)
GROUP BY personid
Hopefully you can see now that this won't return the "first" or "last"
of anything - tables have no fixed concept of order. These queries
return the row(s) for the maximum date. In the case of the second
query, you'll get, at most, one row per PersonId with the
MIN(historytext) value.
David Portas
SQL Server MVP
--|||Yes, there will be multiples for any PersonID. I want to get the text
and date from the MAX(HistoryDate) for each PersonID. I will try your
last suggestion.
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||David,
I think you have the N'H' conditions in the wrong place.
Your query might return nothing for some personid, if
for the latest historydate it is not the case that datecode = N'H',
even if that person does have some rows where datecode = N'H'
I think (but am not sure) that David wants exactly one
result row for every personid with any N'H' rows, and the
one he wants is the latest of those N'H' rows.
If (personid, historydate) is unique, you can just move the
(datecode = N'H') condition into the subquery. If only
(personid, historydate, datecode) is unique, then it should
be in both the main query and the subquery.
Steve Kass
Drew University
David Portas wrote:
>Try:
>SELECT personid, historytext
> FROM dbo.HistoryDates AS H
> WHERE datecode = N'H'
> AND historydate =
> (SELECT MAX(historydate)
> FROM dbo.HistoryDates
> WHERE personid = H.personid)
>However, if you have duplicate values in the HistoryDate column that
>might return more than one row for a given PersonId. Maybe that's not
>what you want, in which case you might do:
>SELECT personid, MIN(historytext)
> FROM dbo.HistoryDates AS H
> WHERE datecode = N'H'
> AND historydate =
> (SELECT MAX(historydate)
> FROM dbo.HistoryDates
> WHERE personid = H.personid)
> GROUP BY personid
>Hopefully you can see now that this won't return the "first" or "last"
>of anything - tables have no fixed concept of order. These queries
>return the row(s) for the maximum date. In the case of the second
>query, you'll get, at most, one row per PersonId with the
>MIN(historytext) value.
>
>
column. In Access I would use First(colname) but I couldn't fing anything
similar in BOL. Thanks.
DavidIn SQL Server there is no direct equivalent of the Access FIRST and
LAST
functions. A table in SQL is not logically ordered so there is no first
or
last row - you have to specify the column/expression that determines
which
rows you want.
Here's an example taken from the Pubs database giving the first and
last
title based on the MIN and MAX values of Title_id.
SELECT
(SELECT title
FROM Titles
WHERE title_id
= (SELECT MIN(title_id)
FROM Titles)) AS First_Title,
(SELECT title
FROM Titles
WHERE title_id
= (SELECT MAX(title_id)
FROM Titles)) AS Last_Title
A common requirement is to extract a single row of a group based on
some
criteria. The query below retrieves values from the "Last" (maximum
title_id) row for each Type in the Titles table.
SELECT T1.type, T1.title_id, T1.title, T1.price
FROM titles AS T1
JOIN
(SELECT MAX(title_id) AS title_id
FROM titles
GROUP BY type) AS T2
ON T1.title_id = T2.title_id
If you need to retrieve the first or last rows based on the order in
which
they were entered into a table then you need to add a DATETIME column
to the
table to record the entry date:
date_created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
David Portas
SQL Server MVP
--|||FIRST has no meaning in a relational database. Maybe you are looking for
MIN or MAX. Or, if you show your table structure, sample data and desired
results (see http://www.aspfaq.com/5006), someone can give you more explicit
help.
http://www.aspfaq.com/
(Reverse address to reply.)
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:OYJV5aSGFHA.428@.TK2MSFTNGP15.phx.gbl...
> I have a grouping view and I want to be able to get the 1st value in a
> column. In Access I would use First(colname) but I couldn't fing anything
> similar in BOL. Thanks.
> David
>|||David C wrote:
> I have a grouping view and I want to be able to get the 1st value in a
> column. In Access I would use First(colname) but I couldn't fing
> anything similar in BOL. Thanks.
> David
What do you mean "First value in a column"?
A column only contains a single value. Are you dealing with some sort of
delimited set of values stored in a single column? If so, you can use
CHARINDEX() function to look for the delimiter you use to delimit the
"words" and then use SUBSTRING() to pull it out.
Probably better to do this on the client, though.
Can you post your DDL and example data.
David Gugick
Imceda Software
www.imceda.com|||This is what I am using.
SELECT TOP 100 PERCENT PersonID, MIN(DISTINCT HistoryText) AS
FirstOfHistoryText
FROM dbo.HistoryDates
WHERE (DateCode = N'H')
GROUP BY PersonID
ORDER BY MAX(HistoryDate) DESC
I thought that it would give me the HistoryText value for the PersonID
for the latest HistoryDate with DateCode = 'H' for that PersonID.
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Try:
SELECT personid, historytext
FROM dbo.HistoryDates AS H
WHERE datecode = N'H'
AND historydate =
(SELECT MAX(historydate)
FROM dbo.HistoryDates
WHERE personid = H.personid)
However, if you have duplicate values in the HistoryDate column that
might return more than one row for a given PersonId. Maybe that's not
what you want, in which case you might do:
SELECT personid, MIN(historytext)
FROM dbo.HistoryDates AS H
WHERE datecode = N'H'
AND historydate =
(SELECT MAX(historydate)
FROM dbo.HistoryDates
WHERE personid = H.personid)
GROUP BY personid
Hopefully you can see now that this won't return the "first" or "last"
of anything - tables have no fixed concept of order. These queries
return the row(s) for the maximum date. In the case of the second
query, you'll get, at most, one row per PersonId with the
MIN(historytext) value.
David Portas
SQL Server MVP
--|||Yes, there will be multiples for any PersonID. I want to get the text
and date from the MAX(HistoryDate) for each PersonID. I will try your
last suggestion.
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||David,
I think you have the N'H' conditions in the wrong place.
Your query might return nothing for some personid, if
for the latest historydate it is not the case that datecode = N'H',
even if that person does have some rows where datecode = N'H'
I think (but am not sure) that David wants exactly one
result row for every personid with any N'H' rows, and the
one he wants is the latest of those N'H' rows.
If (personid, historydate) is unique, you can just move the
(datecode = N'H') condition into the subquery. If only
(personid, historydate, datecode) is unique, then it should
be in both the main query and the subquery.
Steve Kass
Drew University
David Portas wrote:
>Try:
>SELECT personid, historytext
> FROM dbo.HistoryDates AS H
> WHERE datecode = N'H'
> AND historydate =
> (SELECT MAX(historydate)
> FROM dbo.HistoryDates
> WHERE personid = H.personid)
>However, if you have duplicate values in the HistoryDate column that
>might return more than one row for a given PersonId. Maybe that's not
>what you want, in which case you might do:
>SELECT personid, MIN(historytext)
> FROM dbo.HistoryDates AS H
> WHERE datecode = N'H'
> AND historydate =
> (SELECT MAX(historydate)
> FROM dbo.HistoryDates
> WHERE personid = H.personid)
> GROUP BY personid
>Hopefully you can see now that this won't return the "first" or "last"
>of anything - tables have no fixed concept of order. These queries
>return the row(s) for the maximum date. In the case of the second
>query, you'll get, at most, one row per PersonId with the
>MIN(historytext) value.
>
>
Get 10 records fro each group
Hi
I have a table that has fromNode, toNode and idGroup columns
I want to return the TOP 3 toNodes for each IDGroup that the FromNode =
1000
E.G. The query would return
fromNode, toNode, idGroup
1000,2001,1
1000,2002,1
1000,2003,1
1000,2004,2
1000,2005,2
1000,2006,2
1000,2013,3
1000,2014,3
1000,2016,4
But I am having trouble comming up with such a query. Can any one help.
SQL code and inserts below.
CREATE TABLE [dbo].[memberPathsGroups](
[fromNode] [int] NOT NULL,
[toNode] [int] NOT NULL,
[idGroup] [int] NOT NULL,
CONSTRAINT [PK_memberPathsGroups] PRIMARY KEY CLUSTERED
(
[fromNode] ASC,
[toNode] ASC,
[idGroup] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into memberPathsGroups values (1000,2001,1)
insert into memberPathsGroups values (1000,2002,1)
insert into memberPathsGroups values (1000,2003,1)
insert into memberPathsGroups values (1000,2004,2)
insert into memberPathsGroups values (1000,2005,2)
insert into memberPathsGroups values (1000,2006,2)
insert into memberPathsGroups values (1000,2007,2)
insert into memberPathsGroups values (1000,2008,2)
insert into memberPathsGroups values (1000,2009,2)
insert into memberPathsGroups values (1000,2010,2)
insert into memberPathsGroups values (1000,2012,2)
insert into memberPathsGroups values (1000,2013,3)
insert into memberPathsGroups values (1000,2014,3)
insert into memberPathsGroups values (1000,2015,2)
insert into memberPathsGroups values (1000,2016,4)
insert into memberPathsGroups values (1001,2001,1)
insert into memberPathsGroups values (1001,2010,1)
insert into memberPathsGroups values (1001,2012,1)
insert into memberPathsGroups values (1001,2016,2)Here's one way:
SELECT
fromNode,
toNode,
idGroup
FROM dbo.memberPathsGroups mpg
WHERE
mpg.toNode IN
(
SELECT TOP 3
mpg1.toNode
FROM dbo.memberPathsGroups mpg1
WHERE
mpg1.fromNode = mpg.fromNode
AND mpg1.idGroup = mpg.idGroup
ORDER BY mpg1.toNode
)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Rippo" <info@.rippo.co.uk> wrote in message
news:1131373532.924245.290810@.o13g2000cwo.googlegroups.com...
> Hi
> I have a table that has fromNode, toNode and idGroup columns
> I want to return the TOP 3 toNodes for each IDGroup that the FromNode =
> 1000
> E.G. The query would return
> fromNode, toNode, idGroup
> 1000,2001,1
> 1000,2002,1
> 1000,2003,1
> 1000,2004,2
> 1000,2005,2
> 1000,2006,2
> 1000,2013,3
> 1000,2014,3
> 1000,2016,4
> But I am having trouble comming up with such a query. Can any one help.
> SQL code and inserts below.
>
> CREATE TABLE [dbo].[memberPathsGroups](
> [fromNode] [int] NOT NULL,
> [toNode] [int] NOT NULL,
> [idGroup] [int] NOT NULL,
> CONSTRAINT [PK_memberPathsGroups] PRIMARY KEY CLUSTERED
> (
> [fromNode] ASC,
> [toNode] ASC,
> [idGroup] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> insert into memberPathsGroups values (1000,2001,1)
> insert into memberPathsGroups values (1000,2002,1)
> insert into memberPathsGroups values (1000,2003,1)
> insert into memberPathsGroups values (1000,2004,2)
> insert into memberPathsGroups values (1000,2005,2)
> insert into memberPathsGroups values (1000,2006,2)
> insert into memberPathsGroups values (1000,2007,2)
> insert into memberPathsGroups values (1000,2008,2)
> insert into memberPathsGroups values (1000,2009,2)
> insert into memberPathsGroups values (1000,2010,2)
> insert into memberPathsGroups values (1000,2012,2)
> insert into memberPathsGroups values (1000,2013,3)
> insert into memberPathsGroups values (1000,2014,3)
> insert into memberPathsGroups values (1000,2015,2)
> insert into memberPathsGroups values (1000,2016,4)
> insert into memberPathsGroups values (1001,2001,1)
> insert into memberPathsGroups values (1001,2010,1)
> insert into memberPathsGroups values (1001,2012,1)
> insert into memberPathsGroups values (1001,2016,2)
>
I have a table that has fromNode, toNode and idGroup columns
I want to return the TOP 3 toNodes for each IDGroup that the FromNode =
1000
E.G. The query would return
fromNode, toNode, idGroup
1000,2001,1
1000,2002,1
1000,2003,1
1000,2004,2
1000,2005,2
1000,2006,2
1000,2013,3
1000,2014,3
1000,2016,4
But I am having trouble comming up with such a query. Can any one help.
SQL code and inserts below.
CREATE TABLE [dbo].[memberPathsGroups](
[fromNode] [int] NOT NULL,
[toNode] [int] NOT NULL,
[idGroup] [int] NOT NULL,
CONSTRAINT [PK_memberPathsGroups] PRIMARY KEY CLUSTERED
(
[fromNode] ASC,
[toNode] ASC,
[idGroup] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into memberPathsGroups values (1000,2001,1)
insert into memberPathsGroups values (1000,2002,1)
insert into memberPathsGroups values (1000,2003,1)
insert into memberPathsGroups values (1000,2004,2)
insert into memberPathsGroups values (1000,2005,2)
insert into memberPathsGroups values (1000,2006,2)
insert into memberPathsGroups values (1000,2007,2)
insert into memberPathsGroups values (1000,2008,2)
insert into memberPathsGroups values (1000,2009,2)
insert into memberPathsGroups values (1000,2010,2)
insert into memberPathsGroups values (1000,2012,2)
insert into memberPathsGroups values (1000,2013,3)
insert into memberPathsGroups values (1000,2014,3)
insert into memberPathsGroups values (1000,2015,2)
insert into memberPathsGroups values (1000,2016,4)
insert into memberPathsGroups values (1001,2001,1)
insert into memberPathsGroups values (1001,2010,1)
insert into memberPathsGroups values (1001,2012,1)
insert into memberPathsGroups values (1001,2016,2)Here's one way:
SELECT
fromNode,
toNode,
idGroup
FROM dbo.memberPathsGroups mpg
WHERE
mpg.toNode IN
(
SELECT TOP 3
mpg1.toNode
FROM dbo.memberPathsGroups mpg1
WHERE
mpg1.fromNode = mpg.fromNode
AND mpg1.idGroup = mpg.idGroup
ORDER BY mpg1.toNode
)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Rippo" <info@.rippo.co.uk> wrote in message
news:1131373532.924245.290810@.o13g2000cwo.googlegroups.com...
> Hi
> I have a table that has fromNode, toNode and idGroup columns
> I want to return the TOP 3 toNodes for each IDGroup that the FromNode =
> 1000
> E.G. The query would return
> fromNode, toNode, idGroup
> 1000,2001,1
> 1000,2002,1
> 1000,2003,1
> 1000,2004,2
> 1000,2005,2
> 1000,2006,2
> 1000,2013,3
> 1000,2014,3
> 1000,2016,4
> But I am having trouble comming up with such a query. Can any one help.
> SQL code and inserts below.
>
> CREATE TABLE [dbo].[memberPathsGroups](
> [fromNode] [int] NOT NULL,
> [toNode] [int] NOT NULL,
> [idGroup] [int] NOT NULL,
> CONSTRAINT [PK_memberPathsGroups] PRIMARY KEY CLUSTERED
> (
> [fromNode] ASC,
> [toNode] ASC,
> [idGroup] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> insert into memberPathsGroups values (1000,2001,1)
> insert into memberPathsGroups values (1000,2002,1)
> insert into memberPathsGroups values (1000,2003,1)
> insert into memberPathsGroups values (1000,2004,2)
> insert into memberPathsGroups values (1000,2005,2)
> insert into memberPathsGroups values (1000,2006,2)
> insert into memberPathsGroups values (1000,2007,2)
> insert into memberPathsGroups values (1000,2008,2)
> insert into memberPathsGroups values (1000,2009,2)
> insert into memberPathsGroups values (1000,2010,2)
> insert into memberPathsGroups values (1000,2012,2)
> insert into memberPathsGroups values (1000,2013,3)
> insert into memberPathsGroups values (1000,2014,3)
> insert into memberPathsGroups values (1000,2015,2)
> insert into memberPathsGroups values (1000,2016,4)
> insert into memberPathsGroups values (1001,2001,1)
> insert into memberPathsGroups values (1001,2010,1)
> insert into memberPathsGroups values (1001,2012,1)
> insert into memberPathsGroups values (1001,2016,2)
>
Wednesday, March 21, 2012
Generating reports using data feeds from other systems
Dear all,
I've been following this group with interest for a while now but haven't
seen any firm pointers for a solution to meet my problems.
Essentially I would like to be able to
1) Create reports, acknowledgements, delivery notes, invoices, etc using
data supplied from my legacy system Unix.
2) Output them to PDF - some of the parameters of this would be in the data
feed.
3) Email or fax these PDFs to our customers.
It's not possible Reporting Services to query directly our system due to
other limitations, but can we feed RS with data either via uploading a file
to a "watch" directory or via a print spool queue on the windows system - we
already use both methods for other applications.
The reason for a print queue is it's easy to implement from Unix to
Windows - no messing with FTP etc, and print queues already exist in our
application.
The format of the data feed can be XML or indeed anything as we are source
code holders for our product.
The questions are:
1) Is this vaguely possible. ?
2) Can RS take it's feed from a print queue - with possibly us putting
together the software to accept the data on the print queue.
3) Could it handle files dropped into a "watch directory"
4) Can RS create PDFs using parameters in the feed file.
5) Could RS, with perhaps additional development, distribute the PDFs based
on parameters contained with the data feed.
If anyone could shed any light on my thoughts, or perhaps, they've done
something like this already, I would greatly appreciate some feedback.
How about this as a product Microsoft - all those legacy systems out there
suddenly able to print something a little better than the old dot-matrix
print-outs on pre-printed stationary. !!!
Any pointers would be much appreciated.
Kind Rgds
Graham GoodeyThis is possible but you will need to write a custom data extension for both
the data in the report as well as driving the delivery of reports. I think
the file-based approach will be easier than the print queue.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Graham Goodey" <graham_goodey@.yahoo.com> wrote in message
news:O4b500PVEHA.1012@.TK2MSFTNGP09.phx.gbl...
> Dear all,
> I've been following this group with interest for a while now but haven't
> seen any firm pointers for a solution to meet my problems.
> Essentially I would like to be able to
> 1) Create reports, acknowledgements, delivery notes, invoices, etc using
> data supplied from my legacy system Unix.
> 2) Output them to PDF - some of the parameters of this would be in the
data
> feed.
> 3) Email or fax these PDFs to our customers.
> It's not possible Reporting Services to query directly our system due to
> other limitations, but can we feed RS with data either via uploading a
file
> to a "watch" directory or via a print spool queue on the windows system -
we
> already use both methods for other applications.
> The reason for a print queue is it's easy to implement from Unix to
> Windows - no messing with FTP etc, and print queues already exist in our
> application.
> The format of the data feed can be XML or indeed anything as we are source
> code holders for our product.
> The questions are:
> 1) Is this vaguely possible. ?
> 2) Can RS take it's feed from a print queue - with possibly us putting
> together the software to accept the data on the print queue.
> 3) Could it handle files dropped into a "watch directory"
> 4) Can RS create PDFs using parameters in the feed file.
> 5) Could RS, with perhaps additional development, distribute the PDFs
based
> on parameters contained with the data feed.
> If anyone could shed any light on my thoughts, or perhaps, they've done
> something like this already, I would greatly appreciate some feedback.
>
> How about this as a product Microsoft - all those legacy systems out there
> suddenly able to print something a little better than the old dot-matrix
> print-outs on pre-printed stationary. !!!
>
> Any pointers would be much appreciated.
>
> Kind Rgds
>
> Graham Goodey
>
>
>
I've been following this group with interest for a while now but haven't
seen any firm pointers for a solution to meet my problems.
Essentially I would like to be able to
1) Create reports, acknowledgements, delivery notes, invoices, etc using
data supplied from my legacy system Unix.
2) Output them to PDF - some of the parameters of this would be in the data
feed.
3) Email or fax these PDFs to our customers.
It's not possible Reporting Services to query directly our system due to
other limitations, but can we feed RS with data either via uploading a file
to a "watch" directory or via a print spool queue on the windows system - we
already use both methods for other applications.
The reason for a print queue is it's easy to implement from Unix to
Windows - no messing with FTP etc, and print queues already exist in our
application.
The format of the data feed can be XML or indeed anything as we are source
code holders for our product.
The questions are:
1) Is this vaguely possible. ?
2) Can RS take it's feed from a print queue - with possibly us putting
together the software to accept the data on the print queue.
3) Could it handle files dropped into a "watch directory"
4) Can RS create PDFs using parameters in the feed file.
5) Could RS, with perhaps additional development, distribute the PDFs based
on parameters contained with the data feed.
If anyone could shed any light on my thoughts, or perhaps, they've done
something like this already, I would greatly appreciate some feedback.
How about this as a product Microsoft - all those legacy systems out there
suddenly able to print something a little better than the old dot-matrix
print-outs on pre-printed stationary. !!!
Any pointers would be much appreciated.
Kind Rgds
Graham GoodeyThis is possible but you will need to write a custom data extension for both
the data in the report as well as driving the delivery of reports. I think
the file-based approach will be easier than the print queue.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Graham Goodey" <graham_goodey@.yahoo.com> wrote in message
news:O4b500PVEHA.1012@.TK2MSFTNGP09.phx.gbl...
> Dear all,
> I've been following this group with interest for a while now but haven't
> seen any firm pointers for a solution to meet my problems.
> Essentially I would like to be able to
> 1) Create reports, acknowledgements, delivery notes, invoices, etc using
> data supplied from my legacy system Unix.
> 2) Output them to PDF - some of the parameters of this would be in the
data
> feed.
> 3) Email or fax these PDFs to our customers.
> It's not possible Reporting Services to query directly our system due to
> other limitations, but can we feed RS with data either via uploading a
file
> to a "watch" directory or via a print spool queue on the windows system -
we
> already use both methods for other applications.
> The reason for a print queue is it's easy to implement from Unix to
> Windows - no messing with FTP etc, and print queues already exist in our
> application.
> The format of the data feed can be XML or indeed anything as we are source
> code holders for our product.
> The questions are:
> 1) Is this vaguely possible. ?
> 2) Can RS take it's feed from a print queue - with possibly us putting
> together the software to accept the data on the print queue.
> 3) Could it handle files dropped into a "watch directory"
> 4) Can RS create PDFs using parameters in the feed file.
> 5) Could RS, with perhaps additional development, distribute the PDFs
based
> on parameters contained with the data feed.
> If anyone could shed any light on my thoughts, or perhaps, they've done
> something like this already, I would greatly appreciate some feedback.
>
> How about this as a product Microsoft - all those legacy systems out there
> suddenly able to print something a little better than the old dot-matrix
> print-outs on pre-printed stationary. !!!
>
> Any pointers would be much appreciated.
>
> Kind Rgds
>
> Graham Goodey
>
>
>
Generating Report Error, Local Administrator Group
Hi,
I finished creating my reports and have had an issue with generating the
reports on http://localhost/reports. When I login to
http://localhost/reports with a user that is part of the Local Administrators
Group the reports run perfectly correct. But when I login with a user that
is not part of the Local Administrators Group I get the Reporting Services
Error:
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
Cannot create a connection to data source 'OVSDPROD'.
(rsErrorOpeningConnection) Get Online Help
ERROR [08004] [Oracle][ODBC][Ora]Error while trying to retrieve text for
error ORA-12154 ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's
SQLSetConnectAttr failed
The user that I am logged in with has the same exact item-level and
system-level roles as the BUILTIN\Administrators and can navigate throughout
the site to get to the reports but when its time to actually generate the
reports I get the error above.
The data source is configured with "Credentials stored securely in the report
server"...
Even though the error states that this is an ODBC issue, it confuses me
since I am able to generate reports with users that are part of the Local
Administrator Group, but anyone outside of that group can not Generate
Reports. If everyone was having problems generating reports then I would
definitely see how it is an ODBC issue.
I am using ODBC Drivers from Oracle.....
Thanks,
MikeI figured out my problem had to do with the security permissions that were
assigned to the folder where my tnsnames.ora file was located. I needed to
set permissions for Everyone to be able to read that folder so they can
access the file and therefore run the reports.
"mtam79" wrote:
> Hi,
> I finished creating my reports and have had an issue with generating the
> reports on http://localhost/reports. When I login to
> http://localhost/reports with a user that is part of the Local Administrators
> Group the reports run perfectly correct. But when I login with a user that
> is not part of the Local Administrators Group I get the Reporting Services
> Error:
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Cannot create a connection to data source 'OVSDPROD'.
> (rsErrorOpeningConnection) Get Online Help
> ERROR [08004] [Oracle][ODBC][Ora]Error while trying to retrieve text for
> error ORA-12154 ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's
> SQLSetConnectAttr failed
> The user that I am logged in with has the same exact item-level and
> system-level roles as the BUILTIN\Administrators and can navigate throughout
> the site to get to the reports but when its time to actually generate the
> reports I get the error above.
> The data source is configured with "Credentials stored securely in the report
> server"...
> Even though the error states that this is an ODBC issue, it confuses me
> since I am able to generate reports with users that are part of the Local
> Administrator Group, but anyone outside of that group can not Generate
> Reports. If everyone was having problems generating reports then I would
> definitely see how it is an ODBC issue.
> I am using ODBC Drivers from Oracle.....
> Thanks,
> Mike|||I
"mtam79" wrote:
> Hi,
> I finished creating my reports and have had an issue with generating the
> reports on http://localhost/reports. When I login to
> http://localhost/reports with a user that is part of the Local Administrators
> Group the reports run perfectly correct. But when I login with a user that
> is not part of the Local Administrators Group I get the Reporting Services
> Error:
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Cannot create a connection to data source 'OVSDPROD'.
> (rsErrorOpeningConnection) Get Online Help
> ERROR [08004] [Oracle][ODBC][Ora]Error while trying to retrieve text for
> error ORA-12154 ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's
> SQLSetConnectAttr failed
> The user that I am logged in with has the same exact item-level and
> system-level roles as the BUILTIN\Administrators and can navigate throughout
> the site to get to the reports but when its time to actually generate the
> reports I get the error above.
> The data source is configured with "Credentials stored securely in the report
> server"...
> Even though the error states that this is an ODBC issue, it confuses me
> since I am able to generate reports with users that are part of the Local
> Administrator Group, but anyone outside of that group can not Generate
> Reports. If everyone was having problems generating reports then I would
> definitely see how it is an ODBC issue.
> I am using ODBC Drivers from Oracle.....
> Thanks,
> Mikesql
I finished creating my reports and have had an issue with generating the
reports on http://localhost/reports. When I login to
http://localhost/reports with a user that is part of the Local Administrators
Group the reports run perfectly correct. But when I login with a user that
is not part of the Local Administrators Group I get the Reporting Services
Error:
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
Cannot create a connection to data source 'OVSDPROD'.
(rsErrorOpeningConnection) Get Online Help
ERROR [08004] [Oracle][ODBC][Ora]Error while trying to retrieve text for
error ORA-12154 ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's
SQLSetConnectAttr failed
The user that I am logged in with has the same exact item-level and
system-level roles as the BUILTIN\Administrators and can navigate throughout
the site to get to the reports but when its time to actually generate the
reports I get the error above.
The data source is configured with "Credentials stored securely in the report
server"...
Even though the error states that this is an ODBC issue, it confuses me
since I am able to generate reports with users that are part of the Local
Administrator Group, but anyone outside of that group can not Generate
Reports. If everyone was having problems generating reports then I would
definitely see how it is an ODBC issue.
I am using ODBC Drivers from Oracle.....
Thanks,
MikeI figured out my problem had to do with the security permissions that were
assigned to the folder where my tnsnames.ora file was located. I needed to
set permissions for Everyone to be able to read that folder so they can
access the file and therefore run the reports.
"mtam79" wrote:
> Hi,
> I finished creating my reports and have had an issue with generating the
> reports on http://localhost/reports. When I login to
> http://localhost/reports with a user that is part of the Local Administrators
> Group the reports run perfectly correct. But when I login with a user that
> is not part of the Local Administrators Group I get the Reporting Services
> Error:
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Cannot create a connection to data source 'OVSDPROD'.
> (rsErrorOpeningConnection) Get Online Help
> ERROR [08004] [Oracle][ODBC][Ora]Error while trying to retrieve text for
> error ORA-12154 ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's
> SQLSetConnectAttr failed
> The user that I am logged in with has the same exact item-level and
> system-level roles as the BUILTIN\Administrators and can navigate throughout
> the site to get to the reports but when its time to actually generate the
> reports I get the error above.
> The data source is configured with "Credentials stored securely in the report
> server"...
> Even though the error states that this is an ODBC issue, it confuses me
> since I am able to generate reports with users that are part of the Local
> Administrator Group, but anyone outside of that group can not Generate
> Reports. If everyone was having problems generating reports then I would
> definitely see how it is an ODBC issue.
> I am using ODBC Drivers from Oracle.....
> Thanks,
> Mike|||I
"mtam79" wrote:
> Hi,
> I finished creating my reports and have had an issue with generating the
> reports on http://localhost/reports. When I login to
> http://localhost/reports with a user that is part of the Local Administrators
> Group the reports run perfectly correct. But when I login with a user that
> is not part of the Local Administrators Group I get the Reporting Services
> Error:
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Cannot create a connection to data source 'OVSDPROD'.
> (rsErrorOpeningConnection) Get Online Help
> ERROR [08004] [Oracle][ODBC][Ora]Error while trying to retrieve text for
> error ORA-12154 ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's
> SQLSetConnectAttr failed
> The user that I am logged in with has the same exact item-level and
> system-level roles as the BUILTIN\Administrators and can navigate throughout
> the site to get to the reports but when its time to actually generate the
> reports I get the error above.
> The data source is configured with "Credentials stored securely in the report
> server"...
> Even though the error states that this is an ODBC issue, it confuses me
> since I am able to generate reports with users that are part of the Local
> Administrator Group, but anyone outside of that group can not Generate
> Reports. If everyone was having problems generating reports then I would
> definitely see how it is an ODBC issue.
> I am using ODBC Drivers from Oracle.....
> Thanks,
> Mikesql
Monday, March 19, 2012
Generating Custom Messages to User
Hello everyone,
i'm using a foreach loop container to read a group of excel files and pass their information to a Sql Server database. The process runs well but sometimes there could be some excel files that may not be processed correctly so i'm using transacctions to continue to process on the other files, But i'd like to generate a message everytime an excel file is or not processed. I thought that i could generate a flat file to do it, but is there any other way to accomplish this? I'm also generating a log file (on xml format), but It seems too much information for an end-user.
any suggestions?
regards.
You can generate messages that automatically get captured by your log provider. Is this what you want to do?
-Jamie
|||Hello JamieI'm actually using a log provider, so i get the log of the progress.
My solution was to get into the log (which is an xml output file) two kind of messages: the first one, an script that Logs a new "Sucess file" message and another script task which says "Failure process file". So at the end of the tasks i need to run, i get just one of the script tasks to log into my file.
But is there any other way to do it?.
regards
Generating combinations (cells of a multidimensional cube?)
Hi,
(Apologies if I've posted this to the wrong group.)
I have a table of Categories and Category Items, and need to generate
every possible combination, e.g. I have:
CategoryID CategoryItemID
-- --
1 1
1 2
2 1
2 2
2 3
2 4
3 1
3 2
3 3
and I want to generate:
CellID CategoryID CategoryItemID
-- -- --
1 1 1
1 2 1
1 3 1
2 1 1
2 2 1
2 3 2
...
24 1 2
24 2 4
24 3 3
I *could* write a stored proc that iterates over each item in turn to
build up the results set, but it's clunky and inelegant - is there a
smarter way to do this?
Thanks in advance,
NateHi Nate,
You can use following logic to generate every possible combination but
couldnt understand your example.
See if this help.
create table #temptab
(
CategoryID int,
CategoryItemID int
)
insert into #temptab values(1, 1)
insert into #temptab values(1, 2)
insert into #temptab values(2, 1)
insert into #temptab values(2, 2)
insert into #temptab values(3, 1)
insert into #temptab values(3, 2)
insert into #temptab values(3, 3)
select
*
from
(select distinct CategoryID from #temptab) a
cross join (select distinct CategoryItemID from #temptab) b
Regards,
Lakshman.|||Lakshman, thanks for replying.
Sorry, I've not been clear enough. Say I have as input:
Category CategoryItem
-- --
Fruit Apple
Fruit Orange
Car Ford
Car Vauxhall
Car Rover
Car Fiat
Animal Cat
Animal Dog
Animal Tortoise
I need to generate all the possible combinations of the 3 categories:
Comb. # Category CategoryItem
-- -- --
1 Fruit Apple
1 Car Ford
1 Animal Cat
2 Fruit Apple
2 Car Ford
2 Animal Dog
...
24 Fruit Orange
24 Car Fiat
24 Animal Tortoise
Is that any clearer?
(Of course, I need to be able to extend this to a non-fixed number of
categories)|||If this doesn't do what you want, then please see my signature so you can
understand why good specs are better than word problems.
You might also consider storing these in separate tables instead of trying
to partially relate them, if there really is no relevance to the
relationship...
USE TempDB
GO
CREATE TABLE dbo.Categories
(
CategoryID INT,
CategoryItemID INT
)
GO
SET NOCOUNT ON
INSERT dbo.Categories SELECT 1, 1
INSERT dbo.Categories SELECT 1, 2
INSERT dbo.Categories SELECT 2, 1
INSERT dbo.Categories SELECT 2, 2
INSERT dbo.Categories SELECT 2, 4
INSERT dbo.Categories SELECT 3, 1
INSERT dbo.Categories SELECT 3, 2
INSERT dbo.Categories SELECT 3, 3
GO
SELECT c1.CategoryID, c2.CategoryItemID
FROM dbo.Categories c1
FULL OUTER JOIN
dbo.Categories c2
ON c1.CategoryID > 0
GROUP BY c1.CategoryID, c2.CategoryItemID
ORDER BY 1, 2
GO
DROP TABLE dbo.Categories
GO
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Nate" <spamthecan@.gmail.com> wrote in message
news:1115389621.362575.318720@.o13g2000cwo.googlegroups.com...
> Lakshman, thanks for replying.
> Sorry, I've not been clear enough. Say I have as input:
> Category CategoryItem
> -- --
> Fruit Apple
> Fruit Orange
> Car Ford
> Car Vauxhall
> Car Rover
> Car Fiat
> Animal Cat
> Animal Dog
> Animal Tortoise
> I need to generate all the possible combinations of the 3 categories:
> Comb. # Category CategoryItem
> -- -- --
> 1 Fruit Apple
> 1 Car Ford
> 1 Animal Cat
> 2 Fruit Apple
> 2 Car Ford
> 2 Animal Dog
> ...
> 24 Fruit Orange
> 24 Car Fiat
> 24 Animal Tortoise|||Hi Nate,
This example is better than earlier one.
I was able to generate all combinations (72 rows) but i should see
whehter it is possiable to add a comb#.
Let you know if i could find.
Script:
create table #temptab
(
CategoryID int,
CategoryItemID int
)
insert into #temptab values(1, 1)
insert into #temptab values(1, 2)
insert into #temptab values(2, 1)
insert into #temptab values(2, 2)
insert into #temptab values(2, 3)
insert into #temptab values(2, 4)
insert into #temptab values(3, 1)
insert into #temptab values(3, 2)
insert into #temptab values(3, 3)
select
*
from
(select CategoryID from #temptab) a
cross join (select CategoryItemID from #temptab) b
where
exists (
select
*
from
#temptab c
where
c.CategoryID = a.CategoryID
and c.CategoryItemID = b.CategoryItemID)
order by
a.CategoryID
Regards,
Lakshman.|||Hi AB,
Thanks for replying.
>If this doesn't do what you want, then please see my signature so you
can
>understand why good specs are better than word problems.
The table in my original post is the actual table - the IDs are
integers.
>You might also consider storing these in separate tables instead of
trying
>to partially relate them, if there really is no relevance to the
>relationship...
Not entirely sure what you mean here - the categories are unrelated to
each other, but they are all categories. Each row in the CategoryItem
table has a parent Category.
When I run your code, I get impossible combinations, e.g. (1,3) and
(1,4), but there are only two types of Fruit (to use the example in
words from my 2nd post).
I appreciate that I probably haven't explained the situation very well!|||Ah, Lakshman, that's great - I feel you're on to something there!
Using the EXISTS clause like that is something I didn't consider.|||> I appreciate that I probably haven't explained the situation very well!
That's why I have already suggested you read http://www.aspfaq.com/5006 !
It won't hurt you to learn how to "explain the situation" in terms we can
understand and, more importantly, reproduce.|||Nate,
If you have a fixed and known number of categories, it's not as
tricky. See if this helps:
create table Categories (
pk int primary key,
Category varchar(10)
)
insert into Categories values (1,'Fruit')
insert into Categories values (2,'Car')
insert into Categories values (3,'Animal')
go
create table Items (
pk int primary key,
Categorypk int,
CategoryItem varchar(10)
)
insert into Items values (1,1,'Apple')
insert into Items values (2,1,'Orange')
insert into Items values (3,2,'Ford')
insert into Items values (4,2,'Vauxhall')
insert into Items values (5,2,'Fiat')
insert into Items values (6,3,'Dog')
insert into Items values (7,3,'Cat')
go
create function f(
@.pk int
) returns table as return
select C.Category, CI.CategoryItem
from Categories as C join Items as CI
on C.pk = @.pk
and CI.Categorypk = @.pk
and C.pk = CI.Categorypk
go
select
identity(int,1,1) as Combopk,
A.CategoryItem as AI,
B.CategoryItem as BI,
C.CategoryItem as CI
into #temp
from f(1) as A, f(2) as B, f(3) as C
select
T.Combopk,
C.Category,
case C.pk when 1 then T.AI when 2 then T.BI when 3 then T.CI end as Item
from #temp T
cross join Categories C
order by 1,2
go
--drop table Categories, Items, #temp
--drop function f
--Steve Kass
--Drew University
Nate wrote:
>Hi AB,
>Thanks for replying.
>
>
>can
>
>The table in my original post is the actual table - the IDs are
>integers.
>
>
>trying
>
>Not entirely sure what you mean here - the categories are unrelated to
>each other, but they are all categories. Each row in the CategoryItem
>table has a parent Category.
>When I run your code, I get impossible combinations, e.g. (1,3) and
>(1,4), but there are only two types of Fruit (to use the example in
>words from my 2nd post).
>I appreciate that I probably haven't explained the situation very well!
>
>|||Hi Steve,
Many thanks for that. In some cases, the number of categories will be
fixed (or at least, will have a sensible upper bound), so I can extend
your code to cover those cases. Cheers!
Regards,
Nate
(Apologies if I've posted this to the wrong group.)
I have a table of Categories and Category Items, and need to generate
every possible combination, e.g. I have:
CategoryID CategoryItemID
-- --
1 1
1 2
2 1
2 2
2 3
2 4
3 1
3 2
3 3
and I want to generate:
CellID CategoryID CategoryItemID
-- -- --
1 1 1
1 2 1
1 3 1
2 1 1
2 2 1
2 3 2
...
24 1 2
24 2 4
24 3 3
I *could* write a stored proc that iterates over each item in turn to
build up the results set, but it's clunky and inelegant - is there a
smarter way to do this?
Thanks in advance,
NateHi Nate,
You can use following logic to generate every possible combination but
couldnt understand your example.
See if this help.
create table #temptab
(
CategoryID int,
CategoryItemID int
)
insert into #temptab values(1, 1)
insert into #temptab values(1, 2)
insert into #temptab values(2, 1)
insert into #temptab values(2, 2)
insert into #temptab values(3, 1)
insert into #temptab values(3, 2)
insert into #temptab values(3, 3)
select
*
from
(select distinct CategoryID from #temptab) a
cross join (select distinct CategoryItemID from #temptab) b
Regards,
Lakshman.|||Lakshman, thanks for replying.
Sorry, I've not been clear enough. Say I have as input:
Category CategoryItem
-- --
Fruit Apple
Fruit Orange
Car Ford
Car Vauxhall
Car Rover
Car Fiat
Animal Cat
Animal Dog
Animal Tortoise
I need to generate all the possible combinations of the 3 categories:
Comb. # Category CategoryItem
-- -- --
1 Fruit Apple
1 Car Ford
1 Animal Cat
2 Fruit Apple
2 Car Ford
2 Animal Dog
...
24 Fruit Orange
24 Car Fiat
24 Animal Tortoise
Is that any clearer?
(Of course, I need to be able to extend this to a non-fixed number of
categories)|||If this doesn't do what you want, then please see my signature so you can
understand why good specs are better than word problems.
You might also consider storing these in separate tables instead of trying
to partially relate them, if there really is no relevance to the
relationship...
USE TempDB
GO
CREATE TABLE dbo.Categories
(
CategoryID INT,
CategoryItemID INT
)
GO
SET NOCOUNT ON
INSERT dbo.Categories SELECT 1, 1
INSERT dbo.Categories SELECT 1, 2
INSERT dbo.Categories SELECT 2, 1
INSERT dbo.Categories SELECT 2, 2
INSERT dbo.Categories SELECT 2, 4
INSERT dbo.Categories SELECT 3, 1
INSERT dbo.Categories SELECT 3, 2
INSERT dbo.Categories SELECT 3, 3
GO
SELECT c1.CategoryID, c2.CategoryItemID
FROM dbo.Categories c1
FULL OUTER JOIN
dbo.Categories c2
ON c1.CategoryID > 0
GROUP BY c1.CategoryID, c2.CategoryItemID
ORDER BY 1, 2
GO
DROP TABLE dbo.Categories
GO
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Nate" <spamthecan@.gmail.com> wrote in message
news:1115389621.362575.318720@.o13g2000cwo.googlegroups.com...
> Lakshman, thanks for replying.
> Sorry, I've not been clear enough. Say I have as input:
> Category CategoryItem
> -- --
> Fruit Apple
> Fruit Orange
> Car Ford
> Car Vauxhall
> Car Rover
> Car Fiat
> Animal Cat
> Animal Dog
> Animal Tortoise
> I need to generate all the possible combinations of the 3 categories:
> Comb. # Category CategoryItem
> -- -- --
> 1 Fruit Apple
> 1 Car Ford
> 1 Animal Cat
> 2 Fruit Apple
> 2 Car Ford
> 2 Animal Dog
> ...
> 24 Fruit Orange
> 24 Car Fiat
> 24 Animal Tortoise|||Hi Nate,
This example is better than earlier one.
I was able to generate all combinations (72 rows) but i should see
whehter it is possiable to add a comb#.
Let you know if i could find.
Script:
create table #temptab
(
CategoryID int,
CategoryItemID int
)
insert into #temptab values(1, 1)
insert into #temptab values(1, 2)
insert into #temptab values(2, 1)
insert into #temptab values(2, 2)
insert into #temptab values(2, 3)
insert into #temptab values(2, 4)
insert into #temptab values(3, 1)
insert into #temptab values(3, 2)
insert into #temptab values(3, 3)
select
*
from
(select CategoryID from #temptab) a
cross join (select CategoryItemID from #temptab) b
where
exists (
select
*
from
#temptab c
where
c.CategoryID = a.CategoryID
and c.CategoryItemID = b.CategoryItemID)
order by
a.CategoryID
Regards,
Lakshman.|||Hi AB,
Thanks for replying.
>If this doesn't do what you want, then please see my signature so you
can
>understand why good specs are better than word problems.
The table in my original post is the actual table - the IDs are
integers.
>You might also consider storing these in separate tables instead of
trying
>to partially relate them, if there really is no relevance to the
>relationship...
Not entirely sure what you mean here - the categories are unrelated to
each other, but they are all categories. Each row in the CategoryItem
table has a parent Category.
When I run your code, I get impossible combinations, e.g. (1,3) and
(1,4), but there are only two types of Fruit (to use the example in
words from my 2nd post).
I appreciate that I probably haven't explained the situation very well!|||Ah, Lakshman, that's great - I feel you're on to something there!
Using the EXISTS clause like that is something I didn't consider.|||> I appreciate that I probably haven't explained the situation very well!
That's why I have already suggested you read http://www.aspfaq.com/5006 !
It won't hurt you to learn how to "explain the situation" in terms we can
understand and, more importantly, reproduce.|||Nate,
If you have a fixed and known number of categories, it's not as
tricky. See if this helps:
create table Categories (
pk int primary key,
Category varchar(10)
)
insert into Categories values (1,'Fruit')
insert into Categories values (2,'Car')
insert into Categories values (3,'Animal')
go
create table Items (
pk int primary key,
Categorypk int,
CategoryItem varchar(10)
)
insert into Items values (1,1,'Apple')
insert into Items values (2,1,'Orange')
insert into Items values (3,2,'Ford')
insert into Items values (4,2,'Vauxhall')
insert into Items values (5,2,'Fiat')
insert into Items values (6,3,'Dog')
insert into Items values (7,3,'Cat')
go
create function f(
@.pk int
) returns table as return
select C.Category, CI.CategoryItem
from Categories as C join Items as CI
on C.pk = @.pk
and CI.Categorypk = @.pk
and C.pk = CI.Categorypk
go
select
identity(int,1,1) as Combopk,
A.CategoryItem as AI,
B.CategoryItem as BI,
C.CategoryItem as CI
into #temp
from f(1) as A, f(2) as B, f(3) as C
select
T.Combopk,
C.Category,
case C.pk when 1 then T.AI when 2 then T.BI when 3 then T.CI end as Item
from #temp T
cross join Categories C
order by 1,2
go
--drop table Categories, Items, #temp
--drop function f
--Steve Kass
--Drew University
Nate wrote:
>Hi AB,
>Thanks for replying.
>
>
>can
>
>The table in my original post is the actual table - the IDs are
>integers.
>
>
>trying
>
>Not entirely sure what you mean here - the categories are unrelated to
>each other, but they are all categories. Each row in the CategoryItem
>table has a parent Category.
>When I run your code, I get impossible combinations, e.g. (1,3) and
>(1,4), but there are only two types of Fruit (to use the example in
>words from my 2nd post).
>I appreciate that I probably haven't explained the situation very well!
>
>|||Hi Steve,
Many thanks for that. In some cases, the number of categories will be
fixed (or at least, will have a sensible upper bound), so I can extend
your code to cover those cases. Cheers!
Regards,
Nate
Monday, March 12, 2012
Generating a Group of Reports
We are thinking of a possible requirement for our custom application (which
will use Reporting Services), but we are unsure if it is possible.
Say you have 20 individual reports. And, these 20 reports are split up into
4 report groups (5 reports per report group). This is more of a logical
grouping (or maybe the report groups represent folders on the report
server). Anyhow, is it possible to generate the entire report group (thus
generating all 5 reports in the report group at one time) instead of having
to generate each individual report? Does Reporting Services support such an
idea?
Thanks.Yeah, I have a similar question(s).
Is it possible to have 5 separate reports (RDLs) and when they are executed
have the output of each report concatenated into 1 long report (probably
with a page break between each report)?
Or, does one have to loop through each report, calling it via the URL
method, and displaying each report in a separate browser instance (thus 5
separate windows opened to see all five reports)?
Or'?
Any help is appreciated. Thanks in advance.
"Dex" <dex@.yahoo.com> wrote in message
news:%23dePSvpmEHA.3396@.tk2msftngp13.phx.gbl...
> We are thinking of a possible requirement for our custom application
(which
> will use Reporting Services), but we are unsure if it is possible.
> Say you have 20 individual reports. And, these 20 reports are split up
into
> 4 report groups (5 reports per report group). This is more of a logical
> grouping (or maybe the report groups represent folders on the report
> server). Anyhow, is it possible to generate the entire report group (thus
> generating all 5 reports in the report group at one time) instead of
having
> to generate each individual report? Does Reporting Services support such
an
> idea?
> Thanks.
>
will use Reporting Services), but we are unsure if it is possible.
Say you have 20 individual reports. And, these 20 reports are split up into
4 report groups (5 reports per report group). This is more of a logical
grouping (or maybe the report groups represent folders on the report
server). Anyhow, is it possible to generate the entire report group (thus
generating all 5 reports in the report group at one time) instead of having
to generate each individual report? Does Reporting Services support such an
idea?
Thanks.Yeah, I have a similar question(s).
Is it possible to have 5 separate reports (RDLs) and when they are executed
have the output of each report concatenated into 1 long report (probably
with a page break between each report)?
Or, does one have to loop through each report, calling it via the URL
method, and displaying each report in a separate browser instance (thus 5
separate windows opened to see all five reports)?
Or'?
Any help is appreciated. Thanks in advance.
"Dex" <dex@.yahoo.com> wrote in message
news:%23dePSvpmEHA.3396@.tk2msftngp13.phx.gbl...
> We are thinking of a possible requirement for our custom application
(which
> will use Reporting Services), but we are unsure if it is possible.
> Say you have 20 individual reports. And, these 20 reports are split up
into
> 4 report groups (5 reports per report group). This is more of a logical
> grouping (or maybe the report groups represent folders on the report
> server). Anyhow, is it possible to generate the entire report group (thus
> generating all 5 reports in the report group at one time) instead of
having
> to generate each individual report? Does Reporting Services support such
an
> idea?
> Thanks.
>
Wednesday, March 7, 2012
Generate Script including Data
Hello Group:
Is there any way of generate a script which include data and database
structure in SQL SERVER 2000?
Thanks in advance.
Armandohttp://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Armando Ruiz" <armando-ruiz@.telefonica.net> wrote in message
news:OfzTF$7AFHA.1992@.TK2MSFTNGP10.phx.gbl...
> Hello Group:
> Is there any way of generate a script which include data and database
> structure in SQL SERVER 2000?
> Thanks in advance.
> Armando
>|||Hi Armando,
Would you be able to tell us the exact purpose? There are other better ways
to do it.
--
Thanks
Yogish|||Hello and thanks for your quick answers.
I've to update a remote MSDE database, creating 4 new tables and inserting
data.
I can´t use DTS, Export Data, etc., so I'm looking an easy way to update
that database only by 1 step.
Thanks again
Armando
"Yogish" <yogishkamathg@.icqmail.com> escribió en el mensaje
news:E9EBED22-3D66-4DCB-B91E-8DCF5FAE25A8@.microsoft.com...
> Hi Armando,
> Would you be able to tell us the exact purpose? There are other better
ways
> to do it.
> --
> Thanks
> Yogish|||check out DB Ghost - http://www.dbghost.com for scripting, building,
comparing and synchronizing both schema and data optionally using Source
control.
"Armando Ruiz" wrote:
> Hello and thanks for your quick answers.
> I've to update a remote MSDE database, creating 4 new tables and inserting
> data.
> I can´t use DTS, Export Data, etc., so I'm looking an easy way to update
> that database only by 1 step.
> Thanks again
> Armando
>
> "Yogish" <yogishkamathg@.icqmail.com> escribió en el mensaje
> news:E9EBED22-3D66-4DCB-B91E-8DCF5FAE25A8@.microsoft.com...
> > Hi Armando,
> >
> > Would you be able to tell us the exact purpose? There are other better
> ways
> > to do it.
> >
> > --
> > Thanks
> > Yogish
>
>|||Thanks for the reply .
"mark baekdal" <markbaekdal@.discussions.microsoft.com> escribió en el
mensaje news:8F801F86-AD4A-4D1C-90CA-4CC4619D86FB@.microsoft.com...
> check out DB Ghost - http://www.dbghost.com for scripting, building,
> comparing and synchronizing both schema and data optionally using Source
> control.
> "Armando Ruiz" wrote:
> > Hello and thanks for your quick answers.
> >
> > I've to update a remote MSDE database, creating 4 new tables and
inserting
> > data.
> >
> > I can´t use DTS, Export Data, etc., so I'm looking an easy way to update
> > that database only by 1 step.
> >
> > Thanks again
> >
> > Armando
> >
> >
> >
> > "Yogish" <yogishkamathg@.icqmail.com> escribió en el mensaje
> > news:E9EBED22-3D66-4DCB-B91E-8DCF5FAE25A8@.microsoft.com...
> > > Hi Armando,
> > >
> > > Would you be able to tell us the exact purpose? There are other better
> > ways
> > > to do it.
> > >
> > > --
> > > Thanks
> > > Yogish
> >
> >
> >
Is there any way of generate a script which include data and database
structure in SQL SERVER 2000?
Thanks in advance.
Armandohttp://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Armando Ruiz" <armando-ruiz@.telefonica.net> wrote in message
news:OfzTF$7AFHA.1992@.TK2MSFTNGP10.phx.gbl...
> Hello Group:
> Is there any way of generate a script which include data and database
> structure in SQL SERVER 2000?
> Thanks in advance.
> Armando
>|||Hi Armando,
Would you be able to tell us the exact purpose? There are other better ways
to do it.
--
Thanks
Yogish|||Hello and thanks for your quick answers.
I've to update a remote MSDE database, creating 4 new tables and inserting
data.
I can´t use DTS, Export Data, etc., so I'm looking an easy way to update
that database only by 1 step.
Thanks again
Armando
"Yogish" <yogishkamathg@.icqmail.com> escribió en el mensaje
news:E9EBED22-3D66-4DCB-B91E-8DCF5FAE25A8@.microsoft.com...
> Hi Armando,
> Would you be able to tell us the exact purpose? There are other better
ways
> to do it.
> --
> Thanks
> Yogish|||check out DB Ghost - http://www.dbghost.com for scripting, building,
comparing and synchronizing both schema and data optionally using Source
control.
"Armando Ruiz" wrote:
> Hello and thanks for your quick answers.
> I've to update a remote MSDE database, creating 4 new tables and inserting
> data.
> I can´t use DTS, Export Data, etc., so I'm looking an easy way to update
> that database only by 1 step.
> Thanks again
> Armando
>
> "Yogish" <yogishkamathg@.icqmail.com> escribió en el mensaje
> news:E9EBED22-3D66-4DCB-B91E-8DCF5FAE25A8@.microsoft.com...
> > Hi Armando,
> >
> > Would you be able to tell us the exact purpose? There are other better
> ways
> > to do it.
> >
> > --
> > Thanks
> > Yogish
>
>|||Thanks for the reply .
"mark baekdal" <markbaekdal@.discussions.microsoft.com> escribió en el
mensaje news:8F801F86-AD4A-4D1C-90CA-4CC4619D86FB@.microsoft.com...
> check out DB Ghost - http://www.dbghost.com for scripting, building,
> comparing and synchronizing both schema and data optionally using Source
> control.
> "Armando Ruiz" wrote:
> > Hello and thanks for your quick answers.
> >
> > I've to update a remote MSDE database, creating 4 new tables and
inserting
> > data.
> >
> > I can´t use DTS, Export Data, etc., so I'm looking an easy way to update
> > that database only by 1 step.
> >
> > Thanks again
> >
> > Armando
> >
> >
> >
> > "Yogish" <yogishkamathg@.icqmail.com> escribió en el mensaje
> > news:E9EBED22-3D66-4DCB-B91E-8DCF5FAE25A8@.microsoft.com...
> > > Hi Armando,
> > >
> > > Would you be able to tell us the exact purpose? There are other better
> > ways
> > > to do it.
> > >
> > > --
> > > Thanks
> > > Yogish
> >
> >
> >
Sunday, February 26, 2012
Generate Multiple Tables from Column data
I have a large table with 30000+ rows. Most of the rows can be group by Column 1.
Say you had 10000 people and numbers 1 through 3. Every person would be linked to 1, 2 & 3. hence 30000 rows.
What i need to do is create a table for each number. i.e. Table 1 where column 1 is 1 (then it lists 10000 rows)
Table 2 where column 1 is 2 (another 10000 rows) and so on.
I need to create just under 300 tables. Is there a quicker way of doing that than this 300 times?
I'll try and put it into an example of what i'm looking for
Original table
Column 1 Column 2 Column 3 etc...-
1 a b .....
2 a b .....
1 c d .....
2 c d .....
AND THEN TURN IT INTO
Table name '1'
Column 1 Column 2 Column 3 etc...
-
1 a b .....
1 c d .....
Table name '2'
Column 1 Column 2 Column 3 etc...
-
2 a b .....
2 c d .....
Using one piece of code and the data in Column 1 as the table names?
Thanks
it worked like this
Select *into 1 from Original Table where column1 = 1
Select *into 2 from Original Table where column1 = 2
All i did was copy the column names into excell and duplicate the rest and run seperate select queries.
Subscribe to:
Posts (Atom)