How to Print multiple Counts in Single Report

[shobhit.nirala]

I have two tables "Device" and "Location"

I have to print counts in one report on the basis of device_state from device and device_location from locaiton.

Below are two query which I am using using to calculate count of one type

SELECT count(*) FROM device A INNER JOIN location B ON A.device_key=B.device_key WHERE A.device_deleted IS NULL AND A.device_state IN ('Test','Maintenance','Decommissioned','Production','Pre-Production') AND B.device_location LIKE '%UK%';

SELECT count(*) FROM device A INNER JOIN location B ON A.device_key=B.device_key WHERE A.device_deleted IS NULL AND A.device_state IN ('Test','Maintenance','Decommissioned') AND B.device_location LIKE '%ITALY%';

I have to execute 6 queries like above for different outputs

I tried by including dataset but it is not working as $F get overrided by first value.

Also I've tried by doing union , It is getting printed but though value all values get stored in a single variable so not able to perform +&- on different count results also as detail band executed six time so not able to put statis text feild

Please help to print  reports by suggesting alternative method or correct my method

[lisacbeaton]

If you know the device locations and their corresponding state options, I would do the following:

- create a parameterized report that will accept $P{location}. In that report, create another parameter called $P{state_select_sql}. Create a definition for $P{state_select_sql} to build your A.device_state IN clause. For example --

$P{location} == 'UK' ? "('Test', 'Maintenance'...)" : ($P{location} == 'ITALY' ? "do this" : keep going until you get them all)

Insert $P!{state_select_sql} into your query in the WHERE clause. Then you'll have a report that can work for any location.

Next, create an outer "wrapper" report. Create 6 detail bands and add your parameterized report to each detail band. Pass in the location to each as a parameter. The outer wrapper report will run the parameterized report for each location in one final report.

[sanbez]

put screen desired report. Which columns header in this report? Is it 'UK', 'ITALY' and so on?

Which table.field must be display on first (left) column? Is it device.device_state ?

[shobhit.nirala]

Hi Lisa,

 

Thanks alot for your reply, I collect location data in sql query itself by using "case", and print all different query count(*) results within one Detail band by using different list and assign them to different datasets

 

Once again many thanks for your reply