|
|
You're in the
sql server reporting services
group:Formula Question
sql server reporting services:
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. [quoted text, click to view] "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 >>> >>> >>> >>> >> >> > >
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 [quoted text, click to view] "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 [quoted text, click to view] "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 >>>> >>>> >>>> >>>> >>> >>> >> >> > >
try to write:
= "Number of Accepts: " & Sum(IIf(Fields!Accept.Value="Y",1,0)) Good Luck [quoted text, click to view] "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 > > > >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |