HandsOn Connect DocumentationTraining for All Users (staff and administrators)Reports and Dashboards Advanced Report Examples and TechniquesDirect access to a useful report from an object layout. Example: Questions / Answers report from the Occurrence Record Layout

Direct access to a useful report from an object layout. Example: Questions / Answers report from the Occurrence Record Layout

Here's a customer report type and a great way to make reports more accessible when they are needed - that stemmed from some problem-solving between a HandsOn Connect client and our support team. Its presented here as an example of a 'power-use' of reporting!

Melia Tichenor from HandsOn Portland had opened a support ticket asking how she could get contents from one object type to appear in another object type. She was trying to somehow get the custom questions (that are related to the Volunteer Opportunity object), to display the Answers to those questions (which are associated with the Connections records that are connected to each occurrence).

While it IS possible to use formula fields to get items from one object into another - that didn't seem the right way to go about this.

Duplicating fields from one record to the next isn't a best practice in a relational database.

After mulling her goals -- we worked together on finding a more elegant approach. The solution we came up with combines three interesting functionalities and creates a dandy little custom feature that anyone can create!

Cool Beans! Here's how this was done!

Part 1: Creating a custom report type

The best way to combine information from multiple objects is through a report. However since the report type we needed didn't already exist, we created a new report type that combined Occurrences, Connections and Answers. We needed occurrences (so we could specify the occurrence(s) we wanted to draw from), Connections (so we knew who gave the answers, and Answers (where the answers to the questions submitted by the volunteers during signup are stored).

Click on Setup / Create / Report types to create a new report type.

Defining the Custom Report Type

The first step is to set the object relationships. We made a report where the primary object was Occurrences and we only wanted to look at occurrences where there was at least one Connection and that the connection had an Answer associated with it. That way we wouldn't see any results in the report for occurrences that had no connections, or ones that were without custom questions and answers.

See this post for more detail on creating a new report type.

NOTE: When you are ready to make this report available to other profiles - remember to edit the custom report type and make it's Deployment Status "Deployed" rather than "In Development"

You then choose what fields you want to be available in the report

We certainly didn't need all the fields from each of the records, but its easier to just leave them in. We made sure the fields we needed for our report were present.

But oh oh --- the Answers Object has a field called Questions - but it only delivers the Question ID - not the text of the question itself!?!

Unfortunately, the text of the question doesn't exist in the Answers object, only the ID. We want to see the question itself - and the text isn't on the Answers object. (This was why melia originally asked if there was a way to add that text as a custom field in the Answers object. There is of course -- but its not necessary!

As recently pointed out by Chris Pokrana during a community lab session, you can Add fields to a report via lookup!

To do this just pick which object in the drop down list, and click on "Add fields related via lookup"

We are adding a lookup from the Answers Record, and we want it to look up data from the Question object - so we select question as our path:

We are adding a lookup from the Answers Record, and we want it to look up data from the Question object - so we select question as our path:

This pops up a window that shows you the related fields from the Question object that we can lookup from the relationship to answers.

In this case -- we wanted to lookup the QUESTION field, which is related to the Answers object because of the Question ID. So we check that and click OK and the question text is now available info on our report!

This field will have what we need: The question text

This field will have what we need: The question text

The hourglass icon tells us this field is being accessed via a lookup.

Part 2: We're now ready to create our report based on this new report type

There are lots of ways you could create a report that uses these fields. In the illustration above I made a summary formatted report with a few fields.

It's summarized Based on the Volunteer Opportunity Name, Then the Occurrence start date, and then on each question.

Then for each volunteer we see their Answer.

Setting the Report Filters and Saving the Report

Setting the Report Filters and Saving the Report

My original report was designed to list all the occurrences taking place in the next 30 days that had connections and answers. I also filtered out connections where the status was equal to declined. We don't care about the answers for volunteers who are no longer attending or have removed themselves.

And this is now a handy report to have available in your report library.

Since we want to it to be available to our volunteer leaders and partner staff, we save the report in a folder that we have granted customer portal users access to.

We'll come back to these filters in a bit -- when we talk about integrating this report with the occurrence record!

Part 3: Adding a custom button to the occurrence record to provide easy access to the report.

What we needed now was an easy way to give access to the report from the occurrence record! This involved creating a custom button to place on the occurrence page that would run the report and pop it up in a separate window. To create the button we go to Setup / Create / Objects and went to the occurrence object since that's where we want to add the button.

Scroll down the page to the Custom Buttons and Links section.

You'll see the HandsOn Connect button here that pops up the "Print Check-In Sheet". We are going to create a button that pops up our new report! So we click on NEW

1. We give our button a Label so people know what it's going to.

2. We choose the appropriate type of button to create. We chose a button rather than a link. (but we could have gone with a custom link instead of a button if we preferred).

3. We chose to have the report show up in a new window. (There are other behaviors available for different kinds of windows, or just going to a new location in the same window)

4. We chose as our Content Source URL. That means we just provide a link to the report itself since it has a URL. (You can also create buttons that execute Javascript or Visualforce)

5. In this box we simply put in the URL for the report when it runs. We made it a relative link.

To get the URL you need - just RUN the report and copy the relative link portion of it.

To get the URL you need - just RUN the report and copy the relative link portion of it.

When we save the button we're reminded it must be placed in the page layout.

You'll need to put the button on each page layout that you want users to run the report.

Please see this post for detailed instructions on modifying page layouts. Click on buttons, and then drag the button to the top of the occurrence record.

Your layout will now look like this.

Clicking on this button will cause the report to pop up right from the occurrence record!

Part 4: But Wait! Here's the Cool Part!

Our report showed all occurrences that were coming up in the next 30 days. It would only show users the occurrences and answers that were appropriate data for them to see, but it still would have multiple occurrences in one report. What Melia wanted was a way to have the button produce ONLY the questions and answers for the one occurrence record you were viewing.

But how could you pass a merge field into a URL link and make this happen?!?

Melia searched the forums at success.salesforce.com and found info that gave her the answer!

She modified the filters in her report and added, as the first filter Occurrence ID equals""

This version of the report will return no results at all since it only looks at Occurrences with no ID number !?!

Not a useful version of the report for most purposes, but watch what she was able to do with it.

She saved this new version of the report in a folder for reports that were accessible to customer portal users.

You then modify the URL for the button to include a call to merge a value into the report record

Adding ?pv0={!HOC__Occurrence__c.ID} does the following:

1) The first part of the URL specifies the report record to go to

2) the ?pv0= points to the first filter in the report (which was Occurrence ID equals ""

3) the merge field {!HOC__Occurrence__c.ID} says to take the ID number of the occurrence for the page the button is placed on, and merge it into the first filter value.

Bingo! The new button now pops open the report - with only the answers to the questions for that one Occurrence Record!

Here's Melia's version of the report - with a bit more detail that is useful for someone wanting a summary of questions and answers for an individual occurrence.

Notice that when the report is run from the button the formerly blank Occurrence ID filter is populated with the ID# of the occurrence record the button was located on. So we get the report with only the questions and answers for the one occurrence.

This report is now available at a click of a button in the occurrence record and can be exported to excel or .csv if desired.

Note: If you make this report accessible to partners and volunteer leaders, make sure you update their profile to grant them read access to the fields Questions and Answers, and that they have permission to access reports.

Thanks to Melia for digging down deeper and taking the original answers I came up with for creating the report and button to new levels!

This kind of "How can you add new functionality" thinking shows exactly how much power there is in HandsOn Connect and the extent to which a system administrator can figure out a way to do practically anything!

We expect many of our users will find ingenious solutions like this in the future and look forward to your sharing great custom features like this that you can build yourself!

Don't forget our community lab sessions each month are a great place to share your custom creations!

How it might look on your site as a custom button and report


Add your comment

E-Mail me when someone replies to this comment