We are writing in a temorary sql-table with all related IDs. (We use Autokey for each table). Also with the ID we are saving the SQL-Server SPID, which is unique to a current connection. So every user could have as many different connections and this supporst also more then one user. In the ReportBulder DADE you have to only Join (inner) the View with the ID with your mastertable.
The trick is, that the view depends on the SPID of the connection, so the view only sees those IDs you wont.
And its realy fast. For the enduser it is easy, he only have to select two tables (instead of one) and join the ID of the real table, with the ItemID of the View PRINT_IDs. The join must be an "inner" type. That's all, you can link this "Tableset" with every other Table and so on.
Comments
http://www.digital-metaphors.com
info@digital-metaphors.com
if so, i have a interresting solution to get only secial items (like grid
selection).
chris
Would appreciate any advice on how to execute a report against a set of
records selected from a grid.
Cheers,
Ben
for each table). Also with the ID we are saving the SQL-Server SPID, which
is unique to a current connection. So every user could have as many
different connections and this supporst also more then one user. In the
ReportBulder DADE you have to only Join (inner) the View with the ID with
your mastertable.
The trick is, that the view depends on the SPID of the connection, so the
view only sees those IDs you wont.
And its realy fast. For the enduser it is easy, he only have to select two
tables (instead of one) and join the ID of the real table, with the ItemID
of the View PRINT_IDs. The join must be an "inner" type. That's all, you can
link this "Tableset" with every other Table and so on.
Best regards
Chris
Attached some of our code:
The View looks like
####################
ALTER VIEW PRINT_IDs
AS
SELECT * FROM dbo.cnf_PrintIDs()
####################
ALTER FUNCTION cnf_PrintIDs (
) RETURNS TABLE
AS
RETURN ( SELECT i.id + 1 - StartId Idx, ItemId
FROM cnIdItem i, cnIdList l
WHERE i.id BETWEEN l.StartId AND l.EndId
@SPID
)
####################
CREATE TABLE [cnIdItem] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[ItemId] [int] NULL ,
PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
##################
CREATE TABLE [cnIdList] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[CreateDate] [datetime] NOT NULL ,
[ItemCount] [int] NULL ,
[StartId] [int] NULL ,
[EndId] [int] NULL ,
[TableName] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[Info] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
@spid),
[typ] [smallint] NOT NULL CONSTRAINT [DF_cnIdList_typ] DEFAULT (0),
PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
EULANDA Software