Help get this topic noticed by sharing it on Twitter, Facebook, or email.

Report definitions from Metadata API

Are there plans to allow DBAmp to retrieve report definitions through the Metadata API? I'm assuming this doesn't work currently because of the folder structure, but I could just be doing things incorrectly.

I've attempted to set the "Name" column to "Report" and adding the "*" wildcard to the member column of a standard Metadata table. When running List or Retrieve on this table, no rows are returned.
1 person has
this question
+1
Reply
  • So the trick is that you must work with ReportFolders and report names.

    Essentially, you can only pull the metadata for reports in one folder at a time.

    Here is a sql script with comments:

    -- Drop MD_Reports and Create a table to hold the metadata

    IF EXISTS (SELECT * FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[MD_Reports]')
    AND type in (N'U'))
    DROP TABLE [dbo].[MD_Reports]
    GO

    CREATE TABLE MD_Reports (
    [Name] [nvarchar](255) NULL,
    [Member] [nvarchar](255) NULL,
    [MetadataXML] [xml] NULL,
    [CreatedByWildcard] [bit] NULL,
    [CreatedByList] [bit] NULL,
    [Error] [nvarchar](255) NULL,
    [Id] [nchar](18) NULL
    )
    Go

    -- Get a list of Report Folders to choose from
    -- This query will give a list of the report folders on your system
    Select DeveloperName from SALESFORCE...Folder where Type='Report'

    -- Choose one from the above query and add a row to the table
    -- For my example, I choose ServiceTechnicianReports
    -- Replace the member column with a DeveloperName from the above query

    Insert MD_Reports (Name,Member) values('Report','ServiceTechnicianReports')

    -- Now ask for a list of reports in that folder
    exec sf_metadata 'list','SALESFORCE','MD_Reports'

    -- Now retrieve metadata for each report
    exec sf_metadata 'retrieve','salesforce','MD_Reports'
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated kidding, amused, unsure, silly

  • Thanks Bill. I figured it would be something like that. I went ahead and created a stored proc to loop through all the folders and bring down all reports into one large table.

    Just a note for anyone else looking in to this, you must use the DeveloperName from the Folder table, not the Name column.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated kidding, amused, unsure, silly

  • what about report folders that are hidden or do not have a name?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited

  • I am not sure how the salesforce metadata exposes folders with no name or that are hidden. I will do some research and report back.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited indifferent, undecided, unconcerned sad, anxious, confused, frustrated kidding, amused, unsure, silly