CODEWITHSUNDEEP
sql-serverreporting-servicessql-server-2008-r2datasetssrs-2012
Gav_Ed
Gav_Ed

Reputation: 41

SSRS 'Add Query' to dataset

Using SSRS 2012 and SQL Server 2008 R2, I've tried to partly base row sorting of a tablix on a calculated field:

=Max(Assperiod_ID)

The error thrown was that I couldn't have an aggregate in a calculated field, which I can see makes sense.

So with this is mind, I looked at the Add Query Field (Dataset Properties > Fields > Add > Query Field), hoping that this might allow me to aggregate instead.

The problem is, I can find no information or documentation on the use of the 'Add Query Field' option. Can someone tell me:

a.) What this is for and how it's used; b.) Whether it can be used to calculate aggregates?

Many thanks.

Upvotes: 0

Views: 1679

Answers (1)

Hannover Fist
Hannover Fist

Reputation: 10860

The Query Field seems to only be useful for renaming a column.

The Calculated Field is used to calculate a field from a field in the dataset or other calculation.

I think what you want is 

=Max(Assperiod_ID, "DataSet1")

You can't use the aggregate expression in your calculated field, though. You can use this expression in place of where you would use your calculated field.

=IIF(FIELDS!Assperiod_ID.Value = Max(Assperiod_ID, "DataSet1"), TRUE, FALSE)

Another way to get what you want is to create a #TEMP table and add the MAX in the SQL: 

SELECT * 
INTO #TEMP_VALUES 
FROM BLAH_BLAH (YOUR QUERY)...

SELECT *, (SELECT MAX(Assperiod_ID) FROM #TEMP_VALUES) AS MAX_Assperiod_ID
FROM #TEMP_VALUES

MSDN - Dataset Properties Dialog Box, Fields

Upvotes: 1

Related Questions