2 Replies Latest reply on Jun 5, 2018 6:43 AM by Bill Jordan

    Smart Reporting Subquery

    Bill Jordan
      Share This:

      Hi,

       

      I'm trying to add a Subquery in a report, the first time I'm doing this.  The Master query is from Business Service to Application tables.  The Subquery has inner joins from the Application table to the Database table to the Software Server table, the join is on application instance id. 

       

      I can add fields from the Application table and Database table from the Subquery and it displays as expected, all fields from the Master and just the ones that have matching records in the Subquery show up.  When I try to display a field from the software server table it doesn't bring back any data from the Subquery and all I get are Master query records back. 

       

      When I display the Subquery by itself it looks fine, when I run the full query in Sql Server Management Studio against the database it works fine, I see the Applications, Database and Software Servers.  Am I missing something within the report to display additional tables from the Subquery?

       

       

      Thanks,

      Bill

        • 1. Re: Smart Reporting Subquery
          Abhay Bagalkoti

          Hello Bill,

           

          Have you just added a new column within the same subquery or used a new column i.e. for Software Server table in a new subquery?

          It is append or union subquery?

           

          If possible please provide screen shots and SQL query for more understanding.

           

          Regards,

          Abhay

          • 2. Re: Smart Reporting Subquery
            Bill Jordan

            Hi Abhay,

             

            Here are some screen shots.  I’m using an Append join on the BMC_Application table.  When I add the Database Name to the report I get data back, when I add the SoftwareServer Name to the report I get no data back from the subquery, not even the Database Name.  I can add as many fields from Database as I want and they will all display, but when I add any field from SoftwareServer it doesn’t display anything from the subquery.

             

            I’ve tried other table combinations too, for some reason I’m not getting data back in the Report when I add fields past the initial relationship in the subquery, Database in this case.  The query below works when I run it in SQL Server, it’s straight from the report.  We’re on the latest version of Smart Reporting.

             

             

            Master

             

             

            Subquery

             

             

             

             

             

             

             

             

             

             

            SELECT

               T0.C0,

               T0.C1,

               T1.C1,

               T1.C2

            FROM (

               SELECT DISTINCT

                  BMC_CORE_BMC_BusinessService.Name AS C0,

                  BMC_CORE_BMC_Application.Name AS C1

               FROM .[dbo].BMC_CORE_BMC_BaseRelationship

              INNER JOIN .[dbo].BMC_CORE_BMC_Application

               ON (

                  BMC_CORE_BMC_BaseRelationship.Destination_InstanceId = BMC_CORE_BMC_Application.InstanceId

               )

               INNER JOIN .[dbo].BMC_CORE_BMC_BusinessService

               ON (

                  BMC_CORE_BMC_BusinessService.InstanceId = BMC_CORE_BMC_BaseRelationship.Source_InstanceId

               )

             

            ) T0

            LEFT OUTER JOIN (

               SELECT DISTINCT

                  BMC_CORE_BMC_Application.Name AS C0,

                  BMC_CORE_BMC_DataBase.Name AS C1,

                  BMC_CORE_BMC_SoftwareServer.Name AS C2

               FROM .[dbo].BMC_CORE_BMC_BaseRelationship

               INNER JOIN .[dbo].BMC_CORE_BMC_DataBase

               ON (

                  BMC_CORE_BMC_BaseRelationship.Destination_InstanceId = BMC_CORE_BMC_DataBase.InstanceId

               )

               INNER JOIN .[dbo].BMC_CORE_BMC_Application

               ON (

                  BMC_CORE_BMC_Application.InstanceId = BMC_CORE_BMC_BaseRelationship.Source_InstanceId

               )

               AND (

                  BMC_CORE_BMC_Application.DatasetId = 'BMC.ASSET'

               )

               INNER JOIN .[dbo].BMC_CORE_BMC_BaseRelationship AS BMC_CORE_BMC_BaseRelationship1

               ON (

                  BMC_CORE_BMC_DataBase.InstanceId = BMC_CORE_BMC_BaseRelationship1.Destination_InstanceId

               )

               INNER JOIN .[dbo].BMC_CORE_BMC_SoftwareServer

               ON (

                  BMC_CORE_BMC_BaseRelationship1.Source_InstanceId = BMC_CORE_BMC_SoftwareServer.InstanceId

               )

             

            ) T1

            ON T0.C1 = T1.C0

             

             

             

             

            Thanks,

            Bill