MSSQLTips

Though it's possible to write something using the expression editor, it's probably more straight forward to do most of the work in the SQL statement in the dataset. If it's a large dataset, performance will definately be enhanced by aggregating in the SQL (dataset) portion of the report first. Here's one way I can think of handling this off the top of my head. (There are probably many more.)

SELECT   
  [Gov Type],
  [Activity CD],
  COUNT([Activity CD]) AS Expr1,

--calculate the negative value of Active CD for D3s and display in a new column
  CASE WHEN [Activity CD]='D3' THEN COUNT([Activity CD])*-1 ELSE COUNT([Activity CD])  END AS Exp2

FROM         ActsDeactsQuery
WHERE [Gov Type]='State & Local' and ([Activity CD] IN ('AC','D3')
GROUP BY [Gov Type], [Activity CD]

ORDER BY [Activity CD]

The dataset would have the following results:

State & Local   AC      3646   3646  
State & Local   D3      219     -219

Then, in Reporting Services, if the requirement were still to display all values as positive, it would be easy enough to display Exp1 in the detail section of the report, while displaying the total of Exp2 in a (Activity CD) group underneath it. The expression would look something like this:

=Sum(Fields!Exp2.Value)

In my experience with Reporting Services, if it's easy enough to do in a SQL statement, it's a lot more efficient to aggregate the data in SQL first and then using Reporting Services for the presentation, rather than relying on Reporting Services to do all the work.

Look for more material on this topic in a future tip... 

Best Regards
Rob Fisch

Hi Rob, thanks your response makes sense but reporting services is supposed to be able to handle all of the calculations and function as a presentation piece. My biggest problem is that I am not well versed with expressions. I can generate a pretty report with a select statement it is massaging the data which is the problem.

I guess I misunderstood your question. I thought, in addition asking about expression writing, you were asking which would be better, to write an expression or aggregate data in the dataset. I suppose I only answered the latter.

You are right. Learning to write expressions is an important part of report writing. And there some things that you can do in expressions, that you simply can't do at all or at least can't do without great difficulty and thought in the SQL of the dataset.

Expression writing is certainly deserving of a tip. If not just one, then several. Until I get to it, I encourage you to read these notes in the Microsoft documentation...

Creating Expressions in Reporting Services
http://msdn2.microsoft.com/en-us/library/ms345237.aspx

Expression Examples in Reporting Services
http://msdn2.microsoft.com/en-us/library/ms157328.aspx

Using Global Collections in Expressions
http://msdn2.microsoft.com/en-us/library/ms157274.aspx

Using Report Functions in Expressions (Reporting Services)
http://msdn2.microsoft.com/en-us/library/ms159673.aspx

 ...and to dive in and just try some things. The more you do it, the better you will get.

Of particular use is the IIF() function. This is the one I use most frequently. It's a good place to start.

Here's one tip. (This drove me nuts until I just 'learned it'.) In T-SQL, you qualify text with single quotes. In the Expression Editor, text is always qualified with double quotes. 

 Rob

Thanks, Rob.