0

I have a report using Matrix table group by Business with Sub-total and grand total. I have a calculated field called "% of Received" column that uses Report!Items in the detail row, and I want to sort it from highest to lowest value =ReportItems!MTDPACount.Value/ReportItems!MTDLOBTotal.Value, but it is giving me an error that Report Items cannot be used in expression. Please advise. Thanks

Arsee
  • 651
  • 2
  • 11
  • 36

1 Answers1

0

Have you checked whether some of the ReportItems!MTDLOBTotal.Value are NULL or 0? If so, treat ReportItems!MTDLOBTotal.Value first, add ISNULL and WHERE column != 0 to your query or Stored Procedure to filter out those invalid denominator. Or you could directly add condition in the expression to treat 0 or NULL for your ReportItems!MTDLOBTotal.Value

LONG
  • 4,490
  • 2
  • 17
  • 35
  • The totals are sum of the Month to date count so it will never have a zero total for the month. This calculation is in SSRS not in Stored Procedure. – Arsee Dec 14 '16 at 17:04
  • @Arsee and no missing data (blank cell) as well? And be careful with the data type. If these still are not the solution to your answer, would you like to provide the error message when you preview the report? – LONG Dec 14 '16 at 17:06
  • I figured it out. Instead of sorting the calculation formula..I sorted the total monthly count descending order so the highest % is on top of the group. – Arsee Dec 14 '16 at 18:30