1

My query includes 4 groups. Category > Type > Activity > Nature > detailPrice.

I have the report built with 4 different List Items stacked into each other. Each list is one of the four groups. The detailPrice is a table (pink).Each of the 4 groups has a different colored border, to differentiate (just for testing).

Each list shows a Total of the previous list. List 2 (Activity) shows the sum total of the entries in List 1 (Nature), and so on. The total of List 4 (Category) shows the grand total of all the previous lists.

My 3rd List's group (Type) is always split into two sections: Revenue and Expenses. So there are two totals in the 3rd List's grouping: the first is 90033, the second is 37627. Here's the total of Revenue. Only the first number matters, which is 90033.

Inside my fourth list (Category), rather than just show the grand total (of Revenue - Expense), I want to show both the total of Revenue and Expenses, and then show the grand total. For the Grand Total, I simply sum the proper field. But I am unable to get the 90033 and 37627 I need.

I've tried many different options:

  • When the 3rd list sums the Revenue, I put that value in a custom code variable, and then the same with Expenses. =IIF(Fields!Type.Value = "Revenue", Code.addTotal("R",SUM(Fields!detailPrice.Value)) (the function simply adds the detailPrice to one of two variables: totalR and totalE depending on the first parameter)
  • I tried the same with a Report Variable =IIF(Fields!Type.Value = "Revenue", Variables!totalR.SetValue(SUM(Fields!detailPrice.Value)))
  • I also tried Running Values, but didn't get very far.
  • I can't simply access the ReportItem!total of the 3rd group, as I am trying to get it from outside the group.

After all my attempts, my two variables (totalR and totalE) have the same value: 37627. I can never get 90033 as well as 37627. I can get the total of Expenses... but not Revenues. I feel like the total of the Expenses group overrides the Revenue value, but I have no idea how to keep that 90033 to use it outside of the group.

Is it possible to reference each total in a group outside of said group?

snowyphoenix
  • 7
  • 1
  • Are each of the totals used only in one box? You could try to reference the textbox. =ReportItems!TextBoxName.Value – papermoon88 Nov 16 '20 at 18:25
  • @papermoon88 Thanks for answering! Hmm.. the two problems I see with that (when I tried it) is that the two totals are in the same textbox (the same textbox duplicated for each total), so each total doesn't have a unique textbox name... and since I'm trying to reference said totals outside of the group, it won't let me use the !ReportItems command. – snowyphoenix Nov 30 '20 at 21:26
  • Would it be possible for you to create a new dataset for the totals? – papermoon88 Dec 04 '20 at 13:15
  • I was hoping it would not have to come to this, but I think that's how I will have to do it. Create a new dataset entry for those individual totals. Thank you for helping! I'll make sure to do that when I can. – snowyphoenix Dec 07 '20 at 14:21

0 Answers0