CODEWITHSUNDEEP
sql-serverreporting-servicescountssrs-2008-r2
Jeyhun Rahimov
Jeyhun Rahimov

Reputation: 3787

To count rows that is NOT NULL in SSRS

I want to number some base rows in table without mixing the ordering. I have table like this:

Status ProductId

A       12         
NULL    25
B       35
C       56
NULL    89
NULL    99
D       120
E       140

I want to add No column, to count Statuses which is not null with same ProductId ordering, but, don't want to count NULL rows. I want the result like this:

  No      Status  ProductId    
  1       A       12         
          NULL    25
  2       B       35
  3       C       56
          NULL    89
          NULL    99
  4       D       120
  5       E       140

I work on SQL Sever 2008, SSRS. Someone can give solution in SQL side or in RDL file.

Upvotes: 0

Views: 581

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

You can do this:

WITH CTE 
AS
(
  SELECT
    ROW_NUMBER() OVER(ORDER BY Status) AS No,
    Status, ProductId
  FROM table1
  WHERE Status IS NOT NULL
) 
SELECT 
  c.No,
  t.Status,
  t.ProductId
FROM  table1 AS t
LEFT JOIN CTE AS c ON c.ProductId = t.ProductId
ORDER BY ProductId;

You can use this query directly in your report.

Upvotes: 1

Related Questions