Archive for the ‘Cross object reporting’ Category

Apsona Multi-Step Reporting: Learn the Basics

Tuesday, September 19th, 2017

This is the first in a series of posts about Multi-Step Reporting

Introduction

The sky’s the limit with Apsona’s multi-step reporting (MSR) tool for what kind of reports you can generate. Need a report that spans multiple objects, including sibling objects as well as parent/child? No problem, MSR can do it. What about applying a different filter to each object? Yes, got that covered. Can I output calculated fields? Check, and check. Are you excited yet? You should be! However–if you’ve never worked with MSR before, it can be intimidating to start. In the MSR interface you’ll see “steps”, filters, “linkages”, and more. How do you know which objects to pick, how to configure steps, and what to do with linkages? Description: confusion 2.jpg Don’t worry, we’re going to break it all down. In this first in a series of posts about MSR, you’ll learn:

  • a strategy for designing your report
  • what steps are, and how to configure them
  • how to link your steps, and what that means
  • how to filter results to get only the records you want

Let’s work through each of these points using a typical, fairly complex reporting scenario. Imagine your boss has asked for this: I want to see the top three opportunities for all of our accounts, along with the name and email of the contact in the role of business user for each of those opportunities.

Design Your Report

A little upfront planning will save you time (and frustration) in the long run. (As an aside, this is true regardless of the reporting tool you’re using.) Consider these questions:

  • What are you reporting on?
  • What do you want the report output to be?
  • Which records and how many do you want?

Let’s look at our report requirements again: I want to see the top three opportunities for all of our accounts, along with the name and email of the contact in the role of business user for each of those opportunities.

  1. What are you reporting on?This question gets at the objects you’ll need for your report.(Don’t worry about the fields yet–that comes in the next step.) In our example, it would be: Opportunities, Accounts, Contacts. At this point, it can be helpful to do a quick sketch of these objects so you can see how they’re related: Description: IMG_0009.JPG Even though we don’t need any data from the Opportunity Contact Role object, we include it in our sketch because it’s the link between the Opportunity, and the Contact for that Opportunity,
  2. What do you want the report output to be?What fields do you want to see from which objects? In what order? Another quick sketch comes in handy: Description: rpt_output_sketch.JPG
  3. And finally, which records and how many do you want? In other words, are there any restrictions on the records you want returned?

From our report example:

  • Accounts: all of them, no restrictions
  • Opportunities: the top three opportunities
  • Contacts: only business users for the opportunities

Fleshing Out Requirements You might wonder– what does “top three” mean? And can the opportunities be in any stage, or just the closed/won opportunities? And how do I identify business users? Great questions! This is when you’d go back to your boss and ask for clarification. This is also a good chance to review your sketch with her and make sure you’re on the same page in terms of the report output. Let’s say you had that meeting (where your boss was very impressed with your sketches and well-thought out questions.) Now you know that “top three” means the opportunities with the highest value in the amount field, regardless of stage. But speaking of stage, your boss realized she’d also like to see that information in the report output. Oh, and the opportunity amount. You also found out that “business user” means someone with the opportunity contact role of “business user.” Here’s our revised sketch: Description: revised_sketch.JPG

What Are Steps and How Do I Configure Them?

Ok, now that we know where we’re headed, let’s start building!

  • Go to the Multi-step Reports tab and click New:Description: msr_tab.jpg
  • Give your report a name and description, and select a folder to store it in. Then click the Add Step link on the left:Description: start_new_report.jpg The Step interface opens up: Description: new_step_blank.jpg

A “step” is the big cheese of Multi-step reports. Think of a step as a container that holds all the information about one of the objects you want in your report. The step holds information about:

  • which object you want to report on (Step Info tab)
  • which fields you want from that object (Retrieved fields tab)
  • how you want to limit the report results (Filter terms tab)
  • how many records, and what sort order (Range and sort tab)
  • how this step (or object) is related to another step in the report (Linkages tab)

Remember–one step relates to one object. Let’s revisit the sketch we made when designing our report: Description: schema_sketch_steps.jpg As we can see from the sketch, our report spans four objects. So we’ll plan to configure four steps. Let’s get started!

Configure the First Step

In general, you start configuring steps with the top-level object, which in our case, is the Account.

  • On the Step Info tab, add a Step Name, and choose Account as the Database Object to get data from.

Description: step_info.jpg

  • Next click on the Retrieved fields tab and choose Account Name from the Add Field picklist.Description: retrieve_acct_name.jpg
  • Add the Account ID field. (No, we don’t need this field in the report output, but we’ll need it for something else a little later. For now, take it on faith, and hang tight.)

Your Retrieved fields tab looks like this: Description: retrieved_fields_acct.jpg

  • Click the Filter terms tab.

Description: filter_terms_tab_blank.jpg This is where we can restrict which records are returned. Since we decided we want all Accounts with no restrictions, we’ll move on to the next tab.

  • Click the Range and sort tab. Description: range_sort_tab.jpgThis page allows you to choose one of three ways to limit the number of records returned:
    • Limit by number range (1)
    • Limit to one record based on the criteria you pick (2)
    • Limit to the top x number of records (3)

    Again, the report requirements didn’t specify how many Account records or in what order, so we’ll move on.

  • Finally, click the Linkages tab.Description: linkages_tab.jpg Linkages are about relating steps to each other, Since we only have one step configured so far, there’s nothing to link yet.
  • Click Save.Description: click_save.jpg

Congratulations! You’ve configured your first step!

Create the Opportunity Step

Now let’s quickly create a step to get our Opportunity data:

  • Click Add Step.
  • Give the step a name, such as Top 3 Opportunities, and choose Opportunities as the database object.
  • On the Retrieved fields tab:
  • Select the Opportunity Name, Amount, and Stage fields.
  • Also add the Account field, and the Opportunity ID field. (Yes, these are more fields we don’t need in the report output … the mystery will be revealed soon!)
  • Now click the Range and sort tab. Remember, we want only the top three Opportunities (determined by Amount.)
  • Select the bottom option: The first … records when ranked by …
  • Enter 3 for the number, Amount for the field, and descending for sort order:

Description: range_and_sort_top_3_opps.jpg

  • Click the Linkages tab.

This is where we connect our two steps–Accounts and Opportunities. Remember when we added those fields on the Retrieved fields tab that we didn’t need in the report output (Account ID in the first step, Account in this step)? Well … (drum roll please) … this is why: On the Retrieved fields tab of each step, include lookup or picklist fields that relate to objects on another step. These are used on the Linkages tab to create connections between steps. In Salesforce, Opportunities and Accounts have a relationship: the Opportunity’s Account lookup field holds the related Account’s Account ID. By adding the Account field in this Opportunity step, and the Account ID field in the previous Account step, we created a way to link the two steps. (We also included the Opportunity ID field in this step. This will come in handy when we link this step to the next one in the chain.)

  • Choose the first option: Accounts Account ID matches Account.

Description: link_step1_and_2.jpg NOTE: If you forgot (or didn’t realize) you needed to include these linking fields, you would see this message when you clicked on the Linkages tab: Description: no_available_linkages.jpg If that happens, no worries — you can always edit any step to add more fields on the Retrieved fields tab.

Create the Last Two Steps

Follow the procedures above to create the last two steps for Opportunity Contact Role, and for Contact. For the Opportunity Contact Role step:

  • In the Retrieved fields tab, add the Opportunity field (to relate to the Opportunity step), and Contact field (to relate to the Contact step.)
  • On the Filter terms tab, add a filter to select Contact Role records where the Role is Business User. The Filter terms tab will look like this:

Description: filter_terms.jpg

  • Remember to save each step.

Run the Report

Now it’s time to see how all of your hard work has paid off!

  • Click Save and Run to run your report: Description: report_results1.jpgLooks great! But we can make it look even better. Those ID fields are there because we needed them to link steps, but we don’t have to include them in the report output.
  • Simply click Set Columns, and uncheck any fields you don’t want to see: Description: set_columns.jpg

Our final report looks like this: Description: report_results2.jpg

Points to Remember

  • Design your report before you start building, to understand what objects are needed and how they’re related–and save yourself time in the long run.
  • Configure one step for each object. (There is a shortcut for this … stay tuned for details in further posts!)
  • Include lookup and ID fields in the Retrieved fields tab so you can link steps.

Do you have MSR questions? Have a tip or trick to share? Let us know!

Using Logic and Conditionals When Generating Documents

Sunday, July 30th, 2017

Sending a well crafted thank you letter to your donors is an essential part of your non-profit’s fundraising efforts. Acknowledging a donor in a timely manner increases giving and also improves donor retention. And when you need to send large numbers of such letters, you would want to automate the process so that members of your staff can send them off with a couple of clicks. This is when you need Apsona’s Document Generator to set up the process seamlessly.

A use case – Consider a scenario where you need to send your donors a thank you letter after a successful campaign. In this case, the content or the body of the letter is the same for all donors, but the signatures on the letters are determined by the donation amount. Donors who have donated $500 or more get a thank-you letter signed by the President of the organization. Those who have donated between $250 and $500 get letters signed by the Executive Director, and donors who have given under $250 get a letter signed by the Philanthropy Officer.

Setup – Setting up the merge with Apsona’s Document generator is a four-step process: Create a template, creating the data sources for the template, map your data, and generate documents. As the body of the letter is the same for all the donors, the template will only need to have the required merge fields or place-holders for the relevant data to be filled in. With Apsona’s support for conditional directives (i.e., if-then-else), you can selectively include or exclude content in your document based on data conditions. In the use case we are looking at, it is the different signatures on the letters determined by the donation amount.

template1

In the above template, we have just four merge fields as place-holders: First name (Donor name), Donation amount, date the payment was received and the Household name. The data for the merge fields are available in the Contact object, and the data for the merge fields in the body are available in the Opportunity object, and the two are linked via the Primary Contact lookup in the Opportunity object. So you can create a simple Opportunity report that extracts all the necessary data fields to generate this document.

The signature area contains conditional directives to drive the logic outlined above:

«IF Opportunity_Amount>=500»John Doe
President«ELSE IF Opportunity_Amount>=250»Jane Doe
Executive Director«ELSE IF Opportunity_Amount>=100»Jill Doe
Philanthropic Officer
«ELSE»John Doe
President «ENDIF»

In the above example, we use four directives, IF, ELSE IF, ELSE and ENDIF, to provide the logic. Each directive is created as a standard Word merge field. The code in the above example looks for the template field Opportunity_Amount. The text of the signature is interspersed with the directives so that the required logic is implemented.

It is important to ensure that each IF directive must be followed at some later point by a corresponding ENDIF directive so that the text segments that produce the signatures are clearly differentiated from the main body of the document. The document generator checks for this condition, and if a violation is detected, it might produce an error message like the one below.

IF

The example here shows the If – Then – Else conditionals in the signature are of the letter but you can get really creative and use it from the address and body of the letter as well. Detailed documentation on the If -then- Else conditionals is available on the Apsona website. Here is the link.

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.

Updating data without VLOOKUP with Apsona

Saturday, January 31st, 2015

Apsona for Salesforce has built-in tools that can highlight data quality issues, which you can find and replace when ever the need arises. Most businesses are aware that bad data quality can be a huge drain on their productivity and profitability. If you have an active Salesforce org you will always need to update data on a regular basis. Phone numbers, email addresses or mailing addresses of contacts and leads might need updating from time to time. Such mass updating cannot be done natively in Salesforce, but can be done with Apsona.

Here is a real use case. After running a mailing campaign, a user obtains a list of mail recipients who have unsubscribed. So she needs to remove the email address from the corresponding a set of contact records and replace the field value to null. She also wants to update the “Email Opt Out” checkbox for all the mail recipients. The recipient list is a CSV file with just the email addresses that have to be removed. Contact ID’s or even a First and Last name are not available.

With Apsona, you can match such a set of data records in a CSV format against your Salesforce data, and obtain the record ID for each record, if one is available. In this case, you use the email addresses CSV file as the data source and take advantage of Apsona’s import tool. You will be able to match the CSV data records against Salesforce data in steps 1 and 2 of the import/update process. In step 3, instead of clicking the Import button, you will need to click the ‘Download match results’ link. The resulting download will contain your data (the email addresses) with an additional first (leftmost) column containing the unique record IDs of the matched records. So we already not have the ID’s with just a few clicks. Half way there already.

Now for the replace part. In the downloaded file add another column called Email (Null values) and leave the cell values blank. Use the import tool again and choose the update action. Use the Contact ID as the look up field in step 1 and map the Email (null values) columns to Email in step 2. Complete the update in step 3 and you are done.

In the example, we saw how to find the unique ID’s and replace the field data values with a few clicks without having to go though the tedious VLOOKUP function in Excel. http://apsona.com/blog/updating-data-without-vlook-up-with-apsona/ http://apsona.com/blog/updating-data-without-vlook-up-with-apsona/ http://apsona.com/blog/updating-data-without-vlook-up-with-apsona/ http://apsona.com/blog/updating-data-without-vlook-up-with-apsona/ http://apsona.com/blog/updating-data-without-vlook-up-with-apsona/

Download Apsona for Salesforce from the AppExchange and try out your use cases. The app had a 30 day free trial.

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.

Updating Data With Inline Edit

Friday, February 8th, 2013

Apsona’s inline edit feature is gaining a lot of interest from large enterprises. A key reason is that  it  helps the sales rep to be more productive. A sales rep’s primary goal is, of course, to close deals. Whilst the sales rep spends a lot of time talking to customers, listening to their needs and tailoring the sale, he also needs to document and update all his interactions.  Apsona makes this data updating quick and easy.
If you have used Salesforce for any length of time, you know that if an object has record types associated with it, you cannot use inline edit on any of its views. So if you are a sales rep who needs to update a bunch of opportunities, you have to first find them in a view, click each of them in turn, edit and save them, and reopen the view. So each update needs four page refreshes, not counting the actual clicks for editing.
In Apsona, you can just select the Opportunities tabular view. The tabular view resembles an excel worksheet where all records are laid out in rows. Each field value is in a cell which he can click and edit. You can work on several fields in the same opportunity or in different opportunities, and when done, click save. You can invoke pre-built filters at any time to narrow down the list, and then make changes and save the records. You can also build cross object filters on the fly, search for certain data records and make changes without losing context. For example, when working with opportunities, you can select not only Opportunity fields but also fields from any other object related via a lookup or master-detail relationship in either direction (parent or child).
Large enterprises use Apsona tabular views as top level pages. Salesforce admins build Opportunities tabular views with just the fields the sales rep requires to see.  Filters are also pre-built for the reps. So now, the rep need only select a filter, make changes to the records and click save. Thus he ends up saving a lot of valuable time.
This is just one example of how inline editing is being used. This feature works across all Salesforce objects, native and custom, in Apsona for Salesforce. If you have not tried out Apsona, please get our free trial from the Salesforce AppExchange.


Cross Object Reporting For Salesforce Users

Wednesday, November 28th, 2012

We are very happy to see that our new offering on the AppExchange, Apsona Multi-Step Reporting (MSR), is attracting a good deal of interest. Users seem to enjoy the flexibility that the app offers. It imposes no restrictions on how to access objects, nor the number of objects you can access in a single report. It lets you traverse dependencies in either direction (parent-to-child or child-to-parent), and can handle multiple dependencies between the same pairs of objects. For example, you can create a report spanning the Campaign (parent) – Opportunity (child)-Opportunity Contact Role and back to up the chain to Contact (parent). Such structures are difficult to achieve with the native Salesforce reporting. Another plus with Apsona MSR is the ability to run reports without having to create report types.

To help you build a report in Apsona MSR, here are a few tips:

  • Reports are built in steps.
  • Each step retrieves data from one object.
  • Each step carries its own filter conditions, independent of the other steps, and these conditions can be cross-object.
  • Steps are linked to previous steps via lookup fields.
  • To see linkages, select the id field or the look up field you would like to link in each step.

You can build a powerful report incrementally in steps. You can have any number of steps in a single report. For example, let’s say you would like to invite to a Christmas luncheon all the contacts associated with the closed/won Opportunities or donations for the last 3 consecutive years, and you would like to retrieve the Contact records to set up the invitations. Here are the steps you can use to get the data with Apsona MSR.

    Step 1: Get the Contacts from the contact role object and filter by opportunities for the year 2010.
    Step 2:  Get the Contacts from the contact role object and filter by opportunities for the year 2011. Link the contacts to the Step 1.
    Step 3: Get the Contacts from the contact role object and filter by opportunities for the year 2012. Link the contacts to Step 1.
    Save and run.

    Another example use case is to find the person/user who created an opportunity and the person/user who closed the opportunity. Such a report can be obtained in just 2 steps with the Opportunity and Opportunity History objects. With Apsona’s cross object reporting, you can access the User object from the Opportunity and Opportunity History objects and pull in the users who have created and closed Opportunities. So here is an example of working from the Opportunity object down to the Opportunity History object and going up the chain to the User object. With an additional step you can also get the number of Opportunities (count) closed by a person/user by using the powerful metrics feature.

    Once the data for your report has been retrieved, you can visualize the data in charts, groups and matrices. Grouping can be single level or up to five levels. Pivoting or transposing data is a powerful visualization this app offers. Filtering, sorting, drilling-down and summarizing data can now be down in seconds.

    Such reports and pivot visualizations are not possible to build in native Salesforce and yet this information is invaluable to any organization. These are just a couple of examples of the kinds of reports you can build with Apsona MSR. You can now create reports to extract and display data instantly from your Salesforce org, without depending on others for coding or IT involvement.

    We offer a free trial of this app on the AppExchange and will be happy to help you if needed. We can be contacted at support@apsona.com.