How to use the Advanced Report Designer

Version 52

    Verified Product Versions

    Endpoint Manager 9.5Endpoint Manager 9.6Endpoint Manager 9.0 and OlderEndpoint Manager 2016.x

    How to use the LDMS Report Designer

    This document demonstrates how to write a query and design a customized report.


    Scenario 1

    The goal of the included example is to return a report only for devices which have specific SQL Application Suites. The report is designed to break up sections by device and only display the desired application suites for each device while returning a count of machines..


    Before designing the report, a query must be created.

    Below is a sample query where a list of machines is returned where the Application Suite SQL Server 2005 as well as other SQL Server suites exist for the machine inventory record.

    Be sure to select the columns (at the bottom of the query designer) that will contain the data you will want to return in your report for machines that meet your query criteria. In the example we want to see the Display Name, Application Suite names and versions for devices where SQL Server 2005 or specific SQL application suites exists.

    Attached is the query shown in the example: Application Suite.xml



    The design of the example query is to show machines that have any of the selected SQL Server Application Suites. This is accomplished by Inserting each Application Suite by name with an OR operator so it will return machines that have application A OR B OR C etc. Next the Application Suite name and version column sets are added so the query will display which of the Application Suites are on the machine(s).


    Run the query to ensure it returns machine records.

    Because the query was written with OR, it allowed the query to return "Microsoft SQL Server 2005 Tools Express Edition" only for the 88 Core as this was not present for the ALM Core. Adding the columns in the query designer then allowed it to display attributes like Application Suite Name and Version that match the query for each device.

    Notice when the query returns results that the device name is displayed many times (one for each value to be returned). The more items you query on, the less clean this could look. This is where the Report Designer is needed.





    Now open up the Reports Tool: Tools | Reporting /Monitoring | Reports

    From the Reports tool Right click on "My Reports" and Choose "New Custom Report..."




    1) Give the new Report a Title and a Description.

    2) Now Select the query by using the Select function in the Query filter section.

    3) Now that the query is selected, click "Design" to open the report designer.







    First modify the report so the Device name does not repeat for every returned value.


    1) To prevent the device name from repeating for every returned value, delete (device name) from the detail section. Or click on it and change visible to false in its properties, the end result is the same.

    2 Next right click within that section and choose "Insert" | "Group Header"

    3) Drag Device Name from the Data Fields box in the upper right, and drop it into the Group Header section.

    4) Left click on the group header so you can modify its properties on the right hand toolbox.

    5) In the Data Section of the properties select Device Name from the Dropdown.

    6) Add any desired formatting to the group header as desired, such as a line or box.

    To create a line, left click on the line tool in the toolbox. Then in the section you desire, draw a line. Once done you can modify the line's properties in the right window. Giving it weight, style, and color.


    If the sections above are not correctly followed then the grouping will not work.





    Next, total the number of devices that were returned in the query.


    1) Click on Textbox on the lefthand tool box, then inside of the Detail Footer section, make a box. Label this one as Total Devices. This box will act as your label.

    2) Create another Textbox next to the label, this one can be called Count.

    3) With the Count Textbox selected, modify the properties of this box on the right window. Set the following options.


    Count Null Values= False

    Datafield= Device Name


    DistinctField= Device Name

    SummaryFunc= DCount

    SummaryRunning= All

    Summary Type= GrandTotal

    4) Add formatting such as lines or boxes to the DetailFooter to section it out.




    From here the report can be saved and previewed.

    Modify any formatting to get the report to look as desired, saving your final changes.






    Scenario 2


    What if I just want to see the count of the Application Suites for each machine?

    In the event that there is a very large amount of values being returned for each machine, the details can be removed. The functional change in this scenario is that the items from detail are removed, a count is added to the group footer and two count fields are added to the detail footer. This example takes the report designed in Scenario 1 and modifies it further.


    1) Delete the Objects from the Detail section.

    2) Use the group footer section to total application suites by machine in the same method used in scenario one.

    3) Then total the devices and application suites in the detail footer. This way, only total counts are returned on a per-machine basis, instead of several pages of detailed information.

    4) Click the Group Header bar, for the properties set data field to Application Suite.

    5) Select the count object within the group footer to change its properties.

    With the Count Textbox selected, modify the properties of this box on the right window. Set the following options.


    Count Null Values= False

    Datafield= Application Suite


    DistinctField= Application Suite

    SummaryFunc= Count

    SummaryGroup= Group Header1

    SummaryRunning= Group

    Summary Type= Sub Total

    Some additional formatting will be needed when using the group footer instead of detail.


    Example of the what the query designer would look like:



    In the example image above note that Items from the group header and footer are moved to the vertical center. This is because the report designer reserves space for the detail section, which is unused. This helps the format and spacing.


    The Final Result




    Once saved, the report can be scheduled to be published by selecting that option when right clicking the report.

    This creates a new scheduled task, you can right click on it to choose properties and make any necessary changes. This will output a pdf at the recipient location.


    Notes :

    Depending on report requirements, it can be useful to show all application suites either by all machines or a set of machines. In this case the query will need to change to either device ID exists, or can be set to a range of IPs or where a machine name is like X. This type of query returns application suites for all or a specific set of machines.

    When formating, note that deleting sections or making sections visible=false can have negative effects on the overall report formatting. It is suggested to just make the objects invisible or delete them instead.