Hi all,
Another interesting question for ya :P
When contructing a view, I hit across a field that internally is stored as a
single charactor to represent a status, like 'P' = Pending, 'C' = cancelled
etc.
Now, when I create a view, I want this view to say the full word 'Pending'
or 'Cancelled' etc, but when I try to write a conditional expression it kick
it out!
When I use IF statement, it assumes its all a string, and if I use the IIF
it says that the function doesn't exist!?
Seems a little strange how something as simple as a conditional statement
can be made so difficult, so please, someone put me out of my misery and tel
l
me how its done! :P
ThanksTry CASE
"-Ldwater" wrote:
> Hi all,
> Another interesting question for ya :P
> When contructing a view, I hit across a field that internally is stored as
a
> single charactor to represent a status, like 'P' = Pending, 'C' = cancelle
d
> etc.
> Now, when I create a view, I want this view to say the full word 'Pending'
> or 'Cancelled' etc, but when I try to write a conditional expression it ki
ck
> it out!
> When I use IF statement, it assumes its all a string, and if I use the IIF
> it says that the function doesn't exist!?
> Seems a little strange how something as simple as a conditional statement
> can be made so difficult, so please, someone put me out of my misery and t
ell
> me how its done! :P
> Thanks|||> but when I try to write a conditional expression it kick it out!
Can you be more specific? What conditional expression did you try? What
does "kick it out" mean? Do you get an error message? If so, what is it?
What tool are you using to create your view?
> When I use IF statement, it assumes its all a string, and if I use the IIF
> it says that the function doesn't exist!?
(a) you can't use IF in a view. A view is a query, and is not eligible for
logic flow (if is not a conditional expression).
(b) there is no IIF in T-SQL. The closest place you will find this is
Analysis Services, and then Access.
Perhaps you meant to use CASE.
CREATE VIEW dbo.myView
AS
SELECT status = CASE status
WHEN 'P' THEN 'Pending'
WHEN 'C' THEN 'Cancelled'
END, other columns
FROM table
However, the view designer in Enterprise Manager won't allow for CASE, so I
recommend you get in the habit of creating such scripts in Query Analyzer.
See http://www.aspfaq.com/2455
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.|||Use CASE
SELECT
CASE colname
WHEN 'p' THEN 'Pending'
WHEN 'c' THEN 'Cancelled'
ELSE NULL
END
, colname2
FROM...
Or, create another table with two columns, one for the code and another code
the description and do
a join between the tables.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:9C6EAC75-FCAB-4860-9651-F26D31CD05B7@.microsoft.com...
> Hi all,
> Another interesting question for ya :P
> When contructing a view, I hit across a field that internally is stored as
a
> single charactor to represent a status, like 'P' = Pending, 'C' = cancelle
d
> etc.
> Now, when I create a view, I want this view to say the full word 'Pending'
> or 'Cancelled' etc, but when I try to write a conditional expression it ki
ck
> it out!
> When I use IF statement, it assumes its all a string, and if I use the IIF
> it says that the function doesn't exist!?
> Seems a little strange how something as simple as a conditional statement
> can be made so difficult, so please, someone put me out of my misery and t
ell
> me how its done! :P
> Thanks|||The CASE expression is what you need and it's actually much more
powerful than the IIF function that you are probably familiar with from
Access and VB:
SELECT ... ,
CASE status
WHEN 'P' THEN 'Pending'
WHEN 'C' THEN 'Cancelled'
END AS status
FROM YourTable
or
SELECT ... ,
CASE
WHEN status = 'P' THEN 'Pending'
WHEN status = 'C' THEN 'Cancelled'
END AS status
FROM YourTable
David Portas
SQL Server MVP
--|||Try the case statement.
CASE WHEN [Field]='C' THEN 'Cancelled' WHEN [Field]='P' THEN 'Pending' ...
ELSE 'default text' END AS [Aliased Field Name]
You can put in as many WHEN clauses as you like, using the syntax above.
Also note that you don't need the ELSE clause. Don't forget the END like I
always do.
Note that IIF isn't a SQL function. It works in Jet DB queries, but not SQL
Server.
"-Ldwater" wrote:
> Hi all,
> Another interesting question for ya :P
> When contructing a view, I hit across a field that internally is stored as
a
> single charactor to represent a status, like 'P' = Pending, 'C' = cancelle
d
> etc.
> Now, when I create a view, I want this view to say the full word 'Pending'
> or 'Cancelled' etc, but when I try to write a conditional expression it ki
ck
> it out!
> When I use IF statement, it assumes its all a string, and if I use the IIF
> it says that the function doesn't exist!?
> Seems a little strange how something as simple as a conditional statement
> can be made so difficult, so please, someone put me out of my misery and t
ell
> me how its done! :P
> Thanks|||Thanks all, were a little new at writing views, and it seems a bit.. well,
stupid if the CASE statement isn't supported in the Enterprise manager
Thanks for the hints, I think were gonna keep looking into it!|||Enterprise Manager really isn't designed to be a full featured query writing
environment. It is a MANAGEMENT tool and is really not used by anyone
writing serious queries.
Query Analzyer is for writing queries.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:8421F4B4-F86C-40BA-B2DF-3597AB73E7DE@.microsoft.com...
> Thanks all, were a little new at writing views, and it seems a bit.. well,
> stupid if the CASE statement isn't supported in the Enterprise manager
> Thanks for the hints, I think were gonna keep looking into it!
Showing posts with label interesting. Show all posts
Showing posts with label interesting. Show all posts
Monday, March 19, 2012
Sunday, March 11, 2012
conditional relationship to multiple tables
Hello all.
I have what I think is an interesting database issue. In a nutshell, I want to know if it is possible and if so how one can setup a table to optionally relate to different tables. Let me explain, consider the following two tables (in simple psuedo-sql syntax):
Table Messages
------
ID - Int, PK
Name - varchar
Type - varchar
Table MessageFields
------
ID - Int, PK
PID - Int, FK
Name - varchar
Type - varchar
Size - int
Relationship:
MessageFields.PID relates to Messages.ID
These tables store information used to parse messages. They are related via a straight forward one-to-many relationship where the PID in MessageFields is the FK that relates to ID in Messages. In this simple kind of relationship, it is easy to setup referential integrity and cascaded deletes, etc...
Now, this worked fine as long as each message simply had it's fields and that was it. However, some fields can have sub-fields (if field is an array, it will have x number of subfields corresponding to each array element). Also, those sub-fields can have sub-fields. In fact, there is no set limit, although in practice it will probably only go 3 levels deep in subfields.
Anyway, the way to represent an arbirary subfield structure like this is to use a recursive table structure, where the FK field in the table (PID in this case) refers to the PK field in the same table (ID), like so:
Table MessageFields
------
ID - Int, PK
PID - Int, FK
Name - varchar
Type - varchar
Size - int
Relationship:
MessageFields.PID relates to MessageFields.ID
I believe you can even setup referential integrity and cascaded deletes on such a self-referecing, recursive setup.
The problem is, we still need to relate the MessageFields table to the Messages table. Sooo, the only way to do this that I have come up with is a setup like this:
Table Messages
------
ID - Int, PK
Name - varchar
Type - varchar
Table MessageFields
------
ID - Int, PK
PID - Int, FK
ParType - char(1)
Name - varchar
Type - varchar
Size - int
Relationship:
If ParType = 'M' then
MessageFields.PID relates to Messages.ID
elseif ParType = 'F' then
MessageFields.PID relates to MessageFields.ID
endif
Problem is, I don't think it is possible to setup a relationship (and referential integrity) on a condition like this.
So, my question is, is there a way to setup such a relationship? Is this even a good idea, or is there some standard, better way to setup these tables? Of course, I know I can just setup the tables this way and NOT use a defined relationship, and just be careful in the code that I'm not inserting something incorrect, but I'd rather not. One idea I did have was use a trigger to enforce my referential integrity. The trigger could check inserts into the messagefields table and test the value of partype, then test to see if the inserted row matches the appropriate column in the appropriate table. But before I go down that road, I'd like to see what someone else thinks.
Thanks much for any info/insight someone can give me on this.It's an interesting problem and one I've recently encountered. In our logical model we used subtypes. We have a 'type' of locator with 'subtypes' of physical, tele and postal. So we need to relate entities to locators (many-to-many) based on the type of locator.
entity:
entity_id
entity_locator_participation
entity_id
locator_id
locator_type
tele_locator
locator_id
phone_nbr
email_addr
etc...
postal_locator
locator_id
addr1
addr2
city
etc...
We need to be able to relate entity_locator_participation to tele_locator, postal_locator, or physical_locator depending on the value of the locator_type. For now, we just have no relationship and are maintaining it through code(stored procedures), but it's ugly. We've also thought about encapsulating the logic in triggers.
It's very similar to the probem you bring up. To the best of my knowledge, there is no way to do this, so I'm interested to see what other solutions people have come up with!
-Loach|||Yes, I don't think it is acutally going to be possible to define the conditional relationship directly in sqlserver. I'm leaning towards using a trigger, so at least you can still control the relationship at a db level, and the front end programmers don't have to perform the check. I tested the following trigger, which seems to work for check referential integrity on inserts:
CREATE TRIGGER [trigger1] ON [dbo].[MessageFields]
FOR INSERT
AS
begin
declare @.id int
declare @.partype varchar
declare @.pid int
declare @.result int
set @.id = (select id from inserted)
set @.pid = (select pid from inserted)
set @.partype = (select partype from inserted)
if @.partype = 'm' or @.partype = 'M'
begin
set @.result = (select count(*) from message where id = @.pid)
if @.result = 0
begin
print 'problem - no related row in message!'
delete from messagefields where id = @.id
end
end
else if @.partype = 'f'
begin
set @.result = (select count(*) from messagefields where id = @.pid)
if @.result = 0
begin
print 'problem - no related row in messagefields!'
delete from messagefields where id = @.id
end
end
end
Now I guess I need to setup the triggers for the update and especially the delete. The delete trigger will be a nested/recursive trigger. This shouldn't be a problem, as sql server allows like 32 levels of trigger nesting, and we'll never get that deep in our hierarchy.
Tony|||Originally posted by foxybanjo
Yes, I don't think it is acutally going to be possible to define the conditional relationship directly in sqlserver. I'm leaning towards using a trigger, so at least you can still control the relationship at a db level, and the front end programmers don't have to perform the check. I tested the following trigger, which seems to work for check referential integrity on inserts:
CREATE TRIGGER [trigger1] ON [dbo].[MessageFields]
FOR INSERT
AS
begin
declare @.id int
declare @.partype varchar
declare @.pid int
declare @.result int
set @.id = (select id from inserted)
set @.pid = (select pid from inserted)
set @.partype = (select partype from inserted)
if @.partype = 'm' or @.partype = 'M'
begin
set @.result = (select count(*) from message where id = @.pid)
if @.result = 0
begin
print 'problem - no related row in message!'
delete from messagefields where id = @.id
end
end
else if @.partype = 'f'
begin
set @.result = (select count(*) from messagefields where id = @.pid)
if @.result = 0
begin
print 'problem - no related row in messagefields!'
delete from messagefields where id = @.id
end
end
end
Now I guess I need to setup the triggers for the update and especially the delete. The delete trigger will be a nested/recursive trigger. This shouldn't be a problem, as sql server allows like 32 levels of trigger nesting, and we'll never get that deep in our hierarchy.
Tony
I had a similar problem in my database and I used this approach:
I wanted to create some tables to hold some survey template data. The main table held the name of the template and some other general information. There were a number of other tables representing each type of template. A one-many relationship was created between the template table and each of the survey tables. A template type Id in the template table would identify which survey table was to be used and this was set in a view. The table set up looks like:
Template Table:
TemplateID - PK
TemplateName
TemplateTypeID - identifies which template table you are using
Customer Survey Template
CustomerSurveyTemplateID - PK
TemplateID - FK (one - many with the template table)
{other customer survey template columns}
Void Log Survey Template
VoidLogSurveyTemplateID - PK
TemplateID - FK (one - many with the template table)
{other void log survey template columns)
A view is used for each survey filtered by the templatetypeId . I found this to be very flexible and versatile and easy to use in the front end (in my case an Access database with a data grid)
I have what I think is an interesting database issue. In a nutshell, I want to know if it is possible and if so how one can setup a table to optionally relate to different tables. Let me explain, consider the following two tables (in simple psuedo-sql syntax):
Table Messages
------
ID - Int, PK
Name - varchar
Type - varchar
Table MessageFields
------
ID - Int, PK
PID - Int, FK
Name - varchar
Type - varchar
Size - int
Relationship:
MessageFields.PID relates to Messages.ID
These tables store information used to parse messages. They are related via a straight forward one-to-many relationship where the PID in MessageFields is the FK that relates to ID in Messages. In this simple kind of relationship, it is easy to setup referential integrity and cascaded deletes, etc...
Now, this worked fine as long as each message simply had it's fields and that was it. However, some fields can have sub-fields (if field is an array, it will have x number of subfields corresponding to each array element). Also, those sub-fields can have sub-fields. In fact, there is no set limit, although in practice it will probably only go 3 levels deep in subfields.
Anyway, the way to represent an arbirary subfield structure like this is to use a recursive table structure, where the FK field in the table (PID in this case) refers to the PK field in the same table (ID), like so:
Table MessageFields
------
ID - Int, PK
PID - Int, FK
Name - varchar
Type - varchar
Size - int
Relationship:
MessageFields.PID relates to MessageFields.ID
I believe you can even setup referential integrity and cascaded deletes on such a self-referecing, recursive setup.
The problem is, we still need to relate the MessageFields table to the Messages table. Sooo, the only way to do this that I have come up with is a setup like this:
Table Messages
------
ID - Int, PK
Name - varchar
Type - varchar
Table MessageFields
------
ID - Int, PK
PID - Int, FK
ParType - char(1)
Name - varchar
Type - varchar
Size - int
Relationship:
If ParType = 'M' then
MessageFields.PID relates to Messages.ID
elseif ParType = 'F' then
MessageFields.PID relates to MessageFields.ID
endif
Problem is, I don't think it is possible to setup a relationship (and referential integrity) on a condition like this.
So, my question is, is there a way to setup such a relationship? Is this even a good idea, or is there some standard, better way to setup these tables? Of course, I know I can just setup the tables this way and NOT use a defined relationship, and just be careful in the code that I'm not inserting something incorrect, but I'd rather not. One idea I did have was use a trigger to enforce my referential integrity. The trigger could check inserts into the messagefields table and test the value of partype, then test to see if the inserted row matches the appropriate column in the appropriate table. But before I go down that road, I'd like to see what someone else thinks.
Thanks much for any info/insight someone can give me on this.It's an interesting problem and one I've recently encountered. In our logical model we used subtypes. We have a 'type' of locator with 'subtypes' of physical, tele and postal. So we need to relate entities to locators (many-to-many) based on the type of locator.
entity:
entity_id
entity_locator_participation
entity_id
locator_id
locator_type
tele_locator
locator_id
phone_nbr
email_addr
etc...
postal_locator
locator_id
addr1
addr2
city
etc...
We need to be able to relate entity_locator_participation to tele_locator, postal_locator, or physical_locator depending on the value of the locator_type. For now, we just have no relationship and are maintaining it through code(stored procedures), but it's ugly. We've also thought about encapsulating the logic in triggers.
It's very similar to the probem you bring up. To the best of my knowledge, there is no way to do this, so I'm interested to see what other solutions people have come up with!
-Loach|||Yes, I don't think it is acutally going to be possible to define the conditional relationship directly in sqlserver. I'm leaning towards using a trigger, so at least you can still control the relationship at a db level, and the front end programmers don't have to perform the check. I tested the following trigger, which seems to work for check referential integrity on inserts:
CREATE TRIGGER [trigger1] ON [dbo].[MessageFields]
FOR INSERT
AS
begin
declare @.id int
declare @.partype varchar
declare @.pid int
declare @.result int
set @.id = (select id from inserted)
set @.pid = (select pid from inserted)
set @.partype = (select partype from inserted)
if @.partype = 'm' or @.partype = 'M'
begin
set @.result = (select count(*) from message where id = @.pid)
if @.result = 0
begin
print 'problem - no related row in message!'
delete from messagefields where id = @.id
end
end
else if @.partype = 'f'
begin
set @.result = (select count(*) from messagefields where id = @.pid)
if @.result = 0
begin
print 'problem - no related row in messagefields!'
delete from messagefields where id = @.id
end
end
end
Now I guess I need to setup the triggers for the update and especially the delete. The delete trigger will be a nested/recursive trigger. This shouldn't be a problem, as sql server allows like 32 levels of trigger nesting, and we'll never get that deep in our hierarchy.
Tony|||Originally posted by foxybanjo
Yes, I don't think it is acutally going to be possible to define the conditional relationship directly in sqlserver. I'm leaning towards using a trigger, so at least you can still control the relationship at a db level, and the front end programmers don't have to perform the check. I tested the following trigger, which seems to work for check referential integrity on inserts:
CREATE TRIGGER [trigger1] ON [dbo].[MessageFields]
FOR INSERT
AS
begin
declare @.id int
declare @.partype varchar
declare @.pid int
declare @.result int
set @.id = (select id from inserted)
set @.pid = (select pid from inserted)
set @.partype = (select partype from inserted)
if @.partype = 'm' or @.partype = 'M'
begin
set @.result = (select count(*) from message where id = @.pid)
if @.result = 0
begin
print 'problem - no related row in message!'
delete from messagefields where id = @.id
end
end
else if @.partype = 'f'
begin
set @.result = (select count(*) from messagefields where id = @.pid)
if @.result = 0
begin
print 'problem - no related row in messagefields!'
delete from messagefields where id = @.id
end
end
end
Now I guess I need to setup the triggers for the update and especially the delete. The delete trigger will be a nested/recursive trigger. This shouldn't be a problem, as sql server allows like 32 levels of trigger nesting, and we'll never get that deep in our hierarchy.
Tony
I had a similar problem in my database and I used this approach:
I wanted to create some tables to hold some survey template data. The main table held the name of the template and some other general information. There were a number of other tables representing each type of template. A one-many relationship was created between the template table and each of the survey tables. A template type Id in the template table would identify which survey table was to be used and this was set in a view. The table set up looks like:
Template Table:
TemplateID - PK
TemplateName
TemplateTypeID - identifies which template table you are using
Customer Survey Template
CustomerSurveyTemplateID - PK
TemplateID - FK (one - many with the template table)
{other customer survey template columns}
Void Log Survey Template
VoidLogSurveyTemplateID - PK
TemplateID - FK (one - many with the template table)
{other void log survey template columns)
A view is used for each survey filtered by the templatetypeId . I found this to be very flexible and versatile and easy to use in the front end (in my case an Access database with a data grid)
Wednesday, March 7, 2012
Conditional group footer sum
HI,
I am working with a developer who has an interesting problem. They have
data field in a sql table that is of a numeric value a second field that
tells whether or not the previous value is a debit or credit for a general
ledger.
They want to show on a report a sum of the numeric field in a group footer.
We have tried writing a conditional formula(=Iif(FieldB="dr",Sum(FieldA),o))
for a hidden field on the report in the detail row of a table and then using
the Reportitems Syntax to display that fields value in the group footer, but
we get an out of scope error.
Looking for possible suggestions, code sample, or alternatives. Changing
the data in the SQL table is not a possibility.
Thanks!!I recently tried doing something similar to this but I was getting a data
type error. I resolved it by making the following change:
Orig: sum(iif(Fields!Type.Value = 'Dr', Fields!Amount.Value, 0) --Got errors
New: sum(iif(Fields!Type.Value = 'Dr', Fields!Amount.Value,
Fields!Amount.Value*0)
I don't know why mulitplying by 0 gives the correct data type, but simply
putting a 0 in does not, but that is only variation (0, 0.0, 0.00, etc...) I
could find that worked.
"Mark" wrote:
> HI,
> I am working with a developer who has an interesting problem. They have
> data field in a sql table that is of a numeric value a second field that
> tells whether or not the previous value is a debit or credit for a general
> ledger.
> They want to show on a report a sum of the numeric field in a group footer.
> We have tried writing a conditional formula(=Iif(FieldB="dr",Sum(FieldA),o))
> for a hidden field on the report in the detail row of a table and then using
> the Reportitems Syntax to display that fields value in the group footer, but
> we get an out of scope error.
> Looking for possible suggestions, code sample, or alternatives. Changing
> the data in the SQL table is not a possibility.
> Thanks!!|||The reason why multiplying with 0 works is that it preserves the original
datatype of the numeric field (which could be anything like UInt16, Decimal,
etc.).
This should work (the constant value 0.0 is a System.Double at runtime):
=sum(iif(Fields!Type.Value = 'Dr', CDbl(Fields!Amount.Value), 0.0)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
news:9EAB31A6-E7CA-4912-82BC-A678EAE9CA0C@.microsoft.com...
> I recently tried doing something similar to this but I was getting a data
> type error. I resolved it by making the following change:
> Orig: sum(iif(Fields!Type.Value = 'Dr', Fields!Amount.Value, 0) --Got
errors
> New: sum(iif(Fields!Type.Value = 'Dr', Fields!Amount.Value,
> Fields!Amount.Value*0)
> I don't know why mulitplying by 0 gives the correct data type, but simply
> putting a 0 in does not, but that is only variation (0, 0.0, 0.00, etc...)
I
> could find that worked.
> "Mark" wrote:
> > HI,
> >
> > I am working with a developer who has an interesting problem. They have
> > data field in a sql table that is of a numeric value a second field that
> > tells whether or not the previous value is a debit or credit for a
general
> > ledger.
> >
> > They want to show on a report a sum of the numeric field in a group
footer.
> > We have tried writing a conditional
formula(=Iif(FieldB="dr",Sum(FieldA),o))
> > for a hidden field on the report in the detail row of a table and then
using
> > the Reportitems Syntax to display that fields value in the group
footer, but
> > we get an out of scope error.
> >
> > Looking for possible suggestions, code sample, or alternatives.
Changing
> > the data in the SQL table is not a possibility.
> >
> > Thanks!!
I am working with a developer who has an interesting problem. They have
data field in a sql table that is of a numeric value a second field that
tells whether or not the previous value is a debit or credit for a general
ledger.
They want to show on a report a sum of the numeric field in a group footer.
We have tried writing a conditional formula(=Iif(FieldB="dr",Sum(FieldA),o))
for a hidden field on the report in the detail row of a table and then using
the Reportitems Syntax to display that fields value in the group footer, but
we get an out of scope error.
Looking for possible suggestions, code sample, or alternatives. Changing
the data in the SQL table is not a possibility.
Thanks!!I recently tried doing something similar to this but I was getting a data
type error. I resolved it by making the following change:
Orig: sum(iif(Fields!Type.Value = 'Dr', Fields!Amount.Value, 0) --Got errors
New: sum(iif(Fields!Type.Value = 'Dr', Fields!Amount.Value,
Fields!Amount.Value*0)
I don't know why mulitplying by 0 gives the correct data type, but simply
putting a 0 in does not, but that is only variation (0, 0.0, 0.00, etc...) I
could find that worked.
"Mark" wrote:
> HI,
> I am working with a developer who has an interesting problem. They have
> data field in a sql table that is of a numeric value a second field that
> tells whether or not the previous value is a debit or credit for a general
> ledger.
> They want to show on a report a sum of the numeric field in a group footer.
> We have tried writing a conditional formula(=Iif(FieldB="dr",Sum(FieldA),o))
> for a hidden field on the report in the detail row of a table and then using
> the Reportitems Syntax to display that fields value in the group footer, but
> we get an out of scope error.
> Looking for possible suggestions, code sample, or alternatives. Changing
> the data in the SQL table is not a possibility.
> Thanks!!|||The reason why multiplying with 0 works is that it preserves the original
datatype of the numeric field (which could be anything like UInt16, Decimal,
etc.).
This should work (the constant value 0.0 is a System.Double at runtime):
=sum(iif(Fields!Type.Value = 'Dr', CDbl(Fields!Amount.Value), 0.0)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brandon Lunt" <BrandonLunt@.discussions.microsoft.com> wrote in message
news:9EAB31A6-E7CA-4912-82BC-A678EAE9CA0C@.microsoft.com...
> I recently tried doing something similar to this but I was getting a data
> type error. I resolved it by making the following change:
> Orig: sum(iif(Fields!Type.Value = 'Dr', Fields!Amount.Value, 0) --Got
errors
> New: sum(iif(Fields!Type.Value = 'Dr', Fields!Amount.Value,
> Fields!Amount.Value*0)
> I don't know why mulitplying by 0 gives the correct data type, but simply
> putting a 0 in does not, but that is only variation (0, 0.0, 0.00, etc...)
I
> could find that worked.
> "Mark" wrote:
> > HI,
> >
> > I am working with a developer who has an interesting problem. They have
> > data field in a sql table that is of a numeric value a second field that
> > tells whether or not the previous value is a debit or credit for a
general
> > ledger.
> >
> > They want to show on a report a sum of the numeric field in a group
footer.
> > We have tried writing a conditional
formula(=Iif(FieldB="dr",Sum(FieldA),o))
> > for a hidden field on the report in the detail row of a table and then
using
> > the Reportitems Syntax to display that fields value in the group
footer, but
> > we get an out of scope error.
> >
> > Looking for possible suggestions, code sample, or alternatives.
Changing
> > the data in the SQL table is not a possibility.
> >
> > Thanks!!
Subscribe to:
Posts (Atom)