Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

COUNT Expand / Collapse
Author
Message
Posted Thursday, February 6, 2014 1:22 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:28 PM
Points: 229, Visits: 694
I am trying to get a sum of of all devices where the count is > 1
Count(IIF(Count(Fields!Device_Name.Value)>1 , Count(Fields!Device_Name.Value),0)

But the above expression gives me 1 even though there are no devices with count> 1

SUm(IIF(Count(Fields!Device_Name.Value)>1 , Count(Fields!Device_Name.Value),0) gives error.

Any ideas?
Post #1538832
Posted Wednesday, February 19, 2014 7:43 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 435, Visits: 1,348
Can you provide some sample data and I will have a look at this for you. Until then, I will make an assumption:

You could re-write / write a query which will work it out for you in advance.
-- Assuming your test data looks something like this???
DECLARE @TestData TABLE
(
Devicename NVARCHAR(100)
)
INSERT INTO @TestData
( Devicename
)
SELECT 'IPhone'
UNION ALL
SELECT 'IPhone'
UNION ALL
SELECT 'HTC'
UNION ALL
SELECT 'Nokia'
UNION ALL
SELECT 'HTC';
WITH MoreThanone
AS ( SELECT Devicename ,
COUNT(Devicename) AS DeviceCount
FROM @TestData
GROUP BY Devicename
HAVING COUNT(Devicename) > 1
)
SELECT SUM(DeviceCount) AS TotalCountofDevicesWhereCountMoreThanOne
FROM MoreThanone

The above can then be used anywhere on your report.


Failing that, (The only way I can see) break down the problem by putting the returned data into a Tablix on the report and create an expression to return the count if it's greater than 1 otherwise return 0. The unfortunate part is that you cannot then use those values in an aggregation other that the header or footer of a report. In this case I have used the header and all looks good. (See image attached)


  Post Attachments 
Count.jpg (6 views, 15.40 KB)
Post #1543011
Posted Thursday, February 20, 2014 1:07 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 4, 2014 2:07 PM
Points: 105, Visits: 496
I have encountered a similar situation w/r to placing sums only in footers.

What I did instead was to generate in T-SQL a table variable which is first populated by the raw data then another T-SQL statement inserts summed up records. I have added an extra column which flags each record as Raw or Sum and use the SSRS's Conditional Row Visibility to print a subtotal record in a group of the raw records (also filtered as per the Raw flag).
Post #1543701
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse