4 Replies Latest reply on Apr 12, 2018 4:42 AM by Bjorn Calabuig

    ITSM & Analytics - How to work with Analytics derived tables

    Bjorn Calabuig
      Share This:

      Hi List,

       

      Just a quick question.

      We want to modify what it seems OOTB queries but, although I've worked with BO and Crystal,...I'm not an expert working with "universes".

       

      For example, If we take a look at the OOTB query behind HPD:Help Desk (right mouse button-> edit derived table), it looks like this:

       

      SELECT HPD.* FROM HPD_HELP_DESK HPD

      WHERE EXISTS (

      SELECT DISTINCT CTM1.COMPANY FROM CTM_PEOPLE_PERMISSION_GROUPS CTM1

      WHERE REMEDY_LOGIN_ID = @VARIABLE('BOUSER')

      AND CTM1.COMPANY IS NOT NULL

      AND ( CTM1.COMPANY = HPD.COMPANY

                 OR CTM1.COMPANY = HPD.CONTACT_COMPANY

                 OR CTM1.COMPANY = HPD.Direct_Contact_Company

                 OR CTM1.COMPANY = HPD.Assigned_Support_Company

                 OR CTM1.COMPANY = HPD.Owner_Support_Company)

      UNION

      SELECT DISTINCT COMPANY FROM ANA_UNRESTRICT_USER_VW URUSER

      WHERE URUSER.REMEDY_LOGIN_ID = @VARIABLE('BOUSER')

      )

       

      OK, we want to modify this query and also many others.

       

      And which are these "many others"?

      In order to answer this question, firstly, if I'm following the right path, I need to analyze every query...

       

      But, which are  these queries? Again, if I'm not wrong, those related to derived tables.

       

      And here goes my question.

       

      Is there any "decent" way to get all derived table SQL queries? Since two days I'm digging in the dirt, trying to find a way to do just a simple report which I could use as a starting point.

       

      But, as you can see here:

      Extract SQL for all the derived tables

       

      It seems that SAP (yes SAP!) doesn't offers any solution than "double clicking on each derived table" and copying&pasting into a document:

      Universal Design Tool -> Tools -> List of Derived Tables

       

      They must be joking...

      Any help would be appreciated.

       

      Kind Regards,

      Björn.