none
Calculation based on Column Groups? RRS feed

  • Question

  • Hi everyone,

    I have a report designed in Report Builder where I have 3 columns which comes from a Column Group (img. 1). I need to have 2 more extra columns based on the values comes from the columns under the column group (img. 2). Sorry, hope this makes sense : ) The final results I have created in an excel file but want to have it inside the Report Builder.

    Please see the images attached. The Calculated Formulas are in the Image 2. Thanks in advance.

    Image 1:

    Image 1

    Image 2:

    Image 2

    Thanks heaps guys.


    artistdedigital

    Thursday, May 22, 2014 5:05 AM

Answers

  • Hi artistdedigital,

    According to your description, you have a matrix in your report and want to do conditional calculation based on the values in column groups. Right?

    In this scenario, we can add two columns outside of column group. Use expression to determine those values need to be calculated. We have tested this case with sample data in our local environment. Here are steps and screenshots for your reference:

    1. Add to columns outside of group. Put expression into corresponding textbox.

    A:  =SUM(IIF(Fields!IntContractCounter.Value=6 or Fields!IntContractCounter.Value=7,Fields!IntScore.Value,0))/20
    B:  =IIF(Fields!IntContractCounter.Value=5,Fields!IntScore.Value,0)+ SUM(IIF(Fields!IntContractCounter.Value=6 or Fields!IntContractCounter.Value=7,Fields!IntScore.Value,0))/20+Fields!RoundTotalScore.Value

    2. Save and preview. It looks like below:

    Reference:
    Expressions (Report Builder and SSRS)

    If you have any question, please feel free to ask.

    Best Regards,
    Simon Hou

      
    Friday, May 23, 2014 6:53 AM
    Moderator

All replies

  • Can someone please look into this? Thanks.

    artistdedigital

    Friday, May 23, 2014 4:50 AM
  • Hi artistdedigital,

    According to your description, you have a matrix in your report and want to do conditional calculation based on the values in column groups. Right?

    In this scenario, we can add two columns outside of column group. Use expression to determine those values need to be calculated. We have tested this case with sample data in our local environment. Here are steps and screenshots for your reference:

    1. Add to columns outside of group. Put expression into corresponding textbox.

    A:  =SUM(IIF(Fields!IntContractCounter.Value=6 or Fields!IntContractCounter.Value=7,Fields!IntScore.Value,0))/20
    B:  =IIF(Fields!IntContractCounter.Value=5,Fields!IntScore.Value,0)+ SUM(IIF(Fields!IntContractCounter.Value=6 or Fields!IntContractCounter.Value=7,Fields!IntScore.Value,0))/20+Fields!RoundTotalScore.Value

    2. Save and preview. It looks like below:

    Reference:
    Expressions (Report Builder and SSRS)

    If you have any question, please feel free to ask.

    Best Regards,
    Simon Hou

      
    Friday, May 23, 2014 6:53 AM
    Moderator
  • Hi Simon,

    Thanks for the reply. It works great. Thanks heaps man...much appreciated...Cheers.


    artistdedigital

    Monday, May 26, 2014 1:13 AM