Anyone ever tried to use SQL Query Analyzer and Enterprise Manager with their Financial Edge database? I am trying to figure out the whole database set up so that I can hopefully use ODBC with Access 2003 to get some reports to work better in Access then they do in FE. We have complains about reports working slowly and then other reports we want to set up differently. Any help or knowledge of the database set up with linking tables would be greatly appreciated.

If you're looking for a data dictionary to aid in building sql queries then have a look at C:\Program Files\Blackbaud\The Financial Edge\Help\FEDatabaseSchema.chm (thanks Doug Creek!). The RE version of that file really helped me out when I was pulling data from the backend. As for tracing the database, good luck. I was really surprised to see the DML in RE (probably FE too) when a report is run. It seemed to stick data in temporary tables and then run the crystal reports off of that. Dan Larson Programmer/Analyst St. Cloud Hospital St. Cloud, Minnesota

Thank you Dan. And thanks for wishing me luck I need all the luck I can get. the one thing that keeps throwing me off is all these autonumber fields in each table. I have no clue why they are here or what they do.

I have just recently written two reports that use data from both RE and FE, and I'll just tell you now the FE tables are a mess... Post or email questions and I'll do what I can to help you get thru the FE tables with what I've learned. For another tip, don't use Access reports. You'll be MUCH better off just using access to find your way thru the tables, then using Crystal Reports to build the report. Taking the time to learn crystal will help you a lot when you start bumping up to the limits of what you can report in Access... Good luck! Doug Doug Creek University of Alaska Database Administrator [Email Removed] Visit the RE User Forums! http://www.reusers.server-planet.com/

Doug - Did you pull data from both RE and FE into one crystal report? Did you have to create a view? That's on the to-do list for me as well. Anyone know what tables I'd need to hit in sql if I wanted to list the number of records per department code attribute? Our Admin Specialist wants the ability to add to the deparment code attribute table but giving her that access would mean she could delete as well. So I was going to try and see how many records are using those dept. codes. This whole db schema is just so screwy though! Dan Larson Programmer/Analyst St. Cloud Hospital St. Cloud, Minnesota

I did pull the info into 1 crystal report, but here's the trick. Neither Query Analyzer nor SQL server (at least on 2000) will allow you to create views/code between two database (Note: In query analyzer, you can PROBABLY use connect scripts and connect between databases, but I haven't had time to play with it). What I did was create a view on each side (Fund/constituent information on the RE side, GL information on the FE side), and connected between the two using the project/fund ID (technically I used an attribute, but I'm not going there right now). For your problem, I would just create a rystal report or even just a view (assuming it by project) between the GL7Projects, GL7ProjectAttributes, and the Tableentries table, then limit it to the Gl7ProjectAttributes.attrType of whatever number matches your departments (or by the codetable #, which ever is easiest). I think the table name for type is GL7ProjectAttributeTypes (I'm not logged in at the moment). That help? Doug Creek University of Alaska Database Administrator [Email Removed] Visit the RE User Forums! http://www.reusers.server-planet.com/

That sounds like a place to start! I'm not even sure what our Director wants right now, but I had a feeling it might be do-able (with a little hack here and there of course). Interesting that SQL 2000 doesn't allow views between 2 databases, I wonder if 2005 does. So did you connect the 2 views in crystal then? Dan Larson Programmer/Analyst St. Cloud Hospital St. Cloud, Minnesota

Doug- You definitely CAN create a view that pulls from multiple databases using SQL 2000 (and 2k5). I do it all the time. We are an RE shop, no FE here, but we have an additional database on our RE server called REPlus where we create views, supplemental tables, user defined functions, stored procedures, etc. We query between RE, REPlus, and other databases on the same server every day. All that was required was making sure the security was configured correctly and that all references to data in another database was fully qualified (RE7.DBO.records vs DBO.RECORDS or RECORDS). Mitch Mitch Gibbs Director, IS Advocate Charitable Foundation [i]--- Edited at 3/20/2007 1:36:31 PM by Mitch Gibbs[/i]

Crystal is way more powerful then the microsft products :) :) :) I've actually connected SQL Server to our banner oracle database before and pulled information that we needed... basically I created the views, then linked between the two fields. You do SOMETIMES have issues with server side grouping and summaries from the server. and if you're going for beginning fund balance, be ready for a wild ride. Just so you know, the report we wanted was a internal/external report on non-endowed funds and endowed funds. After 2 weeks of work, I think we finally have 99% of the kinks worked out of it (YAY!) Doug Creek University of Alaska Database Administrator [Email Removed] Visit the RE User Forums! http://www.reusers.server-planet.com/

You create the view using Query Analyzer? I haven't been able to find a way to create the view within the SQL enterprise manager across databases... Doug Creek University of Alaska Database Administrator [Email Removed] Visit the RE User Forums! http://www.reusers.server-planet.com/

Across databases ON THE SAME SERVER, it's very easy. Just use the fully qualified name. Databases on DIFFERENT SERVERS is another kettle of fish. It's theoretically possible using linked servers, but I haven't been able to get it to work. Of course, I haven't tried that hard either. Drew J. Drew Allen Children's Hospital of Philadelphia [Email Removed] Check out the user run RE Forum at http://www.blackbus.org/ . Check out the Crystal Reports blog "Crystal Clear" at http://www.blackbus.org/blogs/viewblog.php?userid=63

Ok this may be a little off topic (not by much), does anyone know if unlocking RODBA requires SQL Server authentication? We're strictly Windows authentication and I have no plans on changing that. But when I try to unlock the RODBA module, I'm eventually prompted for database credentials. BB told me both authentications are ok and that it just requires a user w/dbo rights which I've tried and still I get the message that it's not associated with a trusted sql server connection. In the past, that usually meant that something is expecting sql server authentication. Any ideas? Dan Larson Programmer/Analyst St. Cloud Hospital St. Cloud, Minnesota

Drew- You're right. I was thinking of just multiple databases on a single server. I have used OPENROWSET to query across servers on an ad-hoc basis. The information in SQL Books Online makes it pretty straight forward. More complicated than a single server, but not too onerous. Mitch Mitch Gibbs Director, IS Advocate Charitable Foundation

I find it easiest to write a query in Blackbaud close to what I want, go to View menu - SQL, then copy that that sql to the query analyzer and mess with it there. I find that saves LOTS of time. Not sure if that's available in RE7 as well. -- Dom

That is actually what I started to do yesterday. I thought maybe I could create a query then an export to get the information I wanted. The one report that I was working on was a project activity report for all projects. This report takes on the average around 23 minutes to write. Yesterday I was able to create one query that gave the same information that only takes 3 minutes to run. I think that it is crystal that takes the actual report so long to run.