History of data for documents

Hello,
I have following situation:
CREATE TABLE [dbo].[Address] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[CityId] [uniqueidentifier] NOT NULL ,
[CountryId] [uniqueidentifier] NOT NULL ,
[StreetName] [nvarchar] (100) NOT NULL ,
[StreetNo] [nvarchar] (10) NOT NULL ,
[LocalNo] [nvarchar] (10) NOT NULL ,
[PostalCode] [nvarchar] (20) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Contractor] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[AddressId] [uniqueidentifier] NOT NULL ,
[Symbol] [nvarchar] (50) NOT NULL ,
[Name] [nvarchar] (200) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Order] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[OwnerContractorId] [uniqueidentifier] NOT NULL ,
[TargetContractorId] [uniqueidentifier] NOT NULL ,
[Symbol] [nvarchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[OrderProduct] (
[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL ,
[OrderId] [uniqueidentifier] NOT NULL ,
[ProductId] [uniqueidentifier] NOT NULL ,
[Quantity] [float] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Product] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[Symbol] [nvarchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ProductName] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[ProductId] [uniqueidentifier] NOT NULL ,
[CultureName] [nvarchar] (20) NOT NULL ,
[Name] [nvarchar] (200) NOT NULL
) ON [PRIMARY]
GO
The Problem is:
Order - needs information about contractor and his address (address is
related to the contractor by one-to-many
relation). Furthermore, the order needs information about products (its
names, which are stored in
separate table - each product can have several names, depending on language)
.
If a name of a product would change, it will be changed in all orders. But
order is the document and I need to
keep it as it was at the moment of creation (constractor data - names,
address etc. , product data - names etc.).
To do this, I have to design some kind of history of data connection to each
other. It is bad situation to me,
when I change address of some contractor - all orders will have changed
addresses, the proper data connections
will collapse.
My idea is to copy all related data for the record which is edited. For
example if an address for contractor is
changed, the new contractor record is created with new id (guid) and the
same symbol (is unique for all active
records) (old one have flag "history" for example - is deactivated) and the
new address is related to this new contractor record. The same story applies
when a name of product is edited.
This mechanism lets to store correct data for each order in the database -
all documents(orders) are connected to the same data as at the moment of
their creation.
Reports in this case are performed by queries operationg in the "Symbol" of
the contractor - this gives the
possibility to find all orders of one contractor - each having correct
address for the moment of creation of
order.
My question is: is this design corresponding to the "rules of art" somehow?
What are your ideas for solving such problems?
I will be thankful for your opinions
Adam Rozycki & friends>> My question is: is this design corresponding to the "rules of art"
No
I am not sure where you got the idea of using a globally unique identifier
type for every key in your tables.
We all work within existing computational constraints; so no business
segment requires a machine generated global identifier for a table unless
you are researching on such values. Other than the "" factor, abuse &
hype, there is nothing simple, pragmatic or meaningful about using a
uniqueidentifier column as a key for Orders or Products table as in your
situation.
One approach to your problem, based on your narratives, can be like:
CREATE TABLE Orders (
Order_nbr INT NOT NULL PRIMARY KEY,
Product_id INT NOT NULL,
REFERENCES Products ( Product_id )
Contractor_id INT NOT NULL
Address_id INT NOT NULL,
REFERENCES Contractors ( Contractor_id, Address_id )
.. ) ;
CREATE TABLE Contractors (
Contractor_id INT NOT NULL,
Address_id INT NOT NULL,
REFERENCES Addresses ( Address_id ) ,
Name...
PRIMARY KEY ( Contractor_id, Address_id )
) ;
CREATE TABLE Addresses (
Address_id INT NOT NULL PRIMARY KEY,
Address VARCHAR( 40 ) NOT NULL,
City_state_zip VARCHAR( 40 ) NOT NULL,
UNIQUE ( Address, City_state_zip )
.. ) ;
CREATE TABLE Products (
Product_id INT NOT NULL PRIMARY KEY,
Product_name ...
) ;
To keep track of history of change in Product names use another table with
temporal datatypes like:
CREATE TABLE ProductHistory (
Product_id INT NOT NULL,
Product_name VARCHAR ( 100 ) NOT NULL,
Assigned_date DATETIME NOT NULL,
Withdrawn_date DATETIME NOT NULL,
..
PRIMARY KEY ( Product_id, Assigned_date )
CHECK ( Withdrawn_date >= Assigned_date )
);
The same approach can be used for changes in other attributes like symbols
which you mentioned as well.
Anith|||
"Anith Sen" wrote:

> I am not sure where you got the idea of using a globally unique identifier
> type for every key in your tables.
My idea was to single keys instead of complex ones.

> We all work within existing computational constraints; so no business
> segment requires a machine generated global identifier for a table unless
> you are researching on such values.
The reason why I used GUIDs is that this database has to be replicable and
data exchangable with several separate databases. There is to be one master
DB and several slave DBs. Since data can be added in some independent places
- I think I need to use the global identifiers.
My problem is that I have to have a data corresponding to Order, just the
same as it was at the moment of creating of Order. I cannot have situation
when changed contractor data changes data in all orders.
The database has to store names for products in several languages, since
that I cannot put all information about products in one table.
I would like to achieve rather some sort of document revision than history
of action on documents.
Adam|||>> My idea was to single keys instead of complex ones.
Good. Simple keys are a recommended consideration for a primary key.
However, having a uniqueidentifier in a table as the only key in your table
does nothing for entity identification, which is the main purpose of a key
in the first place.
Not necessarily. You could opt for any arbitrary namespace to determine
independent databases distributed over different servers. While
uniqueidentifier type guarantees the value to be unique globally, it cannot
guarantee the uniqueness of the corresponding entity.
For instance, a customer by name Adam in a table in database A cannot be
distinguished from another customer by same name Adam in similar table in a
replicable database B or even in the same table in the same database, just
by virtue of arbitrary GUIDs alone. All you'll have is duplicated entries of
Adam in the table with different GUIDs associated with them. How do you
identify the row corresponding to Adam? How will you enforce entity
integrity? How do you track down an alleged error, for instance in data
entry? Can you use GUIDs for referencing keys reliably without cascading
changes?
It is mostly hard to provide any specific meaningful suggestions here. While
you are familiar with your business model regarding the orders, customers,
languages, symbols, revisions etc., others in this newsgroup have no clue on
what they are or how they are related. You did provide a set of CREATE TABLE
statements without any keys, constraints, references etc. however databases
cannot be designed based on such. Esp. when only a couple of lines of
narrative are provided, there is a high chance that the overall business
model and rules are miscommunicated, misrepresented and/or misunderstood.
As a general suggestion, your approach to use GUIDs all over the table as
primary keys with no identifying attribute seems inherently flawed. However,
if you have made provisions for entity identification using UNIQUE NOT NULL
constraints, perhaps you might be able to work it out to some extent.
Consider using temporal datatypes for tracking historical information unless
you are using them already.
Also a few general design rules of thumb, if it helps:
* When you have a one-to-one relationship between two entity types, unless
there are any non-dependency preserving relationships, you may represent
them in a single table.
* When you have a many-to-one relationship between two entity types, you
should use a referential integrity constraint ( FK ) between the tables
representing these entity types
* When you have a many-to-many relationship between two or more entity
types, you should introduce an "association" table which reduces the schema
to two or more many-to-one relationships on each table representing these
entity types.
Anith|||
"Anith Sen" wrote:
> Good. Simple keys are a recommended consideration for a primary key.
> However, having a uniqueidentifier in a table as the only key in your tabl
e
> does nothing for entity identification, which is the main purpose of a key
> in the first place.[/color]
The rule is - application and database identifies entities by GUID (Id) and
users identifies entities by Symbol.
> All you'll have is duplicated entries of Adam in the table with different
GUIDs
>associated with them. How do you identify the row corresponding to Adam? Ho
w
>will you enforce entity integrity?[/color]
Such data will be input by aware users only - some special roles in
application. It depends on requirements whether database should be able to
store duplicated records or not. I think it should do so for history purpose
s.
> Can you use GUIDs for referencing keys reliably without cascading
> changes?[/color]
Data entites are represented as obiects in application. Identifiers are read
from these obiects - Bussiness Logic takes these identifiers (GUID) and do
with them whatever is needed (search, modify, delete etc.). Bussiness Logic
will take care about all of changes.
> others in this newsgroup have no clue on what they are or how they are related.[/c
olor]
In my first post I have put creationof tables - for general view on my DB
structure (small part of it in fact). Here you are relations added to it:
CREATE TABLE [dbo].[Address] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[CityId] uniqueidentifier NOT NULL ,
[CountryId] uniqueidentifier NOT NULL ,
[StreetName] nvarchar (100) NOT NULL ,
[StreetNo] nvarchar (10) NOT NULL ,
[LocalNo] nvarchar (10) NOT NULL ,
[PostalCode] nvarchar (20) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Contractor] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[AddressId] uniqueidentifier NOT NULL ,
[Symbol] nvarchar (50) NOT NULL ,
[Name] nvarchar (200) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Order] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[ContractorId] uniqueidentifier NOT NULL ,
[Symbol] nvarchar (50) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[OrderProduct] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[OrderId] uniqueidentifier NOT NULL ,
[ProductId] uniqueidentifier NOT NULL ,
[Quantity] float NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Product] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[Symbol] nvarchar (50) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ProductName] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[ProductId] uniqueidentifier NOT NULL ,
[CultureName] nvarchar (20) NOT NULL ,
[Name] nvarchar (200) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Address] ADD
CONSTRAINT [DF_Address_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Contractor] ADD
CONSTRAINT [DF_Contractor_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Contractor] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Order] ADD
CONSTRAINT [DF_Order_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderProduct] ADD
CONSTRAINT [DF_OrderProduct_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_OrderProduct] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Product] ADD
CONSTRAINT [DF_Product_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ProductName] ADD
CONSTRAINT [DF_ProductName_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_ProductName] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Contractor] ADD
CONSTRAINT [FK_Contractor_Address] FOREIGN KEY
(
[AddressId]
) REFERENCES [dbo].[Address] (
[Id]
)
GO
ALTER TABLE [dbo].[Order] ADD
CONSTRAINT [FK_Order_Contractor] FOREIGN KEY
(
[ContractorId]
) REFERENCES [dbo].[Contractor] (
[Id]
)
GO
ALTER TABLE [dbo].[OrderProduct] ADD
CONSTRAINT [FK_OrderProduct_Order] FOREIGN KEY
(
[OrderId]
) REFERENCES [dbo].[Order] (
[Id]
),
CONSTRAINT [FK_OrderProduct_Product] FOREIGN KEY
(
[ProductId]
) REFERENCES [dbo].[Product] (
[Id]
)
GO
ALTER TABLE [dbo].[ProductName] ADD
CONSTRAINT [FK_ProductName_Product] FOREIGN KEY
(
[ProductId]
) REFERENCES [dbo].[Product] (
[Id]
)
GO
Is it now possible that you provide some judge of my record-history idea or
provide some other ideas how to perform this correctly?
Adam|||"Anith Sen" wrote:
> Good. Simple keys are a recommended consideration for a primary key.
> However, having a uniqueidentifier in a table as the only key in your tabl
e
> does nothing for entity identification, which is the main purpose of a key
> in the first place.[/color]
The rule is - application and database identifies entities by GUID (Id) and
users identifies entities by Symbol.
> All you'll have is duplicated entries of Adam in the table with different
GUIDs
>associated with them. How do you identify the row corresponding to Adam? Ho
w
>will you enforce entity integrity?[/color]
Such data will be input by aware users only - some special roles in
application. It depends on requirements whether database should be able to
store duplicated records or not. I think it should do so for history purpose
s.
> Can you use GUIDs for referencing keys reliably without cascading
> changes?[/color]
Data entites are represented as obiects in application. Identifiers are read
from these obiects - Bussiness Logic takes these identifiers (GUID) and do
with them whatever is needed (search, modify, delete etc.). Bussiness Logic
will take care about all of changes.
> others in this newsgroup have no clue on what they are or how they are related.[/c
olor]
In my first post I have put creation of tables - for general view on my DB
structure (small part of it in fact). Here you are relations added to it:
CREATE TABLE [dbo].[Address] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[CityId] uniqueidentifier NOT NULL ,
[CountryId] uniqueidentifier NOT NULL ,
[StreetName] nvarchar (100) NOT NULL ,
[StreetNo] nvarchar (10) NOT NULL ,
[LocalNo] nvarchar (10) NOT NULL ,
[PostalCode] nvarchar (20) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Contractor] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[AddressId] uniqueidentifier NOT NULL ,
[Symbol] nvarchar (50) NOT NULL ,
[Name] nvarchar (200) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Order] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[ContractorId] uniqueidentifier NOT NULL ,
[Symbol] nvarchar (50) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[OrderProduct] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[OrderId] uniqueidentifier NOT NULL ,
[ProductId] uniqueidentifier NOT NULL ,
[Quantity] float NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Product] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[Symbol] nvarchar (50) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ProductName] (
[Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
[ProductId] uniqueidentifier NOT NULL ,
[CultureName] nvarchar (20) NOT NULL ,
[Name] nvarchar (200) NOT NULL ,
[ChangeStamp] timestamp NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Address] ADD
CONSTRAINT [DF_Address_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Contractor] ADD
CONSTRAINT [DF_Contractor_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Contractor] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Order] ADD
CONSTRAINT [DF_Order_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderProduct] ADD
CONSTRAINT [DF_OrderProduct_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_OrderProduct] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Product] ADD
CONSTRAINT [DF_Product_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ProductName] ADD
CONSTRAINT [DF_ProductName_Id] DEFAULT (newid()) FOR [Id],
CONSTRAINT [PK_ProductName] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Contractor] ADD
CONSTRAINT [FK_Contractor_Address] FOREIGN KEY
(
[AddressId]
) REFERENCES [dbo].[Address] (
[Id]
)
GO
ALTER TABLE [dbo].[Order] ADD
CONSTRAINT [FK_Order_Contractor] FOREIGN KEY
(
[ContractorId]
) REFERENCES [dbo].[Contractor] (
[Id]
)
GO
ALTER TABLE [dbo].[OrderProduct] ADD
CONSTRAINT [FK_OrderProduct_Order] FOREIGN KEY
(
[OrderId]
) REFERENCES [dbo].[Order] (
[Id]
),
CONSTRAINT [FK_OrderProduct_Product] FOREIGN KEY
(
[ProductId]
) REFERENCES [dbo].[Product] (
[Id]
)
GO
ALTER TABLE [dbo].[ProductName] ADD
CONSTRAINT [FK_ProductName_Product] FOREIGN KEY
(
[ProductId]
) REFERENCES [dbo].[Product] (
[Id]
)
GO
Can you now provide some judgement of my record-history idea or provide some
other ideas how could it be performed correctly?
Adam

Hierarchy

Hello all,
I have the following table structure (legacy, not my design :-D ):
CREATE TABLE [dbo].[hierarchy]
([idproduct] [int] NOT NULL ,
[name] [varchar] (90) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[idlevel0] [int] NOT NULL ,
[name0] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[idlevel1] [int] NULL ,
[name1] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[idlevel2] [int] NULL ,
[name2] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
)
ON [PRIMARY]
GO
Some data:
insert into hierarchy values (1000, 'Product1', 1, 'root node', 10
'Intermediate'1', 100, 'Intremediate2')
insert into hierarchy values (1000, 'Product1', 1, 'root node', 10
'Intermediate'1', 100, 'Intremediate2')
idproduct field: id of a product
name= name o a product
idlevel0 = id of the root hierarchy
name0 = name of the root hierarchy
idlevel1 = id of the second node in the hierarchy
name1 = name of the second node in the hierarchy
idlevel2 = id of the third node in the hierarchy
name2 = name of the third node in the hierarchy
basically, there can be only three levels in the tree but as much brances as
the number of records are.
what I want is to reformat the hierarchy this way:
nodeid / nodename/ parentid
any hints on this?
Kind regards,
TudorSorry, pressed send before i actually finished:
So, further data:
insert into hierarchy values (1000, 'Product1', 1, 'root node', 10,
'Intermediate10', 100, 'Intremediate200')
insert into hierarchy values (1001, 'Product2', 1, 'root node', 10,
'Intermediate10', 100, 'Intremediate200')
insert into hierarchy values (1003, 'Product3', 1, 'root node', 10,
'Intermediate10', 101, 'Intremediate201')
insert into hierarchy values (1004, 'Product4', 1, 'root node', 11,
'Intermediate11', 100, 'Intremediate202')
insert into hierarchy values (1005, 'Product5', 1, 'root node', 12,
'Intermediate12', 102, 'Intremediate202')
I would like the data to be stored as this:
nodeid nodename parentid
1 rootnode
2 intermediate10 1
3 intermediate200 2
4 intermediate200 2
5 intermediate11 1
etc...
Thanks,
Tudor
"Tudor" <tudor@.hopscotch.com> wrote in message
news:eeN5qxbQGHA.1868@.TK2MSFTNGP09.phx.gbl...
> Hello all,
> I have the following table structure (legacy, not my design :-D ):
> CREATE TABLE [dbo].[hierarchy]
> ([idproduct] [int] NOT NULL ,
> [name] [varchar] (90) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [idlevel0] [int] NOT NULL ,
> [name0] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [idlevel1] [int] NULL ,
> [name1] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [idlevel2] [int] NULL ,
> [name2] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> )
> ON [PRIMARY]
> GO
> Some data:
> insert into hierarchy values (1000, 'Product1', 1, 'root node', 10
> 'Intermediate'1', 100, 'Intremediate2')
> insert into hierarchy values (1000, 'Product1', 1, 'root node', 10
> 'Intermediate'1', 100, 'Intremediate2')
>
> idproduct field: id of a product
> name= name o a product
> idlevel0 = id of the root hierarchy
> name0 = name of the root hierarchy
> idlevel1 = id of the second node in the hierarchy
> name1 = name of the second node in the hierarchy
> idlevel2 = id of the third node in the hierarchy
> name2 = name of the third node in the hierarchy
> basically, there can be only three levels in the tree but as much brances
> as the number of records are.
> what I want is to reformat the hierarchy this way:
> nodeid / nodename/ parentid
> any hints on this?
> Kind regards,
> Tudor
>
>
>

Hierarchical table (count)

Hello

for MS SQL 2000 i am having :

CREATE TABLE [dbo].[Items](
[id_Items] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[id_ItemsSup] [int] NULL,
[Name] [nvarchar] (100) NOT NULL,
[SubItems][int] DEFAULT (0)
) ON [PRIMARY]

with :
UPDATE [Items] SET SubItems = (SELECT COUNT(id_Items) AS ct FROM dbo.Items WHERE id_ItemsSup = 1) WHERE id_Items = 1
I get how many subItems has Item = 1

how can I update the Column SubItems (for each row) ?
to get the total of subItems for each Item ?

thank youupdate Items set subitems = (select count(*) from items where Id_ItemsSup = A.Id_ItemsSup) from Items A|||it works fine

thank you

hiding table with null dataset

=iif( Fields!SomeCol.IsMissing , TRUE, FALSE) does not seem to work in
visibility expression of table properties. any ideas for using
expressions to hide a table with a null dataset would be great.
tia
mikeHi Mike,
In my reports when there is not data to display (null data set) I have a
textbox that is hidden until the NULL dataset is verified. They it shows "No
Data To Display For the Report Criteria" You can use an expression similar
to the following, just revers the True,False logic if you need to.
=iif(rownumber("DataSetName") = 0, false, true)
Hope this helps,
--
Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
http://www.amazon.com/exec/obidos/tg/detail/-/1590594231/102-0081700-1383300
http://www.apress.com/book/bookDisplay.html?bID=365
"michaelwall" <michael.w.i@.gmail.com> wrote in message
news:1102355383.431101.270290@.f14g2000cwb.googlegroups.com...
> =iif( Fields!SomeCol.IsMissing , TRUE, FALSE) does not seem to work in
> visibility expression of table properties. any ideas for using
> expressions to hide a table with a null dataset would be great.
> tia
> mike
>|||perfect ! thanks
"Rodney Landrum" wrote:
> Hi Mike,
> In my reports when there is not data to display (null data set) I have a
> textbox that is hidden until the NULL dataset is verified. They it shows "No
> Data To Display For the Report Criteria" You can use an expression similar
> to the following, just revers the True,False logic if you need to.
> =iif(rownumber("DataSetName") = 0, false, true)
> Hope this helps,
> --
> Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
> http://www.amazon.com/exec/obidos/tg/detail/-/1590594231/102-0081700-1383300
> http://www.apress.com/book/bookDisplay.html?bID=365
>
>
> "michaelwall" <michael.w.i@.gmail.com> wrote in message
> news:1102355383.431101.270290@.f14g2000cwb.googlegroups.com...
> > =iif( Fields!SomeCol.IsMissing , TRUE, FALSE) does not seem to work in
> > visibility expression of table properties. any ideas for using
> > expressions to hide a table with a null dataset would be great.
> > tia
> >
> > mike
> >
>
>|||I responded early - The expression hides the table whether the dataset is
emprty or not.
any further ideas? what do you mean by "until the NULL dataset is
verified" how is this done in the report designer, do i need custom code ? It
seems as if the visibility by expression should cover this but I find very
little documentation and no intellisense to experiment... thanks again ...
"Rodney Landrum" wrote:
> Hi Mike,
> In my reports when there is not data to display (null data set) I have a
> textbox that is hidden until the NULL dataset is verified. They it shows "No
> Data To Display For the Report Criteria" You can use an expression similar
> to the following, just revers the True,False logic if you need to.
> =iif(rownumber("DataSetName") = 0, false, true)
> Hope this helps,
> --
> Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
> http://www.amazon.com/exec/obidos/tg/detail/-/1590594231/102-0081700-1383300
> http://www.apress.com/book/bookDisplay.html?bID=365
>
>
> "michaelwall" <michael.w.i@.gmail.com> wrote in message
> news:1102355383.431101.270290@.f14g2000cwb.googlegroups.com...
> > =iif( Fields!SomeCol.IsMissing , TRUE, FALSE) does not seem to work in
> > visibility expression of table properties. any ideas for using
> > expressions to hide a table with a null dataset would be great.
> > tia
> >
> > mike
> >
>
>|||=iif(ReportItems!txtTotalDistribution.Value >= 1, FALSE, TRUE)
works by looking at an aggregate SUM() field in the table footer
"Rodney Landrum" wrote:
> Hi Mike,
> In my reports when there is not data to display (null data set) I have a
> textbox that is hidden until the NULL dataset is verified. They it shows "No
> Data To Display For the Report Criteria" You can use an expression similar
> to the following, just revers the True,False logic if you need to.
> =iif(rownumber("DataSetName") = 0, false, true)
> Hope this helps,
> --
> Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
> http://www.amazon.com/exec/obidos/tg/detail/-/1590594231/102-0081700-1383300
> http://www.apress.com/book/bookDisplay.html?bID=365
>
>
> "michaelwall" <michael.w.i@.gmail.com> wrote in message
> news:1102355383.431101.270290@.f14g2000cwb.googlegroups.com...
> > =iif( Fields!SomeCol.IsMissing , TRUE, FALSE) does not seem to work in
> > visibility expression of table properties. any ideas for using
> > expressions to hide a table with a null dataset would be great.
> > tia
> >
> > mike
> >
>
>|||Data regions (table, list, matrix, charts, subreports) have a NoRows
property. If set, the NoRows message will be shown instead of the data
region. In addition, style properties (fonts, colors, etc.) directly set on
the data region reportitem (e.g. table) will apply to the NoRows message.
Alternatively, you could use the RowCount aggregate functions to determine
the number of rows in a dataset. E.g. =RowCount("DataSetName")
See also:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_0k6r.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"michaelwall" <michaelwall@.discussions.microsoft.com> wrote in message
news:4D53BEB6-D542-49B1-A8F5-A144990CB93C@.microsoft.com...
> perfect ! thanks
> "Rodney Landrum" wrote:
> > Hi Mike,
> > In my reports when there is not data to display (null data set) I have a
> > textbox that is hidden until the NULL dataset is verified. They it shows
"No
> > Data To Display For the Report Criteria" You can use an expression
similar
> > to the following, just revers the True,False logic if you need to.
> >
> > =iif(rownumber("DataSetName") = 0, false, true)
> >
> > Hope this helps,
> > --
> > Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
> >
http://www.amazon.com/exec/obidos/tg/detail/-/1590594231/102-0081700-1383300
> > http://www.apress.com/book/bookDisplay.html?bID=365
> >
> >
> >
> >
> > "michaelwall" <michael.w.i@.gmail.com> wrote in message
> > news:1102355383.431101.270290@.f14g2000cwb.googlegroups.com...
> > > =iif( Fields!SomeCol.IsMissing , TRUE, FALSE) does not seem to work in
> > > visibility expression of table properties. any ideas for using
> > > expressions to hide a table with a null dataset would be great.
> > > tia
> > >
> > > mike
> > >
> >
> >
> >

hiding null rows from a matrix

All,
I have a matrix that is being populated by a sproc.
The sproc returns data which are broken into sections by fields and
then sorted by another field.
The matrix is group on the section designator.
However, each section is getting an initial row that is null.
I want to drop out the null rows from the matrix.
I am not allowed to change the sproc.
Thanks in advance,
MI have a couple of typos and things to add.
I was trying to say that the matrix is grouped on the section
designator.
within each section this is a null row showing up.
I just want the matrix to filter out the nulls.
I don't have that option to do it from within the sproc.|||Hi Max,
Did you find a solution for this, even I am facing the same problem, I need
to hide the empty rows in a matrix.
Please reply back if you have found any solution for this.
Thanks,
Kiran

Hiding NULL columns from result set

Hello colleagues, I have the following table, that has two flags - show
quantity, price or both. If a flag is not set I would like the corresponding
column to not be included in the result set.
CREATE TABLE Table1 (
Id int IDENTITY (1, 1) NOT NULL ,
Name nvarchar(20) NOT NULL,
Qty int NOT NULL,
Price int NOT NULL,
ShowQty int NOT NULL,
ShowPrice int NOT NULL
)
go
INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('first', 11,
106, 1, 0)
INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('second', 22,
120, 1, 0)
INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('third', 23,
134, 0, 0)
INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('fourth', 44,
90, 1, 0)
INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('fifth', 15,
72, 0, 0)
SELECT
SUM(CASE WHEN ShowQty=1 THEN Qty END) AS 'qty',
SUM(CASE WHEN ShowPrice=1 THEN Price END) AS 'price' /* this field is
null - so it should be hidden*/
FROM
Table1
CheersYOu should handle that from your client application, you cant hide an
expression if you name it in the Select statement. You can check the value
in your client application and reformat the resultset as needed.
May the forces be with you.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"keber" <k@.c.com> schrieb im Newsbeitrag
news:ub9REUiZFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Hello colleagues, I have the following table, that has two flags - show
> quantity, price or both. If a flag is not set I would like the
> corresponding column to not be included in the result set.
> CREATE TABLE Table1 (
> Id int IDENTITY (1, 1) NOT NULL ,
> Name nvarchar(20) NOT NULL,
> Qty int NOT NULL,
> Price int NOT NULL,
> ShowQty int NOT NULL,
> ShowPrice int NOT NULL
> )
> go
> INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('first', 11,
> 106, 1, 0)
> INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('second',
> 22, 120, 1, 0)
> INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('third', 23,
> 134, 0, 0)
> INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('fourth',
> 44, 90, 1, 0)
> INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('fifth', 15,
> 72, 0, 0)
>
> SELECT
> SUM(CASE WHEN ShowQty=1 THEN Qty END) AS 'qty',
> SUM(CASE WHEN ShowPrice=1 THEN Price END) AS 'price' /* this field is
> null - so it should be hidden*/
> FROM
> Table1
> Cheers
>|||You can accomplish a "variable result set" using a stored procedure as follo
ws:
create procedure usp_return_values
as
if (SELECT SUM(CASE WHEN ShowPrice=1 THEN Price END) FROM Table1) is null
SELECT SUM(CASE WHEN ShowQty=1 THEN Qty END) AS 'qty' FROM Table1
else
SELECT
SUM(CASE WHEN ShowQty=1 THEN Qty END) AS 'qty',
SUM(CASE WHEN ShowPrice=1 THEN Price END) AS 'price'
FROM
Table1
go
exec usp_return_values
Edgardo Valdez
MCSD, MCDBA, MCSE
"keber" wrote:

> Hello colleagues, I have the following table, that has two flags - show
> quantity, price or both. If a flag is not set I would like the correspondi
ng
> column to not be included in the result set.
> CREATE TABLE Table1 (
> Id int IDENTITY (1, 1) NOT NULL ,
> Name nvarchar(20) NOT NULL,
> Qty int NOT NULL,
> Price int NOT NULL,
> ShowQty int NOT NULL,
> ShowPrice int NOT NULL
> )
> go
> INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('first', 11,
> 106, 1, 0)
> INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('second', 22
,
> 120, 1, 0)
> INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('third', 23,
> 134, 0, 0)
> INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('fourth', 44
,
> 90, 1, 0)
> INSERT INTO Table1 (Name,Qty,Price,ShowQty,ShowPrice) VALUES ('fifth', 15,
> 72, 0, 0)
>
> SELECT
> SUM(CASE WHEN ShowQty=1 THEN Qty END) AS 'qty',
> SUM(CASE WHEN ShowPrice=1 THEN Price END) AS 'price' /* this field is
> null - so it should be hidden*/
> FROM
> Table1
> Cheers
>
>|||In a tiered arachitecture, display is done in the front end and not the
database. This is a fundamental programming principle .. far more
fundamental than SQL.
We do not use assembly language styles flags in good SQL. Nor do we
use an IDENTITY columns when we have a relational key. Also why is a
price INTEGER and not DECIMAL()? Why do you think that a column is a
field, when they are totally different?
Look at what you wrote; each row would use zero, one or both of the
values in the summations. The results would be meaningless because you
have no data integrity.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1117587045.951057.122160@.g14g2000cwa.googlegroups.com...
[ snip ]

> The results would be meaningless because you
> have no data integrity.
Ahhh ... but for some companies I've done work
for in the past, those are just the type of results
they're looking for. Meaningless.|||Celko, my young friend, I advice you to lose your attitude and not turn this
into a psychopathic discussion of how and what should look like in your
opinion. This is a simplified sample query used for dynamic reporting with
or without any front-end and several entry points. The flags are not a
business rule, they meant to directly affect the resultset.
cheers
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1117587045.951057.122160@.g14g2000cwa.googlegroups.com...
> In a tiered arachitecture, display is done in the front end and not the
> database. This is a fundamental programming principle .. far more
> fundamental than SQL.
> We do not use assembly language styles flags in good SQL. Nor do we
> use an IDENTITY columns when we have a relational key. Also why is a
> price INTEGER and not DECIMAL()? Why do you think that a column is a
> field, when they are totally different?
> Look at what you wrote; each row would use zero, one or both of the
> values in the summations. The results would be meaningless because you
> have no data integrity.
>

Hiding NULL

In SQL 2000 Query Analyzer, you can set up the text output pane to
leave null values blank.

That could give you this (assuming it's set to comma-delimited) --

CREATE TABLE #Test (A int, B int)
INSERT #Test SELECT 1,2
INSERT #Test SELECT NULL,4
INSERT #Test SELECT 5,NULL
INSERT #Test SELECT NULL,NULL
SELECT * FROM #Test

A,B
1,2
,4
5,
,

I can't figure out how to set up 2005 Management Studio the same way.
I can only manage to get outputs like this, with "NULL" instead of
empty fields --

A,B
1,2
NULL,4
5,NULL
NULL,NULL

How can I get rid of the NULLs?

Thanks,
Jim(jim_geissman@.countrywide.com) writes:
> I can't figure out how to set up 2005 Management Studio the same way.
> I can only manage to get outputs like this, with "NULL" instead of
> empty fields --
> A,B
> 1,2
> NULL,4
> 5,NULL
> NULL,NULL
> How can I get rid of the NULLs?

All I can suggest is that you go to
http://lab.msdn.microsoft.com/ProductFeedback/ and submit a suggestion
that Microsoft restores this possibility in the next version of SQL
Server.

There are many features in QA that did not make it to Mgmt Studio. To be
fair, QA has some odd quirks, even if several of them are practical. For
instance, this one is good if you want to copy and paste into Excel. Then
again, in Mgmt Studio, it's easier to copy and paste from grid, as you
now can opt to include the headers. (Tools->Options->Query Results->Results
to Grid.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks, Erland. I will visit MS and make that request.

Jim

Hiding Field if Data is Null

I'm simulating a mailing label-type format that contains mailing
address information:
Name
Title
Company
Address
Address2
City, State Zip
Since not all customers have an address2 line, I want to suppress the
Address2 field if it is null. This way, I don't have a blank line:
Jason Sweet
3154 Alaca Drive
Altadena, CA 91001
Possible?
Thanks in advance.
Jason SweetHi Jason,
I guess you can use the IIF function to set the height of the two text
boxes. something like =IIF(Address2 = "",0,20)
"Jaosn S" wrote:
> I'm simulating a mailing label-type format that contains mailing
> address information:
> Name
> Title
> Company
> Address
> Address2
> City, State Zip
> Since not all customers have an address2 line, I want to suppress the
> Address2 field if it is null. This way, I don't have a blank line:
> Jason Sweet
> 3154 Alaca Drive
> Altadena, CA 91001
> Possible?
> Thanks in advance.
> Jason Sweet
>|||Have each field in a different text box, then in the properties, click the
advanced button and have the visiblity set to expression and use
=iif(Fields!yourfield.Value is Nothing, true, false)
This should then hide the text box if there is no data.
Hope that helps
"Jaosn S" wrote:
> I'm simulating a mailing label-type format that contains mailing
> address information:
> Name
> Title
> Company
> Address
> Address2
> City, State Zip
> Since not all customers have an address2 line, I want to suppress the
> Address2 field if it is null. This way, I don't have a blank line:
> Jason Sweet
> 3154 Alaca Drive
> Altadena, CA 91001
> Possible?
> Thanks in advance.
> Jason Sweet
>|||Shaun,
Did you ever find out how to supress the null lines in an address?
I've tried the following IFF in the Visiblity Hidden Property:
=iif( Fields!D_ADDRESS_LINE_2.Value = "", true, false)
I've tried the following IFF in the Line Height Property:
=iif( Fields!D_ADDRESS_LINE_2.Value = "",0,2)
Neither accomplish the task. Write soon!
carla.thompson@.gwl.com
"Shaun Longhurst" wrote:
> Have each field in a different text box, then in the properties, click the
> advanced button and have the visiblity set to expression and use
> =iif(Fields!yourfield.Value is Nothing, true, false)
> This should then hide the text box if there is no data.
> Hope that helps
> "Jaosn S" wrote:
> > I'm simulating a mailing label-type format that contains mailing
> > address information:
> >
> > Name
> > Title
> > Company
> > Address
> > Address2
> > City, State Zip
> >
> > Since not all customers have an address2 line, I want to suppress the
> > Address2 field if it is null. This way, I don't have a blank line:
> >
> > Jason Sweet
> > 3154 Alaca Drive
> >
> > Altadena, CA 91001
> >
> > Possible?
> >
> > Thanks in advance.
> >
> > Jason Sweet
> >|||Jason,
Did you ever get the correct answer to this question?
Carla
"Jaosn S" wrote:
> I'm simulating a mailing label-type format that contains mailing
> address information:
> Name
> Title
> Company
> Address
> Address2
> City, State Zip
> Since not all customers have an address2 line, I want to suppress the
> Address2 field if it is null. This way, I don't have a blank line:
> Jason Sweet
> 3154 Alaca Drive
> Altadena, CA 91001
> Possible?
> Thanks in advance.
> Jason Sweet
>|||Checking for null (Nothing in VB) can be done two ways:
=(Fields!A.Value is Nothing)
=IsNothing(Fields!A.Value)
When using these expressions for the Visibility.Hidden property you should
get the desired effect.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"C. Lynn" <CLynn@.discussions.microsoft.com> wrote in message
news:787DAFAB-22B6-4BBC-9C13-EA6ADEDBB289@.microsoft.com...
> Shaun,
> Did you ever find out how to supress the null lines in an address?
> I've tried the following IFF in the Visiblity Hidden Property:
> =iif( Fields!D_ADDRESS_LINE_2.Value = "", true, false)
> I've tried the following IFF in the Line Height Property:
> =iif( Fields!D_ADDRESS_LINE_2.Value = "",0,2)
> Neither accomplish the task. Write soon!
> carla.thompson@.gwl.com
>
> "Shaun Longhurst" wrote:
>> Have each field in a different text box, then in the properties, click
>> the
>> advanced button and have the visiblity set to expression and use
>> =iif(Fields!yourfield.Value is Nothing, true, false)
>> This should then hide the text box if there is no data.
>> Hope that helps
>> "Jaosn S" wrote:
>> > I'm simulating a mailing label-type format that contains mailing
>> > address information:
>> >
>> > Name
>> > Title
>> > Company
>> > Address
>> > Address2
>> > City, State Zip
>> >
>> > Since not all customers have an address2 line, I want to suppress the
>> > Address2 field if it is null. This way, I don't have a blank line:
>> >
>> > Jason Sweet
>> > 3154 Alaca Drive
>> >
>> > Altadena, CA 91001
>> >
>> > Possible?
>> >
>> > Thanks in advance.
>> >
>> > Jason Sweet
>> >|||My solution included the following:
1. I used the IFF to set the condition of the Hidden Property of the
Visibility element.
2. If you want just the field to be "Hidden" or blanked out while
maintaining the vertical spacing by not suppressing the entire line, click
the *field*...
3. If you want the entire line to be supressed, click the line tab off to
the left of the table.
4. To set the property, go to the Visibility element and expand it to find
the Hidden Property. Click <expression> and enter the following IFF
statement:
=iif( Fields!D_ADDRESS_LINE_2.Value = "", true, false) <-- substitute your
field name. This till set the Hidden property to True if the field is Null
or False if the field contains a value.
This should work to either suppress or blank either the field or the entire
line.
~~ C. Lynn
"Jaosn S" wrote:
> I'm simulating a mailing label-type format that contains mailing
> address information:
> Name
> Title
> Company
> Address
> Address2
> City, State Zip
> Since not all customers have an address2 line, I want to suppress the
> Address2 field if it is null. This way, I don't have a blank line:
> Jason Sweet
> 3154 Alaca Drive
> Altadena, CA 91001
> Possible?
> Thanks in advance.
> Jason Sweet
>|||I use this setup for doing address labels. It works preety well.
What I do is check if address 2 exists and if it does then print it.
If not then I substitute Address 2 with the city state and zip.
Same with the CSZ line.
I hope this works for you.
=Fields!PayeeName.Value & VBCRLF &
Fields!PayeeAddressLine1.Value & VBCRLF &
IIF(IsNothing(Fields!PayeeAddressLine2.Value), Fields!PayeeCity.Value & ", "
& Fields!PayeeState.Value & " " & Fields!PayeeZipCode.Value,
Fields!PayeeAddressLine2.Value) & VBCRLF &
IIF(IsNothing(Fields!PayeeAddressLine2.Value), "",
Fields!PayeeCity.Value & ", " & Fields!PayeeState.Value & " " &
Fields!PayeeZipCode.Value)
"C. Lynn" wrote:
> My solution included the following:
> 1. I used the IFF to set the condition of the Hidden Property of the
> Visibility element.
> 2. If you want just the field to be "Hidden" or blanked out while
> maintaining the vertical spacing by not suppressing the entire line, click
> the *field*...
> 3. If you want the entire line to be supressed, click the line tab off to
> the left of the table.
> 4. To set the property, go to the Visibility element and expand it to find
> the Hidden Property. Click <expression> and enter the following IFF
> statement:
> =iif( Fields!D_ADDRESS_LINE_2.Value = "", true, false) <-- substitute your
> field name. This till set the Hidden property to True if the field is Null
> or False if the field contains a value.
> This should work to either suppress or blank either the field or the entire
> line.
> ~~ C. Lynn
>
> "Jaosn S" wrote:
> > I'm simulating a mailing label-type format that contains mailing
> > address information:
> >
> > Name
> > Title
> > Company
> > Address
> > Address2
> > City, State Zip
> >
> > Since not all customers have an address2 line, I want to suppress the
> > Address2 field if it is null. This way, I don't have a blank line:
> >
> > Jason Sweet
> > 3154 Alaca Drive
> >
> > Altadena, CA 91001
> >
> > Possible?
> >
> > Thanks in advance.
> >
> > Jason Sweet
> >|||If you use the =iff(IsNothing(field.value),true,false) in the visabillity
expression it will work.
"Fez" wrote:
> I use this setup for doing address labels. It works preety well.
> What I do is check if address 2 exists and if it does then print it.
> If not then I substitute Address 2 with the city state and zip.
> Same with the CSZ line.
> I hope this works for you.
> =Fields!PayeeName.Value & VBCRLF &
> Fields!PayeeAddressLine1.Value & VBCRLF &
> IIF(IsNothing(Fields!PayeeAddressLine2.Value), Fields!PayeeCity.Value & ", "
> & Fields!PayeeState.Value & " " & Fields!PayeeZipCode.Value,
> Fields!PayeeAddressLine2.Value) & VBCRLF &
> IIF(IsNothing(Fields!PayeeAddressLine2.Value), "",
> Fields!PayeeCity.Value & ", " & Fields!PayeeState.Value & " " &
> Fields!PayeeZipCode.Value)
> "C. Lynn" wrote:
> > My solution included the following:
> >
> > 1. I used the IFF to set the condition of the Hidden Property of the
> > Visibility element.
> > 2. If you want just the field to be "Hidden" or blanked out while
> > maintaining the vertical spacing by not suppressing the entire line, click
> > the *field*...
> > 3. If you want the entire line to be supressed, click the line tab off to
> > the left of the table.
> > 4. To set the property, go to the Visibility element and expand it to find
> > the Hidden Property. Click <expression> and enter the following IFF
> > statement:
> > =iif( Fields!D_ADDRESS_LINE_2.Value = "", true, false) <-- substitute your
> > field name. This till set the Hidden property to True if the field is Null
> > or False if the field contains a value.
> >
> > This should work to either suppress or blank either the field or the entire
> > line.
> >
> > ~~ C. Lynn
> >
> >
> > "Jaosn S" wrote:
> >
> > > I'm simulating a mailing label-type format that contains mailing
> > > address information:
> > >
> > > Name
> > > Title
> > > Company
> > > Address
> > > Address2
> > > City, State Zip
> > >
> > > Since not all customers have an address2 line, I want to suppress the
> > > Address2 field if it is null. This way, I don't have a blank line:
> > >
> > > Jason Sweet
> > > 3154 Alaca Drive
> > >
> > > Altadena, CA 91001
> > >
> > > Possible?
> > >
> > > Thanks in advance.
> > >
> > > Jason Sweet
> > >|||Good morning!
I am trying to setup mailing labels and is unable to do so.
I have no idea what I am doing wrong.
Could you please provide me with a solution that you may have.
It would be greatly appreciated.
However, I will pay for the cost of the solution provided if necessary.
Thank you very much for your assistance.
"Fez" wrote:
> I use this setup for doing address labels. It works preety well.
> What I do is check if address 2 exists and if it does then print it.
> If not then I substitute Address 2 with the city state and zip.
> Same with the CSZ line.
> I hope this works for you.
> =Fields!PayeeName.Value & VBCRLF &
> Fields!PayeeAddressLine1.Value & VBCRLF &
> IIF(IsNothing(Fields!PayeeAddressLine2.Value), Fields!PayeeCity.Value & ", "
> & Fields!PayeeState.Value & " " & Fields!PayeeZipCode.Value,
> Fields!PayeeAddressLine2.Value) & VBCRLF &
> IIF(IsNothing(Fields!PayeeAddressLine2.Value), "",
> Fields!PayeeCity.Value & ", " & Fields!PayeeState.Value & " " &
> Fields!PayeeZipCode.Value)
> "C. Lynn" wrote:
> > My solution included the following:
> >
> > 1. I used the IFF to set the condition of the Hidden Property of the
> > Visibility element.
> > 2. If you want just the field to be "Hidden" or blanked out while
> > maintaining the vertical spacing by not suppressing the entire line, click
> > the *field*...
> > 3. If you want the entire line to be supressed, click the line tab off to
> > the left of the table.
> > 4. To set the property, go to the Visibility element and expand it to find
> > the Hidden Property. Click <expression> and enter the following IFF
> > statement:
> > =iif( Fields!D_ADDRESS_LINE_2.Value = "", true, false) <-- substitute your
> > field name. This till set the Hidden property to True if the field is Null
> > or False if the field contains a value.
> >
> > This should work to either suppress or blank either the field or the entire
> > line.
> >
> > ~~ C. Lynn
> >
> >
> > "Jaosn S" wrote:
> >
> > > I'm simulating a mailing label-type format that contains mailing
> > > address information:
> > >
> > > Name
> > > Title
> > > Company
> > > Address
> > > Address2
> > > City, State Zip
> > >
> > > Since not all customers have an address2 line, I want to suppress the
> > > Address2 field if it is null. This way, I don't have a blank line:
> > >
> > > Jason Sweet
> > > 3154 Alaca Drive
> > >
> > > Altadena, CA 91001
> > >
> > > Possible?
> > >
> > > Thanks in advance.
> > >
> > > Jason Sweet
> > >|||Good morning!
I am trying to setup mailing labels and is unable to do so.
I have no idea what I am doing wrong.
Could you please provide me with a solution that you may have.
It would be greatly appreciated.
However, I will pay for the cost of the solution provided if necessary.
Thank you very much for your assistance.
"Shaun Longhurst" wrote:
> Have each field in a different text box, then in the properties, click the
> advanced button and have the visiblity set to expression and use
> =iif(Fields!yourfield.Value is Nothing, true, false)
> This should then hide the text box if there is no data.
> Hope that helps
> "Jaosn S" wrote:
> > I'm simulating a mailing label-type format that contains mailing
> > address information:
> >
> > Name
> > Title
> > Company
> > Address
> > Address2
> > City, State Zip
> >
> > Since not all customers have an address2 line, I want to suppress the
> > Address2 field if it is null. This way, I don't have a blank line:
> >
> > Jason Sweet
> > 3154 Alaca Drive
> >
> > Altadena, CA 91001
> >
> > Possible?
> >
> > Thanks in advance.
> >
> > Jason Sweet
> >|||Good morning!
I am trying to setup mailing labels and is unable to do so.
I have no idea what I am doing wrong.
Could you please provide me with a solution that you may have.
It would be greatly appreciated.
However, I will pay for the cost of the solution provided if necessary.
Thank you very much for your assistance.
"C. Lynn" wrote:
> Jason,
> Did you ever get the correct answer to this question?
> Carla
> "Jaosn S" wrote:
> > I'm simulating a mailing label-type format that contains mailing
> > address information:
> >
> > Name
> > Title
> > Company
> > Address
> > Address2
> > City, State Zip
> >
> > Since not all customers have an address2 line, I want to suppress the
> > Address2 field if it is null. This way, I don't have a blank line:
> >
> > Jason Sweet
> > 3154 Alaca Drive
> >
> > Altadena, CA 91001
> >
> > Possible?
> >
> > Thanks in advance.
> >
> > Jason Sweet
> >|||Good morning!
I am trying to setup mailing labels and is unable to do so.
I have no idea what I am doing wrong.
Could you please provide me with a solution that you may have.
It would be greatly appreciated.
However, I will pay for the cost of the solution provided if necessary.
Thank you very much for your assistance.
"Jaosn S" wrote:
> I'm simulating a mailing label-type format that contains mailing
> address information:
> Name
> Title
> Company
> Address
> Address2
> City, State Zip
> Since not all customers have an address2 line, I want to suppress the
> Address2 field if it is null. This way, I don't have a blank line:
> Jason Sweet
> 3154 Alaca Drive
> Altadena, CA 91001
> Possible?
> Thanks in advance.
> Jason Sweet
>|||I type your suggestion (below) into the visible property of one of my label
lines and it still is not hiding the line. Do you have any other
suggestions? Thanks for your help.
"Linda Anton" wrote:
> If you use the =iff(IsNothing(field.value),true,false) in the visabillity
> expression it will work.
> "Fez" wrote:
> > I use this setup for doing address labels. It works preety well.
> > What I do is check if address 2 exists and if it does then print it.
> > If not then I substitute Address 2 with the city state and zip.
> > Same with the CSZ line.
> > I hope this works for you.
> >
> > =Fields!PayeeName.Value & VBCRLF &
> > Fields!PayeeAddressLine1.Value & VBCRLF &
> > IIF(IsNothing(Fields!PayeeAddressLine2.Value), Fields!PayeeCity.Value & ", "
> > & Fields!PayeeState.Value & " " & Fields!PayeeZipCode.Value,
> > Fields!PayeeAddressLine2.Value) & VBCRLF &
> > IIF(IsNothing(Fields!PayeeAddressLine2.Value), "",
> > Fields!PayeeCity.Value & ", " & Fields!PayeeState.Value & " " &
> > Fields!PayeeZipCode.Value)
> >
> > "C. Lynn" wrote:
> >
> > > My solution included the following:
> > >
> > > 1. I used the IFF to set the condition of the Hidden Property of the
> > > Visibility element.
> > > 2. If you want just the field to be "Hidden" or blanked out while
> > > maintaining the vertical spacing by not suppressing the entire line, click
> > > the *field*...
> > > 3. If you want the entire line to be supressed, click the line tab off to
> > > the left of the table.
> > > 4. To set the property, go to the Visibility element and expand it to find
> > > the Hidden Property. Click <expression> and enter the following IFF
> > > statement:
> > > =iif( Fields!D_ADDRESS_LINE_2.Value = "", true, false) <-- substitute your
> > > field name. This till set the Hidden property to True if the field is Null
> > > or False if the field contains a value.
> > >
> > > This should work to either suppress or blank either the field or the entire
> > > line.
> > >
> > > ~~ C. Lynn
> > >
> > >
> > > "Jaosn S" wrote:
> > >
> > > > I'm simulating a mailing label-type format that contains mailing
> > > > address information:
> > > >
> > > > Name
> > > > Title
> > > > Company
> > > > Address
> > > > Address2
> > > > City, State Zip
> > > >
> > > > Since not all customers have an address2 line, I want to suppress the
> > > > Address2 field if it is null. This way, I don't have a blank line:
> > > >
> > > > Jason Sweet
> > > > 3154 Alaca Drive
> > > >
> > > > Altadena, CA 91001
> > > >
> > > > Possible?
> > > >
> > > > Thanks in advance.
> > > >
> > > > Jason Sweet
> > > >