conditonal counting

is there a way to have conditional counts or conditional running values?
such as counting the number of active customers verses the total number of
customers...
IIF(Fields!CustomerStatus.Value = "Active", Count(Fields!CustomerID.Value),
Nothing)
OR
IIF(Fields!CustomerStatus.Value = "Active",
RunningValue(Fields!CustomerID.Value, Count, Nothing), Nothing)
thanks,
--jimmyI haven't tried this but maybe
Sum(iif(Fields!CustomerStatus.Value="Active",1,0))
Otherwise create a computed column with the IIF,
then SUm( the computed column)
(You might also be able to use Excel's sumif function directly>)
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"jimmy" <jimmy@.discussions.microsoft.com> wrote in message
news:4E42351E-2196-42D8-AB8E-B44B34DDC1E3@.microsoft.com...
> is there a way to have conditional counts or conditional running values?
> such as counting the number of active customers verses the total number
of
> customers...
> IIF(Fields!CustomerStatus.Value = "Active",
Count(Fields!CustomerID.Value),
> Nothing)
> OR
> IIF(Fields!CustomerStatus.Value = "Active",
> RunningValue(Fields!CustomerID.Value, Count, Nothing), Nothing)
>
> thanks,
> --jimmy|||worked perfectly, thanks!
"Wayne Snyder" wrote:
> I haven't tried this but maybe
> Sum(iif(Fields!CustomerStatus.Value="Active",1,0))
> Otherwise create a computed column with the IIF,
> then SUm( the computed column)
> (You might also be able to use Excel's sumif function directly>)
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "jimmy" <jimmy@.discussions.microsoft.com> wrote in message
> news:4E42351E-2196-42D8-AB8E-B44B34DDC1E3@.microsoft.com...
> >
> > is there a way to have conditional counts or conditional running values?
> > such as counting the number of active customers verses the total number
> of
> > customers...
> >
> > IIF(Fields!CustomerStatus.Value = "Active",
> Count(Fields!CustomerID.Value),
> > Nothing)
> >
> > OR
> >
> > IIF(Fields!CustomerStatus.Value = "Active",
> > RunningValue(Fields!CustomerID.Value, Count, Nothing), Nothing)
> >
> >
> > thanks,
> > --jimmy
>
>

Conditional totals

I work in higher ed and have totals for student levels (undergrad, grad,
professional) and then total by institution and system. I have to total
systems that contain only community college information where there are only
undergrads. I would like to remove one of the totals based on the condition.
My example:
School Student Level #
**Community College First Time Freshmen 2100
Sophomore 1800
Total Undergrads 3900
Total at **Community College 3900
I would like to conditional remove the "Total Undergrads" for only community
colleges, but leave it for the universities within the state. Is there a way
of doing this so I don't have two totals giving the exact same total?
Thanks
--
ChrisI forgot to mention this is in a matrix format, where the sub-total give me
the situation in which I seek an answer.
Thank you much,
--
Chris
"cmcdavid" wrote:
> I work in higher ed and have totals for student levels (undergrad, grad,
> professional) and then total by institution and system. I have to total
> systems that contain only community college information where there are only
> undergrads. I would like to remove one of the totals based on the condition.
> My example:
> School Student Level #
> **Community College First Time Freshmen 2100
> Sophomore 1800
> Total Undergrads 3900
> Total at **Community College 3900
> I would like to conditional remove the "Total Undergrads" for only community
> colleges, but leave it for the universities within the state. Is there a way
> of doing this so I don't have two totals giving the exact same total?
> Thanks
> --
> Chris

Conditional Sum/Runnining Total

I have a simple table in ssrs where data is returned from a stored procedure.

I have detail data group totals of the detail data.

I want to be able to create a sum of the detail data matching certain criteria.

i.e.

I have the following total field

sum(Fields!hours_m2.Value)

what I also want to be able to do is create a conditional formula like ...

sum(iif(Fields!Sort_Order.Value = "E1",Fields!hours_m2.Value,0))

When I create this on my report and preview it I get the following message in the field #Error.

Can someone please tell me where I've gone wrong and how to fix ... I know I can change the stored proc but I have 12 columns which I want to do the same thing with which would mean adding 12 columns to my stored proc.

Hello Derek,

In Visual Studio, click on the preview tab and run your report. Then look in the Output window (Ctrl+Alt+O), it should have a description as to what the error is. Can you post that error message?

Jarret

|||

One of the things I have found out about summing in ssrs is that it treats values of doubles and integers seperatley.

try changing the the "0" to "0.0" this will then tell ssrs it is a double type and your sum should work.

|||

description of error message

[rsAggregateOfMixedDataTypes] The Value expression for the textbox ‘textbox101’ uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type.

|||

I have found that using the following

sum(iif(Fields!Sort_Order.Value = "E1" or Fields!Sort_Order.Value = "E2" or Fields!Sort_Order.Value = "F1",cdbl(Fields!hours_m2.Value),cdbl(0.0)))

works

Thanks for you responses

|||

The problem is as Mainiac said. Try this:

=sum(iif(Fields!Sort_Order.Value = "E1", cDec(Fields!hours_m2.Value), cDec(0)))

Hope this helps.

Jarret

Conditional Sum trouble

Can someone help me figure this out? I'm trying to total up 4 columns which
have been X'd to indicate inventory types: RAWX, NSFGX, BUYX and SFGX.
I've tried:
=iif( Fields!RAWX.Value = "X", Sum(Fields!BKIC_PROD_AVGC.Value *
Fields!BKIC_PROD_UOH.Value), 0)
=iif( Fields!NSFGX.Value = "X", Sum(Fields!BKIC_PROD_AVGC.Value *
Fields!BKIC_PROD_UOH.Value), 0)
=iif( Fields!BUYX.Value = "X", Sum(Fields!BKIC_PROD_AVGC.Value *
Fields!BKIC_PROD_UOH.Value), 0)
=iif( Fields!SFGX.Value = "X", Sum(Fields!BKIC_PROD_AVGC.Value *
Fields!BKIC_PROD_UOH.Value), 0)
The reoport runs without errors, but I get a Grand Total for RAWX and 0 for
the other three columns.
If I change the expressions to:
=Sum(iif( Fields!RAWX.Value = "X", (Fields!BKIC_PROD_AVGC.Value *
Fields!BKIC_PROD_UOH.Value), 0))
=Sum(iif( Fields!NSFGX.Value = "X", (Fields!BKIC_PROD_AVGC.Value *
Fields!BKIC_PROD_UOH.Value), 0))
=Sum(iif( Fields!BUYX.Value = "X", (Fields!BKIC_PROD_AVGC.Value *
Fields!BKIC_PROD_UOH.Value), 0))
=Sum(iif( Fields!SFGX.Value = "X", (Fields!BKIC_PROD_AVGC.Value *
Fields!BKIC_PROD_UOH.Value), 0))
I get #ERROR in all columns and a warning for each field:
"The value expression for the textbox â'RAWXâ' uses an aggregate function on
data of varying data types. Aggregate functions other than First, Last,
Previous, Count, and CountDistinct can only aggregate data of a single data
type.
BKIC_PROD_AVGC and BKIC_PROD_UOH are the same data type.
What is this message trying to tell me?Did you try this for the RAWX column?
=iif( Fields!RAWX.Value = "X", Sum(Fields!BKIC_PROD_AVGC.Value) *
Sum(Fields!BKIC_PROD_UOH.Value), 0)
--
I would also try to display the conditional value for the other three
to verify that those fields equal your "X" value
For example (for the NSFGX column), just put
= (Fields!NSFGX.Value = "X")
and see if it returns 'True' or 'False'
that may lead you in the right direction.
--
Also, I'm not sure how your report is set up, but did you notice that
for each of your columns, you're referencing the same values (
Fields!BKIC_PROD_AVGC.Value and Fields!BKIC_PROD_UOH.Value )?

Conditional running total question

Hi there,

I have a problem with trying to get a total of a conditional value. My data output looks as follows:

HEAD OFFICE BRANCH (group1)
(Group 2 below)
Ford Mustang 2001 Blue Excellent
Toyota Raider 2005 Red Good
BMW 5.30 i 2006 Blue Excellent
Mazda MX5 2003 Yellow Good

WESTERN CAPE BRANCH
Ford Fiesta 16i 2002 Blue Good
Renault Clio 2.0d 2005 Red Poor
Nissan Hardbody 2001 Pink Good

I have been trying to find a way to get a value which would be a total number of blue cars for the Branches (group1) as well as the total number of blue cars for the whole report but to no avail with my limited experience. I have read up on people creating extra columns that does counts and sums etc but I am still struggling with aggregate inside aggreagate errors.

Please help me
Mike

Hi,

what about doing a plain SUM(IIF(Fields!autom.Value ="Red",1,0)) etc. in the group footer ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||Hi Jens,

Thanks for the reply. I should have maybe pointed out that I have used a list to do the grouping and can't seem to find a group footer. Is there another thing that I can try?

Sorry if this is a bit of a newbie question.
Mike

Conditional Count for Row Group together with Column Group

Division =Fields!Status.Value
Total
=Fields!Division.Value
=Count(iif("Status"=Fields!Status.Value,1,0),"Division")
Total:
the above is the matrix that i had done to count some values. The
"=Fields!Status.Value" is a column group call Status and the
"=Fields!Division.Value" is a row group call Division. My column goup
contains 3 fields which are "Accepted", "Rejected" and "Pending" and my row
group contais all my division fields. My database had a record of all the
submissions that had been submitted by the different divisions and what is
their submission current status which is either Accepted, Rejected or Pending.
Now what i want to achieved is that i want to display the Number of
Accepted, Rejected and Pending submissions for each Division by using the
Count function. But the expression that i had did does not give me the actual
result. i need some help on what correct expression should i use.
Thansk in advance for all the help and time consumed.ok, managed to solve it with a simple expression =Count(Fields!Status.Value)
Thanks anyway. =)
"JiaN" wrote:
> Division =Fields!Status.Value
> Total
> =Fields!Division.Value
> =Count(iif("Status"=Fields!Status.Value,1,0),"Division")
> Total:
> the above is the matrix that i had done to count some values. The
> "=Fields!Status.Value" is a column group call Status and the
> "=Fields!Division.Value" is a row group call Division. My column goup
> contains 3 fields which are "Accepted", "Rejected" and "Pending" and my row
> group contais all my division fields. My database had a record of all the
> submissions that had been submitted by the different divisions and what is
> their submission current status which is either Accepted, Rejected or Pending.
> Now what i want to achieved is that i want to display the Number of
> Accepted, Rejected and Pending submissions for each Division by using the
> Count function. But the expression that i had did does not give me the actual
> result. i need some help on what correct expression should i use.
> Thansk in advance for all the help and time consumed.

Conditional column select

Is there a statement to change the column in a select clause?

For example:

select Groups, sum ((if group like '%total%' then select TotalHours else if group like '%Direct%' then select DirectHours endif endif)) as Hours, count(*) from tblGroups. group by Groups.

Examples

A. Use a SELECT statement with a simple CASE function

Within a SELECT statement, a simple CASE function allows only an equality check; no other comparisons are made. This example uses the CASE function to alter the display of book categories to make them more understandable.

USE pubs
GO
SELECT Category = 
 CASE type
 WHEN 'popular_comp' THEN 'Popular Computing'
 WHEN 'mod_cook' THEN 'Modern Cooking'
 WHEN 'business' THEN 'Business'
 WHEN 'psychology' THEN 'Psychology'
 WHEN 'trad_cook' THEN 'Traditional Cooking'
 ELSE 'Not yet categorized'
 END,
 CAST(title AS varchar(25)) AS 'Shortened Title',
 price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE AVG(price) BY type
GO

B. Use a SELECT statement with simple and searched CASE function
Within a SELECT statement, the searched CASE function allows values to be replaced in the result set based on comparison values. This example displays the price (a money column) as a text comment based on the price range for a book.
USE pubs
GO
SELECT 'Price Category' = 
 CASE 
 WHEN price IS NULL THEN 'Not yet priced'
 WHEN price < 10 THEN 'Very Reasonable Title'
 WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
 ELSE 'Expensive book!'
 END,
 CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price
GO

C. Use CASE with SUBSTRING and SELECT
This example uses CASE and THEN to produce a list of authors, the book identification numbers, and the book types each author has written. 
USE pubs
SELECT SUBSTRING((RTRIM(a.au_fname) + ' '+ 
 RTRIM(a.au_lname) + ' '), 1, 25) AS Name, a.au_id, ta.title_id,
 Type = 
 CASE 
 WHEN SUBSTRING(ta.title_id, 1, 2) = 'BU' THEN 'Business'
 WHEN SUBSTRING(ta.title_id, 1, 2) = 'MC' THEN 'Modern Cooking'
 WHEN SUBSTRING(ta.title_id, 1, 2) = 'PC' THEN 'Popular Computing'
 WHEN SUBSTRING(ta.title_id, 1, 2) = 'PS' THEN 'Psychology'
 WHEN SUBSTRING(ta.title_id, 1, 2) = 'TC' THEN 'Traditional Cooking'
 END
FROM titleauthor ta JOIN authors a ON ta.au_id = a.au_id

|||

How would I change the static text into the column that I want?

If I used this for example:

SELECT Category =
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN Price
END,
CAST(title AS varchar(25)) AS 'Shortened Title',
price AS Price
FROM titles

I would like my result to be like:

Category Shortened Title Price

2.9900 The Gourmet Microwave 2.9900
19.9900 Silicon Valley Gastronomi 19.9900
Popular Computing Secrets of Silicon Valley 20.0000
Popular Computing But Is It User Friendly? 22.9500