shobhit.nirala Posted January 9, 2013 Share Posted January 9, 2013 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 typeSELECT 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 feildPlease help to print reports by suggesting alternative method or correct my method Link to comment Share on other sites More sharing options...
Solution lisacbeaton Posted January 9, 2013 Solution Share Posted January 9, 2013 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. Link to comment Share on other sites More sharing options...
sanbez Posted January 10, 2013 Share Posted January 10, 2013 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 ? Link to comment Share on other sites More sharing options...
shobhit.nirala Posted February 15, 2013 Author Share Posted February 15, 2013 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 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now