Thanks Augarte , this is my jrxml file.
What I have done in the report:
1 - I have created the main report (sql query SELECT 1 from DUAL)
2 - create 12 different datasets
3 - create 12 detail band, in each band 1 table (each table datasource corresponds to 1 dataset)
Whenever a table is empty, I want the band to be hidden. Otherwise I get empty lines in the report.
Thank you.
+++++++++++ JRXML FILE +++++++++ For some reason I am not able to post an answer ++++++++++++++
<?xml version="1.0" encoding="UTF-8"?>
select SUM(iaas.init_qu - iaas.cancel_qu), service.finalclass as Service, operation.name as Operation, MONTHNAME(ch.date) as Month, 'Signed' as 'Signed'</p><p>
from itop_serviceiaas iaas inner join itop_service service on iaas.id=service.id inner join itop_operationfamily operation on service.operationfamily_id=operation.id inner join itop_priv_changeop changeop on changeop.objkey=service.id inner join itop_priv_change ch on changeop.changeid=ch.id WHERE changeop.optype = 'CMDBChangeOpCreate' AND changeop.objclass=service.finalclass group by Month,Operation;
select SUM(iaas.init_qu - iaas.cancel_qu), service.finalclass as Service, operation.name as Operation, MONTHNAME(ch.date) as Month, 'Installed' as 'Installed'</p><p>
from itop_serviceiaas iaas inner join itop_service service on iaas.id=service.id inner join itop_operationfamily operation on service.operationfamily_id=operation.id inner join itop_priv_changeop changeop on changeop.objkey=service.id inner join itop_priv_change ch on changeop.changeid=ch.id WHERE changeop.optype = 'CMDBChangeOpCreate' AND changeop.objclass=service.finalclass AND (service.status='Completed' OR service.status='Delivered') group by Month,Operation;
]]></fieldDescription></p><p>
</field></p><p>
</subDataset></p><p>
<subDataset name="vmstotals" uuid="d229988a-8515-4ddb-b3a5-6914978fde71"></p><p>
<queryString></p><p>
<![CDATA[select service.finalclass as Service, SUM(iaas.init_qu-iaas.cancel_qu) as 'Active VMs', operation.name as Operation, 'Total' as 'Total', 'Signed' as 'Signed' from itop_service service</p><p>
inner join itop_operationfamily operation on service.operationfamily_id=operation.id</p><p>
inner join itop_serviceiaas iaas on iaas.id=service.id WHERE service.status!='Terminated' group by operation.name;
select service.finalclass as Service, SUM(iaas.init_qu-iaas.cancel_qu) as 'Active VMs', operation.name as Operation, 'Total' as 'Total', 'Installed' as 'Installed'</p><p>
from itop_service service</p><p>
inner join itop_operationfamily operation on service.operationfamily_id=operation.id</p><p>
inner join itop_serviceiaas iaas on iaas.id=service.id WHERE (service.status='Completed' OR service.status='Delivered') group by operation.name;
]]></fieldDescription></p><p>
</field></p><p>
</subDataset></p><p>
<subDataset name="spacetotal" uuid="6e2882d9-8654-4fbf-b305-84bb385527cc"></p><p>
<queryString></p><p>
<![CDATA[select service.finalclass as Service, SUM(space) as 'Space', operation.name as Operation, 'Total' as 'Total', 'Signed' as 'Signed'</p><p>
from itop_service service</p><p>
inner join itop_operationfamily operation on service.operationfamily_id=operation.id</p><p>
inner join itop_servicecolocspace space on space.id=service.id WHERE service.status!='Terminated' AND (space.nracks=0 OR space.nracks IS NULL OR space.nracks='')</p><p>
group by operation.name;
]]></fieldDescription></p><p>
</field></p><p>
<field name="Operation" class="java.lang.String"/></p><p>
<field name="Total" class="java.lang.String"/></p><p>
<field name="Signed" class="java.lang.String"/></p><p>
</subDataset></p><p>
<subDataset name="spacetotalinstalled" uuid="5ef73cea-3df2-4abe-807d-b422de6a7324"></p><p>
<queryString></p><p>
<![CDATA[select service.finalclass as Service, SUM(space) as 'Space', operation.name as Operation, 'Total' as 'Total', 'Installed' as 'Installed'</p><p>
from itop_service service</p><p>
inner join itop_operationfamily operation on service.operationfamily_id=operation.id</p><p>
inner join itop_servicecolocspace space on space.id=service.id WHERE (service.status='Completed' OR service.status='Delivered') AND (space.nracks=0 OR space.nracks IS NULL OR space.nracks='')</p><p>
group by operation.name;
]]></fieldDescription></p><p>
</field></p><p>
<field name="Operation" class="java.lang.String"/></p><p>
<field name="Total" class="java.lang.String"/></p><p>
<field name="Installed" class="java.lang.String"></p><p>
<fieldDescription><![CDATA[
select SUM(space), service.finalclass as Service, operation.name as Operation, MONTHNAME(ch.date) as Month, 'Signed' as 'Signed' from itop_servicecolocspace space inner join itop_service service on space.id=service.id inner join itop_operationfamily operation on service.operationfamily_id=operation.id inner join itop_priv_changeop changeop on changeop.objkey=service.id inner join itop_priv_change ch on changeop.changeid=ch.id WHERE changeop.optype = 'CMDBChangeOpCreate' AND changeop.objclass=service.finalclass AND (space.nracks=0 OR space.nracks IS NULL OR space.nracks='') group by Month,Operation;
select SUM(space), service.finalclass as Service, operation.name as Operation, MONTHNAME(ch.date) as Month, 'Installed' as 'Installed' from itop_servicecolocspace space inner join itop_service service on space.id=service.id inner join itop_operationfamily operation on service.operationfamily_id=operation.id inner join itop_priv_changeop changeop on changeop.objkey=service.id inner join itop_priv_change ch on changeop.changeid=ch.id WHERE changeop.optype = 'CMDBChangeOpCreate' AND changeop.objclass=service.finalclass AND (space.nracks=0 OR space.nracks IS NULL OR space.nracks='') AND (service.status='Completed' OR service.status='Delivered') group by Month,Operation;
]]></fieldDescription></p><p>
</field></p><p>
</subDataset></p><p>
<subDataset name="rackstotal" uuid="84d6fc21-64c3-4c33-88a5-ebe1ec34ac8f"></p><p>
<queryString></p><p>
<![CDATA[select SUM(numracks), Service,Operation,'Total' as 'Total','Signed' as 'Signed' from</p><p>
(select service.finalclass as Service, SUM(case rack.init_qu WHEN '1' then 1 WHEN '0.5' then 0.5 WHEN '0.25' THEN 0.25 WHEN '0' then 0 END)-SUM(case rack.cancel_qu WHEN '1' then 1 WHEN '0.5' then 0.5 WHEN '0.75' then 0.75 WHEN '0.25' THEN 0.25 WHEN '0' then 0 END) AS numracks , operation.name as Operation from itop_service service join itop_operationfamily operation on service.operationfamily_id=operation.id inner join itop_servicecolocrack rack on rack.id=service.id WHERE service.status!='Terminated' group by Operation</p><p> </p><p>
UNION</p><p> </p><p>
select service.finalclass as Service, SUM(space.nracks) AS numracks, operation.name as Operation from itop_service service</p><p>
inner join itop_operationfamily operation on service.operationfamily_id=operation.id</p><p>
inner join itop_servicecolocspace space on space.id=service.id WHERE service.status!='Terminated' group by Operation</p><p>
)c group by Operation
select SUM(numracks), Service,Operation,'Total' as 'Total','Installed' as 'Installed' from</p><p>
(select service.finalclass as Service, SUM(case rack.init_qu WHEN '1' then 1 WHEN '0.5' then 0.5 WHEN '0.25' THEN 0.25 WHEN '0' then 0 END)-SUM(case rack.cancel_qu WHEN '1' then 1 WHEN '0.5' then 0.5 WHEN '0.75' then 0.75 WHEN '0.25' THEN 0.25 WHEN '0' then 0 END) AS numracks , operation.name as Operation from itop_service service join itop_operationfamily operation on service.operationfamily_id=operation.id inner join itop_servicecolocrack rack on rack.id=service.id WHERE (service.status='Completed' OR service.status='Delivered') group by Operation</p><p> </p><p>
UNION</p><p> </p><p>
select service.finalclass as Service, SUM(space.nracks) AS numracks, operation.name as Operation from itop_service service</p><p>
inner join itop_operationfamily operation on service.operationfamily_id=operation.id</p><p>
inner join itop_servicecolocspace space on space.id=service.id WHERE (service.status='Completed' OR service.status='Delivered') group by Operation</p><p>
)c group by Operation
]]></fieldDescription></p><p>
</field></p><p>
</subDataset></p><p>
<subDataset name="rackstotalpermonth" uuid="b814df13-d94a-4f54-8fe4-2222b8a0ab89"></p><p>
<queryString></p><p>
<![CDATA[select SUM(Racks),Service,Operation,Month,'Signed' as 'Signed' from</p><p>
(</p><p>
select SUM(case rack.init_qu WHEN '1' then 1 WHEN '0.75' then 0.75 WHEN '0.5' then 0.5 WHEN '0.25' THEN 0.25 WHEN '0' then 0 END)- SUM(case rack.cancel_qu WHEN '1' then 1 WHEN '0.5' then 0.5 WHEN '0.25' THEN 0.25 WHEN '0' then 0 END)</p><p>
AS Racks ,</p><p>
MONTHNAME(c.date) as Month,</p><p>
service.finalclass as Service,</p><p>
operation.name as Operation</p><p>
from itop_priv_changeop changeop</p><p>
inner join itop_priv_change c on changeop.changeid=c.id</p><p>
inner join itop_servicecolocrack rack on rack.id=changeop.objkey</p><p>
inner join itop_service service on rack.id=service.id</p><p>
inner join itop_operationfamily operation on service.operationfamily_id=operation.id WHERE changeop.optype = 'CMDBChangeOpCreate' AND changeop.objclass=service.finalclass</p><p>
group by MONTHNAME(c.date), operation.name</p><p> </p><p>
UNION</p><p> </p><p>
select SUM(space.nracks)</p><p>
AS Racks ,</p><p>
MONTHNAME(c.date) as Month,</p><p>
service.finalclass as Service,</p><p>
operation.name as Operation</p><p>
from itop_priv_changeop changeop</p><p>
inner join itop_priv_change c on changeop.changeid=c.id</p><p>
inner join itop_servicecolocspace space on space.id=changeop.objkey</p><p>
inner join itop_service service on space.id=service.id inner join itop_operationfamily operation on service.operationfamily_id=operation.id WHERE changeop.optype = 'CMDBChangeOpCreate' AND changeop.objclass=service.finalclass</p><p>
group by MONTHNAME(c.date), operation.name</p><p>
)a group by Month, Operation
select SUM(Racks),'Colocation_Rack' as Service,Operation,Month, 'Installed' as 'Installed' from</p><p>
(</p><p>
select SUM(case rack.init_qu WHEN '1' then 1 WHEN '0.75' then 0.75 WHEN '0.5' then 0.5 WHEN '0.25' THEN 0.25 WHEN '0' then 0 END)- SUM(case rack.cancel_qu WHEN '1' then 1 WHEN '0.5' then 0.5 WHEN '0.25' THEN 0.25 WHEN '0' then 0 END)</p><p>
AS Racks ,</p><p>
MONTHNAME(c.date) as Month,</p><p>
service.finalclass as Service,</p><p>
operation.name as Operation</p><p>
from itop_priv_changeop changeop</p><p>
inner join itop_priv_change c on changeop.changeid=c.id</p><p>
inner join itop_servicecolocrack rack on rack.id=changeop.objkey</p><p>
inner join itop_service service on rack.id=service.id</p><p>
inner join itop_operationfamily operation on service.operationfamily_id=operation.id WHERE changeop.optype = 'CMDBChangeOpCreate' AND changeop.objclass=service.finalclass AND (service.status='Completed' or service.status='Delivered')</p><p>
group by MONTHNAME(c.date), operation.name</p><p> </p><p>
UNION</p><p> </p><p>
select SUM(space.nracks)</p><p>
AS Racks ,</p><p>
MONTHNAME(c.date) as Month,</p><p>
service.finalclass as Service,</p><p>
operation.name as Operation</p><p>
from itop_priv_changeop changeop</p><p>
inner join itop_priv_change c on changeop.changeid=c.id</p><p>
inner join itop_servicecolocspace space on space.id=changeop.objkey</p><p>
inner join itop_service service on space.id=service.id inner join itop_operationfamily operation on service.operationfamily_id=operation.id WHERE changeop.optype = 'CMDBChangeOpCreate' AND changeop.objclass=service.finalclass AND (service.status='Completed' or service.status='Delivered')</p><p>
group by MONTHNAME(c.date), operation.name</p><p>
)a group by Month, Operation
]]></fieldDescription></p><p>
</field></p><p>
</subDataset></p><p>
<parameter name="SUBREPORT_DIR" class="java.lang.String" isForPrompting="false"></p><p>
<defaultValueExpression><
