none
SSRS 2008 report, How to get difference between 2 columns

    Question

  • I have a dataset with zone wise temperature on a given date

    Mydataset looks like this

    Datetime zone temp

    01/01/2013 f1_south 78

    01/01/2013 f1_west 67

    01/01/2013 f2_south 74

    01/01/2013 f2_west 65

    my report looks like this

    f1 f2

    Date_Time South west south west

    01/01/2013 78 67 74 65

    I got till here.

    i need to show the difference in the forecasted temperature for a given zone on a givendate

    f1 f2 Diff

    Date_Time South west south west South west

    01/01/2013 78 67 74 65 4 3

    How do i achieve that in SSRS 2008 R2

    Sunday, April 21, 2013 7:52 PM

Answers

  • Hello,
    Based on your description, it seems that three are four fields in your report: Date_time, zone(f1,f2),directions(south, west), and  temperature. And then you want to display difference temperature based on the direction of zone.
    Please refer to the following steps to design the report:
    1. Add a Matrix into the report design surface.
    2.Add the "Date_time" field as row group, the "direction" field as parent column group and "zone" as child column group of "zone" group.
    3.Add the "temp" field into the data cell of the Matrix and insert a new column inside the parent column group, and using the following expression for the new column:
    =SUM(IIF(Fields!Zone.value="f1",Fields!temp.value,0))-SUM(IIF(Fields!Zone.value="f2",Fields!temp.value,0))

    If I have any misunderstanding, please share the report and dataset with sample data for us, it benefits us for further analysis.

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click here.


    Fanny Liu
    TechNet Community Support

    Monday, April 22, 2013 2:12 AM

All replies

  • Hi there

    You should do these types of lookups in the database - the query would seem to be relatively straightforward.

    If you need help with the query try on the T-SQL forums. 



    Thanks! Josh

    Sunday, April 21, 2013 11:57 PM
  • Hello,
    Based on your description, it seems that three are four fields in your report: Date_time, zone(f1,f2),directions(south, west), and  temperature. And then you want to display difference temperature based on the direction of zone.
    Please refer to the following steps to design the report:
    1. Add a Matrix into the report design surface.
    2.Add the "Date_time" field as row group, the "direction" field as parent column group and "zone" as child column group of "zone" group.
    3.Add the "temp" field into the data cell of the Matrix and insert a new column inside the parent column group, and using the following expression for the new column:
    =SUM(IIF(Fields!Zone.value="f1",Fields!temp.value,0))-SUM(IIF(Fields!Zone.value="f2",Fields!temp.value,0))

    If I have any misunderstanding, please share the report and dataset with sample data for us, it benefits us for further analysis.

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click here.


    Fanny Liu
    TechNet Community Support

    Monday, April 22, 2013 2:12 AM