Grand Total Column with Cross Tab Variable Number of Months

Question

So I am trying to output/mimic a Business Objects report and the user is able to query a specific reporting month which will pull a life to date balance along with the amounts that compose the current years transactions. I've cross tabbed the "Monetary Amount" based on the column header "Accounting Period" and added subtotals and grand totals and it looks good except now if I query to dataset that doesn't have 12 accounting periods the grand total column bombs out in the database.

Example DB.yxmd

rkl303 · Answer

Thank you for your help. I didn't even think of doing a join. I was also looking to keep the subtotals and grand totals (Rows/Columns) in the report but I think I should be able to figure it out.

In regards to the BO we are pushing away from it but the benefit of it is the people that designed it did a good job and so when we build reports the SQL relationships between very distant tables are easily identified and consistently work versus free handing it in Anaplan which we can't do as it just freezes so we have to literally build/test the SQL in another tool and then plop it into Anaplan and most of us don't know all the relationships that would be necessary to get there.

mceleavey · Answer

Hi @rkl303 ,

First of all, Business Objects is still a thing??? Reach out and I can fix that for you 🙂 The thought of using BO again is giving me horrible flashbacks...

Second, I've attached a workflow for you. Just a bit of pivoting action should do the trick.

I hope this is what you're after.

M.

Example DB solution.yxmd