Hiding a Row Depending on Values in Different Datasets

This blog explains the significance of returning values from one dataset to the other dataset using subreport.

It would explain the problem and walks you through the solution.

Problem:

The initial report which is the combination of 2 tables i.e.Table 1 and Table 2 is as shown.

Actual Output:

Employee and current are in Table 1 and YTD is in Table 2

Table 1 Query :

select  ‘Annie’ as Employee, 1000 as Current

union

select ‘Bunny’ as Employee, 0 as Current

union

select ‘Sunny’ as Employee,0 as Current

Table 2 Query :

select  1000 as YTD

union

select 800 as YTD

union

select 0 as YTD

A row which has both current and YTD values as 0 need to be hidden.Since both values are from different datasets, writing a condition in Print When Expression field is not feasible. To solve this problem, it is better to use a Subreport and return a value instead of using 2 tables. Writing an expression in a field itself would provide a solution.

Solution:

• Create a new report using Table 1 Query as the main dataset query.
• Pull the fields in detail band as shown.

The output is as shown.

• Add a Subreport in detail band and add a text field ‘YTD’ in column header such that it acts as heading for the field in subreport as shown.

• Create a Parameter ‘Employee’ in the subreport.
• Modify Table 2 Query and use it in a subreport.

Modified Table 2 Query :

Select a.YTD from

(select  ‘Annie’ as Employee,1000 as YTD

union

select ‘Bunny’ as Employee,800 as YTD

union

select ‘Sunny’ as Employee,0 as YTD

)  a

where Employee=$P{Employee}

Here, field Employee is passed as a Parameter to subreport from main report i.e. YTD value of particular employee only gets displayed.

• Subreport created is as shown.

• To make a connection between the main report and subreport such that parameter ‘Employee’ is passed, click on Subreport–>click on Edit parameters and add a parameter as shown.

• Create a variable ‘YTD’ in the main report.
• Create a variable ‘ytd value’ in subreport and use ‘$F{ytd}==0?”Zerovalue“:”Nonzerovalue“’ as expression.

This variable gives ‘Zerovalue’ if the YTD value is 0 else it gives ‘Nonzerovalue’.

• Click on YTD field in the subreport and write an expression ‘$V{ytd value}.equals(“Nonzerovalue“)’ in Print When Expression i.e. YTD value is displayed only when it is a non zero value.
• Click on Subreport and click on ‘Edit Return Values’. Enter variable name created in subreport as from variable as shown.

• Use Expression ‘$V{YTD}.equals(“Nonzerovalue“)?$F{employee}:””’ in place of text field ‘employee’ and ‘$V{YTD}.equals(“Nonzerovalue“)?$F{current}:””’ in place of text field ‘current’ in the main report.

i.e. restricting the display of Employee and Current values based on the value of YTD in the subreport.

• Set Evaluation time as ‘Band’ for the above 2 fields.
• After the above-mentioned changes are made, the report is as shown.

• The final output of the report is as follows:

Best Open Source Business Intelligence Software Helical Insight is Here

A Business Intelligence Framework

Free
Download

In Case, if you have any queries please get us at support@helicaltech.com

Thank You

Mounika

Helical IT Solutions Pvt Ltd