Using query markers

I have a map with multiple queries - all requiring the same date range but against different date columns.

My goal is to use the ? in the BETWEEN part of the WHERE clause. So far, I've not been able to get the ? to work outside of the map. When in IO Design on the map, and click Data Preview, I get a pop up requesting a date and it works fine. But when using that map as a Data Source, forget about it.... I don't get an error, don't get a result set. And that's with a simple, one date replacement.

So, there are two questions here, one, how to make the rptdesign work with a single ? and how to get a complex map to work with two ? used in several related queries.

fyi: Actuate BIRT Report Designer Professional
Version: 2.3.2
Build id: 20100416

Any help is appreciated...

Find more posts tagged with

Comments

mwilliams

If you have two report parameters, one named startDate and one named endDate, you should be able to link these to your dataSet parameters that are created when you use a query marker, '?'. You'd link them in the parameters section of the dataSet editor. You can link these same report parameters to the dataSet parameters in multiple dataSets. Hope this answers your question. Let me know.

leoj

Each sub query has a requirement for a date range so there will be 2 ? in each subquery - BIRT wants to make a parameter for each - a total of 16 parameters...

example:

(
--Assigned stuff
-- CANCELLED APPs
-- Denied stuff
SELECT stuff
FROM aTable
JOIN aNotherTable ON
aNotherTable .ID1 = aTable.ID1
WHERE (b1_alt_id like '%APP%')
AND b1_appl_status = 'Denied'
AND aTable.STATUS_DATE BETWEEN ? AND ?

) RESULTS1,
(
-- CANCELLED APPs
-- Denied stuff
SELECT stuff
FROM aTable
JOIN aNotherTable ON
aNotherTable .ID1 = aTable.ID1
WHERE (b1_alt_id like '%APP%')
AND b1_appl_status = 'Denied'
AND aTable.STATUS_DATE BETWEEN ? AND ?

) RESULTS2,

(
-- Denied stuff
SELECT stuff
FROM aTable
JOIN stillAnotherTable ON
stillAnotherTable .ID1 = aTable.ID1
WHERE (b1_alt_id like '%APP%')
AND b1_appl_status = 'Denied'
AND stillAnotherTable.some_other_DATE_col BETWEEN ? AND ?

) RESULTS3

mwilliams

Ah, I see what you're saying now. Since all of the where clauses end in "BETWEEN ? AND ?", you could replace this with a marker, like parameterClause or something. Then, you can replace this marker in your query in your beforeOpen script of your dataSet with the report parameters. You won't have any dataSet parameters in this case, so you won't have to deal with BIRT wanting 16 of them. You'll just run a replace all on your queryText replacing the marker with your actual check. Hope this helps.

leoj

Great idea! Ok, haven't done any scripting in BIRT, but that makes sense.

Thanks for the quick response!

mwilliams

You're welcome. Let me know if you run into any issues with this. Also, let us know whenever you have questions!

Tubal

Why not just use $1 and $2? After you declare them once, you can just use $n to specify the parameter.

(
--Assigned stuff
-- CANCELLED APPs
-- Denied stuff
SELECT stuff
FROM aTable
JOIN aNotherTable ON
aNotherTable .ID1 = aTable.ID1
WHERE (b1_alt_id like '%APP%')
AND b1_appl_status = 'Denied'
AND aTable.STATUS_DATE BETWEEN ? AND ?

) RESULTS1,
(
-- CANCELLED APPs
-- Denied stuff
SELECT stuff
FROM aTable
JOIN aNotherTable ON
aNotherTable .ID1 = aTable.ID1
WHERE (b1_alt_id like '%APP%')
AND b1_appl_status = 'Denied'
AND aTable.STATUS_DATE BETWEEN $1 AND $2

) RESULTS2,

(
-- Denied stuff
SELECT stuff
FROM aTable
JOIN stillAnotherTable ON
stillAnotherTable .ID1 = aTable.ID1
WHERE (b1_alt_id like '%APP%')
AND b1_appl_status = 'Denied'
AND stillAnotherTable.some_other_DATE_col BETWEEN $1 AND $2

) RESULTS3

mwilliams

Thanks for the alternate solution, Tubal!