Auto Create Reports from Web Links

Share

There is a little documented trick for building dynamic reports that can be launched from Web Links on a specific record. You can setup reports with pre-set criteria and pass the criteria values dynamically to the report through a URL string. To do so, pass the parameter values in the URL string where

pv0 is the value of the first parameter
pv1 is the value of the second parameter
pv2 is the value of the third parameter
and so on…

The URL would be:

https://na1.salesforce.com/id_of_the_report?pv0=value&pv1=value

Build this link this using a weblink or sforce control.

For example, suppose your company uses a Parent-Child account hierarchy. Suppose you do business with a number of GE subsidiaries and have a parent GE account is used to bring all of those accounts together. You want to easily run a report for all of the Opportunities related to the child accounts of that GE parent account.

To do this, create a Web Link to dynamically generate the report from an Account record.

Here’s how you do it:

Create an Opportunity report with the format you want. Add a single criteria line using the Parent Account ID field. Set the condition to “equals”. Leave the value blank. Save the report and note the URL of the report.

Create an Account web link that is a URL. The URL is “/id of the report?pv0={Account ID}“. Put that web link on the account page layout. When you run this report, the weblink will pass the Account ID into the first report criteria value. In this case, that’s the Account ID. So this report will pull all Opportunities where the Parent Account ID equals the Account ID of the Account record you clicked the weblink from. If you run that report from the GE record, it will pull up all Opportunities for child accounts of GE.

The parent-child situation is just 1 example. The concept can be used for a lot of situations where you cannot hardcode the criteria you need into the report.

10 Comments »

  1. Jeff Douglas Said,

    April 5, 2010 @ 6:30 am

    Scott, this is AWESOME! Thanks for the post. This is exactly what I need to solve a problem of NOT building 1M reports.

  2. Peter Yao Said,

    July 25, 2010 @ 7:42 pm

    Scott,

    I’ve been using this for years – thanks for posting! I find it very helpful in conjunction with Firefox’s keyword shortcuts.

    Our marketing group recently needed a way to pass a parameter to a report via “Connect for Office” (the Excel plugin that is supported by Salesforce). The only way I could figure it out is to write a little macro that uses your pv0 trick (see below). It’s still in the works, but thanks for posting your trick!

    Peter

    Sub ImportReport()
    Dim url As String
    Dim wSSummary As Worksheet
    Dim wSData As Worksheet

    Set wSSummary = Sheets(“Summary Sheet”)
    Set wSData = Sheets(“Data Sheet”)

    ‘ Clear data sheet
    LastRow = wSData.UsedRange.Rows.Count
    wSData.Range(“1:1”, wSData.UsedRange.Rows.Count & “:” & wSData.UsedRange.Rows.Count).Delete

    ‘ Import 00O30000004W4B4 report to data sheet using parameter on Summary!C4
    url = “URL;https://na1-api.salesforce.com/00O30000004W4B4?isExcel=1&pv0=” & _
    wSSummary.Range(“C4”) & _
    “&export=1&xf=cls&enc=UTF-8”
    With wSData.QueryTables.Add(Connection:=url, Destination:=wSData.Range(“A1”))
    .Name = “Data Import”
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = False
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlEntirePage
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh
    End With

    ‘ The last 5 rows are report metadata
    wSData.Range(wSData.UsedRange.Rows.Count – 5 & “:” & wSData.UsedRange.Rows.Count – 5, wSData.UsedRange.Rows.Count & “:” & wSData.UsedRange.Rows.Count).Delete

    ‘ Reports are imported as csv, reformat to columns
    wSData.Range(“A:A”).TextToColumns Destination:=wSData.Range(“A1”), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=True, Space:=False, Other:=False, _
    TrailingMinusNumbers:=True

    wSData.Range(“A:A”).Columns.AutoFit

    End Sub

  3. Isaias Martinez Said,

    August 17, 2010 @ 11:31 am

    Scott –

    Great post, this is exactly what I was looking for to pass multiple parameters… Thanks for the post.

    Isaias

  4. Monday AM Admin: Take Dynamically Filtered Reports Up A Notch « Said,

    May 9, 2011 @ 8:19 am

    […] to dynamically filter it using the well known “pv0=” URL technique.  There are plenty of resources with instructions for how to construct such a link so I won’t go into detail here.  (However, in […]

  5. Jeremy Said,

    December 15, 2011 @ 4:59 pm

    I am trying to create a custom link from the home page to run a report with the filter set to User Alias but for some reason it won’t work. I pass pv0={!$User.Alias} at the end of the URL (this is the only filter) and it reads that as literal instead of the current users actual alias. If I hardcode the Alias it works, but that obviously doesn’t solve my problem. Any ideas on how to pass User variables in URL to a report filter?

  6. Pranay Said,

    February 16, 2012 @ 11:34 pm

    Hi,

    I am displaying reports in client portal and while displaying the report we see all the ugly filter parameters like all the account ids i had passed and all the years and months I was passing.
    Is there a way to add a parameter to my report string which would hide the filter parameters or any other way would do too.
    By the way I am using iframe for displaying the reports.

    Pranay

  7. Thomas Said,

    February 28, 2012 @ 5:33 pm

    I was wondering if this would work with “Customer Portal” user types? Customer Portal users are unable to change ANY parameters in a report (even date range). I was looking for something in which we could dynamically pass parameters via a VF page so at least a portal user could change up basic parameters in a report.

    Thanks

  8. Pranay Said,

    February 29, 2012 @ 11:46 pm

    Hi Thomas,

    If you are talking about passing report parameters through VF in customer portal, then this should be easy. I am doing the same by retrieving the parameters in my controller using getter and setter and then generating a report string and displaying using iframe or redirect. We just need to add the VF page to the Customer Portal profile.

    Pranay

  9. Niels Christensen Said,

    April 17, 2013 @ 6:16 am

    Hi everyone,

    This works great for us in standard reports but we need it to work with a Joined Report; anyone have experience doing that?

    Thanks!
    Niels

  10. Rebecca Ralls Said,

    August 13, 2013 @ 11:37 am

    I was also wondering about the options for using this trick on a joined report … is that possible?

RSS feed for comments on this post · TrackBack URI

Leave a Comment

All comments are moderated. Other visitors will not see your comment until it has been approved.