Something fishy with an account balance?
It’s nice to be able to drilldown in FRx instead of going to the GL. Saves a few steps. I’m not going to show you what the finished product looks like because I’m in a demo database and the transactions look pretty tacky. But suffice to say that there’s probably a lot more to drilldown than you ever dreamed.
First, here are a few settings in the catalog, then I’ll show you the column layout. In the catalog, make sure the Detail Level is set to Financial & Transaction. It’s also a good idea to change the orientation to Landscape (on the Page Setup subtab) and make the margins as narrow as you can get them (.25).
As soon as you generate a report and drilldown on a few accounts to test, you can figure out which of these codes you like and which don’t do a bit of good. Then you can edit the column layout and delete the ones you don’t need. But be aware that the information they provide changes with the type of account you’re drilling into, so it’s good to test on several accounts before you start deleting.
Oh and by the way, these are called T-codes since they, ah, start with the letter T.
Here’s the column layout to use to start with:
Just so you know, the T-codes give different data depending on what general ledger you’re using. And on the finished report, they don’t show up until you drill on the account level data, so don’t freak out when you don’t see them on the report just after it finishes generating. You have to, you know, drill down to get to them.
I like to set up one report in FRx specifically for transaction level drilldown. Transaction level reports take longer to process and heaven help you if you send the whole thing to the printer. For most other reports I leave the Detail Level set to Financial & Account. Give this a shot—hope it saves some time.
So, cute tricks on the transaction detail. Do you know how to export the report to excel – showing the transaction detail?
Thanks!
Yes: if the catalog has the output option set to excel, and if Detail level is set to Financial & Transaction, both levels will export to excel.
If you’re exporting to excel from the drilldown viewer, there’s a dialog box that pops up, and there’s a box to mark for “Detail (supporting) report”. If you mark it, then you can choose either Account details or Transaction details. Jan
I can see down to the Transaction Details in an FRx report on the screen. However, when I sent it to the printer it prints a blank page. When I move back up to the Account Details level, it prints out on the printer. Do you know what would cause this?
Maybe check the printer driver, but a workaround may be to sent the transaction detail to excel and print it there. Last but not least regarding cause, if it’s not the printer driver, you might check your service pack updates. Good luck…Jan
I am having the same problem the person before indicated. My detail will not print or export to excel. It comes up but I can’t get any further.
Maybe check your service pack…
When i drill down to the GL level a Dynamics Login box comes on the screen requesting a password, is there a way i can make it so the box doesn’t come up when i drill down?
Is there a way to exclude specific transactions when using TCodes? For example, if I only want to see PM source codes and not GJ source codes when drilling down on accounts?
Yes, check out Attribute Filter in the column—do a search in Help. You can also filter in the row format—choose the row modifier dropdown in the Column H Link to GL, then you can select from the Attributes dropdown. It’s very limited, but you can do this for select rows. Cheers…Jan
I have developed a pretty good looking transactions detail report although there are two things that I would like to correct.
1) The values coming from TAPL are not automatically sorted chronologically – for example there could be a transaction for “November 2011” then “January 2012” then “December 2011”. Is it possible to have the transactions in each account sorted chronologically?
2) Is it possible for the totals to appear in bold?
Thanks!
Hi Jon…glad to hear it looks great…I haven’t tried the sort and don’t know of a way off the top other than the old standby export to excel (sigh). No to the totals in bold. Wish I had a better answer. Cheers…Jan
Is there an issue when you have account filters on the columns (Sales, Expense, OPEX) that prevents you from drilling to the transactions?
I haven’t seen that, but you do normally need both CUR and YTD columns because of the way FRx handles the beginning balance. Jan
Hi Jan,
Thanks for all of the great support. I’ve been referring to frxbuzz for 4-5 years with much success. In reference to exporting from the drilldown viewer; specifically the export selection dialog box that you discussed in this comment above: “Jan Harrigan CPA March 15, 2011 at 9:37 am” the ‘Detail (supporting) report’ box is greyed out. Thoughts?
Thanks again for your help.
Solved it! Somebody changed the detail level to Financial Report. When I changed it back to Financial & Transaction the box is no longer greyed out. Hope this will help someone else one day;)
Excellent!
When I go to drill down to the transaction level i get an error that comes up and say’s “no records to drill into for this row” and I have the catalog set for financial & transactions.. Could there be another reason this error is coming up?
Working with someone on their FRx report regarding transaction detail.
When they run the report, it appears to double up on the transactions (but not the total itself).
Is there a setting (or two) that I need to be sure is/isn’t set to fix this?
Hi Jan,
Great site!
I implemented the suggestions and see transaction detail only for the current month. Is there a way to set it up so that the YTD transaction detail is available?
Thanks for the compliment Charlene!
Transaction detail normally needs both CUR and YTD columns. You can set up multiple CUR columns, one for each period, and restrict the printing using the conditional print controls in the Print Control cell. They end up staggered across the page, but at least you’ll have it. Good luck. Jan
I’m trying to export this from a report that has several levels of drill down (between tree and row format you have to drill 3x before you get to the actual detail level information) and i cannot get the TCode data to show on the export. is my report too complicated to display this? I’m reporting on actuals and budget data, both CUR and YTD, if that makes any difference. I’m stumped!
Hi Mike…I just did a quick test in FRx SP11 and my TCode data wouldn’t export either. You should probably discuss with support to see if there’s a fix or workaround. First, make sure you’re selecting Transaction Details on the Export to Excel dialog…but that probably goes without saying. Jan
when I drill down to the transaction level, is FRX able to show the transaction description in the report?
Yes, see TDESC in the screenshot above.
Jan it turned out it was a excel version issue. Frx apparently wont export the t-code data past Excel 2008 (we are running 2010). we are having a new issue now that we have upgraded to MR though, our t-codes seem to bring in strange data (like transaction description brings in the journal entry number) or nothing at all. have you seen a list out there that details the t-codes and what they actually represent?
OK good to know re excel version. Re a list of T-codes (called Attributes in Management Reporter), that list will be specific to the ERP, so if you’re on GP, they’ll be somewhat different than the ones in AX or SL. BTW, MR’s CU6 added lots of attributes for AX and I posted the list recently. Meanwhile, there should be a short description next to each attribute when you drop down the box to select it. But haven’t seen the description bringing in the JE number. Cheers…Jan
after some more digging, it looks like the correct information comes through, but if you have a column where info could be blank (like originating master), MR shifts data to fill holes. So if you have Originating Master, and then Journal Entry number, a purchasing trx will look correct, but a GJ trx will have the journal entry number in the originating master column. We moved things around to rectify it, but it’s still annoying!!
Indeed, annoying as hell. Is this maybe after export to Excel? Or is this happening before export as well?
before as well!
Can you help me I found a File that I can open in drilldown and I need to edit the dates please
Hi Ysabel…an FRx drilldown file is a read only file, so you won’t be able to edit the dates. You’d need to open FRx and rerun the report for the appropriate date. Good luck…Jan