February 6, 2014 at 1:22 pm
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?
February 19, 2014 at 7:43 am
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)
February 20, 2014 at 1:07 pm
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).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply