NULL Column in PIVOT type (SQL Server 2000 Report)

• Dhandapani Palani

  Newbie

  Points: 2

  More actions

  August 10, 2006 at 1:26 am

  #115024

  Hi All

  This is Pani from India, I am facing an issue with SQL server 2000 Reporting Service with Pivoting the data, as mentioned below

  Source Table Data

  No     Material        ReasonName    Qty

  1       M1              R1                   10

  2       M1              R2                   1

  3       M1              R3                   145

  4       M2              R1                   14

  5       M2              R2                   12

  6       M3              NULL                0

  Trasforming into PIVOT using SQL server 2000 report Matrix type

  output is being displayed as follows

   

         Material       R1          R2           R3      <<NULL>>

            M1              10           1            145      NULL

            M2              14           12           NULL     NULL

            M3                0           0           0             0

   

  I would like to AVOID NULL value of Reason to be transformed into Column.

  kindly help me to resolve this

  Thanks in Advance

  Best regards

  Pani

   

   

   

   

• htt

  SSCertifiable

  Points: 5242

  More actions

  August 10, 2006 at 11:38 am

  #654388

  You can use CASE in the select statement to display NULL as Unknown.  This way you will see Unknown instead of <<NULL>> in the report.

   

  hth,

   

• Dhandapani Palani

  Newbie

  Points: 2

  More actions

  August 10, 2006 at 6:16 pm

  #654457

  Dear Mr.Hthan

  Thanks for your prompt help response, Can't it be possible to hide the null column totally from the report (at report level)

  Best regards

  Pani

• Dave Klug

  SSCertifiable

  Points: 5811

  More actions

  August 11, 2006 at 5:03 am

  #654508

  Yes add a filter to your query, such as "where ReasonName IS NOT NULL"