CaptnTony

Can you post the SQL you're using? Which database are you using? What is the column type for the date column you are trying to retrieve (in the database)?

is this a calculation based on data returned from the dataset? If so, I might move the calculation to the data definition so that you can sort it there (and only return negative values) and display it in your report. Hope this helps.

I create a connection parameter in the master report and pass it to the subreport. Has worked when I need to go to various SQL Server databases so I'm guessing it should work for you as well.

Personally, I'd let the database do the data management and let the report engine report on what is returned. This means that I might use the IsNull() in my SQL Stmt. Syntax: Select IsNull(<field>, 'N/A') AS MyValue From <tablename> Where <constraints> In this case, if my <field> contained a null value, the query would return 'N/A' instead of null. Hope this helps. Post Edited by G. Todd Frahm at 01/12/09 21:28 Post Edited by G. Todd Frahm at 01/12/09 21:28

I don't think you can recall "parameters" from a database as they wouldn't be parameters any longer. You may be able to retrieve a record set using a Master/Subreport, then pass the information gathered in the Master through to the subreport as use them as parameters there to format things. I'm not sure there is such a thing as 'style sheets' for JasperReports yet, might be nice to have. Or, if you're using java or something to start the report, you can have the application read the parameters from a table and pass them into your report so you can try to use them to set formats. Post Edited by G. Todd Frahm at 01/09/09 18:38

run acrobat reader as an external call from within java passing the filename as the parameter maybe?

NO idea really...just pulling something out of my backside for chuckles... Start subreport on 'new page'? :] Not sure the export utility recognizes that as the key to make a new sheet though.

I ended up getting a xtab 'engine' on my sql 2000 server which produces a standard dataset to be given to iReport. I can then do the alternate colors in the detail section w/o having to deal with the iReport xtab control. I found a couple and ended up paying something like 49USD for the dll I got. I've been impressed by it so far, so worth the money (imo).

I haven't done any graph reports yet, so I really don't know how they effect the master report. But I do a LOT of master / sub reports. The data source for the Master report is generally something like SELECT GETDATE() as GeneratedOn. I've also used SELECT 1 AS temp just to give it something. Hope that gives you something to try that may not be as memory intensive.

Is this still the accepted method? It doesn't seem to work for me. I have a rather long varchar column (up to 4096 chars) that is used to store instructions. These instructions can be a simple one line action or a long list of activities. I can't seem to get the textfield to stretch and make the detail section grow properly. I don't want to have this HUGE textfield "just in case" and then have the other information 3 pages further in the report for the 1 line instructions. Any ideas are appreciated. Thanks in advance.

Ah, yes. I Have many a report (even though they are 'containers' for subReports - they need no data as a source) that have SELECT 1 AS temp (that way nothing has to travel to/from the database server for naught) as datasource :)

I'm still working on it :) But I had to try the double carriage return thing. Post Edited by G. Todd Frahm at 12/09/08 16:04

Sorry, got pulled into a time sensitive project. I've never run into this sort of problem unless I put the subReport into the detail (or group header/footer) of a master report that returns multiple rows as its data source. For instance: Master Report data source: SELECT * FROM tbl WHERE rowID BETWEEN 0 AND 10 Then in the Master's Detail section I insert the subReport (subA) and have it return a calculated total(s) (mTotalA) back to the master variable (mSubReportA_Total) using a Calculation Type of SUM on the definition of mSubReportA variable. . Admittedly, I'm no expert in reading the xml, but it looks to me that the variables are all defined as java.lang.Double, so there shouldn't be any weird conversions going on there. . With that, I'm not sure what might be causing it unless it's a layout issue, sorry.

Do you have the reports that can be uploaded here, I'll try it in mine to see if I can see anything. Someone here is probably better able to discuss whether / what data is available in when the header of a report is processed/filled with data. I'm guessing that your query isn't processing in the manner/order you think it is. I generally use subreports (mine are filled with data dependent upon 'current' data in the parent report though) in the detail and summary sections.

PS, how are you getting the carriage return in your posts (I've been trying, with various success putting a Shift-Enter and a period on the empty line). Didn't work this last time it appears. Sorry.

Thanks Matt, I didn't mind the crosstab object at all. . Maybe it can work and I just haven't 'seen' it designed in my head yet. Let me explain what I'm doing. To begin with, I'm creating a month end summary report of sales information (Monthly Long Distance revenue, Monthly Local Service Revenue <--the one I'm trying to use a crosstab in, Monthly Misc Charges revenue, and a summary of Taxes collected). . The subreport in question here is the Local Service Revenue report. There are multiple categories that have to be summarized (Voice, Data, and Mixed purpose) Local Services. Within each of test categories there are different price models that we want to see totals for (Resale-Vendor A, Resale-Vendor B, ..., Facility, Other...). Fore each of these local service category/price model combonations, we'd like to produce subtotals for the actual service charges (4 categories currently). . I have successfully created a data grid from the crosstab object ordered by Classification, PriceModel and then having columns for each SUM(Charge). The wizard was quite straight forward and easy to use. It just displayed the results as one grid with totals at the bottom and right (nice touch) (if I recall correctly). What I was hoping to be able to do is create a dataset on the server and pass it to iReport so I could specify the grouping on Classification. I could then get subTotals using a report variables for each of the calculated charge columns and a grand total at the bottom using another set of report variables. . I finally found a tool that works very well to create the crosstab data on the server. It cost less than $50USD and has a tool/wizard to help design the stored procedure call parameters (syntax). I now create a temp table on the server and update the necessary information and pass it back to iReport by calling a stored procedure as the iReport data source. Works very well, very little data passed back and forth, and I get the advantage of using the server to perform the xtab rather than a 'slow' (never know what a user has loaded) workstation. We're dealing with approximately 500k rows each month, so data transfer is a significant consideration (though the localized creation of the crosstab information in the crosstab control was VERY fast). . I will definately use the crosstab control in the future, it just wasn't quite what I was looking for in this instance. . I still wonder why the stored procedure would work in SQL query analyzer but not in the iReport data source. I'm sort of done with it, but I'll get back to it again when I have some time and get a chance to run through a set of tests to help me figure out what would cause it to not process the sp the way I expect it would.

Hash table for multiple values in a single passed in variable maybe? I guess I'm not sure as I've never run into that issue before. Second, I use -- as my comment indicator in the datasource area. Hope it helps a little.

Hey, took me a full day of pulling hair out to think about that. Glad it helps out. Wait until they want to run stored procedures/functions :> I like those a LOT more than putting the code in the report. Just a thought. . . Oh, as far as seeing what the query is when you 'run' it. I haven't found a way to do that either. I generally watch what it's doing by looking at the query profiler in MS SQL. Post Edited by G. Todd Frahm at 12/04/08 15:13

Ok, as an update. I implemented the Crosstab object. Not exactly what I was hoping for. I'd like to be able to provide a group expression so the information is broken down a bit more (nicely formatted, group subtotals etc). The only way I found to implement it was by putting it in the Summary Section so I'll keep looking for hacks to make MS SQL 2000 produce a dataset that has been xtab'd.

will have to do something like new java.lang.date(....) as the default value expression I think.

do you truly want to be doing a SUM on the variable returned (calling the subreport multiple times). If so, are you returning the same value each time (ten times), by mistake?

Ok, I tried to manually add the fields so I didn't have to 'discover' them. I received the same error message. BUT! While I was looking around the toolbar I found the Crosstab object. I may try to get that to create the crosstab from the table data rather than using the 'hack' procedure to do so. I'd still be interested in hearing why something will run in the Query Analyzer but not in iReport (if anyone has bumped into same issue and solved it).

I have developed (ahem, copied from the web actually) a stored procedure that runs perfectly well in the SQL Query Analyzer but I get an error while tryig to run it from within an iReport file. I have many procedures that I've written that work fine, but this one doesn't. I've shut down iReport and the computer with no success. I wish I wrote the script to do the xtab but I didn't. Therefore, I'm not sure what needs to be modified as it's a typical 'hack' job to make SQL 2000 produce a XTAB query result. . . The question is, what might cause something to run just fine in the Query Analyzer but not execute in iReport. The offending procedure is actually a procedure that is called by the procedure I'm executing from iReport. I did try to run the offending procedure from within a new report with no success either. I get the error message when I'm trying to retrieve the fields. . . Brain kicked in. Is it because the fields returned by the XTAB hack procedure are undefined until the procedure is executed? If that's the case has anyone figured out a way around it. I know what the columns are (in this case the width isn't variable). Thanks in advance. Post Edited by G. Todd Frahm at 12/03/08 18:57 Post Edited by G. Todd Frahm at 12/03/08 18:58

Thanks Steve. I've been trying to figure this one out on and off for quite some time now. I've never used a frame, but I'll find it somewhere and work with it.

WHERE projnum LIK '%' + '$P!{PN}' + '%' iReport will quite literally replace the contents of the $P!{PN} with your parameter, so since they are strings (Like '%xxxx%' for instance) you must have your parameter placed inside single quotes. Hope that helps.