+ Start a Discussion
LexicLexic 

How to pass an entered value to a report in a custom link

How do I  run a report from a custom link, which selects records based on a date range

 

I want the user to enter start and end dates of the date range,and pass these to the report

 

The pv0, pv1 method seems to only reference fields in the current record

 

I want to ask for two dates then pass these to the report

Navatar_DbSupNavatar_DbSup

Hi,

You can pass the parameter in the report. Follow the below steps:

  1.  Create a Report having blank values in filters and save the report.
  2. Run the report and note down the report id.
  3. Create a custom link /00O900000020y66?pv0={!DATEVALUE( Broker__c.CreatedDate )}&pv1={!DATEVALUE( Broker__c.LastModifiedDate )}

 above highlighted text is the reportid.

Did this answer your question? If not, let me know what didn't work, or if so, please mark it solved. 

 

LexicLexic
Thanks - this will produce a report using the values in the fields of the current record However, I want to ask the operator to type two dates on the keyboard and use these dates in the report Is this possible?
Navatar_DbSupNavatar_DbSup

Hi,

 

There are three fields in the criteria that you will need to pass data to in order to  fully control the criteria.

pc0: Corresponds to the field in the report criteria.
pn0: Corresponds to the operator in the report criteria.
pv0: Corresponds to the value in the report criteria.

These inputs are 0 based that means that you will access the first set of filter fields using 0 (e.g. pc0, pn0, pv0). As you add more filters, you just increase the number (pc0, pc1, pc2...).

Now to the operators. Here are the values that Salesforce is expecting:

eq = equals
ne = not equals
lt = less than
le = less than or equals
gt = greater than
ge = greater than or equals
co = contains
nc = does not contain
sw = starts with
in = includes

Bringing it all together. Run the desired report and copy the report ID from the URL. We'll use our Won Opportunities report for this example (https://na12.salesforce.com/00OU0000000aYlZ). Paste the report id into the link window. You can then append filter parameters at your leisure.

"/00OU0000000aYlZ?pc0=ACCOUNT_ID&pn0=eq&pv0={!Account.Id}&pc1=WON&pn1=eq&pv1=TRUE"

In the example above we are passing the Account ID in dynamically based on the record the user is in when he/she clicks on the link. Keep in mind that you can use the methods we have covered here to override filter criteria for existing report allowing you to reuse them when necessary instead of having to create a report with predefined filters where you only pass the filter value (pv). There are things you will need to tweak here and there. For example when passing currencies in your pv parameter you will want to URLENCODE() to make sure that value gets passed correctly.

 

LexicLexic

Understood.  But is there a way to ask for the dates to use in the report.  The examples given use values saved in the current record

 

I want to click the custom link and be ASKED for the dates to use in the report?

 

Thanks for your help

ministe2003ministe2003
I know this is a really old thread and I hate to dredge it up but I'm working on this right now and wanted to add a little missing information.  I havne't been able to find this elsewhere so hopefully it'll help anyone who finds this thread.

The second response from Navatar_DbSup is the best answer I've been able to find, I don't know where he managed to find all that info but I think it's the best source of info on this subject on the web right now.  However it's just missing one piece of information.  If you're working with only standard fields then that response will work perfectly, just pass the column name you want to filter on in your PC paramater (if you don't know the column name - it's different from the API name and the label - you can find it in the Workbench tool).

This is the crucial missing part though - If you're passing through a custom field you must pass through the custom field ID, NOT its column name.  You don't need the "CF" prefix before the ID like you do with url hacking usually, just the raw 18 digit id.

Using this information you can also append new filters to existing reports which have any number of existing filters.  You don't need to add blank filters and populate their criteria with this url hack, you can just have your report with no filters and add them completely.  Likewise if you have a report with 5 existing filters, you can add new ones on top just by starting the numbering higher up.  ie instead of using pc0, pn0 and pv0 you just start it at pc5 or whatever number you need (bare in mind filter numbering starts at 0, like a list).  Finally with this information you're able to change the existing filters too, again just use the correct filter number in your URL hack and you can replace any part of the existing filter!
EHatEHat

Hi @ministe2003,

I have been unable to get this to work for me as I cannot change the pc paramters, and saw that 4 years ago(!) other people had a similar issue to myself: https://developer.salesforce.com/forums/?id=906F00000008pFrIAI 

The problem is that when I attempt to change an existing pc it wipes that filter, and if I add a new pc onto the url it does not add a filter.

Have you ever encountered this issue?

ministe2003ministe2003
I don't really understand your issue Eleanor but try following these general rules:
  • parameters start at 0, like a list
  • you must provide all three parts of the parameter, pc, pn and pv.  Each of these must be suffixed with the number of the parameter like pc0, pn0 and pv0.
  • the pc parameter is the field.  For standard fields you need to use the report name of the field, which you can get by going to workbench and pustting this in the REST explorer: /services/data/v36.0/analytics/reports/xxxreportIdxxxx/describe and then opening reportExtendedMetadata and checking out the column info.  For custom fields use its 15 digit Id.
  • pn and pv have been well explained above, but remember you need to make the URL safe so don't just pass spaces, commas and brackets.  You need to pass the URL safe codes for characters like these.
  • Put default parameters into the report, enough of them to cover the number of parameters you'll be passing into the URL.  Just use something like Created Date Not Equal To ''
EHatEHat

Hi Ministe2003, thanks for the explanation - I should have made mine more clear.

I am using Drawloop to create word documents from Salesforce, and within that I can use reports to fill out tables with my data.  The report is attached to the Drawloop document, along with a "filter" which is the pv0=... and so on.  The problem I'm encountering is that I cannot find the correct filter to reduce the results on my report.

To test out the filters, I have been opening the report in salesforce and appending the filter to the URL.  When I do this, the filter I am changing on the report simply disappears, rather than changing.  The report is no longer filtered, and the detail of the report no longer shows the filter.

For example, I added ?pc1=(Record ID)&pn1=ne&pv1=""  to the end of the URL and it got rid of the filter that was originally in place and did not replace it.  

Marie BiroMarie Biro
Hi Ministe2003,
Thank you for the information. Very helpful! Do you know if cross filters can be used in custom links? Thanks.
Marie
Serobanyane MateteSerobanyane Matete
Hi all,

There are three fields in the criteria that you will need to pass data to in order to  fully control the criteria.
pc0: Corresponds to the field in the report criteria.
pn0: Corresponds to the operator in the report criteria.
pv0: Corresponds to the value in the report criteria.

What is the equibalent of these (pc0,pn0,pv0) in Lightning? I know in Lightning 'pv0' becomes 'fv0'. But what about pc0 and pn0?
I need to dynamically remove the field (pc0) in the report criteria.

Thanks
ministe2003ministe2003
There's no lightning equivalent for those.  You can only alter the value, not the field or the operator.  https://releasenotes.docs.salesforce.com/en-us/spring17/release-notes/rn_rd_reports_filter_url.htm