SSRS - how to use a percentage allocation in report builder

Question

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.

Answer 1

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.

Answer 2

ok thanks everyone. I appreciate the feedback.