I recently had a requirment where a list of any kind of sObject could be given to a visualforce page, and it should spit out a PDF report of those objects. The fields returned could possibly be defined by a field set, passed in the URL directly, or I could get passed nothing and would just have to query for all fields on the object. It was decided that the best course of action was to write a nice re-useable apex class that can handle these requirements and use the visualforce renderas attribute to make it easy to generate printable reports. You can easily rig up a custom button on a list view to get the checked elements and pass them into the exporter page as well to basically allow exporting from any list view. The following is the first draft of said functionality.
4/03/2013 EDIT: Thanks to a good tip by Cal Smith I changed how the visualforce page outputs the content and it seems to be much faster and probably safer too. I also included two new params for the exporter. You can now provided a field to order by by specifying order_by in the url. Also, if you want the records returned in the same order the ids were provided in the url you can specify return_in_order=true. This is probably slow on large data sets but in cases where your users may have put records in the order they want you can then pass the ids in that order to the controller and the PDF will be generated with the same order. Kinda a nice feature I thought.
4/05/2013 EDIT: I added the force_download param and filename params to allow you to force the user to download the file and specify a name for the downloaded file instead of letting them view it in their browser. Not totally sure why someone might want this, but it was a request I got and it was fairly easy to add.
The Apex Class
/*
Name: queryGenerator
Author: Daniel Llewellyn
Date: 4/02/2013
Description: Meant to be invoked by a visualforce page. This class can take url params to query for a list of any kind of
sObject. Those sObjects can then be used to power user interface elements. By passing in a list of Ids and an
option field set, this class is able to determine the object type, find a matching field set, use a default field set
or if none is specified query for all fields defined on the object. Useful for generating lists of sObjects when the type
of object, and desired fields is not known ahead of time.
URL Params:
name type req description
--------------------------------------------------------------------------------------------------------------------
ids: csv of ids true a list of sObject Ids seperated by commas. The objects to include in the return query
fields: string false a comma seperated list of fields to include. Takes precidense over fieldSet if specified.
fieldSet: string false the name of a fieldset to use to determine which fields to include. Used if fields param not specified. If both are null, all fields are queried.
order_by string false the name of a field on the object to order the results by
return_in_order boolean false should the results be returned in the same order they were provided in the URL? Overrides the order_by param if set to true.
force_download boolean false should the PDF file be forced to donwload instead of displayed in the browser window?
filename string false the name to assign to the downloaded file if force_download is set to true. Defaults to object label + ' report.pdf' Do not include .pdf. It is appened automatically.
Gotchas:
Due to the way the query is built (filtering by a list of Ids) you can only get probably about 500 records max before the query length gets too long.
Shouldn't be a big deal though, a report of more than 500 records starts to get kind of meaningless most of the time. It will attempt to gracefully handle
any errors and return them nicely to the user.
*/
public class queryGenerator
{
//Params. Can be used in your visualforce page to customize report data.
public Schema.SObjectType OBJECT_SCHEMA_TYPE{get;set;}
public string OBJECT_TYPE_NAME{get;set;}
public string OBJECT_TYPE_LABEL{get;set;}
public string ORDER_BY{get;set;}
public boolean RETURN_IN_ORDER{get;set;}
public list<string> OBJECT_FIELDS{get;set;}
public map<string,string> OBJECT_FIELD_MAP{get;set;}
public list<id> OBJECT_IDS{get;set;}
public list<sobject> OBJECTS{get;set;}
public integer RECORD_COUNT{get {
return objects.size();
}set;}
public queryGenerator(){
try
{
OBJECT_FIELD_MAP = new map<string,string>();
//get the list of ids to query for. We expect them to come in a url param called ids, and they should be
//comma seperated. Since we know that, we can split them based on , to get a list of ids.
if(ApexPages.currentPage().getParameters().get('ids') == null)
{
throw new applicationException('Please include a list of a comma seperated ids to query for in the url by specifying ?ids=id1,id2,id3 etc');
}
OBJECT_IDS = ApexPages.currentPage().getParameters().get('ids').split(',');
//use the ids getSObjecType method to figure out what kind of objects these are we got passed.
OBJECT_SCHEMA_TYPE = OBJECT_IDS[0].getSObjectType();
//caching describe results makes for faster iteration
map<string,Schema.sObjectField> fieldMap = OBJECT_SCHEMA_TYPE.getDescribe().fields.getMap();
for(Schema.SObjectField field : fieldMap.values())
{
OBJECT_FIELD_MAP.put(field.getDescribe().getName(),field.getDescribe().getLabel());
}
//get the name of this object type
OBJECT_TYPE_NAME = OBJECT_SCHEMA_TYPE.getDescribe().getName();
OBJECT_TYPE_LABEL = OBJECT_SCHEMA_TYPE.getDescribe().getLabel();
//get the list of fields we will query for and display
if(ApexPages.currentPage().getParameters().get('fields') == null)
{
OBJECT_FIELDS = getObjectQueryFields(OBJECT_SCHEMA_TYPE, ApexPages.currentPage().getParameters().get('fieldset'));
}
else
{
OBJECT_FIELDS = ApexPages.currentPage().getParameters().get('fields').split(',');
}
//set the order by statment. If no order by is specified, just tell it to order by Id to prevent a syntax error
if(ApexPages.currentPage().getParameters().get('order_by') != null)
{
ORDER_BY= ApexPages.currentPage().getParameters().get('order_by');
}
else
{
ORDER_BY = 'Id';
}
RETURN_IN_ORDER = false;
if(ApexPages.currentPage().getParameters().get('return_in_order') != null && ApexPages.currentPage().getParameters().get('return_in_order') == 'true')
{
RETURN_IN_ORDER = true;
}
OBJECTS = getSojects();
if(ApexPages.currentPage().getParameters().get('force_download') != null && ApexPages.currentPage().getParameters().get('force_download') == 'true')
{
string fileName = 'Report of '+OBJECT_TYPE_LABEL+'.pdf';
if(apexPages.currentPage().getParameters().get('filename') != null)
{
fileName = apexPages.currentPage().getParameters().get('filename') +'.pdf';
}
Apexpages.currentPage().getHeaders().put('content-disposition', 'attachemnt; filename='+fileName);
}
}
catch(exception ex)
{
//catch and return errors. Most often will happen from a bad Id of fieldname being passed in.
system.debug('\n\n\n------Error occured during page init!');
system.debug(ex.getMessage() + ' on line ' + ex.getLineNumber());
ApexPages.addmessage(new ApexPages.message(ApexPages.severity.WARNING,ex.getMessage() + ' on line ' + ex.getLineNumber()));
}
}
//this method will be invoked by a visualforce page. It will determine the sObject
//type by examining the Ids passed in the ids param. Once it knows the object type it will
//then attempt to find a locate the specified fieldset if one was passed in the URL. If no fieldset
//was provided, then it will query for all sObject fields.
public list<sobject> getSojects()
{
list<sobject> queryResults;
//lets get a list of fields to query for by using the getObjectQueryFields method. We will pass in the object type
//and the fieldset url param (which may be null, but that doesnt matter).
string queryFields = listToCsv(OBJECT_FIELDS);
//build this query string
string queryString = 'select ' + queryFields + ' from ' + OBJECT_TYPE_NAME + ' where id in :OBJECT_IDS ORDER BY '+ORDER_BY;
if(queryString.length() > 10000)
{
throw new applicationException('Query too long ('+queryString.length()+'). Please reduce the number of ids or reduce the number of fields queried for to get the length under 10,000');
}
//run the query.
queryResults = database.query(queryString);
if(RETURN_IN_ORDER)
{
queryResults = sortQueryInOrder(OBJECT_IDS, queryResults);
}
return queryResults;
}
//takes the list of sObjects and sorts them in the order they were passed in the URL. This allows for a custom sorting order to be passed in
//without having to make use of the SOQL order by clause which may not be robust enough to handle the types of sorts desired.
//WARNING THIS IS PROBABLY PRETTY DAMN SLOW!
public list<sObject> sortQueryInOrder(list<id> objectOrder, list<sObject> objects)
{
map<id,sObject> objectMap = new map<id,sObject>();
list<sObject> sortedList = new list<sObject>();
for(sObject obj : objects)
{
objectMap.put((id) obj.get('id'), obj);
}
for(id objId : objectOrder)
{
sortedList.add(objectMap.get(objId));
}
return sortedList;
}
//takes an sObject type and optional name of a fieldset for that sObject type (can be null). Returns a list
//of strings of fields to query for either based on the fieldset, or by finding all sObject fields if no fieldSet
//is specified, or a matching fieldSet can not be found.
public list<string> getObjectQueryFields(Schema.SObjectType objectType, string fieldSetName)
{
set<string> fields = new set<string>();
Schema.FieldSet thisFieldSet = null;
//first any fieldsets that are defined for this object type. It is possible this might be empty.
Map<String, Schema.FieldSet> fieldSetMap = objectType.getDescribe().fieldSets.getMap();
//check to see if the user passed in a field set, and if so, does it exist?
//if so, use that fieldset. Otherwise, use all fields on the object
if(fieldSetName != null && fieldSetMap.containsKey(fieldSetName))
{
thisFieldSet = fieldSetMap.get(fieldSetName);
//now that we know what field set we are using we have to iterate over it and get it feildsetmembers
//and add each field into the query string.
for(Schema.FieldSetMember f : thisFieldSet.getFields())
{
fields.add(f.getFieldPath());
}
}
//if there are no field sets defined for this object, then lets just query for all the fields
else
{
fields = getObjectFields(objectType);
}
//return our variable that contains a properly comma seperated list of all the fields to query for.
list<string> fieldList = new list<string>();
fieldList.addAll(fields);
return fieldList;
}
//a simple possibly overly abstracted method to get the fields on an object
public set<string> getObjectFields(Schema.SObjectType objectType)
{
return objectType.getDescribe().fields.getMap().keySet();
}
//takes a list of strings and returns them in a comma seperated fashion, suitable for feeding into a query.
public string listToCsv(list<string> stringList)
{
string itemList = '';
for(string thisString : stringList)
{
itemList += thisString+',';
}
itemList=itemList.substring(0,itemList.length()-1);
return itemList;
}
@isTest
public static void testQueryGenerator()
{
//setup our test account
Account testAccount = new Account();
testAccount.name = 'My Test account';
testAccount.billingStreet = '1234 Test Street';
testAccount.billingState = 'NY';
testAccount.billingPostalCode = '55555';
testAccount.billingCountry = 'USA';
insert testAccount;
test.StartTest();
PageReference pageRef = Page.exportPdf;
Test.setCurrentPage(pageRef);
//run it with no ids. It will come back with no records since it will error. Since the error gets caught
//we don't need to try/catch here though.
queryGenerator qg = new queryGenerator();
//run test with nothing but ids specified. This will make it query for all fields
ApexPages.currentPage().getParameters().put('ids', testAccount.id);
qg = new queryGenerator();
//make sure it found our account
system.assertEquals(1,qg.RECORD_COUNT);
system.assertEquals(testAccount.name,(string) qg.OBJECTS[0].get('name'));
ApexPages.currentPage().getParameters().put('fields', 'name,id,billingStreet');
qg = new queryGenerator();
//make sure it found our account
system.assertEquals(1,qg.RECORD_COUNT);
system.assertEquals(testAccount.billingStreet,(string) qg.OBJECTS[0].get('billingStreet'));
ApexPages.currentPage().getParameters().put('order_by', 'name');
ApexPages.currentPage().getParameters().put('return_in_order', 'true');
ApexPages.currentPage().getParameters().put('force_download', 'true');
ApexPages.currentPage().getParameters().put('filename', 'My PDF file');
qg = new queryGenerator();
}
class applicationException extends Exception {}
}
The ExportPDF visualforce Page
<apex:page controller="queryGenerator" renderAs="pdf" standardStylesheets="false">
<head>
<style>
@page {
size:landscape;
margin : .5in;
@top-center {
content : element(header);
}
@bottom-left {
content : element(footer);
}
}
table
{
width:100%;
}
@bottom-left {
content : element(footer);
}
div.footer {
position : running(footer) ;
}
</style>
</head>
<apex:pageMessages></apex:pageMessages>
<h1>Report of {!OBJECT_TYPE_LABEL} ({!RECORD_COUNT} Records)</h1>
<table>
<tr>
<apex:repeat value="{!OBJECT_FIELDS}" var="FieldLable">
<apex:outputText><th>{!OBJECT_FIELD_MAP[FieldLable]}</th></apex:outputText>
</apex:repeat>
</tr>
<apex:repeat value="{!OBJECTS}" var="rec">
<tr>
<apex:repeat value="{!OBJECT_FIELDS}" var="FieldLable">
<apex:outputText><td>{!rec[FieldLable]}</td></apex:outputText>
</apex:repeat>
</tr>
</apex:repeat>
</table>
<div class="footer">
<apex:outputText value="The Date: {0,date,MMMMM dd, yyyy 'at' hh:mm a}" styleClass="footer" >
<apex:param value="{!NOW()}" />
</apex:outputText>
</div>
</apex:page>
Sample List View Button
window.open('/apex/exportPdf?ids='+ {!GETRECORDIDS($ObjectType.YOUR_OBJECT_TYPE)}+'&fieldset=YOUR_FIELD_SET_NAME_HERE&order_by=name&return_in_order=false','1364931211178','width=700,height=500,toolbar=0,menubar=0,location=0,status=1,scrollbars=1,resizable=1,left=0,top=0')
You’ll need to replace the $ObjectType.YOUR_OBJECT_TYPE and the fieldset=YOUR_FIELD_SET_NAME_HERE in the list view button. Or you can just remove the fieldset part entirly, or replace it with a ‘fields’ attribute where you can specify a comma separated list of fields to query for. You’ll probably want to play with the formatting of the report a little but, but I’ll leave that as an exercise to the reader. Hopefully this helps someone out there.
They are essentially a way to provide categories for any sObject in Salesforce. They are most frequently used with knowledge articles. The Apex calls, describes and schema for them is unlike anything else in the Salesforce schema. Categories are their own objects and they can be nested to infinite complexity. In short, they are complicated and take a while to really get your head around them (I still don’t know if I really do). Thankfully I’ve done a bunch of hard work and discovery so that you don’t have to. For this particular project, we are going to build a nifty tree style selector that allows a user to select any data category for a given sObject type. You can then do whatever you want with that info. Yes I know there are some built in visualforce components for handling data categories, but they aren’t super flexible and this is just a good leaning experience. In the end, you’ll have an interactive tree that might look something like this.
