By using this website, you agree to our Terms of Use (click here)
Bit of a strange one so will try and provide the detail as I've been trying to think outside the box and can't quite crack it.
I have created a P&L for a client who is on Weekly Financial Periods. They do have Month end adjustments that they don't want impacting the weekly figures so I have 2 Ledgers Actual & MTHReport. Day to day trans go to Actual and the month end adjustments for to MTHReport.
IN the P&L Column Set I specify a column for each Ledger Ending balance and add the 2 together to give the YTD postition.
So far so good.
Then the client steps in and says that for certain GL accounts I want the Budget amount to report in both the actuals AND the budget columns. So I isolate the accounts in the Rowset and set these to the Budget Ledger. Due to specifying the ledger in the data source in the Rowset I now am getting a double up in the value for that account in my YTD total because the ledger specification in the Column set is overridden by the BUDGET specification in the rowset. To get around this I put a condition in the column =IIF(@RowCode In('0350','0311','0360','0381','0383','0385','0456'),G,G+H) which works perfectly.... for each individual line. The issue is when I get to the Sum of the group of accounts. I am getting a double up on the lines I specify as BUDGET in the rowset as it Overrides the Actual and MTHReport Ledgers specified in their columns.
I was hoping the @LEdger would be a Parameter that I could use but it doesn't appear to be 🙁
If you look at the Admin & GEneral Expenses Group there is 1 line Italicised which denotes that it is pinned to the BUDGET ledger in the Rowset. The sum of the lines should be 71,377.63 when it is displaying 87,053.63
I have attached the rowset and Column set. Any suggestions are welcome 🙂
Gotta love requests like this. Can you say job security? Hopefully you're getting paid by the hour on this one.
Copying the relevant section of your Excel file here with italicized lines highlighted:
Can you attach the entire report definition XML file? Might be easier to troubleshoot than using the Excel files.
It's totally not intuitive, but the XML file will carry the Row Set and Column Set definitions along with it.
You can get it by choosing the Export as XML option on the Report Definitions (CS206000) screen like this:
I think the crux of the problem here is that the Column Set calculations happen after the Row Set calculations.
Interesting thought about using @Ledger as a parameter.
Have you tried copying your formula from Column I
=IIF(@RowCode In('0350','0311','0360','0381','0383','0385','0456'),G,G+H)
Into Column G and changing it to look like this:
=IIF(@RowCode In('0350','0311','0360','0381','0383','0385','0456'),G,0)
And into Column H and changing it to look like this:
=IIF(@RowCode In('0350','0311','0360','0381','0383','0385','0456'),H,0)
Thanks Tim, just heading off for the weekend but will send xml early next week and as usual really appreciate your input
Hi Tim
I did try what you suggested which makes logical sense but it appears that the Value conditions only work if the Column type is Calc 🙁
Ah, good point. Sorry, I didn't actually try it. It would be a little messy, but you could make columns G and H hidden, then reference them with two calculated columns (I and J) using the formulas above.
Annoyingly this works on the lines I want but the group totals don't calculate those lines as zero
Got a little excited that @BookCode Parameter actually related to Ledger!!
Can't get it to work the way I want though 🙁
Have you considered using something like the free Velixo Reports Add-In for Excel instead? That would give you better cell-by-cell control of your formulas without having to keep battling the Row Set vs. Column Set calculation priorities.
No but will have a look at it. Thanks
Cracked it.
I brought the condition into the Rowset and set the COlumnCode condition there 🙂
Nice work!
Would you mind posting a scrceenshot of your Rowset formula? I wasn't able to import your .xml file for some reason. I'm getting this error:
This import would lead to insertion of a Ledger record with key 5. Such action is prohibited by the relations model, please check if your destination is correctly set up
You probably have some additional ledgers defined that I don't have.
Awesome. Thank you for sharing this @julian-axsys
Online Members
