# Variable vs Calculated field in Dataset

### Question

• What are the advantages of a variable over a calculated field in the dataset?  both take SSRS expressions, and both are reusable across the report...  why choose one over the other?

Javier Guillen
http://www.msbicentral.com/Blogs/JavierGuillen.aspx
Tuesday, December 14, 2010 8:23 PM

### All replies

• One reason I have found is that it is easier to do subtotals (sometimes).   In the main query I have a field bal_due, and days_past_due.  I create several calculated fields in the dataset as

Current  =   =Iff(days_past_due < 1, bal_due, 0)

PD130    =   =Iff(days_past_due >= 1 and days_past_due < 31,  bal_due, 0)

PD3160  =   =Iff(days_past_due  >= 31 and days_past_due < 61,  bal_due, 0)

In the report I can then have 3 simple column calculations:

Current                                    PD 1-30                                PD 31-60

=sum(Current, "customer')          =sum(PD130, "customer")          =sum(PD3160, "customer")

Tuesday, December 14, 2010 10:47 PM
• Another reason, especially if you go against, for example, an oracle database you have to use the commands of that database.

Sometime they are just not there or to hard to use.  By using a calculated field you have much more flexibility (way easier) to manipulate the data before taking it to the report.

For example, Oracle does not have an “IIF” statement.  It uses “DECODE”.

IIF is way easier to use than the native "DECODE" when doing complex commands.

Tuesday, December 14, 2010 10:56 PM
• But, couldn't you use the same expression on a report variable?  =Iff(days_past_due < 1, bal_due, 0)

The end result would be the same.   So why use the calculated field instead?

Javier Guillen
http://www.msbicentral.com/Blogs/JavierGuillen.aspx
Wednesday, December 15, 2010 1:36 AM
• You can use variables to assign a value when a group is evaluated (group variables) or when the report begin execution (report variables) and then use this variables where required. The values are set only once and can be referred throug out the report execution.

Please refer the below links that highlights some of the uses of group & report variables which achieving via calculated fields is greatly complex if not impossible.

http://blogs.msdn.com/b/sqlrsteamblog/archive/2008/07/21/group-variables-in-reporting-services-2008.aspx

Hope this helps.

BH

Wednesday, December 15, 2010 4:28 AM
• I guess you may find below link also useful in determining the uses and cases of variables vs expressions (calculdated fields).

http://sqlserverpedia.com/blog/sql-server-bloggers/deciding-whether-to-use-an-expression-or-a-variable-in-ssrs/

Regards

BH

Wednesday, December 15, 2010 4:50 AM
• Hi Javier Guillen,

Sometimes we could utilize replace variables with calculated field, however variables is not limited to custom aggregation, but can be expanded into more complex solutions. Calculated field is based on datafield in your dataset, however datafield is not a must to variables.

There are many scenario that calculated field could not replace variables, please refer to this for more information http://msdn.microsoft.com/en-us/library/ff487482.aspx

Thanks,

Challen Fu [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Wednesday, December 15, 2010 5:46 AM
• This was a simple example.  Sometimes it gets a little complex and it's easier to split it into 2 parts.  The calculated field and the report

Wednesday, December 15, 2010 9:50 AM
• Hi

We have SSRS 2008 but I have BIDS 2008 R2. I have added some calculated fields on a cube query that need to sum. They work in BIDS but give an #error in the cell when deployed. Should I use a variable instead?

Our cube data has a single dimension variable - Instrument variable that can be many different things.

Instrument_variable   Value

Sales                         \$300

Purchases                   \$200

Wages                         \$50

etc

I can make a calculated variable My_sales and filter so that it gets the sum of the sales types. However when I deploy this sum to our reporting server I get #error in the cells

Is this only supported on R2?

Thursday, December 16, 2010 12:03 AM
• I have had this problem when going against an oracle database.  There is nothing wrong with the data.  To make it work I had to do something like =sum(CDec(<field_name)).       My best guess is that it cannot determine that the "type" is a numeric field and errors out.
Saturday, December 18, 2010 10:38 AM
• Thanks

This works - a huge thank you - was about to go on holiday for Christmas with this still unresolved!!!!!

Wednesday, December 22, 2010 2:09 AM