SSRS - how to use a percentage allocation in report builder

asked 2020-09-09T21:24:50.133+00:00
littlehelp 21 Reputation points

I have been working with a SSRS matrix report using report builder to report costs amongst the various business units.

I developed a hierarchy for the column grouping based on the business unit. I have a row grouping based on the Acccount number and other variations of dimensions for the row. This is simply an aggregate of the accounts for row in the query.

On the text box under the column i have an expression that reports the Sum of the field amount "=IIF(isnothing(Round(SUM(Fields!cAmount.Value),2)),"N/A",Round(SUM(Fields!cAmount.Value),2))".

The column group expands based on the hierarchy.

I have a request to re-allocate or reclass some costs that are reported in one of the column groups aka buckets (For example, if a cost is originally part of column group - Atlanta, I need to re-class a portion of the costs another column group - NYC).

How can I reallocate some costs that are reported in one column to another based on a percentage. For example, if Account #10300 is reporting $1000 for Atlanta. I want to take 30% of this cost, and reallocate to NYC and the remainder balance to Boston.

23479-image.png

23575-image.png

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,089 questions
{count} votes

Accepted answer
  1. answered 2020-09-10T08:34:00.343+00:00
    ZoeHui-MSFT 18,606 Reputation points Microsoft Employee

    Hi @littlehelp

    I think it's really hard to meet your needs by report builder directly.

    I'd like to suggest processing the data you want in the raw table first, then apply it with report builder.

    You may achieve with T-SQL query.

    Regards,

    Zoe


    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    No comments

1 additional answer

Sort by: Most helpful
  1. answered 2020-09-10T12:21:43.617+00:00
    littlehelp 21 Reputation points

    ok thanks everyone. I appreciate the feedback.

    No comments