christrueman

you could try the sql where table.column like $P{PROMPT}||'%'

Try this. Hope it helps and that I've put the right files up Post Edited by christrueman at 12/09/2009 17:09

Here is an example that works for me on a prompt "From " + (new SimpleDateFormat("dd/MM/yyyy HH:mm")).format($P{1_first_date})

OK, thanks again for the replies. I come from a background of Business Objects where to do this sort of thing is very simple. Business objects understand the relationships of the returned data and automatically accumulates measure objects whilst grouping dimension objects. Simply copying the report table and deleting column 2 would give the first table (vice versa for the second). I'll just have to change my way of thinking! I have successfuly created the report based on your previous post. Even made it look good! If I get some time I'll try the variable approach, after the one of investment I may be able to re-use the code aganist other reports. Life is good Chris

Many thanks, I was thinking that my SQL would return 1 book and 1 newspaper on the 15th, 5 newspapers and 1 postcard on the 16th etc therefore I could sum nr_of_records in relation to column1 to get sales by date and sum nr_of_records in relation to column2 to get sales by type. Is this what you mean by creating some variables to tease out the data? If so could you point me in the right direction? Whilst the example seems simple the actual data means a long run time and I would expect to face this challenge many times if I was to take on Jaspersoft Thanks again for the reply

I'm trying to run a report showing two tables/lists from one run of SQL and I'm not sure whether this is possible or the correct procedure to use. I would appreciate any help. SQL Select column1, column2, count(1) as nr_of_records from table group by column1, column 2 I would like one report to show column1 and sum(nr_of_records) and the other to show column2 and sum(nr_of_records) Can I sum all the records and display column1 nr_of_records column2 15/06/2009 2 books 12 16/06/2009 6 postcards 3 17/06/2009 4 newspapers 13 18/06/2009 8 Total 28 19/06/2009 1 20/06/2009 3 21/06/2009 4 Total 28 I can run two seperate SQL statements and subreports but this would not be very efficient Hope someone can help Chris