none
Variable vs Calculated field in Dataset

Answers

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

    Please feel free to discuss your opinions.

    Hope this helps.

     


    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    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

     


    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    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 

    For more information about variables, please refer to this professional blog http://blogs.msdn.com/b/robertbruckner/archive/2008/07/20/using-group-variables-in-reporting-services-2008-for-custom-aggregation.aspx 

    For more information about Calculated field, please refer to this document: http://msdn.microsoft.com/en-us/library/ms345330(v=SQL.90).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