Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

COUNT Expand / Collapse
Posted Thursday, February 6, 2014 1:22 PM


Group: General Forum Members
Last Login: Monday, December 5, 2016 9:48 AM
Points: 406, Visits: 1,219
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

Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:45 AM
Points: 1,017, Visits: 2,602
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???
Devicename NVARCHAR(100)
( Devicename
SELECT 'Nokia'
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
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:34 AM
Points: 377, Visits: 1,201
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