Showing posts with label SharePoint. Show all posts
Showing posts with label SharePoint. Show all posts

Sunday, July 27, 2014

RE Users: Crystal Reports is not your only option!

Something that drives me crazy for The Raiser's Edge community is the perception that Crystal Reports is the best way to provide reports (outside of the built-in reports and dashboards) because that is what comes with The Raiser's Edge.  The Raiser's Edge is a SQL-based database and as a result there are many alternatives to reporting. Below are a few options, separate from Crystal Reports and the built-in RE Reports.  This post is just scratching the surface of what is possible.  Not covered are topics such as static vs dynamic reports, automation, integration with other data sources, KPIs and metrics, and even automatic notifications.


Microsoft Excel

One technology that nearly everyone uses, but many don't have a "super-user" grasp of, is Microsoft Excel.  Once data lands in Excel it is amazing what people can do with it.  And I would be willing to bet that many RE, non-techie users, can at least get the data into Excel via Export or Query.  Once the data is in Excel, developers, or power-users can then get very creative and write VBA macros, or leverage Pivot Tables and Charts, and use conditional formatting among other things to generate reports and dashboards that can easily be shared.  Dynamic filtering can be implemented to let users get a different picture of the data.

In addition,  through add-ins and macros, these reports can be automated such that executives and directors, or even other users, can pull the data at the click of a button, without having to log into RE and develop queries and exports.  Heck, once it is in Excel, these reports can be posted onto SharePoint and rich reports displayed on web pages in SharePoint.

Update - Go check out this Microsoft Add-In which reports on The Raiser's Edge recurring gifts that I have created to show you how you can leverage Microsoft Excel a bit better.

Microsoft Access

Another application in the Microsoft Office suite of products is Microsoft Access. Data can be imported or linked through Access and then presented in a way that can be reported on.  Queries can be written, views created, forms generated, and data can even be taken offline for times when disconnection to The Raiser's Edge ensues.  When using Access, one must be careful to not update data directly in the SQL Server backend as the database could be corrupted with "bad data" that does not adhere to The Raiser's Edge front-end application. 

SQL Server Reporting Services (SSRS)

With SQL Server Reporting Services, you can create interactive, tabular, graphical, or free-form reports from relational, multidimensional, or XML-based data sources. Reports can include rich data visualization, including charts, and maps. You can publish reports, schedule report processing, or access reports on-demand. You can select from a variety of viewing formats, export reports to other applications such as Microsoft Excel, and subscribe to published reports. The reports that you create can be viewed over a Web-based connection or as part of a Microsoft Windows application or SharePoint site. You can also create data alerts on reports published to a SharePoint site and receive email messages when report data changes.


SharePoint Web Parts

SharePoint may be a tool that many of you are already using.  It is a great way to organize content such as documents and list-type information as well as enable knowledge sharing and collaboration.  However, when one views it truly as a knowledge sharing tool, letting it provide dashboards and reports to those users that don't need access to The Raiser's Edge application, but rely on reports generated from it can be opportunity.  Web pages can be created with custom web parts, or data view web parts, that pull information from The Raiser's Edge via SQL and display these reports and dashboards within the pages themselves.  This even allows for information sharing across geographic locations, or for those people that may travel, since the content is served up via web pages.drill-down.  SharePoint presents a great opportunity to share your Raiser's Edge data.

Check out my post on displaying a webpart in SharePoint reporting on Raiser's Edge data as an example.


Custom Application Development

Having a software developer create a dashboard application, or an application that contains dashboards, is a great way to have your Raiser's Edge data tell you a story.  A Dashboard application must serve another purpose as well to be worth the investment.  Maybe these reports need to come alive and offer drill-down capabilities, such as our Raiser's Edge Financial Dashboard.  Or maybe they have some further integration capabilities with the Raiser's Edge.  Even a Raiser's Edge plug-in could be created that is available from within The Raiser's Edge itself that provides additional reporting and dashboarding capabilities.  Whatever the extra requirements you have, custom application development can go a long way.

There are many other Business Intelligence (BI) and Analytics tools out there, such as Tableau, Spotfire and Logi, that can access a SQL Server database (which most Raiser's Edge customers use).  What I have presented to you is just a snippet of opportunities that many of you may already have in-house.  If you are hosted by Blackbaud then your options are severely limited.  There are other options out there should you want to explore those to allow you to improve your investment in The Raiser's Edge.  Should you want to explore one of these options or see what may be best for your organization to get more out of your Raiser's Edge investment you can go to our website for Tucamino Solutions and contact us via our website.

Monday, July 15, 2013

Using BCS to report on Raiser's Edge information in SharePoint - Part I

Following up on our earlier post of Some Possibilities to Display Raiser's Edge data in SharePoint, I am going to turn my attention to the Business Connectivity Services (BCS) functionality.  BCS, formerly known as the Business Data Catalog, is intended to provide "out-of-the-box" integration capabilities with other data sources. This integration provides external data in a SharePoint list, with it looking and acting like a SharePoint list, even though the data continues to "live" in the external database.  Once this data is consumed in SharePoint, the end-user gets the benefit of SharePoint views, column sorting, filtering, plus much more.

The integration also means view, edit, add, and delete capabilities on these non-SharePoint datasources (think of SQL Server databases).  The Raiser's Edge runs as a SQL Server database, thus we can grab this information and leverage BCS to provide that information to our end-users.  This same concept can be applied to any other SQL Server database, but we will focus on The Raiser's Edge for this post.
Raiser's Edge Proposals shown in SharePoint via BCS

At first blush some may say, wow, that is pretty awesome.  Others, myself included, may become very wary of all of those capabilities.  For instance, I have found that most COTS solutions that leverage SQL Server have a highly normalized database with many relationships, or foreign keys, throughout the database tables.  So to pull information from the source database into a format that is consumable and makes sense to the end-user, views may need to be created that join these many tables to get at the actual values that are important (a text value instead of numeric ID for example).  When you think in these terms, the "View" capabilities are much more powerful than the Create, Update, and Delete (from CRUD).  I know there will be instances, probably in more homegrown databases designed to work with the SharePoint BCS, where all CRUD capabilities can be pretty powerful.

With all that being said, lets take the BCS for a spin to pull Raiser's Edge Proposal data into SharePoint so we can provide that information to our wider business community.  The first thing we need to do is to create a view in SQL Server to select the proposals from The Raiser's Edge. The Proposal table in the Raiser's Edge has lots of foreign keys (or references to other reference data or code table data) and alone is insufficient.  By creating a view in SQL Server we can join the Proposal table to these other tables (Tableentries, Campaigns, etc.) to display the textual values for these columns.


After setting up the view in SQL Server, we can open up SharePoint Designer to perform the next series of tasks.  External data is exposed via External Content Types in SharePoint.  We created a new external content type and named it REProposal.  After the initial creation (as seen below) we have a base external content type from which to expose this Raiser's Edge proposal information in SharePoint.  We still need to do some configuration of this external content type.



Now that we have the external content type defined, we need to configure the connection to the SQL Server Raiser's Edge database.  There are multiple connection types for the external content type, but I have chosen the SQL Server.  This connection type allows for the extraction of information directly from the SQL Server database.  After establishing the connection, we then have the Raiser's Edge tables and views, as seen at the right, available for selection.

Because I created the view that exposes the Raiser's Edge proposal information in the format that I want, I navigate to the view I created by expanding the Views folder.  When we find our view, we right-click on the view to see which operations we can then include in our external content type.  As I mentioned previously, we could have full CRUD capabilities but I believe this is not appropriate,  at least for what we are trying to accomplish today.  We need to select two operations: Read Item Operation and Read List Operation.  Read List provides the capabilities to pull the entire dataset and Read Item exposes an individual item in the list for view (such as an item dialog form to see the item's details).  These operations are seen at the left.  There are some further configurations that need to take place with the columns.

Now we have the external content type created, we must create a List with this external content type.  This option is available in the Ribbon of the external content type in SharePoint Designer.
Once the list is created, lets navigate to the list in SharePoint.  So I open Internet Explorer and navigate to the URL that the list should have been created at.  Uh-Oh.  Take a look at the right:  I must have forgot something, I am getting an "Access Denied" message when I was expecting the list of Raiser's Edge proposals. 

I forgot to set the permissions on the external content type in SharePoint Central Administration.  I forgot to navigate to the Application Management, BCS service application management section to set the permissions, as seen below on the external content type I created.


Now after setting the permissions, I am able to navigate to the list in SharePoint and I have my list of Raiser's Edge proposals.  I can now use this list as if it were a SharePoint list, even though the data itself resides in the Raiser's Edge SQL Server database.  I can create custom, filtered views, add a web part to a web page with a specific view of the RE Proposals, or do any number of things with the list of proposals, as if we were managing it in SharePoint.  This type of functionality can be created on many types of datasources, including other SQL Server databases.  Coming soon, another post that then takes this external list of data to show how it behaves like a normal SharePoint list.  Contact me at Tucamino Solutions if you need help leveraging this kind of functionality at your organization or want to find out how else we can help you.



Friday, June 21, 2013

Displaying a Raiser's Edge Gift List in a SharePoint page with an ASP.NET Web Part

In an earlier post I wrote about some of the possible ways Raiser's Edge data could be reported or displayed using SharePoint, allowing other people in your organization to access these reports, dashboards, and lists without having to log into The Raiser's Edge.  Today we are going to focus on one of those ways:  using the available ASP.NET web controls or web parts in SharePoint Designer.

In this post I am going to focus on creating a simple recent gift list displayed on a SharePoint web page.  As mentioned in the previous post, there are many ways to go about this.  For those developers that know how to use the ASP.NET web parts, a quick and easy way to accomplish this is to drop one of the available web parts onto a Web Part Page, connect the web part to the Raiser's Edge database, write the SQL statement, and format the web part - pretty simple!!
The first step is creating a new web part page.  We need a place that we want to display the gifts and the natural location is to create a Web Part Page in a library that is on the site.  You may want to create your own library for pages, or use the built-in Site Pages library.  It is really up to you.  In addition a Wiki Page may present problems, thus we recommend that you create a Web Part Page.  When creating the page we selected a layout that was one column as we are only going to have one web part on the page and we want as much real estate hortizontally as possible to add as many columns as we might want.

After creating the page, I open up the site with SharePoint Designer, check out the web part page, and open the page in design mode.  When in this mode you can add SharePoint web parts as well as other web parts.  As mentioned above, we are going to use an ASP.NET web part (some of these are pictured at the left).  We are going to choose the GridView for its tabular or grid format, simple rendering of the data in a list format, and easy styling the control to make it a bit more professional.
After placing the GridView web part on the page, we need to tell the web part how to connect to The Raiser's Edge database.  To do this we create a new data connection.  When setting up this connection, you will be presented a similar dialog form to many of the sql tools and utilities in the marketplace.  Because The Raiser's Edge is a SQL Server database, we select this option then enter the appropriate details to connect to the database.  I recommend that you have your administrator create a new SQL Login and provide it the appropriate privileges (read-only to protect the integrity of the data).  Note, this SQL Login is not the same as the "new user" created in The Raiser's Edge application, but a SQL Login created via the SQL Server Management Studio.   We entered the SQL Server instance where the RE database resides, the SQL Login user name and password, along with the Raiser's Edge database.  I recommend that you also click the "Test Connetion" button to ensure that the connection details you have entered work!

 
Now that we have the connection details defined, we need to write the SQL to pull our gifts.  As many of you may know, writing a SQL statement to pull information from The Raiser's Edge may not be a simple SQL.  I tried to display enough information to keep it interesting but not go too overboard.  I also chose to pull just cash gifts as well (see the WHERE G.TYPE IN (1,2,3,31) at the left).  We could write a function to be included in the SQL to show the gift type and allow all gift types to be displayed, but this shows how we can really get at specific information in our reports.

After writing the SQL we then have a Grid View that displays a rough outline of how the grid is going to look.  I chose an out of the box "Professional" style which applied the formatting to the headers, columns and rows.  You could really spend a lot of time customizing the look and feel of the GridView and the individual elements, but for our exercise I thought keeping it simple was the best approach.

Now that we have the SQL written, a connection to the database configured, and the web part styled, it is just a matter of checking in the web part page and opening the SharePoint site to see how it looks and making any edits we might need to.  Below is the outcome of our little project: a web part page that displays recent gifts sorted by the gift date, with the most recent at the top.  Someone that wants to see this information doesn't have to log into The Raiser's Edge or ask someone for the "report", they can just go to the web page to see the information.  Obviously you can extend this type of functionality to dashboards, other reports, adding filtering and search criteria to make it truly dynamic and powerful, even create a mini RE application (having one page that shows constituent information about a specific constituent, another for a campaign, etc. and connecting via links embedded on the pages - now that would be fun).  And the best of all is someone doesn't have to log into The Raiser's Edge to get this information; they can get it on-demand, and it looks professional.



If you would like to explore this type of functionality or run an idea by us, please go to our website to see what else we can do for you or send us a contact us request.  I would even love to hear what other ideas you would like for me to explore on this blog that you think could be powerful, especially if it pertains to The Raiser's Edge or SharePoint.   Also, I want to say thanks for checking out my blog.  If you have any feedback, don't hesitate to reach out to me via this blog or the website referenced above.

Thursday, June 20, 2013

Some Interesting Possibilities Displaying Raiser's Edge Reports and Dashboards in SharePoint

So you have invested in Microsoft SharePoint and you are trying to figure out how you can get the most bang for your buck.  Your organization also has purchased and implemented The Raiser's Edge.  You may be feeling that the reports in The Raiser's Edge are lacking, or that you don't have enough licenses to allow everyone in your organization to connect to The Raiser's Edge at the same time.  Perhaps you don't want to have to train all your end-users, especially those that only need reports anyway, on how to use The Raiser's Edge.

Well, you can leverage the SharePoint platform to display information from The Raiser's Edge via web pages.  It could be dashboards and other reports, lists of data, or even detailed information about your constituents, events, or other records in RE.  Imagine if you have users that need only read-only access: being able to have a web page in SharePoint where the user can enter filter or search criteria and view those constituents that meet the criteria along with details about those constituents, all without having to log into The Raiser's Edge.

Below are some of the ways that SharePoint can be leveraged to display this RE information, or any other information from any SQL database or other data sources:

ASP.NET Web Parts
Many ASP.NET web parts, such as the Grid View and Data List, available with SharePoint Designer, allow us to query and display data from different data sources in many types of formats, typically in a table or form layout. They can work with any data source that’s available in SharePoint Designer, like SQL Server, XML files, and more.  And with The Raiser's Edge being SQL-Server based, these web parts can be used to display this data. Another advantage is that it is easy to customize and considered "out-of-the-box"  and a "no-code" solution.  Check out this blog post where I use an ASP.NET to show a recent gift list from The Raiser's Edge.

Business Connectivity Services
The Business Connectivity Services allows SharePoint to connect to other systems, such as The Raiser's Edge, you already have, and work with them in various ways. A developer can configure the data connections to connect to The Raiser's Edge and import data into external content types, which can then be viewed in a SharePoint list.  What is great is this capability is native to SharePoint 2010 and 2013, however it can be a bit awkward at times, depending on what you might want to do.  In addition, it is meant to allow for data interaction (adding, editing, etc.) and not only for the consumption of data.  With The Raiser's Edge being a complex relational database model, it is advised that this feature not be utilized to update Raiser's Edge data, but to only view data.  Our first entry to show how BCS might be used is posted at the following link.

Excel Services
Excel Services provides the capability to display a named item from a Microsoft Excel workbook, such as a named range or chart, as a web part in a SharePoint web page.  This allows end users to build reports in Microsoft Excel and with developer assistance, display these for others to view as components in a web page.  A developer could even automate the creation of the Excel reports, the publication of the Excel workbook to SharePoint, and WHAM, all the reports are automagically updated.

SQL Server Reporting Services
SQL Server Reporting Services provides a full range of ready-to-use tools and services to help you create, deploy, and manage reports for your organization, as well as programming features that enable you to extend and customize your reporting functionality.  With Reporting Services, you can create interactive, tabular, graphical, or free-form reports from relational, multidimensional, or XML-based data sources. Reports can include rich data visualization, including charts, maps, and sparklines. You can publish reports, schedule report processing, or access reports on-demand. You can select from a variety of viewing formats, export reports to other applications such as Microsoft Excel, and subscribe to published reports. The reports that you create can be viewed over a Web-based connection or as part of a Microsoft Windows application or SharePoint site. You can also create data alerts on reports published to a SharePoint site and receive email messages when report data changes.

Visual Studio Custom Web Part
Visual Studio can be a powerful medium for creating components that can hook into SharePoint or The Raiser's Edge.  A custom web part that gives the developer more control over the visual presentation can be created with this development platform.  For example, a developer could create a visual web part that displays a chart based on some data from The Raiser's Edge.  This chart could be created by hooking into Google Charts to render the visual presentation of the chart.  I hope to create an example of this to show the power.  Parameters and Properties could be added to the web part to give further implemention options.  For examnple, one web part could allow the person deploying the web part on one page to display a pie chart for each Campaign, but on another the same web part could display the chart per Fund.  All in all, this is the most customizable option to creating that perfect set of web parts.

There are more options but these are a few of the highlights of possible ways to deploy Raiser's Edge reports and dashboards to SharePoint.  I hope to post an example for each of these in the coming months to give you detailed examples.  This type of reporting can be used for many other databases as well.  Contact us via email or go to our website to let us know how we can help you improve the way you work with The Raiser's Edge or Sharepoint.