locked
Calculate the variance between column grouping based on quarterly results for data RRS feed

  • Question

  • I am using report builder to create a report showing row groups (Accounts) and columns groups (Quarterly) for the results. The columns expand based on the report parameter. For example if the data parameter is from Jan to Dec, the columns expand for Q1, Q2, Q3 and Q4. I am using a tablix style report in the report builder. How do I calculate the variance between Q2 - Q1, Q3 -2, Q4 - 1.

    I need a dynamic variance column that subtracts the previous quarter from the current quarter.

    This is starting hurt my head.. please help

    Wednesday, June 19, 2019 9:56 PM

All replies

  • Hi Myranthiru,

    For this kind of question, it would be better if you could share us picture or example of what you are trying and how the tablix should look like .

    Based on my current understanding, I think if there were only like three values we are after.

    We could set the table column visibility by parameter(instead of what dynamic expansion method you are using now). We could use value subtraction only, to get difference between two textboxes.

    E.g:

    =ReportItems!Q2textboxName.value-ReportItems!Q1textboxName.value

    BTW, are those Q2, Q1,Q4 quarter summation ?

    IF the parameter will cross the years, I will suggest to redesign the report structure, for that, using previous kind of function will be not easy and not "safe" working in report.

    Regards,

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, June 20, 2019 1:59 AM
  • Hello 

    I was unable to reference the Report Item text box. It was giving me an error it is a dynamic group column that expands based on the data parameter. If the data parameter/selection on the report parameter was from Jan 1 to Dec 31, then this table/column would expand to show results for all 4 quarters of the year.

    The challenge for me is getting the variance from Q2 - Q1 and onwards. And hiding or ignoring the variance for the first column as there is no point of reference for Q1 vs a prior year quarter as that difference would be out of scope for this report. In that case just i would want to hide it. 


    Sample report once extracted to Excel:

    

    Wednesday, June 26, 2019 5:36 PM
  • I added a column to the right of the existing column group - gave it a name of Net Change.

    Tried this expression:

    =Sum(IIF(Fields!Quarter.Value=Q2,Fields!ProductA.Value,Nothing))-(Sum(IIF(Fields!Quarter.Value=Q1,Fields!ProductA.Value,Nothing)))

    When I did that it gave me an error:

    The Value expression for the text box 'Textbox4' refers to the field Quarter. Report Item expressions can only refer to fields within the current dataset scope or if inside an aggregate the specified datascope. Letters in the names of fields must use the correct case.

    Wednesday, June 26, 2019 8:15 PM
  • This error seems has nothing to do with your current expression.

     Use Properties explorer  to find this Textbox4, delete the report item and change the expression in it.


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 28, 2019 1:52 AM