Post reply

Adding extra columns in SSRS as expressions

  • October 26, 2011 at 8:19 am

    #245223

    Hello All

    New to this forum. Exciting!

    I can develop basic reports in SSRS and been using it for few months. Now I have a query.

    Lets say I need to create extra columns in a report as a formula or expression and calculate a value based on other columns. Forexample,

    =Fields!Sales.Value - Fields!Cost.Value

    So I did that. I created a column named foreample Margin and added the above formula to it.

    Now lets say I want to add another column which is Margin%. The calculation of this column will be based on Margin column I created previously.

    The formula for this column will be (this is where I am struggling)

    Margin / Fields!Sales.Value * 100

    Now how can I put the above formula as expression in SSRS? How can I refer to Margin column I created? Fields!Margin.Value won't work?

    What is the solution? Do I need to create variable? If yes then how?

    Please any help will be appreciated.

    Thanks.

  • October 26, 2011 at 8:32 am

    #1399642

    In ssrs 2005 I'm able to add a calculated field in the dataset as x1 and then another 1 as x1 + 1.

    Then you can use those in the tables & matrices.

    I personally preffer to do that server side so I see everything when I debug the queries.

  • October 26, 2011 at 8:46 am

    #1399660

    Great! Got it.

    Thanks.

  • October 26, 2011 at 8:49 am

    #1399666

    HTH & welcome to our little world!

  • October 31, 2011 at 10:02 am

    #1401182

    imranx101 (10/26/2011)

    Hello All

    New to this forum. Exciting!

    I can develop basic reports in SSRS and been using it for few months. Now I have a query.

    Lets say I need to create extra columns in a report as a formula or expression and calculate a value based on other columns. Forexample,

    =Fields!Sales.Value - Fields!Cost.Value

    So I did that. I created a column named foreample Margin and added the above formula to it.

    Now lets say I want to add another column which is Margin%. The calculation of this column will be based on Margin column I created previously.

    The formula for this column will be (this is where I am struggling)

    Margin / Fields!Sales.Value * 100

    Now how can I put the above formula as expression in SSRS? How can I refer to Margin column I created? Fields!Margin.Value won't work?

    What is the solution? Do I need to create variable? If yes then how?

    Please any help will be appreciated.

    Thanks.

    You can also refer to the text box (i.e., the cell) where you entered the Margin formula using the ReportItems collection syntax: ReportItems!Textbox1.Value

    Note that the name of the item in this syntax is the name of the text box - BIDS provides a generic "textbox#" format, but you can change the name of a text box in its Properties dialog.

    Assuming that you change the name of the text box with the Margin formula to "Margin", you can do this for Margin%:

    = ReportItems!Margin.Value/Fields!Sales.Value * 100

    Jason Wolfkill

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply