Dynamic Reports: Fields and Records

OK, here’s where we really get into the details.

FIELDS

The Fields in your Dynamic Reports (DR) object are basically three different types:

• Variables that will be passed to reports,
• Variables that are used to filter List Views,
• Hyperlinks to reports.

Variable Fields
1.  Identify the variables that you will want to dynamically pass to your reports.  These can either be records that exist within Salesforce, like users, products or accounts, or they could be pick list values like Region, Product Category, time frame etc.  They can be lookup fields, text fields, numbers or dates.

You can also use formula fields as variable fields in your Dynamic Report object.  For example, to create my List Views for East and West, I created a ‘Region’ field on the DR object, which is a Lookup field to the Region field from the Sales Rep’s SFDC User record.

The fields can be from on multiple objects in your Salesforce instance – Accounts, Opportunities, Cases, Users, Leads, or any other custom of standard object.  And some fields may be used in some list views, but not in others.

If you really want to get creative, you could create Variable Fields that are text boxes or Picklists in Dynamic Reports.  For example, you could create a text field called “Account Name” to let your users enter a partial account name to match.  Or you could create a picklist field that holds the different variables for that field you may want to pass, like a Product Category, or a date Parameter like “This Month”, “Next Month”, “This Quarter” or “Next Quarter.”  If you want to take this approach, you will need to set your permissions to Allow Editable List Views, and allow your users’ profiles to update Dynamic Reports records.

2.  Next, identify the variables you’re going to want to use to build different list views on your Dynamic Reports.  This could be things like user roles or titles, or region.  Generally, these will be variables that delineate one list view from another.
For example, you may want to have an East list view and a West list view.  Therefore Region should be one of your fields.

Report Link Fields
The other main set of fields you will be building are the fields that launch your reports.  You’ll need one of these fields for each Report you’ll be launching.

This involves a little bit of creative URL hacking, but it’s very similar to creating a Custom Report Link on an object (reference).  But these links are more powerful, since your Dynamic Report fields can reference data from any or multiple objects.

There are formula fields with a Formula Return Type = Text.

Here’s the formula, then I’ll break down the components:

= HYPERLINK(“/00OC0000005KjI0?pv2=”&User_Name__c ,”Open Opps”)

HYPERLINK(“/	This is the beginning of the formula, which will convert the result into a clickable link
00OC0000005KjI0?	This is Salesforce page ID for your report, copied directly from the URL bar in your browser
pv2=”&User_Name__c ,	pv stands for ‘passed variable’ and the number is sequence of the Field Filters on your report; see the image for more info.And in this case, User_Name__c is the field from the Dynamic Reports record that we’ll be passing into the report
“Open Opps”)	And the text between the quotes will be the link you click to run the report.

The URL format here will open the report in a new browser tab.  I find this useful, since my Sales Managers can keep the DR List View open and run all the relevant reports for each Sales Rep, then just close those tabs when they are done.

The great thing about this is to create new Report Link fields, you just have to Copy / Paste the URL from one field when you create another, and change the following components of the formula field:
– the 15 character record id of the called report
– the variable sequence (pv0, pv1′ etc.) from the report
– the value (field) from the dynamic report record that you are passing
– and the short text label that will display in the Dynamic Report list view

Here’s a few important tips when building these report links.

• Keep the Field Name short – the Field Names will be the column headings on your List Views, so you don’t want them too wide.
• I put a period in front of each report field name, so I can easily identify this as a report link field, and group them together on the custom object.

On your page layout, you may want to create a layout section for each type of report, like Sales Reports, or Case Reports, just to help you keep things organized.

Here’s a couple screenshots / images that will help explain these concepts:

Sample Dynamic Reports page layout, showing the different field types.

A graphical representation of how to build a report link formula field.

RECORDS
This part is kind of tricky.  Each Record that you add to your Dynamic Reports field exists for the sole purpose of launching a report with a variable from that record.

My primary reason for creating Dynamic Reports was to launch reports for different sales reps, which are Salesforce User records.  You can use it for any type of user record referenced on an object, i.e. Case Owner, Lead Owner, Sales Manager, etc.

Or you may want to create records for different product categories, States in a region, or other variables you commonly report on.
I would recommend you make very few fields required on Dynamic Reports, since not all variables will apply to all reports, and your admins should be the only ones adding records.

You may end up with a lot of records in your DR object.  I would recommend you check all your List Views often, so you make sure the views are displaying the records you want.

Static Records

I use Static Records if I want the variable name to always appear on the List View.

For example, in my instance I have a record for every Sales Rep, so I can build list views for my Sales Managers that show all their reps on one page.  When a new rep comes on board, we have to add a record fore that user to Dynamic Reports.  When we launched Dynamic Reports, we imported all the Sales Reps User Ids into Dynamic Reoorts.  But when a Sales Rep leaves the company, I don’t have to delete the record, because my List View filter only looks for Active Sales Reps.

Editable Records

You could also create Variable Fields that are text boxes or Picklists in Dynamic Reports.  The advantage here is that you don’t have to create a DR record for each possible variable you’d want to report on.

But, as I mentioned before, you’d have to set your permissions to Allow Editable List Views, and Allows your users’ profiles to update Dynamic Reports records.

You should also make sure that only Admins have Add or Delete privileges on DR records.  Your users will only be using Dynamic Reports to launch reports from List Views.

IMPORTANT – the actual records in the Dynamic Report object will be meaningless to your users.  Their main interface to this object is the List View.  If a user opens up a Dynamic Report record, they’ll see a bunch of field and report links that don’t make sense to them, because every record will have every variable and report link field you’ve created, regardless of whether it’s pertinent to their list views or not.

What I have done is to make sure the Dynamic Report List Views I make available to my users don’t have any links to the record – no Record ID field or Record Name field is displayed.  That way, they can ONLY click the report links.

BUT, I did make an Admin List View, with links to records, accessible only to me and other admins, so we can get into the records and troubleshoot any issues.