SSRS Getting Column Names from CTE in the Fields of a Dataset

Question

I am running a query in SSRS that is using 2 common table expressions. The query runs fine in the query designer, but the when I press ok and the dataset is formed; the fields in the dataset are the columns in the select * statement inside the cte. How do I get the columns i created in the cte to show up in the fields of my dataset in the ssrs? Any help is much appreciated.

IF @FilterByEventCode IS NULL 
  BEGIN 
      SELECT * 
      FROM 
  dbo.Historywithqualityfilter(@FQN, '.Event Code,.Event Description', 
         Dateadd(mi, -10, @DateStart), @DateStop, 'good', 'KLN-FTVP') 
  END 
ELSE 
  BEGIN 
      WITH t1(timestamp, eventcode) 
           AS (SELECT localtimestamp, 
                      valueasstring 
               FROM   dbo.Historywithqualityfilter (@FQN, '.Event Code', 
                      Dateadd(mi, -10, @DateStart), 
                              @DateStop, 'good', 'KLN-FTVP') 
               WHERE  @FilterByEventCode = valueasstring), 
           t2(timestamp, eventdescription) 
           AS (SELECT localtimestamp, 
                      valueasstring 
               FROM   dbo.Historywithqualityfilter (@FQN, '.Event Description', 
                              Dateadd(mi, -10, @DateStart), @DateStop, 'good', 
                      'KLN-FTVP')           
              ) 
      SELECT * 
      FROM   t1 a 
             INNER JOIN t2 b 
                     ON a.timestamp = b.timestamp 
  END

score 1 · Accepted Answer · answered Dec 11 '14 at 09:14

What I've noticed is that SSRS has problems getting all fields in such way even when you're in query builder and you set all the parameters so that the maximum of fields apear when you click on "Refresh Fields" it'll still do what it wants to do.

For me you have only two solutions, the first is to edit your query so that the maximum of fields apear no mater what parameters you enter, push refresh fields, and then change the query back to what it was (however be carful not to refresh the fields again so cancel any request from the report builder to do such a thing).

The second is to manually create the missing fields.

SSRS Getting Column Names from CTE in the Fields of a Dataset

1 Answers1