Archive for the ‘Technology’ Category

Fundraising Reporting with Apsona Multi-step Reporting

Friday, February 27th, 2015

Constituent reporting on individual donors and their giving histories is invaluable information for a non-profit organization. One such report requirement was posted on the Power Of Us Hub where the user wanted to find the increase or decrease in donation amounts by their donors. Here is the question:

  • My fundraising team wants to know how many donors increased their donation size from the last donation to the current one. I told them I could do Total Gifts This Year vs Total Gifts Last Year, but not Current Gift vs Previous Gift. Can you all think of a way to measure an increase (or decrease) in donation amount from one to the next?

Reading the responses it became obvious that this report is not possible to create with the native Salesforce reporting. However, with Apsona Multi-step Reporting, this report can be built in a few minutes. This report will require two query steps and one calculated step. Let’s take a look at how to build out such a report.

For the query steps, you first get the donors and filter them by campaign name or opportunity close date depending on the required criteria. Next, retrieve the opportunities to the donors, filter by close date and Role. Multi-step has the ability to get ranked data, which will need to be applied when querying for the opportunities. Limiting the 2 donations in descending order will give you the last two donation amounts to each donor. The steps will be woven together with the contact ID.

As there is no direct link between the Contacts (donors) and Opportunities (Donations), data for the two query steps will come from the Opportunity Contact Role (OCR) object. This object is best suited for this task, since it is treated as a stand-alone object in Apsona, and is also the junction between Donors and Donations. Therefore, you retrieve fields from the Donor (Contacts) as well as Donation objects (Opportunities) as they are both have lookup relationships from the OCR. Thus, you can retrieve the contact ID in both the steps and weave them together. Unlike in Salesforce reporting, you can use the same object more than once in Apsona Multi-step with different filter criteria and is ideal for this use case.

Now that you have the donors and their last two donations, you will need to find the difference between the amounts. For this you use a calculated step and add a formula as follows – {!Last 2 Donations 1.Amount} – {!Last 2 Donations 2.Amount}. Once you save and run the report will see the donor information, the last 2 donations amounts to the donor and the difference between the donation amounts.

When you run the report, you will also want to see the two donation amounts to each donor in a single row to make the comparison.

In a multi-step report, you have the option to display the output of a step as a sequence of blocks of columns instead of the usual row layout. Below is an image of the final report.

2amts

Donor Adam Dunn has donated $250,000 and $ 300, 000. You also see the difference in the Amount column as $50,000. The reason the amount is showing in parentheses is because the formula in the calculated steps is set to subtract Donation 1 from donation 2.

This shows how you can combine two important operations – transposing rows to columns, and using calculated values – to produce the result you want.

We hope you found this blog useful and try out Apsona Multi-step Reporting.

Searching across a chain of objects in Salesforce

Saturday, December 28th, 2013

Salesforce users often need to search for data records in one object that depend on conditions in a related object. Frequently, the dependency carries over to multiple related objects, or to chains of such relationships. For example, suppose you need to find all the Campaigns that have targeted the contacts from your Partner accounts. To retrieve those Campaign records, your primary search condition is imposed on the Account object (looking for Partner accounts). Having found those accounts, you must find the Contacts in those accounts, and then the Campaign Member records of those Contacts, and finally the Campaigns to which those member records refer. Thus we have a four-object chain of dependencies that must be traversed to produce the results you need.

Such a query is not very easily constructed in native Salesforce. But Apsona for Salesforce provides the tools you need to solve problems like this. The way Apsona solves the problem is by repeatedly applying the idea of a filter, to produce what is called a nested filter, as follows. A filter is simply a name given to a specific search condition, associated with a specific object. For example, you can create a filter on the Account object, that asks for Account Type matching Partner, and name it Partner Accounts. You can then apply this filter in the Contact object, since the Contact is related to (actually, a child of) the Account object. When doing so, you would retrieve the Contacts of your Partner accounts, and you would use the Partner Accounts filter as a nested filter. You have thus carried a search condition on the Account object over to the related Contact object. You can then repeat this step over the entire chain of objects, thus producing the result you want. See the diagram below, showing the relationships among these objects.

Here is the series of steps to produce your results:

  1. Start with the “primary” object that drives the filter condition. In this case, it is the Account object, since we start with the condition derived from partner accounts. Create a filter on the Account object, identifying your Partner Accounts. You can do this either in the console view (list-and-detail) or in the tabular views in Apsona. Save it with the name “Partner Accounts.”
  2. Create a filter on the Contact object. In doing so, if you open the Account panel of the filter builder, and select the record id field of the Account object, you will see the “in filter” option available. Select that option, and then select the “Partner Accounts” filter. Save this filter with the name “Contacts from Partner Accounts.”
  3. Create a filter on the Campaign Member object, using the Contact filter created in step 2, in the same way.
  4. Finally, create a filter on the Campaign object using the Campaign member filter from step 3.

What we did here was make a filter on the primary object, namely Account, and applied the filter to the related object namely Contact. You can repeat this process as many times as you want, thus carrying filter conditions across a chain of relationships. We use the term nested filter to refer to a filter that uses another filter within it, in this manner.

Searches, filters and reports in Apsona work across the board whether it is retrieve data for campaign management, sales management or case management. Apsona for Salesforce can be downloaded from our AppExchange listing for a 30 day free trial, and works with all editions of Salesforce.

Thank-you Letters with Apsona’s Document Merge

Friday, August 30th, 2013

Thank-you appreciation letters by snail mail are the lifeblood of  nonprofit organizations. The donor needs to be convinced that he/she has made a  right investment and the donation will be used wisely. Appreciation letters need to be sent not only after every donation, but also once or twice a year. Personalizing the letters is key to a positive experience, but when this has to be repeated to hundreds of users, it is a major challenge to small/medium non profit organizations. The cost and effort of producing such personalized letters several times a year can be rather daunting.

Apsona’s Document Merge add-on for Apsona for Salesforce is exactly what is needed in these cases.  Users can build flexible templates that can contain multiple fields drawn from multiple objects – Contact, Donations and so on. Aggregate fields and sub lists can also be included in the template.  Fields names in the template and in the tables can be adhoc and need not be exact matches to the field names in Salesforce or in the reports. What Apsona brings is the utter simplicity and flexibility of the entire process for the template builder. Apsona’s  DocMerge will produce  a single or batch of .docx document files from a .docx template and the results can be downloaded either as a.zip file  or as a single .docx file with many pages, one for each record and print them.

Here is the simple step by step process.

  1. A thank you letter will typically have top level merge fields from the Contacts object like the name and address. When creating this letter template in Microsoft Word, create merge fields with the usual Quick parts – Field – merge field. You can also Apsona’s macro to build quick merge fields, and that is definitely a time saver and less tedious (our humble opinion).
  2. Next, if you need to include a list of donations to the contact, you build tables. Tables are very easily created. The leftmost cell in the table should include the TableStart field, which specifies a record group name following a colon. For example, for the tag TableStart:Donation, the suffix Donation will be the name of the record group for the table. This name is just a mnemonic indicating the kind of list being produced. You can make up any name you want, as long as it does not conflict with any other merge field name in the document. The rightmost cell in the table should similarly include the TableEnd field. These two markers indicate the region of the document that must be replicated, once for each Donation.

  3. The data source for a template can be obtained from pre-built Apsona reports, or directly from an object. For example, in a single template data for a list of donations can be obtained from a Donation report and data for a list of  in-kind donations can be got directly from the Assets object. Before running a pre-built merge template, you can also apply additional filter terms on the fly if required.

That’s it — system administrators can set up templates and its data sources, and all that the end user has to do is run an existing merge action whenever the need arises.

Apsona’s merge tool also supports Excel merge and email merge. For a more details on these features please visit our product description page.

Filters in Apsona – Part 1

Wednesday, July 31st, 2013

Searching or querying of data is a very common need for Salesforce users. The required data that drives the filtering frequently does not reside in just one object, but rather in several related objects. For example, you might want to find Contact records whose Accounts are in a particular city, so that even though the data sought is Contact data, the filtering condition is on the related Account. And once you have figured out all the filtering conditions you want to apply, and retrieved the data you want, you would want to save the search, so that you can reuse it later.  Apsona for Salesforce offers powerful search and filter capabilities for all Salesforce objects, both native and custom, out of the box. All searches can be “cross-object” in the sense that you can look for records of one type based on conditions enforced by records of a related type.

A query/filter can be built in two ways from the Apsona user interface.

The first one is by clicking Search and More options, and the second is by clicking the filter dropdown and selecting New filter as seen in the screen shot above.

The same filter editor opens up with both options, and here is where you will specify the search terms. When you click the drop-down to specify search terms, you will see a list of panels, one for each object. Each panel is labeled with the object name, and contains the fields for that object. The object at the top of the list is the one from which you are running the filter, with all its fields. Below that panel will appear all its related objects (and all their fields) which are one step away – basically the children and parents of that object. Note that one panel appears not just for each related object, but rather for each relationship. For instance, in the screen shot above, the Account object contains two lookup fields, Master Record and Parent Account, both referring to the Account object, so you see a panel corresponding to each.

If the current object is the parent in the relationship, you will see an asterisk next to the name. In the above screen shot, the Contact or Lead panels both have asterisks shown, since the Account object is a parent of each of those objects. But if the current object is a child in the relationship (as in the case of the Master Record and Parent  Account relationships), no asterisk is shown.

When running a filter in a one to many relationship, you can run a Quantified search. In this search, you look for records of one type such that all related records of a related type meet a certain condition, e.g., contact records for which all related tasks have a “completed” status. This search is called quantified because it uses the quantifiers all or none for related records. Let’s take an example where we want to find Accounts whose contacts have the salutation of Dr. (Doctor). We run a search from the Accounts object. For the search terms, we select the field “Salutation” from the Contacts table. Since the field is a picklist we get the option of choosing is among as one of the field operators. For the quantifier, let us choose the option for all records. Click search. We now get all the Accounts whose contacts have the salutation Dr. Notice that the returned list might include Accounts which have NO contacts in them. This is because an Account with no contacts satisfies – albeit vacuously – the requirement that all its contacts have a salutation of Dr.  Since we selected the quantifier as “all records”, Accounts with no contacts are also entitled to qualify.

This is just one option of the 3 options available in the quantified search. The” no records” and the “atleast for one record” are equally powerful and will be touched upon in the coming blogs.

You can try out these searches and more by downloading Apsona for Salesforce from the AppExchange.

Data analysis with Apsona reporting

Thursday, March 28th, 2013

At Apsona, we interact with awesome people from non-profit organizations (NPO) who work for some very noble causes. There are over 800 non-profit organizations (NPO) using the Apsona apps. Like for any organization, the most important thing for a NPO is data – the quality and quantity of their donor database. Quality means the donor’s contact information, addresses, email and phone. Direct mail is also widely used for soliciting and for acknowledging donor gifts and contributions. Apsona for Salesforce helps NPOs to maintain the quality by checking for duplicates while importing data, mass-updating filtered data, mass-adding members to a campaign, and so on.

The quantity aspect in a non-profit organization’s database refers to running reports and analyzing data. The Apsona apps help the non-profit users run reports and get information and pattern trends of donors – for example, who are the top donors, how often do they donate, and how much do they donate. Such information is invaluable, especially when a fundraiser is being organised. Let us see how we can build a common use case report with Apsona Multi-step Reporting.

Use case – To generate a report for a fundraiser to analyze giving patterns of donors. Data to be retrieved are high/top donors in the last 3 years. The donors who qualify should have given at least $1000 since the year 2010. The final report should be visualized with the donors to show up in the rows and the year in columns. This way we will be able to see each donors by year- if they donate every year, just for one year, etc.

Step 1:
  • Step info – Select the Donation Contact Role object.
  • Fields – Contact lookup field from the Donation Contact Role object, Donation Name, Donation Amount and close date from the Donation object, contact Full name from the Contact object.
  • Filter terms – Opportunity Amount at least 1000 and Opportunity close date on on after 1/1/2010.
  • Range & Sort – All matching records. Specify a number if you have one for the number of records to be retrieved.
  • Linkages – No linkages in step 1.

Click Save.

Step 2:
  • Step info – Select the Contacts object.
  • Fields – Contact ID and Contact Full name field.
  • Filter terms – None
  • Range & Sort – All matching records.
  • Linkages – Choose Contact ID matches Donations:Contact

Click Save and Run.

Let us visualize the data by clicking on the Visualization tab and selecting visualize.

  • Select the Matrix(Pivoting) option.
  • Rows grouped by Contact Name.
  • Columns grouped by – Date field – Donation close date in groups by year.
  • In each cell select Donation Amount: Sum.
Click Ok. You will now see each donor by year.

Apsona Apps – simple, reliable, effective.

Apsona Multi-step Reporting

Tuesday, August 21st, 2012

Apsona.com is happy to announce their new app Apsona Multi-step Reporting on the Salesforce AppExchange. This is a powerful browser based tool for reporting across any number of Salesforce objects – native and custom. The only requirement is that you need to have installed Apsona For Salesforce before using this app. The key benefits include:
– Cross object reports on any number of objects, native or custom, no matter how the objects are related
– Fully browser-based, no plugins or add-ons needed
– Works within your salesforce org
– Simple, intuitive user interface
– Your data stays in your org: it is not copied to an external store, unlike many other reporting tools
– Reports reflect the most current state of your data, not a snapshot stored in some external store
– No need to create or manage report types in Salesforce
– You can export the report results instantly into an Excel spreadsheet or in CSV format
– No Apex, VisualForce or other coding required

Apsona Multi-step Reporting is a good fit for routine data management needs, where you need a quick report that you wish to export to a spreadsheet program (such as Excel) where you can perform further analysis, manipulation and import or update back into Salesforce.  It is built using the foundation layer provided by Apsona for Salesforce. A 30 day free trial is offered on the AppExchange.

To try Multi-step Reporting, you will need Apsona for Salesforce if you do not already have it. Below are the links to our listings.

AppExchange listing for Apsona for Salesforce
AppExchange listing for Apsona Multi-step Reporting

    We welcome all feedback.

    Analytics in Salesforce’s Spring ’12 release

    Sunday, January 8th, 2012

    It is interesting to read users’ impressions about Salesforce’s upcoming Spring ’12 release. Users will now at last be able to create exception reports with outer joins, report on multiple children and other analytics features, satisfying a very common request on IdeaExchange. However, the word is that all these features will come with a price tag.

    The new analytics edition seems to include high-end features similar to those found in the more expensive BI tools such as BIRST or GoodData. These are a good fit for enterprises and large organizations which can afford the higher cost. But if you are a smaller company or a non-profit, or if your needs are more toward quick-and-easy reporting rather than full-fledged analytics, you might find these offerings less of a fit for you.

    In such a situation, Apsona for Salesforce might be a better fit. With Apsona for Salesforce, you can quickly create, edit and run reports with outer joins spanning multiple child objects, and export the data for further analysis. Apsona’s reports also include charting and graphing facilities for simple visualizations.

    We are constantly improving our product to fit your needs. Partners love Apsona and refer to it as a Swiss Army knife as it is so diverse in its offering – importing, exporting, updating, exception filtering and reporting. It supports all editions of Salesforce and comes in a snappy web 2.0 interface. We at Apsona routinely incorporate new features into that are suggested by you – our partners and end users. You are obviously the domain experts, and the enhancements benefit all of us. Many of the newer features that you see in Apsona for Salesforce today owe their existence to inputs from all of you. Should you need some Apsona customizations that is very specific to you we get involved in the development for a fee. We are able to create customizations that will appear in just the one specific installation.
    When someone wants Apsona for Salesforce in their non-profit org, we simply ask for a copy of their 501(c)3 letter (or their country’s equivalent that certifies that they are non-profit) to be e-mailed to us. Once we get the letter, we enable the 10 free licenses. All these licenses get the same functionality as our paid users do.

    In 2012 we hope to bring out many more features to suit your needs. Your feedback is very important to us so please keep it coming. The Apsona team wishes all its users A Happy New Year!

    Finding neglected contacts in your salesforce database

    Thursday, October 13th, 2011

    Keywords: salesforce.com, search, find, filter, contact filtering, lead filtering, exception search

    Would you like to know the people in your salesforce.com database who haven’t been contacted in a while? Particularly the ones who have given you repeat business? Sales managers know that it’s a lot easier to keep existing customers than to acquire new ones, so it’s critical to maintain and nurture customer relationships. One way to ensure this is to periodically find such “neglected” contacts, make sure to call or reach out to them and keep them aware of your product or service.

    From the technical perspective, finding such contact records amounts to retrieving those records for which there are no related tasks or events in the recent past, where “recent” might mean “the last two months” or “the last year” depending on your business. Unfortunately, with the search tools available within salesforce.com, it is not very easy to find such contact records. One of our clients asked us precisely this question: Can we use Apsona for Salesforce to find these contact records?

    Fortunately, the answer is yes, we can. Apsona includes the ability to filter your data records in many ways. In particular, you can create a filter (i.e., a set of search criteria) on an object, and then apply that filter to restrict the data on a related object. In this example, we can:

    Finding neglected contacts (3 min 50 sec)

    • create a filter on the Task object, one that retrieves recent tasks that have been completed; and
    • apply the negation of that filter on the Contact object, i.e., asking for Contacts that have no such related tasks.

    View the video on the right to get a more detailed perspective on how this is done.

    With Apsona, you can use this technique with any object that has associated tasks or events. For instance, you might want to find Opportunities, Leads or Accounts that have had no recent activities. You can also use this technique to find Contacts or Leads that have not been part of any of your campaigns, or those that have been excluded from specific campaigns that you choose.

    We think Apsona’s search and filtering is one if its most powerful features. And we’d love to get your feedback on it. Please try it out—visit our AppExchange listing, install it and use our free 30-day trial. Please let us know what you think.

    Easier searching and filtering of salesforce.com data

    Monday, October 3rd, 2011

    Keywords: salesforce.com, search, find, filter, cross-object search, exception search

    The feature set that salesforce.com provides is both rich and deep. It lets you maintain all kinds of data in your database, ranging from contacts and accounts to customers, invoices, projects, and any other custom data objects your business requires. It also lets you customize your application in  a myriad different directions.


    Searching: a short intro (2 m 30 s)

    Apsona for Salesforce adds a layer to salesforce.com to make it easy to search your data in multiple ways, and very quickly find the data records you want. It includes many searching and filtering functions that make salesforce.com much easier to use, but are not available within the native salesforce.com interface (view the video at right for a quick overview):

    • Cross-object search: You can filter the data records for one object based on records in a related object. For example, when searching for Account records, you can use fields from related objects such as Opportunities, Contacts, and Assets to refine your search results. In fact, you can combine fields from multiple related objects in the same search – for example, you can find Accounts that have both Contacts and Opportunities but no Assets.

    • Exception filtering
      (3 m 20 s)
      Exception search: As a special case of cross-object search, you can find records in one object that have no records in a related object. For example, you might want to find Accounts that have associated Contacts, or Leads that have no related Tasks – both examples of actionable information. This feature is one of the most widely-requested features on salesforce.com.
    • Sum and Count filtering: This feature enables you to filter by “aggregate” functions on related objects. For example, you can use this feature to find those Account records whose total Opportunity value is at least $100,000. Such a search is not currently possible within the native salesforce.com UI.
    • Respecting the field’s data type: When you choose a field on which to filter, Apsona for Salesforce automatically adjusts the allowed operations and values for the field. For example, if you choose a picklist field, Apsona for Salesforce shows the range of values allowed for the picklist, and lets you choose one. In contrast, within the salesforce.com UI, you have to remember and manually type in the picklist value you want too match. Similarly, if you choose a date field, Apsona for Salesforce shows you a menu containing filtering choices such as “today”, “tomorrow” and “yesterday”, as well as a date picker, making it much easier to specify dates.
    • Quantified searches: Suppose, for example, that you want to find those Account records which have related Contact records, but for which none of the related contacts have e-mail addresses. This is a fairly common use case – it arises, for example, when you want to quickly locate the Account records that have not been reached in any of your e-mail campaigns. This is an example of a quantified search, in which you impose a “none” or “all” quantifier on the data in the related (Contact) record. Apsona for Salesforce enables you to perform such a search, but the native salesforce.com does not.
    • Saved searches: Having performed a search, you can save its terms as a named filter, e.g., Accounts with total Opportunity value > 200k. Such a saved filter is available in all the views of the object: tabular view, console view and calendar views. You can even re-use saved filters in “nested” filters, so that you can carry searches across objects that are not directly related. More details are available here.
    • Searching in-place: The search terms are shown in a panel that remains in place when the search results are displayed below. This feature, combined with the rapid Ajax UI, lets you quickly revise your search terms to hone in on the data you want, without losing context. By contrast, if you wanted to make a change to a search term in your salesforce.com view, you’d have to click “Edit view” to navigate to a different page, edit the view, and redraw the screen, requiring four clicks and three page refreshes.

    Since Apsona for Salesforce is an AppExchange application, all of these features are available directly within your browser, viewing and filtering your salesforce.com data directly. And these features are available on all objects and fields, both native and custom.

    Technology-wise, Apsona for Salesforce runs as a JavaScript application within your browser. It dynamically generates the SOQL query for each search request, sends it over to salesforce.com, and displays the returned results in your browser.

    You can sign up for a free 30-day trial via our AppExchange listing. Our documentation area includes a lot more detail about the search capability.

    Your feedback is very welcome.

    Apsona’s Tabular View For Salesforce.com

    Tuesday, September 20th, 2011

    Apsona for Salesforce comes in a snappy Web 2.0 interface and delivers a user experience that is one of a kind. Users can see their data in several different views, including a tabular view, a list-and-detail view with child tabs, and calendar views.

    The tabular view offers many possibilities. To see data for an object in a tabular view, select the tabular view option from the object’s menu drop down. The tabular view comes up instantly. With this view, each user can choose his or her individual preferred field setting. Click the “Set columns” button at the top right of the table and drag and drop fields from the selected box to the available box. Click apply and your preferred view is now in place. You can re-size columns widths, sort on fields and edit field values inline. All updates happen instantly with no page refreshes. Similarly, you can update values for up to 5 fields all in a single click in this view. Other possibilities include quick searches, invoking saved filters and even exporting data to a csv file. A whole new record can also be added manually with the Add button while working in the tabular view.

    The tabular view which has an excel feel to it also had the ability to have a nested grid. For example, this view can be modified such that you can see all your product line items to an opportunity. See image below.


    Apsona for Salesforce being a pure Web-2.0 Ajax application, most of the screen updates happen in-place, with no jarring screen refreshes. This lets you retain your work context and you can focus on being more productive instead of waiting for the screen to refresh.

    One of our users summed up the tabular as follows: “Having a view of all relevant data in one screen is important to many users who work with Excel frequently, and the tabular view is an alternative view of Salesforce data to those who enjoy working with Excel. ”

    Apsona for saleforce has a lot to offer. Check out our exception reports and filters as well Get a free sign from the appexchange http://appexchange.salesforce.com/listingDetail?listingId=a0N30000003JejzEAC