Report-Manager - Extended Rackreport - Capacity in racks with location

um3n

Hey guys,
I have to admit directly that my SQL is unfortunately only sufficient for simple SQL selects and such. Therefore I fail to adjust the report "Available capacities of all cabinets".
I only need one additional column to show the location of the racks.

SELECT
isys_obj__id AS '__id__',
isys_obj__title AS 'Schrank',
isys_obj__sysid AS 'SYS-ID',
SUM(
    IF(
        !ISNULL(
            isys_catg_location_list__pos
        )
        AND isys_catg_location_list__pos != 0, 1, 0
    )
) AS 'Anzahl Elemente',

CONCAT(
    IF(
        !ISNULL(rack.isys_catg_formfactor_list__rackunits),
        rack.isys_catg_formfactor_list__rackunits - SUM(
            IF(
                ISNULL(
                    isys_catg_location_list__pos
                )
                OR isys_catg_location_list__pos = 0
                , 0, dev.isys_catg_formfactor_list__rackunits
            )
        ),
        rack.isys_catg_formfactor_list__rackunits
    )
    , ' HE'
) AS 'Verbleibende Kapazitaet'

FROM isys_obj
INNER JOIN isys_obj_type ON isys_obj_type__id = isys_obj__isys_obj_type__id
INNER JOIN isys_catg_formfactor_list rack ON rack.isys_catg_formfactor_list__isys_obj__id = isys_obj__id
LEFT JOIN isys_catg_location_list ON isys_catg_location_list__parentid = isys_obj__id
LEFT JOIN isys_catg_formfactor_list dev ON dev.isys_catg_formfactor_list__isys_obj__id = isys_catg_location_list__isys_obj__id
WHERE isys_obj_type__const = 'C__OBJTYPE__ENCLOSURE'
AND isys_obj__status =2
GROUP BY isys_obj__id

Would be great if someone could provide the cue. Thanks for the support and kind regards.

Ulli

Philipp Hörselmann

Hi @um3n
I'm working on a solution
If I'm ready, I will let you know und publish the solution.

Cheers,
Phil