4

Using Reporting Services in SQL Server 2005: Is there a way to count only records that are not null; similar to "COUNTA" in Excel? I would think this would be very simple process, but nothing I have tried has worked. For example, I have tried using the following expression for "Completed", which is one column I am trying to count: =count(IIF(Fields!Completed.Value="END")) However, this throws the "wrong number of arguments" error. "Completed" will have a value of "End" or be null.

If necessary, I can try to work this into my SQL query, but the query is already incredibly complicated.

Also, I've found very little documentation for how to calculate report totals, and how to total from groups. Would anyone have any recommendations on what to use as a reference?

Update: upon further inspection, the expression =SUM(IIF(IsNothing(Fields!Completed.Value),0,1)) DOES indeed return the appropriate number of records. I made the mistake of thinking that the report would tally up the number of records in the actual report with "end" for a value. Since the report groups on "Completed", "End" only shows up once in the report for each unique ID (also being grouped on, above Completed). So I really need to be counting (suming?) based on subtotals...if that's even possible.

user329266
  • 53
  • 1
  • 1
  • 5

3 Answers3

15
=SUM(IIF(IsNothing(Fields!Completed.Value),0,1))
Kenneth
  • 1,364
  • 1
  • 8
  • 11
  • This does not work. The column I am trying to count will either have a value of "end", or it will be null. So CountDistinct returns only one value. – user329266 Jun 14 '10 at 16:40
  • Should do exactly what you need now. – Kenneth Jun 14 '10 at 17:02
  • Thanks, Kenneth. I tried your revised expression, but the result is closer to the total number of null values than to the number of non null values. Trying to figure out what the opposite of "IsNothing" might be. – user329266 Jun 14 '10 at 17:19
  • This works perfectly for me. RS2005? Swap the 0 & 1 to test your theory. – Kenneth Jun 14 '10 at 17:36
  • Just tried swapping the 0 and 1. Also tried using Count and not Sum, since I want the total count of results that are not null. Results are the same when I swap 0 with 1 as they are in their original positions. Just for the sake of trying something different, I also tried Sum, with 0 and 1 in the originaly positions and then swapped. Neither returns the correct results. Yes, this is Reporting Services 2005. – user329266 Jun 14 '10 at 17:52
  • Are you positive expected NULLs are truly NULL? The IIF expression I posted is evaluating the contents of each 'completed' field. If the result is nothing (NULL) it returns a 0, if it contains any data (even blank space) a 1 is returned. The outer SUM adds up all of the ones & zeros giving an accurate count of NOT NULL rows. I am not sure why you we be seeing different behavior unless this is being used inside of a group, or list that would alter the scope. Maybe you can dump your query results right to a table object and put that expression in the footer (which is how I tested it). – Kenneth Jun 14 '10 at 18:13
  • Good question...If I run the query from my data tab in SQL Server 2005, the results that come up are either "End" or NULL. But in RS, I'm grouping on the "Completed" column, and trying to count the total for Completed in the footer, so...you're saying that if the report is grouped, it's given a value regardless? Argh! Will need to try dumping the results in a table object as suggested... – user329266 Jun 14 '10 at 18:25
  • Ok...upon further inspection, the expression above DOES indeed return the appropriate number of records. I made the mistake of thinking that the report would tally up the number of records *in the actual report* with "end" for a value. Since the report groups on "Completed", "End" only shows up once in the report for each unique ID (also being grouped on, above Completed). So I really need to be counting (suming?) based on subtotals...if that's even possible. – user329266 Jun 14 '10 at 19:28
  • Marking this as the correct answer because it is the closest. I ended up revising my query to ony pull in one record for each ID so I don't have duplicate rows that the report tries to total. Even so...one would think that reporting software would have an easier system of calculating totals. Oh well. – user329266 Jun 21 '10 at 12:38
  • In column 1 "TEST" appears 11 times and "NOTEST" appears 15 times but the number is dynamic. How can I use a COUNT statement to get how many times TEST appears and how many times "NOTEST" appears? – Si8 Apr 04 '14 at 16:52
2

Generally, if you are trying to sum a column value then you must remember that NULL + anything is NULL. So to get around this, you can do something like:

SELECT SUM(coalesce(col1,0)) col1Sum
  FROM your_table
 WHERE <conditions>

What that code does is make sure that if col1 has a NULL value, we make it 0 before trying to sum so that we will always get a valid sum result.

So when doing things like getting report totals, etc., it's important to remember this step when summing up your values.

As for getting records that are NOT NULL, you need to define what that means. Does that mean "every column in the row must be NOT NULL", only certain ones, etc?

In general, you can check a column for not null by issuing:

SELECT *
  FROM your_table
 WHERE col1 IS NOT NULL

You would need to repeat that WHERE clause condition for all columns you want to not be NULL.

dcp
  • 54,410
  • 22
  • 144
  • 164
  • The first query could also be written as `SELECT SUM(ISNULL(COL1,0))...` – eidylon Jun 14 '10 at 16:14
  • @eidylon - Yes, they are equivalent in this example. – dcp Jun 14 '10 at 16:23
  • Ok, but I'm trying to count only null values in the report, as opposed to the SQL statement...unless there is a way to plug this into the report outside of the SQL statement on the Data tab? I've been advised to not use subreports, if that's what you were thinking of. – user329266 Jun 14 '10 at 16:39
  • To reiterate from the post below, the column I am trying to count (not sum) will either have a value of "end", or it will be null. – user329266 Jun 14 '10 at 16:47
  • @user329266 - I don't really understand what the difference between "null values in the report" and "null values in the data" is. If you want get a count of the number of times a given column is null, then you could just do "SELECT COUNT(*) FROM your_table WHERE col1 IS NULL". Maybe you can edit your question and supply some real data and what you are expecting as output. – dcp Jun 14 '10 at 16:54
  • DCP, I've rephrased the original question. I had put "SQL Server 2005 Reporting Services" in the title of the question - guess I needed to reiterate that a few more times. Reporting Services is really the same thing as if you create a report in Visual Studio. At this point, I would prefer not to alter the query being used in the report, which is very complicated, and I won't paste it here because of security concerns. – user329266 Jun 14 '10 at 17:04
0

In your last comment you say "I really need to be counting (suming?) based on subtotals...if that's even possible"

A way that I work around this in SSRS is to compute a dataset for the report which has columns for my subtotal values. Basically I precalculate the subtotals, and then I have those values available everywhere in my report. This can be done in a report datamart, or in the stored procedure that produces the dataset. Although this is kind of awkward, I find that it is sometimes easier than trying to bend SSRS to my will. Thought I would mention it in case it is useful.

You can execute this code to get a visual for what I mean - 

-- use this code to create a sample data set 
-- Note that I am simply stuffing the correct subtotal values in - 
-- but in reality, you would calculate these from your base data
CREATE TABLE SampleDataSet( Salesperson varchar(50), Region varchar(20), 
Country varchar(30), Sales float, RegionSales float, CountrySales float, GrandTotalSales float ) 

INSERT INTO SampleDataSet Values( 'Brown', 'East', 'Canada', 1000.40, 6780.00, 108686.56, 705043.89 ) 
INSERT INTO SampleDataSet Values( 'Smith', 'East', 'Canada', 3420.76, 6780.00, 108686.56, 705043.89 ) 
INSERT INTO SampleDataSet Values( 'Williams', 'East', 'Canada', 2358.84, 6780.00, 108686.56, 705043.89 ) 
INSERT INTO SampleDataSet Values( 'Simons', 'West', 'Canada', 6298.68, 101906.56, 108686.56, 705043.89 ) 
INSERT INTO SampleDataSet Values( 'Miller', 'West', 'Canada', 95607.88, 101906.56, 108686.56, 705043.89 )
INSERT INTO SampleDataSet Values( 'Knight', 'North', 'UK', 596357.33, 596357.33, 596357.33, 705043.89 ) 

-- inspect data 
SELECT * FROM  SampleDataSet

SELECT Region, SUM(Sales) as RegionSales FROM  SampleDataSet
GROUP BY Region

SELECT Country, SUM(Sales) as CountrySales FROM  SampleDataSet
GROUP BY Country

SELECT SUM(Sales) as GrandTotalSales FROM  SampleDataSet
soo
  • 7,100
  • 1
  • 18
  • 10