Can I show a band conditionally

robert.abed · July 8, 2013

Hi, I have created a report as follow:

each detail band contains 1 table. Each table is linked to 1 dataset.

Can I hide the band whenever the result set of the query returns Empty set (no result found)?

how can I achieve this?

Thanks,

rha.

augarte · July 8, 2013

Hi,

You can use "Print When Expression" to hide report bands. You have to get in a parameter of field if your query returns and empty set and set an expression as follows:

$F{data} != null ? Boolean.TRUE : Boolean.FALSE

In this case, if the field "data" is null the band in which you set the expression will be hided, otherwise it will be shown.

Hope this helps,

Regards

robert.abed · July 8, 2013

Thanks for your reply.

this is the part that I cannot figure out how to do:

"You have to get in a parameter of field if your query returns and empty set and set an expression"

augarte · July 8, 2013

Not sure if it is the best one, but a possible solution could be the following one:

You create a Variable, which counts the number of elements you have in your query. For example, imagine that you retrieve the field "Fld" in your query. You can create a variable with the following properties:

- Name: NumRow

- Calculation: Count

- Variable Expression: $F{Fld}

This variable will calculate the number of rows you have in your query, if it is 0, it means there is not any row so you can hide the band. In the "Print When Expression" you should set something like:

$V{NumRow} != 0 ? Boolean.TRUE : Boolean.FALSE

Hope this helps.

Regards

robert.abed · July 9, 2013

Thanks a lot...this is a great way to achieve this.

However, whenever I write the "Print When Expression" for a certain detail band, I am only able to to choose parameter from the main dataset and not from the subdataset...is there a way to "link" detail band and subdataset.

Thanks again for your help

augarte · July 9, 2013

Hi Robert,

I don't understand what you mean exactly with subdatasets... are you using subreports? In your first post you say that each detail contains 1 table of a dataset. Aren't you able to create a variable for each dataset and then use the same condition (with the corresponding variable in each detail band) to hide/show the band? May be attaching your jrxml will be easier to understand your problem and help you.

Regards

robert.abed · July 9, 2013

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' 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' 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> </field> </subDataset> <subDataset name="vmstotals" uuid="d229988a-8515-4ddb-b3a5-6914978fde71"> <queryString> <![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 inner join itop_operationfamily operation on service.operationfamily_id=operation.id 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' from itop_service service inner join itop_operationfamily operation on service.operationfamily_id=operation.id inner join itop_serviceiaas iaas on iaas.id=service.id WHERE (service.status='Completed' OR service.status='Delivered') group by operation.name;

]]></fieldDescription> </field> </subDataset> <subDataset name="spacetotal" uuid="6e2882d9-8654-4fbf-b305-84bb385527cc"> <queryString> <![CDATA[select service.finalclass as Service, SUM(space) as 'Space', operation.name as Operation, 'Total' as 'Total', 'Signed' as 'Signed' from itop_service service inner join itop_operationfamily operation on service.operationfamily_id=operation.id 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='') group by operation.name;

]]></fieldDescription> </field> <field name="Operation" class="java.lang.String"/> <field name="Total" class="java.lang.String"/> <field name="Signed" class="java.lang.String"/> </subDataset> <subDataset name="spacetotalinstalled" uuid="5ef73cea-3df2-4abe-807d-b422de6a7324"> <queryString> <![CDATA[select service.finalclass as Service, SUM(space) as 'Space', operation.name as Operation, 'Total' as 'Total', 'Installed' as 'Installed' from itop_service service inner join itop_operationfamily operation on service.operationfamily_id=operation.id 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='') group by operation.name;

]]></fieldDescription> </field> <field name="Operation" class="java.lang.String"/> <field name="Total" class="java.lang.String"/> <field name="Installed" class="java.lang.String"> <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> </field> </subDataset> <subDataset name="rackstotal" uuid="84d6fc21-64c3-4c33-88a5-ebe1ec34ac8f"> <queryString> <![CDATA[select SUM(numracks), Service,Operation,'Total' as 'Total','Signed' as 'Signed' from (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  UNION  select service.finalclass as Service, SUM(space.nracks) AS numracks, operation.name as Operation from itop_service service inner join itop_operationfamily operation on service.operationfamily_id=operation.id inner join itop_servicecolocspace space on space.id=service.id WHERE service.status!='Terminated' group by Operation )c group by Operation

select SUM(numracks), Service,Operation,'Total' as 'Total','Installed' as 'Installed' from (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  UNION  select service.finalclass as Service, SUM(space.nracks) AS numracks, operation.name as Operation from itop_service service inner join itop_operationfamily operation on service.operationfamily_id=operation.id inner join itop_servicecolocspace space on space.id=service.id WHERE (service.status='Completed' OR service.status='Delivered') group by Operation )c group by Operation

]]></fieldDescription> </field> </subDataset> <subDataset name="rackstotalpermonth" uuid="b814df13-d94a-4f54-8fe4-2222b8a0ab89"> <queryString> <![CDATA[select SUM(Racks),Service,Operation,Month,'Signed' as 'Signed' from ( 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) AS Racks , MONTHNAME(c.date) as Month, service.finalclass as Service, operation.name as Operation from itop_priv_changeop changeop inner join itop_priv_change c on changeop.changeid=c.id inner join itop_servicecolocrack rack on rack.id=changeop.objkey inner join itop_service service on rack.id=service.id inner join itop_operationfamily operation on service.operationfamily_id=operation.id WHERE changeop.optype = 'CMDBChangeOpCreate' AND changeop.objclass=service.finalclass group by MONTHNAME(c.date), operation.name  UNION  select SUM(space.nracks) AS Racks , MONTHNAME(c.date) as Month, service.finalclass as Service, operation.name as Operation from itop_priv_changeop changeop inner join itop_priv_change c on changeop.changeid=c.id inner join itop_servicecolocspace space on space.id=changeop.objkey 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 group by MONTHNAME(c.date), operation.name )a group by Month, Operation

select SUM(Racks),'Colocation_Rack' as Service,Operation,Month, 'Installed' as 'Installed' from ( 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) AS Racks , MONTHNAME(c.date) as Month, service.finalclass as Service, operation.name as Operation from itop_priv_changeop changeop inner join itop_priv_change c on changeop.changeid=c.id inner join itop_servicecolocrack rack on rack.id=changeop.objkey inner join itop_service service on rack.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') group by MONTHNAME(c.date), operation.name  UNION  select SUM(space.nracks) AS Racks , MONTHNAME(c.date) as Month, service.finalclass as Service, operation.name as Operation from itop_priv_changeop changeop inner join itop_priv_change c on changeop.changeid=c.id inner join itop_servicecolocspace space on space.id=changeop.objkey 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') group by MONTHNAME(c.date), operation.name )a group by Month, Operation

]]></fieldDescription> </field> </subDataset> <parameter name="SUBREPORT_DIR" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["/Users/robertabed/Desktop/Jasper/"

sELECT 1 from DUAL

Management Report

signed/Installed

Month/Total

Number

service

Operation

$F{Operation}

$F{Service}

$F{Active VMs}

$F{Total}

$F{Signed}

$P{REPORT_CONNECTION}

$F{Operation}

$F{Service}

$F{SUM(iaas.init_qu - iaas.cancel_qu)}

$F{Month}

$F{Installed}

$P{REPORT_CONNECTION}

$F{Operation}

$F{Service}

$F{SUM(iaas.init_qu - iaas.cancel_qu)}

$F{Month}

$F{Signed}

$P{REPORT_CONNECTION}

$F{Operation}

$F{Service}

$F{Active VMs}

$F{Total}

$F{Installed}

$P{REPORT_CONNECTION}

$F{Operation}

$F{Service}

$F{Space}

$F{Total}

$F{Signed}

$P{REPORT_CONNECTION}

$F{Operation}

$F{Service}

$F{Space}

$F{Total}

$F{Installed}

$P{REPORT_CONNECTION}

$F{Operation}

$F{Service}

$F{SUM(space)}

$F{Month}

$F{Signed}

$P{REPORT_CONNECTION}

$F{Operation}

$F{Service}

$F{SUM(space)}

$F{Month}

$F{Installed}

$P{REPORT_CONNECTION}

$F{Operation}

$F{Service}

$F{SUM(numracks)}

$F{Total}

$F{Signed}

$P{REPORT_CONNECTION}

$F{Operation}

$F{Service}

$F{SUM(numracks)}

$F{Total}

$F{Installed}

$P{REPORT_CONNECTION}

$F{Operation}

$F{Service}

$F{SUM(Racks)}

$F{Month}

$F{Signed}

$P{REPORT_CONNECTION}

$F{Operation}

$F{Service}

$F{SUM(Racks)}

$F{Month}

$F{Installed}

Sign In

Can I show a band conditionally

Recommended Posts

robert.abed

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

augarte

Link to comment

Share on other sites

robert.abed

Link to comment

Share on other sites

augarte

Link to comment

Share on other sites

robert.abed

Link to comment

Share on other sites

augarte

Link to comment

Share on other sites

robert.abed

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Activity

Products

Explore