+ Start a Discussion
RDN_LHRRDN_LHR 

Running report from URL and adding date criteria

Hi,
 
I'm trying to call reports via URLs, and I would like to pass a date value through the URL as filter criteria.  I've done this lots with text values but not dates.
 
My URL looks like this:
 
 
When I click this, I get the report's criteria page , with the date placed where it belongs in the filter area, but with an "Error: Invalid date" message which says that the date needs to be formatted like "29/09/2008" -- which it clearly is.  Without making any changes at all to the date in the box, I click Run Report and the report runs fine.
 
Naturally, I'd like my end users to just get the report results, not have to see the criteria page, plus the resulting error, and then have to click Run Report.
 
Can anyone help?
devNut!devNut!
You are missing some parameters in your URL:

https://emea.salesforce.com/00O20000001zI6o?pv0=29/09/2008

You also need a pn0 and pv0

To figure out what pnX should be, open a report in salesforce and go to the "step 6: select your report criteria section." do a view source and search for pc0, pn0, pv0.


RDN_LHRRDN_LHR
What does pn0 do?  Is that what you need to get a date to work properly?  I've never had to specify that in any of my other custom links of this type that pass text.
devNut!devNut!
Just re-read your original post..


try date as 09/29/2008
RDN_LHRRDN_LHR

I get the feeling that if I changed my settings from English UK to English US and then passed the date in US format, I'm still going to get the same error.

You can't input US format dates into a report criteria page without being in English US.

I really hope I can solve this.  Salesforce.com themselves are of no help.

devNut!devNut!
How about this:

go to the report in salesforce and enter the criteria -  what criteria value (date format) will let you run the report?


RDN_LHRRDN_LHR
UK format runs the report.  The problem is that when you pass the value through the URL, it errors and gives you the criteria page, with the date value put into the right place.  Making absolutely no changes, you can click Run Report and it works.
 
I'm trying to avoid the first error and the presentation of the report criteria page and just have the report run.
devNut!devNut!
This is an odd problem.


How about trying: 29%2F09%2F2008
RDN_LHRRDN_LHR
I tried that first and that's what prompted me to post here in the forum.  I get the feeling that Salesforce.com is just going to respond with a "sorry that isn't supported" email.
smartrms2smartrms2

Hello, I just wonder if you could resolve your issue with passing a date value through a url ...

 

I would be extremly intrested in the solution as I am trying to resolve this issue too.

 

I have noticed that the problem doesn't occur if the Local Settings on the user detail is set to "English (United States)" but this is not really exceptable for users i.e. in Europe.

 

Looking forward to hearing from you or someone else.

smartrms2smartrms2

Hello, 

 

I have now resolved this for my org.

 

Here is my solution which is of interest for orgs which use the European time format DD/MM/YYYY:

 

1. In the report URL, do NOT include the "Date Field" which is sending the value 29/09/2008 https://emea.salesforce.com/00O20000001zI6o?pv0=Date Field . - Why? When running the report through the url, salesforce.com is always interpeting the value 29/09/2008 as MM/DD/YYYY instead of DD/MM/YYYY. The error message occurs because there is obviously no month 29.

 

2. INSTEAD create new a formula text field called i.e. "Date Report Field" where your initial Date Field is embedded. The formula is: text(MONTH(Date Field)) & "/" & text(DAY( Date Field)) & "/" & text(YEAR( Date Field)) . Note that the order of the elements in the formula must be month, day, year! - There is no need to include this field into any page layouts.

 

3. Now, create your report URL based on the new FORMULA TEXT FIELD as follows: https://emea.salesforce.com/00O20000001zI6o?pv0=Date Report Field. Done! Your report will run smoothly.

 

This is working well for my org, even if I change the user local settings from English UK to English USA.

 

Good luck to you!

Message Edited by smartrms2 on 08-25-2009 06:14 AM
Marvin BonifacioMarvin Bonifacio

Thank you very much, this solved my problem. Thanks for posting this answer!  :)