Formula Question

I have a field "Accept" that can be Null or contain either "Y" or "N". In my
test data I have a total of 4 rows, 2 with "Y", 1 with "N" and 1 Null. I
want a count of each case in the footer along with some descriptive text. I
tried the formula
=Count(Fields!Accept.Value="Y")
But it returns "4"? The same thing happens if I use
=Count(Fields!Accept.Value="N")
What is wrong in this formula?
Also, I really want the report to display something like: "Number of
Accepts: 2" but if I try to code the field as:
"Number of Accepts: " & =Count(Fields!Accept.Value="Y")
the formula displays as text?
TIA
Waynetry to write:
= "Number of Accepts: " & Sum(IIf(Fields!Accept.Value="Y",1,0))
Good Luck
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:uiB9sAU3FHA.3400@.tk2msftngp13.phx.gbl...
> I have a field "Accept" that can be Null or contain either "Y" or "N". In
my
> test data I have a total of 4 rows, 2 with "Y", 1 with "N" and 1 Null. I
> want a count of each case in the footer along with some descriptive text.
I
> tried the formula
> =Count(Fields!Accept.Value="Y")
> But it returns "4"? The same thing happens if I use
> =Count(Fields!Accept.Value="N")
> What is wrong in this formula?
> Also, I really want the report to display something like: "Number of
> Accepts: 2" but if I try to code the field as:
> "Number of Accepts: " & =Count(Fields!Accept.Value="Y")
> the formula displays as text?
>
> TIA
> Wayne
>
>|||Liz;
Thank you very much. I never would have figured that one out. Seems like
making a point the hard way. I've ordered a couple of books that I hope will
explain the logic of creating expressions.
Wayne
"Liz Matyas" <lizmts@.mail.com> wrote in message
news:uK3U8FZ3FHA.1276@.TK2MSFTNGP09.phx.gbl...
> try to write:
> = "Number of Accepts: " & Sum(IIf(Fields!Accept.Value="Y",1,0))
> Good Luck
>
> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
> news:uiB9sAU3FHA.3400@.tk2msftngp13.phx.gbl...
>> I have a field "Accept" that can be Null or contain either "Y" or "N". In
> my
>> test data I have a total of 4 rows, 2 with "Y", 1 with "N" and 1 Null. I
>> want a count of each case in the footer along with some descriptive text.
> I
>> tried the formula
>> =Count(Fields!Accept.Value="Y")
>> But it returns "4"? The same thing happens if I use
>> =Count(Fields!Accept.Value="N")
>> What is wrong in this formula?
>> Also, I really want the report to display something like: "Number of
>> Accepts: 2" but if I try to code the field as:
>> "Number of Accepts: " & =Count(Fields!Accept.Value="Y")
>> the formula displays as text?
>>
>> TIA
>> Wayne
>>
>>
>|||The Count RDL aggregate function works similar as the Count aggregate in
SQL: it will count all rows that are NOT NULL. The expresson
Fields!Accept.Value="Y" will return a boolean value (true/false) for all
rows, that's why the count results in the value 4.
Besides using =Sum(iif(...)) as shown by Liz, you could also use
=Count(iif(Fields!Accept.Value="Y", 1, Nothing))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:u8BwZda3FHA.128@.tk2msftngp13.phx.gbl...
> Liz;
> Thank you very much. I never would have figured that one out. Seems like
> making a point the hard way. I've ordered a couple of books that I hope
> will explain the logic of creating expressions.
> Wayne
> "Liz Matyas" <lizmts@.mail.com> wrote in message
> news:uK3U8FZ3FHA.1276@.TK2MSFTNGP09.phx.gbl...
>> try to write:
>> = "Number of Accepts: " & Sum(IIf(Fields!Accept.Value="Y",1,0))
>> Good Luck
>>
>> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
>> news:uiB9sAU3FHA.3400@.tk2msftngp13.phx.gbl...
>> I have a field "Accept" that can be Null or contain either "Y" or "N".
>> In
>> my
>> test data I have a total of 4 rows, 2 with "Y", 1 with "N" and 1 Null. I
>> want a count of each case in the footer along with some descriptive
>> text.
>> I
>> tried the formula
>> =Count(Fields!Accept.Value="Y")
>> But it returns "4"? The same thing happens if I use
>> =Count(Fields!Accept.Value="N")
>> What is wrong in this formula?
>> Also, I really want the report to display something like: "Number of
>> Accepts: 2" but if I try to code the field as:
>> "Number of Accepts: " & =Count(Fields!Accept.Value="Y")
>> the formula displays as text?
>>
>> TIA
>> Wayne
>>
>>
>>
>|||Robert;
Thanks for that additional information. It helps me understand the process.
Wayne
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:eZK0yma3FHA.3036@.TK2MSFTNGP10.phx.gbl...
> The Count RDL aggregate function works similar as the Count aggregate in
> SQL: it will count all rows that are NOT NULL. The expresson
> Fields!Accept.Value="Y" will return a boolean value (true/false) for all
> rows, that's why the count results in the value 4.
> Besides using =Sum(iif(...)) as shown by Liz, you could also use
> =Count(iif(Fields!Accept.Value="Y", 1, Nothing))
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
> news:u8BwZda3FHA.128@.tk2msftngp13.phx.gbl...
>> Liz;
>> Thank you very much. I never would have figured that one out. Seems like
>> making a point the hard way. I've ordered a couple of books that I hope
>> will explain the logic of creating expressions.
>> Wayne
>> "Liz Matyas" <lizmts@.mail.com> wrote in message
>> news:uK3U8FZ3FHA.1276@.TK2MSFTNGP09.phx.gbl...
>> try to write:
>> = "Number of Accepts: " & Sum(IIf(Fields!Accept.Value="Y",1,0))
>> Good Luck
>>
>> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
>> news:uiB9sAU3FHA.3400@.tk2msftngp13.phx.gbl...
>> I have a field "Accept" that can be Null or contain either "Y" or "N".
>> In
>> my
>> test data I have a total of 4 rows, 2 with "Y", 1 with "N" and 1 Null.
>> I
>> want a count of each case in the footer along with some descriptive
>> text.
>> I
>> tried the formula
>> =Count(Fields!Accept.Value="Y")
>> But it returns "4"? The same thing happens if I use
>> =Count(Fields!Accept.Value="N")
>> What is wrong in this formula?
>> Also, I really want the report to display something like: "Number of
>> Accepts: 2" but if I try to code the field as:
>> "Number of Accepts: " & =Count(Fields!Accept.Value="Y")
>> the formula displays as text?
>>
>> TIA
>> Wayne
>>
>>
>>
>>
>sql

Formula Issue

Thanks in advance for any help.
I am spoofing rows to force an X # of rows in a table, which is working
well. However, the spoofed rows are causing an error with the formula
below.
=cstr(iif(Fields!ServiceEnd.Value.ToString = "", " ",
cstr(DatePart("m", Fields!ServiceEnd.Value))))
Without the formula, the rows have no data and no error. Any
suggestions?
Thanks,
MorganNot quite sure what yo're trying to do but might one of these formulas work:
=Iif(IsDate(Fields!ServiceEnd), cstr(DatePart("m",
Fields!ServiceEnd.Value)), "")
OR
=Iif(IsNothing(Fields!ServiceEnd), "", cstr(DatePart("m",
Fields!ServiceEnd.Value)))
HTH
--
Magendo_man
Freelance SQL Reporting Services developer
Stirling, Scotland
"Morgan" wrote:
> Thanks in advance for any help.
> I am spoofing rows to force an X # of rows in a table, which is working
> well. However, the spoofed rows are causing an error with the formula
> below.
> =cstr(iif(Fields!ServiceEnd.Value.ToString = "", " ",
> cstr(DatePart("m", Fields!ServiceEnd.Value))))
> Without the formula, the rows have no data and no error. Any
> suggestions?
>
> Thanks,
> Morgan
>|||Thank you.
A slightly modified version of your suggestions seemed to do the trick.
=iif(IsDate(Fields!ServiceBegin.Value), Day(Fields!ServiceBegin.Value),
"")
magendo_man (donotspam) wrote:
> Not quite sure what yo're trying to do but might one of these formulas work:
> =Iif(IsDate(Fields!ServiceEnd), cstr(DatePart("m",
> Fields!ServiceEnd.Value)), "")
> OR
> =Iif(IsNothing(Fields!ServiceEnd), "", cstr(DatePart("m",
> Fields!ServiceEnd.Value)))
> HTH
> --
> Magendo_man
> Freelance SQL Reporting Services developer
> Stirling, Scotland
>
> "Morgan" wrote:
> > Thanks in advance for any help.
> >
> > I am spoofing rows to force an X # of rows in a table, which is working
> > well. However, the spoofed rows are causing an error with the formula
> > below.
> >
> > =cstr(iif(Fields!ServiceEnd.Value.ToString = "", " ",
> > cstr(DatePart("m", Fields!ServiceEnd.Value))))
> >
> > Without the formula, the rows have no data and no error. Any
> > suggestions?
> >
> >
> > Thanks,
> >
> > Morgan
> >
> >

Formatting Rows on one line?

I am working on a report with 4 seperate data sources. I want my
report to look like this:
Consulting Staffing
Active Pipeline $ 100,000 $ 150,000
Lifetime Value $ 200,000
Instead because Staffing is coming from a difference data source I am
getting a line break after staffing.
Consulting Staffing
$ 150,000
Active Pipeline $ 100,000
Lifetime Value $ 200,000
Is there any way I can make this work?Do you have to use individual data sources? Trying to "unite" your
data sources into a single table within RS, which is what you have
above, can be tricky. However, if you open your report in Design mode
and select "Expressions" for the field for Staffing, you can select
fields from other datasets.
Personally, I do as much manipulation of data within the data backend
(SQL 2005 for me) so that I can get a result-set from a view (or if
the report calls for a ton of grouping and/or calculations, I set up a
stored procedure) that closely approximates how my Report will appear.
HTH!
On Apr 7, 2:51 pm, BLAW <brad...@.gmail.com> wrote:
> I am working on a report with 4 seperate data sources. I want my
> report to look like this:
> Consulting Staffing
> Active Pipeline $ 100,000 $ 150,000
> Lifetime Value $ 200,000
> Instead because Staffing is coming from a difference data source I am
> getting a line break after staffing.
> Consulting Staffing
> $ 150,000
> Active Pipeline $ 100,000
> Lifetime Value $ 200,000
> Is there any way I can make this work?

formatting output

Hi, I am running a script which inserts certain rows into a table and at the end of the execution, I do a select statement to show the inserted data as output in the results pane and it is showing as truncated...How can I show the full results..
Here is my script to show the results.
----
SET NOCOUNT ON
DECLARE @.errorCount INT
SELECT @.errorCount = COUNT(*) FROM error_report WHERE id != - 2 AND id != - 5
IF @.errorCount = 0
BEGIN
INSERT INTO error_report VALUES( '' , - 1 , 'No error found.' )
END
INSERT INTO error_report VALUES( '' , - 2 , 'The Report was generated on ' + CAST(CONVERT(VARCHAR(23), GETDATE(), 1) AS VARCHAR) )
GO
SELECT table_name + ' table has bad data at id = ' + CAST(CONVERT(VARCHAR(23), id) AS VARCHAR) + ' (' + CAST(reason AS VARCHAR) + ')'FROM error_report WHERE id > 0
SELECT table_name + ' table has bad data (' + CAST(reason AS VARCHAR) + ')' FROM error_report WHERE id = 0
SELECT reason FROM error_report WHERE id = - 1
SELECT ''
SELECT reason FROM error_report WHERE id = - 2
SET NOCOUNT OFF
GO
--------
The Results in the bottom pane looks like this below
------
NODETABLE table has bad data (There are 2 duplicate subclass)
Propertytable table has bad data (at Parentid = 2000000859 and p)
Propertytable table has bad data (at Parentid = 10122 and proper)
------
But, when I do a select, they are like this below
--

NODETABLE 0 There are 2 duplicate subclass name: Specification
Propertytable 0 at Parentid = 2000000859 and propertyid = 721
Propertytable 0 at Parentid = 10122 and propertyid = 9That sounds like a grid limitation to me. I'd suggest Ctrl-T to set text mode, then Ctrl-E to execute the query again.

-PatP|||Hi Pat,
It outputted in the text format but the results are the same. However when I do select statement, it is fine.|||You need to explicitly specify the size for VARCHAR fields.|||Hi LOOKING AT THE ABOVE CODE, CAN YOU TELL ME WHERE IT IS?|||For example, here:

'The Report was generated on ' + CAST(CONVERT(VARCHAR(23), GETDATE(), 1) AS VARCHAR(8)) )

But you also don't need that CAST.

'The Report was generated on ' + CONVERT(VARCHAR(8), GETDATE(), 1)

Form type reports... Possible?

I need to create data reporting forms that does not necesserily have
repeating rows, but are composed of field names (test labels) and values
(read/calculated from database fields) scattered on page (similar to a manual
data entry form). I can do this with Cristal Reports but is this possible
with the SQL Server Reportsing Services? Thank you very much for your
opinions.
DDAbsolutely - just set up you dataset (or datasets) and drag the fields from
the field browser directly onto the page. Add labels and you're done!
"Dursun" wrote:
> I need to create data reporting forms that does not necesserily have
> repeating rows, but are composed of field names (test labels) and values
> (read/calculated from database fields) scattered on page (similar to a manual
> data entry form). I can do this with Cristal Reports but is this possible
> with the SQL Server Reportsing Services? Thank you very much for your
> opinions.
> DD