COUNT

  • 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?

  • 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)

  • 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