Crosstabs Reference values and Totals

[jesus-njit]

Hi all,

Still somewhat new to Cognos, I'm not an admin, but a user working with a relational data source and trying to sort out a Crosstab design.

I'm querying transactions for an invoice, the data set already had an invoice number identifier which I define the highest as "Current Bill" and all previous invoices are "Previous Bill". These transactions are the key values for my Crosstab. However for the design I've joined this data with Ledger balances for each account pool that pays for that category of a transaction during the period(month) of the transaction so the join has this "Budget Amount" column for this information, Understand each pool can have multiple transactions per invoice so transactions are aggregated for the Crosstab. I was able to get the Crosstab to show the Current and Previous Bill Amounts and Totals correctly, however getting the referencing "Budget Amount" was a bit of an ordeal.

I ended up creating another column with Crosstab Spaces, and have a hidden trick using the Budget Amount as a subsection to the Account Pool descriptions to get the amounts to display correctly(not Aggregating or Totaling the Budget Amount from the ledger). However I'm having an impossible time to show the total of these Budget Amounts on the bottom row of the  Crosstab. I've tried the process of unlocking the table turn define contents to "Yes"  and tried methods like using a Singleton in the cell. However In most cases the cell is empty or sometimes I try throwing a total of the budget amount and it actually end up blanking the column on output.

Not sure what else to do to get this to work. I'm attaching a couple screenshots of the design and output to visualize the issue. Really would like to get these balances and its total correctly showing.