Raisers Edge 7 and ODBC

Ray Berthelette · March 2018

Hi All:

I'm not sure what category this falls under, but my question is how can I go about creating an ODBC connection to our RE database? My goal is to be able to connect to it in MS Excel so that we can build a custom spreadsheet that pulls in various amounts of data into different cells. I don't think this is something I can do as a custom report in RE since my data is going to come from mulitiple queries. Maybe I'm looking in the wrong direction? Any help is greatly appreciated!

Thanks,

Ray

Steven Cinquegrana 2 · March 2018

Hi Ray,

Sorry for what might be an annoying question, but if your end goal is reporting, why go through Excel? You could probably run the data directly via SQL/Reporting Services (or a reporting tool of your choice) and "cut out the middle man".

The direct SQL option gives you a lot of data manipulation options as well, with pivots and the like. Plus you can build views for your frequently accessed data.

And then there are the data warehousing options ...

Let me know if I’m on the wrong track here!

Cheers,

Steve Cinquegrana | CEO and Principal Developer | Protégé Solutions

James Andrews · March 2018

Ray Berthelette:

Hi All:

I'm not sure what category this falls under, but my question is how can I go about creating an ODBC connection to our RE database? My goal is to be able to connect to it in MS Excel so that we can build a custom spreadsheet that pulls in various amounts of data into different cells. I don't think this is something I can do as a custom report in RE since my data is going to come from mulitiple queries. Maybe I'm looking in the wrong direction? Any help is greatly appreciated!

Thanks,

Ray

Steve has a point that once you get into the SQL world, Excel becomes a less attractive reporting option than a lot of other things.

That said, some people are comfortable and productive in Excel, and there's a ton you can do with Power Query, so if that's how you want to do it, go for it.

You just need to have permissions on your server. Then it's control panel, administrative tools, data sources, system DSN and create the RE7 connection there.

Ray Berthelette · March 2018

Thanks for the reply Steve and James! The reason I'm looking to use Excel is because other people in our office are comfortable with that and and have been using it for years. Trying to teach another method would take some time. With that said, I'm familiar with setting up a DSN, but not sure what Server and credentials I would need to enter in order to connect to RE7. Any idea what that would be?

The SQL Reporting option sounds promising, but again, what are the credentials I would need to connect to our RE7 database?

Thanks,

Ray

John Heizer · March 2018

Ray Berthelette:

Hi All:

I'm not sure what category this falls under, but my question is how can I go about creating an ODBC connection to our RE database? My goal is to be able to connect to it in MS Excel so that we can build a custom spreadsheet that pulls in various amounts of data into different cells. I don't think this is something I can do as a custom report in RE since my data is going to come from mulitiple queries. Maybe I'm looking in the wrong direction? Any help is greatly appreciated!

Thanks,

Ray

The official "Blackbaud Supported" method is through their RODBA module (included free in upper levels of support contracts).

https://kb.blackbaud.com/articles/Article/75705

Steven Cinquegrana 2 · March 2018

... what are the credentials I would need to connect to our RE7 database?

Depending on the version of Excel you're running, you can use Power Query (later called Get & Transform, apparently) as James suggests or, as per my olde worlde 2010 version of Excel, simply, Get External Data. (I guess you can't get clearer than that!)

This, you probably already know.

On the SQL Server end, you can either use Windows Trusted or SQL credentials, depending on your network setup. I'd definitely suggest not using sa Login(!) if using explicit cred.s, so you will probably need to add a server Login (mapped to a DB User). For example, I just did this creating a Login/User "ReportsTest" and it worked fine, pulling every record in the RECORDS table from our sample database.

There's a Microsoft reference you can have a look at which I found by just Googling "Excel SQL connect" (a long URL sitting on my phone starting "support.office.com" if that's any help). It has instructions for most Excel versions. Getting the SQL end right - enough rights but not too much!* - was the fiddliest bit. (* Eg, you probably only need role memberships public and db_datareader.)

Hope that helps.

PS And John's right, RODBA is suggested by BB - and works fine - but you might not currently have it and getting it can be tricky in my experience.

Ray Berthelette · March 2018

Thanks Steve. In Excel 2010 it's called Connections. Regardless of if I go in there and try to manually create a connection, or point to a DSN that I created, I'm unable to connect to the RE production database. I tried using the same username and password that I use to connect to RE. I think it's because I need the RODBA module. I have sent an e-mail to BB asking about this and have yet to hear back.

I also see that they use Crystal Reports to create .RPT files that are used in the Reporting tab. I was thinking if I could connect via DSN to the production RE database using Crystal Reports, then I could create my own RPT files and have them included in our install of RE. This way our users only have to run the report.

For now I found sort of a work around. I found that if I create my queries in RE, then export each as an XLSX file, I can then connect to each of these files in my Excel report as a datasource connection. Once I do that I can pull in all of the information into individual sheets in this workbook. I then use the first sheet as my summary page that runs all of the calculations I need. It's not ideal, but it is still faster than how it is being done now, which is running queries and someone manually typing in the data they need into the Excel report.

I'm open to any other suggestions.

Thanks Everyone!

Steven Cinquegrana 2 · March 2018

To the left of Connections on the Excel ribbon should be Get External Data. That's where I created the connection that you can then use in Connections, though it's not mandatory to do so.

You can't use a straight RE-created login, not even Supervisor, as the RE client uses an abstracted login (REUSER for which the password is secret/encrypted) through to the back-end (as far as I recall anyway).

As I said, you need to use - or create - a SQL Server Login mapped to a DB User. This gets around needing RODBA but that's hopefully an option open to you.

Cheers, Steve

Ray Berthelette · March 2018

Steven Cinquegrana:

To the left of Connections on the Excel ribbon should be Get External Data. That's where I created the connection that you can then use in Connections, though it's not mandatory to do so.

You can't use a straight RE-created login, not even Supervisor, as the RE client uses an abstracted login (REUSER for which the password is secret/encrypted) through to the back-end (as far as I recall anyway).

As I said, you need to use - or create - a SQL Server Login mapped to a DB User. This gets around needing RODBA but that's hopefully an option open to you.

Cheers, Steve

I don't have "Get External Data" in my version of Excel, but I do have "From Other Sources". I go there and select "From SQL Server", enter the name of the Server using Windows Authentication, and then when I go to select the database my only options are "master, msdb, tempdb". The name of our production database does not show up. I'm guessing this is because it is hidden by RE. I tried using my RE login and that doesn't work. At this point it doesn't matter which user I use because I don't think the table is exposed.

Thanks,

Ray

Steven Cinquegrana 2 · March 2018

From Other Sources is an item in the Get External Data ribbon group:

2c5f489ad0d155dd11d28d4c6946c68a-huge-08

You still need to set up your SQL Server/Instance/Database access (Login/User) properly, I think.

Raisers Edge 7 and ODBC

Comments

Categories