April 11, 2011

Passing report filters as query params to Salesforce reports !

Today, I stumbled across an interesting problem, where one needs to

“Run a report by passing parameters through link or button click”

Their is no direct way visible in report configuration screen for the same. So I scanned salesforce developer boards for the same, and found this solution that worked perfectly for me, thanks “mikef” for sharing this solution(can’t find your twitter profile Sad smile). Using the same example in discussion board, we will see next how you create a sample Account report and pass its filters as query params. These steps mentioned by “mikef”  will be followed next in the post:

First step: Create your report. Make sure the report is saved somewhere other then your customer report folder.

Second step: Add all the filters you need, ie if you want to pass in the State value create a fileter ShippingState equals and leave the value box blank.

Third step: Create your button to the report. use the id of the report. ie. /00O800000044Dq6?pv0={!ShippingState}

Now you see the /ID and then after that is a '?' that starts your url query string. The 'pv0' is the first value box in the report filters, so if you want the second you say pv1 and so on.

 

First Step : Create an Account report !

I am assuming we all are too good in this, so skipping details of this Smile I have completed this step and I am on report configuration page for Account sobject.

Second Step : Add filters !

On the Account report configuration view, I added filters on two fields i.e. “Type” and “Industry”, each with value equals blank(“”). Same is shown below :

image

Hit “Save”, followed by “Run report”. Obtain the report ID shown next after you save or run report, easiest way to do that is copy it from your browser’s address bar. In my case its something like “https://ap1.salesforce.com/00O90000001HwVr”, so my report id turns to be “00O90000001HwVr”

Third Step : Create a link/button for executing this report.

Here is the sample visualforce page code that creates the hyperlink and passes two params, each for “Type=Customer-Direct” and “Industry=Energy”. 

<apex:page >
    <a href="/00O90000001HwVr?pv0=Customer%20-%20Direct&pv1=Energy" > Run Accounts Report </a>
</apex:page> 

One can also run this report by directly hitting following URL in browser, I used this practice to test several combinations as its much faster then creating pages and buttons.

https://ap1.salesforce.com/00O90000001HwVr?pv0=Customer%20-%20Direct&pv1=Energy

Either way, i.e. hyperlink or direct url. Results will be as follows

image

You might need to tweak and play with param “pv”, “pv0--------pvN” to get the correct index of filters in query params. One can easily do that by saving the report once and playing with params using the report id url shown above.

Needless to say, you can create hyperlink and buttons in many more smarter and dynamic ways, to pass user-provided inputs to run these reports in visualforce pages etc, (including that piece is out of scope for this post)

Important points to consider !

As said by mikef in the discussion board thread

Some negative issues are if a user changes your report the button might not work correctly, and there is no error checking for null values.

So if we are using this hack, one needs to be careful about changes/addition of new filters to the reports.

If you have other interesting ways to pass dynamic params to report, please share !