I have a report in which I need to display an Image in the header, that is
coming from Database or sometimes an alternative text in place of the image.
Both ways I couldn't do it.
Case Text: I have tried several ways such as using ReportItem Parameters,
but the problem is the text is displayed in the first page of a group. If the
group region needs morethan one page when the report is rendered then the
next pages will not have the header or footer text.
Case Image: No means at all!!!!!
Is there any way of achieving this? Please help ...
--
GoitThere is an option on the Grouping and Sorting Properties dialog to repeat
group headers/footers. If you select the checkbox, the group header will be
repeated and hence the referenced reportitem value will show up in page
header/footer.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Goitom" <Goitom@.discussions.microsoft.com> wrote in message
news:D4AB0430-7F67-4CC1-BDE2-4B29E7254F48@.microsoft.com...
>I have a report in which I need to display an Image in the header, that is
> coming from Database or sometimes an alternative text in place of the
> image.
> Both ways I couldn't do it.
> Case Text: I have tried several ways such as using ReportItem Parameters,
> but the problem is the text is displayed in the first page of a group. If
> the
> group region needs morethan one page when the report is rendered then the
> next pages will not have the header or footer text.
> Case Image: No means at all!!!!!
> Is there any way of achieving this? Please help ...
> --
> Goit|||Thanks Robert,
You are right, I will check this out but how about the image? Your solution
could be used only for the case of text field. But still I am not able to
display an image logo in the header of the report that needs to be repeated
in all the pages and its source is database
Goit
"Robert Bruckner [MSFT]" wrote:
> There is an option on the Grouping and Sorting Properties dialog to repeat
> group headers/footers. If you select the checkbox, the group header will be
> repeated and hence the referenced reportitem value will show up in page
> header/footer.
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Goitom" <Goitom@.discussions.microsoft.com> wrote in message
> news:D4AB0430-7F67-4CC1-BDE2-4B29E7254F48@.microsoft.com...
> >I have a report in which I need to display an Image in the header, that is
> > coming from Database or sometimes an alternative text in place of the
> > image.
> > Both ways I couldn't do it.
> > Case Text: I have tried several ways such as using ReportItem Parameters,
> > but the problem is the text is displayed in the first page of a group. If
> > the
> > group region needs morethan one page when the report is rendered then the
> > next pages will not have the header or footer text.
> >
> > Case Image: No means at all!!!!!
> >
> > Is there any way of achieving this? Please help ...
> > --
> > Goit
>
>|||It sounds like you want the same image to show up in the header of every
page. There are several options to achieve this:
* use an "embedded" image in the report. This works well if the image is
e.g. a company logo.
* use a "project" image which is part of the report solution. The image will
get published as resource to the report server
* use a "web" image (aka external image) - which requires at least RS 2000
SP1.
If you really need the image to come from the database, you can do the
following:
* add a dataset DImage to the report which retrieves the image data - e.g.
as ImageData column.
* add a new report parameter PImage of type String. Make the parameter
hidden (non-prompted). Set the parameter default as query-based and choose
ImageData as column.
* add a new Image report item in the page header/footer. Set the image type
to "Database". For the image value expression use =Parameters!PImage.Value.
Don't forget the set the mimetype correctly.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Goitom" <Goitom@.discussions.microsoft.com> wrote in message
news:F071F4C7-08AD-4654-911C-32DA43EA3441@.microsoft.com...
> Thanks Robert,
> You are right, I will check this out but how about the image? Your
> solution
> could be used only for the case of text field. But still I am not able to
> display an image logo in the header of the report that needs to be
> repeated
> in all the pages and its source is database
>
> --
> Goit
>
> "Robert Bruckner [MSFT]" wrote:
>> There is an option on the Grouping and Sorting Properties dialog to
>> repeat
>> group headers/footers. If you select the checkbox, the group header will
>> be
>> repeated and hence the referenced reportitem value will show up in page
>> header/footer.
>>
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Goitom" <Goitom@.discussions.microsoft.com> wrote in message
>> news:D4AB0430-7F67-4CC1-BDE2-4B29E7254F48@.microsoft.com...
>> >I have a report in which I need to display an Image in the header, that
>> >is
>> > coming from Database or sometimes an alternative text in place of the
>> > image.
>> > Both ways I couldn't do it.
>> > Case Text: I have tried several ways such as using ReportItem
>> > Parameters,
>> > but the problem is the text is displayed in the first page of a group.
>> > If
>> > the
>> > group region needs morethan one page when the report is rendered then
>> > the
>> > next pages will not have the header or footer text.
>> >
>> > Case Image: No means at all!!!!!
>> >
>> > Is there any way of achieving this? Please help ...
>> > --
>> > Goit
>>|||Thanks a lot, I have tried this approach several times with different
techiniques but the end result is that the image box control shows the no
image icon.
--
Goit
"Robert Bruckner [MSFT]" wrote:
> It sounds like you want the same image to show up in the header of every
> page. There are several options to achieve this:
> * use an "embedded" image in the report. This works well if the image is
> e.g. a company logo.
> * use a "project" image which is part of the report solution. The image will
> get published as resource to the report server
> * use a "web" image (aka external image) - which requires at least RS 2000
> SP1.
> If you really need the image to come from the database, you can do the
> following:
> * add a dataset DImage to the report which retrieves the image data - e.g.
> as ImageData column.
> * add a new report parameter PImage of type String. Make the parameter
> hidden (non-prompted). Set the parameter default as query-based and choose
> ImageData as column.
> * add a new Image report item in the page header/footer. Set the image type
> to "Database". For the image value expression use =Parameters!PImage.Value.
> Don't forget the set the mimetype correctly.
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Goitom" <Goitom@.discussions.microsoft.com> wrote in message
> news:F071F4C7-08AD-4654-911C-32DA43EA3441@.microsoft.com...
> > Thanks Robert,
> >
> > You are right, I will check this out but how about the image? Your
> > solution
> > could be used only for the case of text field. But still I am not able to
> > display an image logo in the header of the report that needs to be
> > repeated
> > in all the pages and its source is database
> >
> >
> > --
> > Goit
> >
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> >> There is an option on the Grouping and Sorting Properties dialog to
> >> repeat
> >> group headers/footers. If you select the checkbox, the group header will
> >> be
> >> repeated and hence the referenced reportitem value will show up in page
> >> header/footer.
> >>
> >>
> >> -- Robert
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "Goitom" <Goitom@.discussions.microsoft.com> wrote in message
> >> news:D4AB0430-7F67-4CC1-BDE2-4B29E7254F48@.microsoft.com...
> >> >I have a report in which I need to display an Image in the header, that
> >> >is
> >> > coming from Database or sometimes an alternative text in place of the
> >> > image.
> >> > Both ways I couldn't do it.
> >> > Case Text: I have tried several ways such as using ReportItem
> >> > Parameters,
> >> > but the problem is the text is displayed in the first page of a group.
> >> > If
> >> > the
> >> > group region needs morethan one page when the report is rendered then
> >> > the
> >> > next pages will not have the header or footer text.
> >> >
> >> > Case Image: No means at all!!!!!
> >> >
> >> > Is there any way of achieving this? Please help ...
> >> > --
> >> > Goit
> >>
> >>
> >>
>
>|||Are you sure the image is really stored as image in the database (rather
than being stored as OLE image, which for instance Access would do)?
If it is stored as OLE image, you have to get rid of the OLE header. If the
bitmap was created on an English system, the OLE header is 78 bytes (e.g. on
Spanish it would be 88 bytes). Use the following image value expression to
make the image show up (79 == header length + 1):
=System.Text.Encoding.Default.GetBytes(Mid(System.Text.Encoding.Default.GetString(Fields!Picture.Value),79))
Alternatively, you can use the following expression (which only works for
images created on an en-US system!):
=System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),105))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Goitom" <Goitom@.discussions.microsoft.com> wrote in message
news:ED81ACCF-9D9E-4E4A-9A58-2A6485E338D9@.microsoft.com...
> Thanks a lot, I have tried this approach several times with different
> techiniques but the end result is that the image box control shows the no
> image icon.
> --
> Goit
>
> "Robert Bruckner [MSFT]" wrote:
>> It sounds like you want the same image to show up in the header of every
>> page. There are several options to achieve this:
>> * use an "embedded" image in the report. This works well if the image is
>> e.g. a company logo.
>> * use a "project" image which is part of the report solution. The image
>> will
>> get published as resource to the report server
>> * use a "web" image (aka external image) - which requires at least RS
>> 2000
>> SP1.
>> If you really need the image to come from the database, you can do the
>> following:
>> * add a dataset DImage to the report which retrieves the image data -
>> e.g.
>> as ImageData column.
>> * add a new report parameter PImage of type String. Make the parameter
>> hidden (non-prompted). Set the parameter default as query-based and
>> choose
>> ImageData as column.
>> * add a new Image report item in the page header/footer. Set the image
>> type
>> to "Database". For the image value expression use
>> =Parameters!PImage.Value.
>> Don't forget the set the mimetype correctly.
>>
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Goitom" <Goitom@.discussions.microsoft.com> wrote in message
>> news:F071F4C7-08AD-4654-911C-32DA43EA3441@.microsoft.com...
>> > Thanks Robert,
>> >
>> > You are right, I will check this out but how about the image? Your
>> > solution
>> > could be used only for the case of text field. But still I am not able
>> > to
>> > display an image logo in the header of the report that needs to be
>> > repeated
>> > in all the pages and its source is database
>> >
>> >
>> > --
>> > Goit
>> >
>> >
>> > "Robert Bruckner [MSFT]" wrote:
>> >
>> >> There is an option on the Grouping and Sorting Properties dialog to
>> >> repeat
>> >> group headers/footers. If you select the checkbox, the group header
>> >> will
>> >> be
>> >> repeated and hence the referenced reportitem value will show up in
>> >> page
>> >> header/footer.
>> >>
>> >>
>> >> -- Robert
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >> "Goitom" <Goitom@.discussions.microsoft.com> wrote in message
>> >> news:D4AB0430-7F67-4CC1-BDE2-4B29E7254F48@.microsoft.com...
>> >> >I have a report in which I need to display an Image in the header,
>> >> >that
>> >> >is
>> >> > coming from Database or sometimes an alternative text in place of
>> >> > the
>> >> > image.
>> >> > Both ways I couldn't do it.
>> >> > Case Text: I have tried several ways such as using ReportItem
>> >> > Parameters,
>> >> > but the problem is the text is displayed in the first page of a
>> >> > group.
>> >> > If
>> >> > the
>> >> > group region needs morethan one page when the report is rendered
>> >> > then
>> >> > the
>> >> > next pages will not have the header or footer text.
>> >> >
>> >> > Case Image: No means at all!!!!!
>> >> >
>> >> > Is there any way of achieving this? Please help ...
>> >> > --
>> >> > Goit
>> >>
>> >>
>> >>
>>
Showing posts with label values. Show all posts
Showing posts with label values. Show all posts
Monday, March 19, 2012
I'm Stumped - Adding values of two columns
I am at a loss...I have tried everything.. Hopefull this is so easy
I'm missing it all together.
I have a very simple data entry table that is going to be used in other
areas (asp, etc.)
The table contains several columns. I need to have some of the columns
automatically equal the values of other columns added together.
ie.
Column Value
JanSalesGoals 4
FebSalesGoalsn 6
MarSalesGoals 3
Qtr1SalesGoals =[JanSalesGoals]+[FebSalesGoals]+[MarSal
esGoals]
Having the column of Qtr1SalesGoals come out to equal '13'.
The best I have acheived is getting the column to equal 463, which is
not the desired result.
There is a reason why I am trying to make it work this way in the table
itself.
Any and all sugestions are aprreciated.Convert to integer before adding, see example
declare @.val1 char(1)
declare @.val2 char(1)
declare @.val3 char(1)
select @.val1='4',@.val2 ='6',@.val3 ='3'
select @.val1+ @.val2 + @.val3
select convert(int,@.val1)+ convert(int,@.val2) + convert(int,@.val3)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
ehorde@.burgoonberger.com wrote:
> I am at a loss...I have tried everything.. Hopefull this is so easy
> I'm missing it all together.
> I have a very simple data entry table that is going to be used in other
> areas (asp, etc.)
> The table contains several columns. I need to have some of the columns
> automatically equal the values of other columns added together.
> ie.
> Column Value
> JanSalesGoals 4
> FebSalesGoalsn 6
> MarSalesGoals 3
> Qtr1SalesGoals =[JanSalesGoals]+[FebSalesGoals]+[MarSal
esGoals]
> Having the column of Qtr1SalesGoals come out to equal '13'.
> The best I have acheived is getting the column to equal 463, which is
> not the desired result.
> There is a reason why I am trying to make it work this way in the table
> itself.
> Any and all sugestions are aprreciated.|||What is the datatype of "value"? If it is not a numeric datatype, why not?
This is not VBScript, and you can't just switch in math operators against
strings. SQL Server doubles up the use of + for string concatenation and
mathematical addition. Observe:
DECLARE @.foo VARCHAR(12), @.bar VARCHAR(12);
SET @.foo = 4;
SET @.bar = 3;
SELECT @.foo + @.bar;
SELECT CONVERT(INT, @.foo) + CONVERT(INT, @.bar);
GO
DECLARE @.foo INT, @.bar INT;
SET @.foo = 4;
SET @.bar = 3;
SELECT @.foo + @.bar;
SELECT CONVERT(VARCHAR(12), @.foo) + CONVERT(VARCHAR(12), @.bar);
<ehorde@.burgoonberger.com> wrote in message
news:1149270430.339282.245030@.h76g2000cwa.googlegroups.com...
>I am at a loss...I have tried everything.. Hopefull this is so easy
> I'm missing it all together.
> I have a very simple data entry table that is going to be used in other
> areas (asp, etc.)
> The table contains several columns. I need to have some of the columns
> automatically equal the values of other columns added together.
> ie.
> Column Value
> JanSalesGoals 4
> FebSalesGoalsn 6
> MarSalesGoals 3
> Qtr1SalesGoals =[JanSalesGoals]+[FebSalesGoals]+[MarSal
esGoals]
> Having the column of Qtr1SalesGoals come out to equal '13'.
> The best I have acheived is getting the column to equal 463, which is
> not the desired result.
> There is a reason why I am trying to make it work this way in the table
> itself.
> Any and all sugestions are aprreciated.
>|||Generally you should never have columns like that, since you can always
calculate the values on the way back out for viewing and reporting.
So how are you attempting to update this column? In code? In a trigger?
Bottom line, convert the values to numeric first before adding. You are
seeing string concatenation.
Jeff
<ehorde@.burgoonberger.com> wrote in message
news:1149270430.339282.245030@.h76g2000cwa.googlegroups.com...
>I am at a loss...I have tried everything.. Hopefull this is so easy
> I'm missing it all together.
> I have a very simple data entry table that is going to be used in other
> areas (asp, etc.)
> The table contains several columns. I need to have some of the columns
> automatically equal the values of other columns added together.
> ie.
> Column Value
> JanSalesGoals 4
> FebSalesGoalsn 6
> MarSalesGoals 3
> Qtr1SalesGoals =[JanSalesGoals]+[FebSalesGoals]+[MarSal
esGoals]
> Having the column of Qtr1SalesGoals come out to equal '13'.
> The best I have acheived is getting the column to equal 463, which is
> not the desired result.
> There is a reason why I am trying to make it work this way in the table
> itself.
> Any and all sugestions are aprreciated.
>|||Setting to int from varchar fixed the formula.
([Jan Sales Goals] + [Feb Sales Goals] + [Mar Sales Goals])
Thanks very much,
Trees in the way of the Forrest View syndrome
SQL Menace wrote:
> Convert to integer before adding, see example
> declare @.val1 char(1)
> declare @.val2 char(1)
> declare @.val3 char(1)
> select @.val1='4',@.val2 ='6',@.val3 ='3'
> select @.val1+ @.val2 + @.val3
> select convert(int,@.val1)+ convert(int,@.val2) + convert(int,@.val3)
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
> ehorde@.burgoonberger.com wrote:
I'm missing it all together.
I have a very simple data entry table that is going to be used in other
areas (asp, etc.)
The table contains several columns. I need to have some of the columns
automatically equal the values of other columns added together.
ie.
Column Value
JanSalesGoals 4
FebSalesGoalsn 6
MarSalesGoals 3
Qtr1SalesGoals =[JanSalesGoals]+[FebSalesGoals]+[MarSal
esGoals]
Having the column of Qtr1SalesGoals come out to equal '13'.
The best I have acheived is getting the column to equal 463, which is
not the desired result.
There is a reason why I am trying to make it work this way in the table
itself.
Any and all sugestions are aprreciated.Convert to integer before adding, see example
declare @.val1 char(1)
declare @.val2 char(1)
declare @.val3 char(1)
select @.val1='4',@.val2 ='6',@.val3 ='3'
select @.val1+ @.val2 + @.val3
select convert(int,@.val1)+ convert(int,@.val2) + convert(int,@.val3)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
ehorde@.burgoonberger.com wrote:
> I am at a loss...I have tried everything.. Hopefull this is so easy
> I'm missing it all together.
> I have a very simple data entry table that is going to be used in other
> areas (asp, etc.)
> The table contains several columns. I need to have some of the columns
> automatically equal the values of other columns added together.
> ie.
> Column Value
> JanSalesGoals 4
> FebSalesGoalsn 6
> MarSalesGoals 3
> Qtr1SalesGoals =[JanSalesGoals]+[FebSalesGoals]+[MarSal
esGoals]
> Having the column of Qtr1SalesGoals come out to equal '13'.
> The best I have acheived is getting the column to equal 463, which is
> not the desired result.
> There is a reason why I am trying to make it work this way in the table
> itself.
> Any and all sugestions are aprreciated.|||What is the datatype of "value"? If it is not a numeric datatype, why not?
This is not VBScript, and you can't just switch in math operators against
strings. SQL Server doubles up the use of + for string concatenation and
mathematical addition. Observe:
DECLARE @.foo VARCHAR(12), @.bar VARCHAR(12);
SET @.foo = 4;
SET @.bar = 3;
SELECT @.foo + @.bar;
SELECT CONVERT(INT, @.foo) + CONVERT(INT, @.bar);
GO
DECLARE @.foo INT, @.bar INT;
SET @.foo = 4;
SET @.bar = 3;
SELECT @.foo + @.bar;
SELECT CONVERT(VARCHAR(12), @.foo) + CONVERT(VARCHAR(12), @.bar);
<ehorde@.burgoonberger.com> wrote in message
news:1149270430.339282.245030@.h76g2000cwa.googlegroups.com...
>I am at a loss...I have tried everything.. Hopefull this is so easy
> I'm missing it all together.
> I have a very simple data entry table that is going to be used in other
> areas (asp, etc.)
> The table contains several columns. I need to have some of the columns
> automatically equal the values of other columns added together.
> ie.
> Column Value
> JanSalesGoals 4
> FebSalesGoalsn 6
> MarSalesGoals 3
> Qtr1SalesGoals =[JanSalesGoals]+[FebSalesGoals]+[MarSal
esGoals]
> Having the column of Qtr1SalesGoals come out to equal '13'.
> The best I have acheived is getting the column to equal 463, which is
> not the desired result.
> There is a reason why I am trying to make it work this way in the table
> itself.
> Any and all sugestions are aprreciated.
>|||Generally you should never have columns like that, since you can always
calculate the values on the way back out for viewing and reporting.
So how are you attempting to update this column? In code? In a trigger?
Bottom line, convert the values to numeric first before adding. You are
seeing string concatenation.
Jeff
<ehorde@.burgoonberger.com> wrote in message
news:1149270430.339282.245030@.h76g2000cwa.googlegroups.com...
>I am at a loss...I have tried everything.. Hopefull this is so easy
> I'm missing it all together.
> I have a very simple data entry table that is going to be used in other
> areas (asp, etc.)
> The table contains several columns. I need to have some of the columns
> automatically equal the values of other columns added together.
> ie.
> Column Value
> JanSalesGoals 4
> FebSalesGoalsn 6
> MarSalesGoals 3
> Qtr1SalesGoals =[JanSalesGoals]+[FebSalesGoals]+[MarSal
esGoals]
> Having the column of Qtr1SalesGoals come out to equal '13'.
> The best I have acheived is getting the column to equal 463, which is
> not the desired result.
> There is a reason why I am trying to make it work this way in the table
> itself.
> Any and all sugestions are aprreciated.
>|||Setting to int from varchar fixed the formula.
([Jan Sales Goals] + [Feb Sales Goals] + [Mar Sales Goals])
Thanks very much,
Trees in the way of the Forrest View syndrome
SQL Menace wrote:
> Convert to integer before adding, see example
> declare @.val1 char(1)
> declare @.val2 char(1)
> declare @.val3 char(1)
> select @.val1='4',@.val2 ='6',@.val3 ='3'
> select @.val1+ @.val2 + @.val3
> select convert(int,@.val1)+ convert(int,@.val2) + convert(int,@.val3)
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
> ehorde@.burgoonberger.com wrote:
I'm retarded and need help with Derived Column or Script Task
I am moving data from an oracle database to sql server. Two of the source fields are dates, which sometimes contain values I know to be incorrect ('0001/01/01' and '1900/01/01').
I'd like to use either the derived column or script task (or any other appropriate one) to update these incorrect values (in both columns) to null before inserting into sql server, smalldatetime field.
In sql 2000 dts, I would simply use a VBScript IF statement, but I'm somewhat clueless when it comes to .Net.
Does anyone have any ideas or sample code that may help?
Thank you much.
In a derived column you could do a comparison expression such as:
[ORACLE_COL] == (DT_DBTIMESTAMP)"0001/01/01" || [ORACLE_COL] == (DT_DBTIMESTAMP)"1900/01/01" ? NULL(DT_DBTIMESTAMP) : [ORACLE_COL]
|||That worked perfectly, thank you. However it also looks like it's slowing me down quite a bit. I'm moving a lot of data, 16 million records, from oracle to sql server. Moving this into a temp table without the derived column task only took 8 minutes. After adding the new task I can see it would take far longer.
Do you know of any good learning resources for SSIS besides Books on Line?
Thank you.
|||www.sqlis.com is the recognised community site.
The guys that run it (Darren and Allan) are also running a new wiki site: http://wiki.sqlis.com/
-Jamie
|||I found that the WROX press book "SQL Server 2005 Integration Services",, ISBN 0-7645-8435-9 is a very good resource. I normally do not like using anything other than online resources either.Monday, March 12, 2012
I'm confused about "Null" fields and Default values
I have defined a table with a "DateCreated" field. I put "(getdate())"
(w/o quotes) in the Default Value field when I defined the table (Allow
Nulls). First I tried adding records (via a VB.NET app) and the records
added fine, but no dates. So I turned off "Allow Nulls". Now, I am getting
an error if I don't fill in the date field from the app..."Null not
allowed." I thought (wrongly, obviously) that the point of "Default Value"
was to furnish a "Default Value."
Please straighten me out...
TIA,
Larry WoodsNULL != empty string! If your column doesn't allow NULLs, and you want to
supply a default value, you should not specify that column at all in your
insert statement.
"Larry Woods" <larry@.lwoods.com> wrote in message
news:e7xPbEZoDHA.488@.tk2msftngp13.phx.gbl...
> I have defined a table with a "DateCreated" field. I put "(getdate())"
> (w/o quotes) in the Default Value field when I defined the table (Allow
> Nulls). First I tried adding records (via a VB.NET app) and the records
> added fine, but no dates. So I turned off "Allow Nulls". Now, I am
getting
> an error if I don't fill in the date field from the app..."Null not
> allowed." I thought (wrongly, obviously) that the point of "Default
Value"
> was to furnish a "Default Value."
> Please straighten me out...
> TIA,
> Larry Woods
>|||I had "hoped" that I could either (1) insert the date myself or, if not,
then SQL would insert the default date (getdate()) that I had specified.
Guess not, huh?
Larry
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%23fgAdJZoDHA.424@.TK2MSFTNGP10.phx.gbl...
> NULL != empty string! If your column doesn't allow NULLs, and you want to
> supply a default value, you should not specify that column at all in your
> insert statement.
>
>
> "Larry Woods" <larry@.lwoods.com> wrote in message
> news:e7xPbEZoDHA.488@.tk2msftngp13.phx.gbl...
> > I have defined a table with a "DateCreated" field. I put "(getdate())"
> > (w/o quotes) in the Default Value field when I defined the table (Allow
> > Nulls). First I tried adding records (via a VB.NET app) and the records
> > added fine, but no dates. So I turned off "Allow Nulls". Now, I am
> getting
> > an error if I don't fill in the date field from the app..."Null not
> > allowed." I thought (wrongly, obviously) that the point of "Default
> Value"
> > was to furnish a "Default Value."
> >
> > Please straighten me out...
> >
> > TIA,
> >
> > Larry Woods
> >
> >
>|||> I had "hoped" that I could either (1) insert the date myself or, if not,
> then SQL would insert the default date (getdate()) that I had specified.
> Guess not, huh?
Yes, you can! You need to make sure you understand what "it not" means.
Leave the column out of your INSERT statement, rather than setting it (or
not setting it).
CREATE TABLE blat
(
id INT,
dateColumn DATETIME DEFAULT GETDATE()
)
-- now compare:
INSERT blat(id) VALUES(1)
INSERT blat(id, dateColumn) VALUES(2, '2003-10-31')
INSERT blat(id, dateColumn) VALUES(3, NULL)
INSERT blat(id, dateColumn) VALUES(4, '')
SELECT * FROM blat
DROP TABLE blat|||Aaron is it possible to do somthing like this, I'm sure I've read it
somewhere:
INSERT blat(id, dateColumn) VALUES(4, DEFAULT)
Al.
On Sun, 2 Nov 2003 18:32:31 -0500, "Aaron Bertrand [MVP]"
<aaron@.TRASHaspfaq.com> wrote:
>> I had "hoped" that I could either (1) insert the date myself or, if not,
>> then SQL would insert the default date (getdate()) that I had specified.
>> Guess not, huh?
>Yes, you can! You need to make sure you understand what "it not" means.
>Leave the column out of your INSERT statement, rather than setting it (or
>not setting it).
>CREATE TABLE blat
>(
> id INT,
> dateColumn DATETIME DEFAULT GETDATE()
>)
>-- now compare:
>INSERT blat(id) VALUES(1)
>INSERT blat(id, dateColumn) VALUES(2, '2003-10-31')
>INSERT blat(id, dateColumn) VALUES(3, NULL)
>INSERT blat(id, dateColumn) VALUES(4, '')
>SELECT * FROM blat
>DROP TABLE blat
>|||> Aaron is it possible to do somthing like this, I'm sure I've read it
> somewhere:
> INSERT blat(id, dateColumn) VALUES(4, DEFAULT)
Sure. You can use the DEFAULT keyword to let SQL Server apply a
timestamp/rowversion, value generated by a default constraint or a NULL. Not
an identity, though.
--
Tibor Karaszi
"Harag" <harag@.softGETRIDOFCAPLETTERShome.net> wrote in message
news:ro2cqv86knq0mjl89av3enng7nv3s4gemd@.4ax.com...
> Aaron is it possible to do somthing like this, I'm sure I've read it
> somewhere:
> INSERT blat(id, dateColumn) VALUES(4, DEFAULT)
> Al.
> On Sun, 2 Nov 2003 18:32:31 -0500, "Aaron Bertrand [MVP]"
> <aaron@.TRASHaspfaq.com> wrote:
> >> I had "hoped" that I could either (1) insert the date myself or, if
not,
> >> then SQL would insert the default date (getdate()) that I had
specified.
> >> Guess not, huh?
> >
> >Yes, you can! You need to make sure you understand what "it not" means.
> >Leave the column out of your INSERT statement, rather than setting it (or
> >not setting it).
> >
> >CREATE TABLE blat
> >(
> > id INT,
> > dateColumn DATETIME DEFAULT GETDATE()
> >)
> >
> >-- now compare:
> >INSERT blat(id) VALUES(1)
> >INSERT blat(id, dateColumn) VALUES(2, '2003-10-31')
> >INSERT blat(id, dateColumn) VALUES(3, NULL)
> >INSERT blat(id, dateColumn) VALUES(4, '')
> >
> >SELECT * FROM blat
> >
> >DROP TABLE blat
> >
> >
>|||To All:
Thanks for the advice. The problem comes down to this: I am using ADO.NET
and a Data Adapter. The Data Adapter gens the INSERT and obviously doesn't
handle the DEFAULT... The INSERT has a placeholder defined for the date
field, and seemingly no logic to handle the test for a default value. Guess
I will have to plug in the date myself.
My problem is that I am coming from the "kiddie" world of Access, and it
handled this situation just fine; i.e., if you didn't enter a value it
plugged in the default.
Oh, well.
Thanks, again.
Larry
"Larry Woods" <larry@.lwoods.com> wrote in message
news:%23VyH%23hZoDHA.2272@.tk2msftngp13.phx.gbl...
> I had "hoped" that I could either (1) insert the date myself or, if not,
> then SQL would insert the default date (getdate()) that I had specified.
> Guess not, huh?
> Larry
> "Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
> news:%23fgAdJZoDHA.424@.TK2MSFTNGP10.phx.gbl...
> > NULL != empty string! If your column doesn't allow NULLs, and you want
to
> > supply a default value, you should not specify that column at all in
your
> > insert statement.
> >
> >
> >
> >
> >
> > "Larry Woods" <larry@.lwoods.com> wrote in message
> > news:e7xPbEZoDHA.488@.tk2msftngp13.phx.gbl...
> > > I have defined a table with a "DateCreated" field. I put
"(getdate())"
> > > (w/o quotes) in the Default Value field when I defined the table
(Allow
> > > Nulls). First I tried adding records (via a VB.NET app) and the
records
> > > added fine, but no dates. So I turned off "Allow Nulls". Now, I am
> > getting
> > > an error if I don't fill in the date field from the app..."Null not
> > > allowed." I thought (wrongly, obviously) that the point of "Default
> > Value"
> > > was to furnish a "Default Value."
> > >
> > > Please straighten me out...
> > >
> > > TIA,
> > >
> > > Larry Woods
> > >
> > >
> >
> >
>|||Hi Larry,
Thanks for your feedback. I think this article will help you a lot.
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=gZPKVHzg
DHA.2624%40cpmsftngxa06.phx.gbl&rnum=1&prev=/groups%3Fq%3Dv-kevy%2Bdefault%2
Bvalue%2Bsql%2Bdataset%2Btyped%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%2
6selm%3DgZPKVHzgDHA.2624%2540cpmsftngxa06.phx.gbl%26rnum%3D1
Please feel free to post in the group if this solves your problem or if you
would like further assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||I found the Google response. It says to put the default value in the xsd
property for the field. If I want either "now()" or "date()" as the
default, how do I specify that? I tried both and got errors both times. By
looking at the XML I can see why, I just don't know enough about the format
of the XSD to know how to specify "code" in the XSD.
Please advise...
TIA,
Larry
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:RTDs5$toDHA.2700@.cpmsftngxa06.phx.gbl...
> Hi Larry,
> Thanks for your feedback. I think this article will help you a lot.
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=gZPKVHzg
>
DHA.2624%40cpmsftngxa06.phx.gbl&rnum=1&prev=/groups%3Fq%3Dv-kevy%2Bdefault%2
>
Bvalue%2Bsql%2Bdataset%2Btyped%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%2
> 6selm%3DgZPKVHzgDHA.2624%2540cpmsftngxa06.phx.gbl%26rnum%3D1
> Please feel free to post in the group if this solves your problem or if
you
> would like further assistance.
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>|||Hi Larry,
Thanks for your feedback. As I understand, you are using ADO.NET with
Dataadapter to connect SQL Server. You want to find a way to set the
default value of the column before you update the dataset. If I have
misunderstood, please feel free to let me know.
Based on my research, I would like you to try to add the following
statements in the codes to see if they solve your problem.
this.dataset11.Tables["<TableName>"].Columns["<ColumnName>"].DefaultValue =DateTime.Now;
dataset11 is the name of the Dataset object.
For additional information regarding this issue, please refer to the
following article.
DataColumn.DefaultValue Property
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfSystemDataDataColumnClassDefaultValueTopic.asp
Also, it seems that current problem in this issue is related to ADO.NET
programming. I think the current news group is not the best one for this
problem. To resolve this problem, you may need to program the code with
DefaultValue property of the DataColumn. Therefore, I suggest that you post
this question in the microsoft.public.dotnet.framework.adonet newsgroup,
which is primarily for issues involving ADO.NET programming.
The reason why we recommend posting appropriately is you will get the most
qualified pool of respondents, and other partners who read the newsgroups
regularly can either share their knowledge or learn from your interaction
with us. I hope the problem can be resolved quickly.
Thank you for using our Newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
(w/o quotes) in the Default Value field when I defined the table (Allow
Nulls). First I tried adding records (via a VB.NET app) and the records
added fine, but no dates. So I turned off "Allow Nulls". Now, I am getting
an error if I don't fill in the date field from the app..."Null not
allowed." I thought (wrongly, obviously) that the point of "Default Value"
was to furnish a "Default Value."
Please straighten me out...
TIA,
Larry WoodsNULL != empty string! If your column doesn't allow NULLs, and you want to
supply a default value, you should not specify that column at all in your
insert statement.
"Larry Woods" <larry@.lwoods.com> wrote in message
news:e7xPbEZoDHA.488@.tk2msftngp13.phx.gbl...
> I have defined a table with a "DateCreated" field. I put "(getdate())"
> (w/o quotes) in the Default Value field when I defined the table (Allow
> Nulls). First I tried adding records (via a VB.NET app) and the records
> added fine, but no dates. So I turned off "Allow Nulls". Now, I am
getting
> an error if I don't fill in the date field from the app..."Null not
> allowed." I thought (wrongly, obviously) that the point of "Default
Value"
> was to furnish a "Default Value."
> Please straighten me out...
> TIA,
> Larry Woods
>|||I had "hoped" that I could either (1) insert the date myself or, if not,
then SQL would insert the default date (getdate()) that I had specified.
Guess not, huh?
Larry
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%23fgAdJZoDHA.424@.TK2MSFTNGP10.phx.gbl...
> NULL != empty string! If your column doesn't allow NULLs, and you want to
> supply a default value, you should not specify that column at all in your
> insert statement.
>
>
> "Larry Woods" <larry@.lwoods.com> wrote in message
> news:e7xPbEZoDHA.488@.tk2msftngp13.phx.gbl...
> > I have defined a table with a "DateCreated" field. I put "(getdate())"
> > (w/o quotes) in the Default Value field when I defined the table (Allow
> > Nulls). First I tried adding records (via a VB.NET app) and the records
> > added fine, but no dates. So I turned off "Allow Nulls". Now, I am
> getting
> > an error if I don't fill in the date field from the app..."Null not
> > allowed." I thought (wrongly, obviously) that the point of "Default
> Value"
> > was to furnish a "Default Value."
> >
> > Please straighten me out...
> >
> > TIA,
> >
> > Larry Woods
> >
> >
>|||> I had "hoped" that I could either (1) insert the date myself or, if not,
> then SQL would insert the default date (getdate()) that I had specified.
> Guess not, huh?
Yes, you can! You need to make sure you understand what "it not" means.
Leave the column out of your INSERT statement, rather than setting it (or
not setting it).
CREATE TABLE blat
(
id INT,
dateColumn DATETIME DEFAULT GETDATE()
)
-- now compare:
INSERT blat(id) VALUES(1)
INSERT blat(id, dateColumn) VALUES(2, '2003-10-31')
INSERT blat(id, dateColumn) VALUES(3, NULL)
INSERT blat(id, dateColumn) VALUES(4, '')
SELECT * FROM blat
DROP TABLE blat|||Aaron is it possible to do somthing like this, I'm sure I've read it
somewhere:
INSERT blat(id, dateColumn) VALUES(4, DEFAULT)
Al.
On Sun, 2 Nov 2003 18:32:31 -0500, "Aaron Bertrand [MVP]"
<aaron@.TRASHaspfaq.com> wrote:
>> I had "hoped" that I could either (1) insert the date myself or, if not,
>> then SQL would insert the default date (getdate()) that I had specified.
>> Guess not, huh?
>Yes, you can! You need to make sure you understand what "it not" means.
>Leave the column out of your INSERT statement, rather than setting it (or
>not setting it).
>CREATE TABLE blat
>(
> id INT,
> dateColumn DATETIME DEFAULT GETDATE()
>)
>-- now compare:
>INSERT blat(id) VALUES(1)
>INSERT blat(id, dateColumn) VALUES(2, '2003-10-31')
>INSERT blat(id, dateColumn) VALUES(3, NULL)
>INSERT blat(id, dateColumn) VALUES(4, '')
>SELECT * FROM blat
>DROP TABLE blat
>|||> Aaron is it possible to do somthing like this, I'm sure I've read it
> somewhere:
> INSERT blat(id, dateColumn) VALUES(4, DEFAULT)
Sure. You can use the DEFAULT keyword to let SQL Server apply a
timestamp/rowversion, value generated by a default constraint or a NULL. Not
an identity, though.
--
Tibor Karaszi
"Harag" <harag@.softGETRIDOFCAPLETTERShome.net> wrote in message
news:ro2cqv86knq0mjl89av3enng7nv3s4gemd@.4ax.com...
> Aaron is it possible to do somthing like this, I'm sure I've read it
> somewhere:
> INSERT blat(id, dateColumn) VALUES(4, DEFAULT)
> Al.
> On Sun, 2 Nov 2003 18:32:31 -0500, "Aaron Bertrand [MVP]"
> <aaron@.TRASHaspfaq.com> wrote:
> >> I had "hoped" that I could either (1) insert the date myself or, if
not,
> >> then SQL would insert the default date (getdate()) that I had
specified.
> >> Guess not, huh?
> >
> >Yes, you can! You need to make sure you understand what "it not" means.
> >Leave the column out of your INSERT statement, rather than setting it (or
> >not setting it).
> >
> >CREATE TABLE blat
> >(
> > id INT,
> > dateColumn DATETIME DEFAULT GETDATE()
> >)
> >
> >-- now compare:
> >INSERT blat(id) VALUES(1)
> >INSERT blat(id, dateColumn) VALUES(2, '2003-10-31')
> >INSERT blat(id, dateColumn) VALUES(3, NULL)
> >INSERT blat(id, dateColumn) VALUES(4, '')
> >
> >SELECT * FROM blat
> >
> >DROP TABLE blat
> >
> >
>|||To All:
Thanks for the advice. The problem comes down to this: I am using ADO.NET
and a Data Adapter. The Data Adapter gens the INSERT and obviously doesn't
handle the DEFAULT... The INSERT has a placeholder defined for the date
field, and seemingly no logic to handle the test for a default value. Guess
I will have to plug in the date myself.
My problem is that I am coming from the "kiddie" world of Access, and it
handled this situation just fine; i.e., if you didn't enter a value it
plugged in the default.
Oh, well.
Thanks, again.
Larry
"Larry Woods" <larry@.lwoods.com> wrote in message
news:%23VyH%23hZoDHA.2272@.tk2msftngp13.phx.gbl...
> I had "hoped" that I could either (1) insert the date myself or, if not,
> then SQL would insert the default date (getdate()) that I had specified.
> Guess not, huh?
> Larry
> "Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
> news:%23fgAdJZoDHA.424@.TK2MSFTNGP10.phx.gbl...
> > NULL != empty string! If your column doesn't allow NULLs, and you want
to
> > supply a default value, you should not specify that column at all in
your
> > insert statement.
> >
> >
> >
> >
> >
> > "Larry Woods" <larry@.lwoods.com> wrote in message
> > news:e7xPbEZoDHA.488@.tk2msftngp13.phx.gbl...
> > > I have defined a table with a "DateCreated" field. I put
"(getdate())"
> > > (w/o quotes) in the Default Value field when I defined the table
(Allow
> > > Nulls). First I tried adding records (via a VB.NET app) and the
records
> > > added fine, but no dates. So I turned off "Allow Nulls". Now, I am
> > getting
> > > an error if I don't fill in the date field from the app..."Null not
> > > allowed." I thought (wrongly, obviously) that the point of "Default
> > Value"
> > > was to furnish a "Default Value."
> > >
> > > Please straighten me out...
> > >
> > > TIA,
> > >
> > > Larry Woods
> > >
> > >
> >
> >
>|||Hi Larry,
Thanks for your feedback. I think this article will help you a lot.
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=gZPKVHzg
DHA.2624%40cpmsftngxa06.phx.gbl&rnum=1&prev=/groups%3Fq%3Dv-kevy%2Bdefault%2
Bvalue%2Bsql%2Bdataset%2Btyped%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%2
6selm%3DgZPKVHzgDHA.2624%2540cpmsftngxa06.phx.gbl%26rnum%3D1
Please feel free to post in the group if this solves your problem or if you
would like further assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||I found the Google response. It says to put the default value in the xsd
property for the field. If I want either "now()" or "date()" as the
default, how do I specify that? I tried both and got errors both times. By
looking at the XML I can see why, I just don't know enough about the format
of the XSD to know how to specify "code" in the XSD.
Please advise...
TIA,
Larry
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:RTDs5$toDHA.2700@.cpmsftngxa06.phx.gbl...
> Hi Larry,
> Thanks for your feedback. I think this article will help you a lot.
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=gZPKVHzg
>
DHA.2624%40cpmsftngxa06.phx.gbl&rnum=1&prev=/groups%3Fq%3Dv-kevy%2Bdefault%2
>
Bvalue%2Bsql%2Bdataset%2Btyped%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%2
> 6selm%3DgZPKVHzgDHA.2624%2540cpmsftngxa06.phx.gbl%26rnum%3D1
> Please feel free to post in the group if this solves your problem or if
you
> would like further assistance.
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>|||Hi Larry,
Thanks for your feedback. As I understand, you are using ADO.NET with
Dataadapter to connect SQL Server. You want to find a way to set the
default value of the column before you update the dataset. If I have
misunderstood, please feel free to let me know.
Based on my research, I would like you to try to add the following
statements in the codes to see if they solve your problem.
this.dataset11.Tables["<TableName>"].Columns["<ColumnName>"].DefaultValue =DateTime.Now;
dataset11 is the name of the Dataset object.
For additional information regarding this issue, please refer to the
following article.
DataColumn.DefaultValue Property
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfSystemDataDataColumnClassDefaultValueTopic.asp
Also, it seems that current problem in this issue is related to ADO.NET
programming. I think the current news group is not the best one for this
problem. To resolve this problem, you may need to program the code with
DefaultValue property of the DataColumn. Therefore, I suggest that you post
this question in the microsoft.public.dotnet.framework.adonet newsgroup,
which is primarily for issues involving ADO.NET programming.
The reason why we recommend posting appropriately is you will get the most
qualified pool of respondents, and other partners who read the newsgroups
regularly can either share their knowledge or learn from your interaction
with us. I hope the problem can be resolved quickly.
Thank you for using our Newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Friday, February 24, 2012
IIF Statment to compare two values in joined tables
Hi
I'm trying to compare two varchars to check if they are the same, if they are the same then the color must turn red, if not then they must remain black
SELECT *
from members m, client c
where C.ClientID = m.ClientID
AND c.ClientID in (87,86)
AND m.email in ('dassd@.fdskjh.com','asdfas@.sdfd.net', etc...)
my results will give me two of the same email addresses but with different ClientID's, now when it
finds the same email it needs to make them both "RED"
Please help, any advice would be helpful
Kind Regards
Carel Greaves
Two ways occur to me:
- add a COUNT(c.ClientId) and GROUP BY m.email to your sql query, then check that count when displaying the data in your report - if the count is greater than 1 then you can colour the email field red
- use a row group in your report, grouping by email address, then have the other fields following - but if you use this approach you won't need to colour the email addresses red, as it will be instantly obvious which addresses are assigned to more than one client
Sunday, February 19, 2012
IIF Count
Cannot get this to work, any suggestions? (Need to count where values
are as below)
=Count(iif(LCase(Fields!Exceptions.Value Like) "*center*" AND
(LCase(Fields!Server.Value) Like "ser*" AND Fields!Production.Value = "1", 1, Nothing)))Bracket in wrong place?
Should be:
=Count(iif((LCase(Fields!Exceptions.Value)) Like "*center*" AND
(LCase(Fields!Server.Value)) Like "ser*" AND Fields!Production.Value ="1"), 1, Nothing))
Craig
"d4" <d4mann@.gmail.com> wrote in message
news:1141939610.708232.138940@.j33g2000cwa.googlegroups.com...
> Cannot get this to work, any suggestions? (Need to count where values
> are as below)
>
> =Count(iif(LCase(Fields!Exceptions.Value Like) "*center*" AND
> (LCase(Fields!Server.Value) Like "ser*" AND Fields!Production.Value => "1", 1, Nothing)))
>|||Get build errors:
The value expression for the textbox 'textbox25' has a scope
parameter that is not valid for an aggregate function. The scope
parameter must be set to a string constant that is equal to either the
name of a containing group, the name of a containing data region, or
the name of a data set.
and
The value expression for the textbox 'textbox25' uses an aggregate
expression with an invalid recursive/simple flag. The valid values for
this flag are 'Recursive' and 'Simple'.
Below does work, but I need to add another AND value to further select
correct one...
=Count(iif(LCase( Fields!Exceptions.Value ) Like "*center*" AND
Fields!Production.Value = "1", 1, Nothing))|||Got it:
=Count(iif(LCase(Fields!Exceptions.Value) Like "*center*" AND
LCase(Fields!Server.Value) Like "ser*" AND Fields!Production.Value ="1"), 1, Nothing))
Now, does anyone know how to do a NOT LIKE?|||put a NOT in front ... IIF(NOT (condition),truepart,falsepart)
"d4" wrote:
> Got it:
> =Count(iif(LCase(Fields!Exceptions.Value) Like "*center*" AND
> LCase(Fields!Server.Value) Like "ser*" AND Fields!Production.Value => "1"), 1, Nothing))
> Now, does anyone know how to do a NOT LIKE?
>
are as below)
=Count(iif(LCase(Fields!Exceptions.Value Like) "*center*" AND
(LCase(Fields!Server.Value) Like "ser*" AND Fields!Production.Value = "1", 1, Nothing)))Bracket in wrong place?
Should be:
=Count(iif((LCase(Fields!Exceptions.Value)) Like "*center*" AND
(LCase(Fields!Server.Value)) Like "ser*" AND Fields!Production.Value ="1"), 1, Nothing))
Craig
"d4" <d4mann@.gmail.com> wrote in message
news:1141939610.708232.138940@.j33g2000cwa.googlegroups.com...
> Cannot get this to work, any suggestions? (Need to count where values
> are as below)
>
> =Count(iif(LCase(Fields!Exceptions.Value Like) "*center*" AND
> (LCase(Fields!Server.Value) Like "ser*" AND Fields!Production.Value => "1", 1, Nothing)))
>|||Get build errors:
The value expression for the textbox 'textbox25' has a scope
parameter that is not valid for an aggregate function. The scope
parameter must be set to a string constant that is equal to either the
name of a containing group, the name of a containing data region, or
the name of a data set.
and
The value expression for the textbox 'textbox25' uses an aggregate
expression with an invalid recursive/simple flag. The valid values for
this flag are 'Recursive' and 'Simple'.
Below does work, but I need to add another AND value to further select
correct one...
=Count(iif(LCase( Fields!Exceptions.Value ) Like "*center*" AND
Fields!Production.Value = "1", 1, Nothing))|||Got it:
=Count(iif(LCase(Fields!Exceptions.Value) Like "*center*" AND
LCase(Fields!Server.Value) Like "ser*" AND Fields!Production.Value ="1"), 1, Nothing))
Now, does anyone know how to do a NOT LIKE?|||put a NOT in front ... IIF(NOT (condition),truepart,falsepart)
"d4" wrote:
> Got it:
> =Count(iif(LCase(Fields!Exceptions.Value) Like "*center*" AND
> LCase(Fields!Server.Value) Like "ser*" AND Fields!Production.Value => "1"), 1, Nothing))
> Now, does anyone know how to do a NOT LIKE?
>
Ignoring NULL values in LOOKUP transformation.
Hi,
Can you please tell me the way to configure the LOOK UP transformation so that it will ignore all the null values ? I want to configure a Look up component for the column "Col1" as follows
All the NULL values of Col1 should not be considered for look-up process. They should be passed to the downstream component as valid rows.
All NOT NULL values of Col1 should be processed by the Look up component.
If there is no matching value present for any NOT NULL value of Col1 then it should be directed to error output.
Regards,
Gopi
Use a conditional split to direct the not nulls to the lookup(!ISNULL(Col1)). use a union to combine the results from the lookup with the records where Col1=NULL from the other conditional split path. set your error on the lookup to redirect rows to handle the records that have no match.
Frank
|||Frank,
Thank you for your reply.
But, Is there any way to avoid conditional split ? I would like to configure Look up component itself.
Cheers,
Gopi
|||If you pass the NULL Col1 records to the lookup, you would need an entry in the lookup table to handle the NULL. otherwise, it will fail the lookup and end up in the error table along with the not NULL Col1 records that failed the lookup.
Frank
Subscribe to:
Posts (Atom)