This is a public Forum  publicRSS

Topic

    Bastiaan van der Kooij
    Use ROQL to get AnalyticsReport with a specific ColumnAnswered
    Topic posted April 18, 2017 by Bastiaan van der KooijGuru, last edited April 18, 2017 
    133 Views, 9 Comments
    Title:
    Use ROQL to get AnalyticsReport with a specific Column
    Content:

    I am trying to get AnalyticsReports back in ROQL with a specific Column Name, like so;

    SELECT * FROM AnalyticsReport WHERE AnalyticsReport.Columns.Heading = 'MyHeading'
    

    This throws the error 'Columns is not an object'. Anyone knows a way to do this?

    Bastiaan

    Version:
    Nov 2016

    Best Answer

    Rajan Davis

    Weird, I did the DESCRIBE statement on May 2016 and February 2017 from the REST API and I could not see columns. 

    It does say at the bottom of the image "Note: the data will differ from the data returned by a native DESCRIBE command in ROQL", so I think maybe you can only access this columns property using the ConnectPHP API. It looks like it is accessible from the REST API as well, but only for basic CRUD opertaions for the analyticsReport object. I don't think you can query against it...

    Is there any reason why you need to search reports by columns? I do not think it would be a bad feature to have, but I am curious as far as what you are looking to achieve.

    Answer

     

    • Rajan Davis

      Hi Bastiaan,

      Can you elaborate more on what you are trying to achieve with this functionality?

      I just checked with a 'DESCRIBE AnalyticsReport' statement on the latest version of Service Cloud and an older one and did not see  'columns' as exposed through ROQL. Not sure if what you are looking to do is possible.

      I do not think that there is a way to query reports based on their columns, but I know in the Reports Management module that you can go through the list of created reports and you can try to find it that way. Usually, if I am unsure about where to find a report, I use modified version of this report and try to use the dates and Auto Filter to find the report that I am looking for.

      Hope this helps.

      Kind Regards,

      Rajan

    • Bastiaan van der Kooij

      Hey Rajan,

      It is part of an Add-In where I need to fetch report with a specific Column Header, I know it's the same line, but that's really all there is to it.. :) 
      What strikes me is that I cannot find any difference between the Emails array in Contacts and the Columns array in AnalyticsReport, from a Roql perspective, so I do not see any indication that this should not be possible, like it is with emails (and phones etc) on the Contact object, like;

      Select Phones.Number FROM Contact WHERE Phones.PhoneType.ID = 0 

       

      here my Describe on a Nov2016 site;

      Bastiaan
       

    • Rajan Davis

      Weird, I did the DESCRIBE statement on May 2016 and February 2017 from the REST API and I could not see columns. 

      It does say at the bottom of the image "Note: the data will differ from the data returned by a native DESCRIBE command in ROQL", so I think maybe you can only access this columns property using the ConnectPHP API. It looks like it is accessible from the REST API as well, but only for basic CRUD opertaions for the analyticsReport object. I don't think you can query against it...

      Is there any reason why you need to search reports by columns? I do not think it would be a bad feature to have, but I am curious as far as what you are looking to achieve.

    • Dietrik

      It does not look like the Columns field is supported by ROQL. CUD operations are not supported, only Read.

      I don't know of any specific list of ROQL supported fields, only below limitation:

      The AnalyticsReport objects allows you to fetch an existing report definition by ID, LookupName, or by a ROQL advanced query, but it should be noted that the queries can only fetch limited information about an AnalyticsReport such as the ID and LookupName.

    • Bastiaan van der Kooij

      Rajan / Dietrik,

      thanks guys, it looks like I got mislead by the fields returned from Describe in the Object Explorer. So that's what the warning is about.. Should really start reading them :)

      Note: This data will differ from the data returned by the native DESCRIBE command in ROQL and has been enriched with extra information.

      Although it seems strange that in the object explorer the Describe command defeats its own purpose by returning extra fields, since if I want extra fields I open the object to explore it.

      Bastiaan

    • Dietrik

      Who would have though anyone ever needed to find a report by it's header  label ?

      Post it to the idea lab and i'll vote for it.

    • Bastiaan van der Kooij

      it's a feature for a Mail Merge Add-In I build (cxbuilders.com/solutions/mailmerge) whereby the user can select a report, but I only want to show reports that have a column called ID. Roql also does not expose the folder name, so showing a browser window for the Report gets a bit problematic anyway. Using a custom report for this just ads a dependency which I really don't like. So I found one standard report that actually shows all reports and folders so I think I'll go with that and do the Column check upon selection..

    • Dietrik

      A header can be called anything, it doesn't mean much for the data in the column.

      I usually just merge the available data in the report selected or workspace records. Or give the opportunity to enter a query.

      Creating custom reports or column naming dependencies can indeed better be avoided for a generic solution.

    • Bastiaan van der Kooij

      A header can be called anything, it doesn't mean much for the data in the column.

      If I agree with you that the header with the Name "ID" contains the ID of the primary object that you want to render it does mean something in that context. With generic solutions like this in a Framework like OSC you have to settle with that sometimes to get a good working solution.