Raw Data Report - Detailed General Ledger
Hello,
Does anyone know if there is a specific report that can be built in FE NXT that will give me all the details of the general ledger; basically a data dump. I need a report that provides me with (in columns) the FY, Period Posted, Transaction Date, Post Date, Project #, GL #, GL Name, Debits, Credits, Transaction Description, etc.
I need to build a report with all these data that is all nicely formatted already, not where I have to spend another 3-4 hours cleaning up the report to analyze and create pivot tables out of it.
Any help is greatly appreciated!
Thank you!
Comments
-
@Siladeth Rattanakongkham
I believe all of this is part of the canned report : General Ledger Report. Have you used that one yet? usually, all you need to do is tell it the timeframe to run the report for and edit any filters for it.0 -
@Siladeth Rattanakongkham
I'm not sure that the FE NXT can produce such a report. If you are asking what I think you are asking, I've used a query in the database mode to generate a complete FY GL transaction dump. I have to do some splitting of the account columns into my account-region-dept, etc and then I do a pivot table.0 -
@Paul Wu
Thank you. Let me try this. The only problem with the reports I am getting from FE NXT and database is that I have to edit them to how I want them, and it takes hours. I with there was a BI portion of FE where we can pull and create the report that we want.0 -
@Kevin Brazell
Thank you. Let me try this.0 -
Something that has been asked for in the Idea Bank is an export to Excel button from the account. But I think you are looking for one step more in order to get a range of accounts. I use to be able to do this in our previous accounting package and it was so easy to get raw data dumps to do analysis work in Excel.
FENXT-I-1355 /
1 -
@Siladeth Rattanakongkham
A good way to get raw data dump, and potentially making “modification” to it for use in pivot is to use FE's SKY API to get the data. Using that I'm able to get these data as raw data to run BI report on it.Though I do not know if you want to have this done. (it can be done yoursevles, or through a partner of Blackbaud such as npAutomate, if you interested, you can contact @Barton Dyson)
0 -
@Siladeth Rattanakongkham
The best “out-of-the-box” method is to use a GL Transaction Query in Database view. On the criteria tab, filter by post status of Posted and choose a date range unless you want everything. If encumbrances are used, filter on encumbrance status of regular to exclude. In the output tab, most of these fields can be selected, but you'll need to calculate FY and Period Posted based on the Post Date field. Be sure to use the amount under Account distribution versus transaction amount, in case a transaction has been distributed to multiple projects. There isn't really a transaction date field but you can use transaction date added. The result can be exported to multiple data formats including CSV.The other option is what Alex suggested, using the SKY API to grab transaction detail and storing in a data warehouse if there are lots of transactions. My team can assist with the second option if there is interest in setting up an FE data warehouse for further analysis.
I've included a screenshot of the output tab for the Transaction Query:
1 -
@Alex Wong
Hi Alex,Yes! I think this is exactly what I am looking for. Is there an area that I can built this in FE NXT, or do I need a 3rd party software? Also, is this something already been built by @Barton Dyson?
Thank you.
0 -
@Siladeth Rattanakongkham
What I show you is not build on FE NXT, it is a custom build utilizing FE's SKY API, BB's programming interface.Barton's post details some way you can do this on database view, you can try to do it. Otherwise, it is a custom work that need to be done. You can check out developer.blackbaud.com if you want to get started on expanding reporting and additional custom functionality on FE NXT, or you can contract a partner to help you do it.
0 -
@Barton Dyson
Thank you so much! I will try to the transaction query first as I need this right now; however, I would like reach out about the SKY API as I think this option has more of what I am looking for. How can I connect with your team regarding it?0 -
Let's discuss further. You can email me at barton.dyson@npautomate.com or feel free to call me at 843.343.3579.
0 -
@Siladeth Rattanakongkham
If you like using Excel and pivot tables, I suggest dumping the data into a folder and using power query to clean the data once. Every time you add to the folder and refresh the data, it will automatically populate the table, so you only have to clean the data the first time.0 -
@Barton Dyson
What is the difference between Amount and Transaction Amount?0 -
@Kyle Lewis
It depends on where you are accessing data. Using the API endpoint to get transaction detail, there is a transaction amount and distribution amount field. There is also a transaction id and distribution id. It's safest to pull from distribution amount and index/PK on distribution id. That ensures that, if one line of a journal entry is being split across multiple projects or t-codes, you'll get the lowest level of data. If a transaction amount was split across three lines, the three distribution amounts for that transaction id will add up to the total amount for the line.When using a transaction query, instead of pulling the amount, you should go to account distribution and pull the amount from that section to ensure you are getting the lowest level of transaction data.
1 -
@Barton Dyson
Thank you. Since it is a short week, I will reach out some time next week.0
Categories
- All Categories
- 6 Blackbaud Community Help
- 213 bbcon®
- 1.4K Blackbaud Altru®
- 403 Blackbaud Award Management™ and Blackbaud Stewardship Management™
- 1.2K Blackbaud CRM™ and Blackbaud Internet Solutions™
- 15 donorCentrics®
- 360 Blackbaud eTapestry®
- 2.6K Blackbaud Financial Edge NXT®
- 656 Blackbaud Grantmaking™
- 577 Blackbaud Education Management Solutions for Higher Education
- 3.2K Blackbaud Education Management Solutions for K-12 Schools
- 940 Blackbaud Luminate Online® and Blackbaud TeamRaiser®
- 84 JustGiving® from Blackbaud®
- 6.7K Blackbaud Raiser's Edge NXT®
- 3.7K SKY Developer
- 249 ResearchPoint™
- 119 Blackbaud Tuition Management™
- 165 Organizational Best Practices
- 241 Member Lounge (Just for Fun)
- 34 Blackbaud Community Challenges
- 37 PowerUp Challenges
- 3 (Open) PowerUp Challenge: Grid View Batch
- 3 (Closed) PowerUp Challenge: Chat for Blackbaud AI
- 3 (Closed) PowerUp Challenge: Data Health
- 3 (Closed) Raiser's Edge NXT PowerUp Challenge: Product Update Briefing
- 3 (Closed) Raiser's Edge NXT PowerUp Challenge: Standard Reports+
- 3 (Closed) Raiser's Edge NXT PowerUp Challenge: Email Marketing
- 3 (Closed) Raiser's Edge NXT PowerUp Challenge: Gift Management
- 4 (Closed) Raiser's Edge NXT PowerUp Challenge: Event Management
- 3 (Closed) Raiser's Edge NXT PowerUp Challenge: Home Page
- 4 (Closed) Raiser's Edge NXT PowerUp Challenge: Standard Reports
- 4 (Closed) Raiser's Edge NXT PowerUp Challenge: Query
- 796 Community News
- 3K Jobs Board
- 54 Blackbaud SKY® Reporting Announcements
- 47 Blackbaud CRM Higher Ed Product Advisory Group (HE PAG)
- 19 Blackbaud CRM Product Advisory Group (BBCRM PAG)