SQL Server procedure being called from iReport

[CaptnTony]

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

[CaptnTony]

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).

[CaptnTony]

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.

[mdahlman]

CaptnTony,

 

If your overall goal is to get a crosstab-style report, then it's likely that this will be easier to do using the Crosstab object in iReport rather than writing the query to do the grouping and subtotaling itself.

 

If the crosstab is working correctly in the summary but not in other sections, then it's a standard issue. Putting the crosstab in the detail band is nearly always bad: you don't want 1000 crosstabs, just one with data summarized from 1000 rows. (You probably already knew this one.) Putting the crosstab in the title band makes sense... but if you are trying to use your main query as the source for the crosstab then there is no data yet when the title band is filled. Create a subdataset containing the main query and use this as the datasource for the crosstab. Putting the crosstab in the summary band is often the simplest solution. That way you already have all of the data, so everything works as expected.

 

You wrote, "I'd like to be able to provide a group expression so the information is broken down a bit more". This should be possible with the iReport crosstab object. The wizard you use to create the crosstab is hard-coded to allow only 2 row groupings and 2 column groupings. This is enough for a big majority of crosstabs. But if you need more groupings you can add them to the crosstab after you finish with the wizard. Likewise, you can add more measures (this is more common).

 

Where is the built-in crosstab falling short of your needs? Perhaps there's a way to get it to do what you want.

 

Regards,
Matt

[CaptnTony]

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.

[CaptnTony]

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.

[mdahlman]

You need to put double carriage returns in your original posts to get the paragraphs to show up in the published post. (Not shift+return, which creates a br, but regular carriage returns. Actually, double shift+return might also work... I haven't tried that.)

 

Yes, I'm serious. No, that doesn't make any sense. But there you go.

 

-Matt

[mdahlman]

It sounds like your solution to do the heavy lifting on the database side makes a lot of sense.

 

Regarding the stored procedure: I agree that it's strange for it to work in one place (Query Analyzer) but not in iReport. In general SPs work fine in iReport. The JR Definitive guide mentions these requirements:
"The stored procedure must return a java.sql.ResultSet when called through JDBC."
"The stored procedure cannot have OUT parameters."

 

Oracle stored procedures require an OUT parameter, so they're the best known exception. There's a project that was created to solve this issue here. But you're using MS SQL Server... so that shouldn't be the problem you're hitting.

 

Post again when you try investigate further. I'll be interested to figure out what is causing the problem.

 

Regards,
Matt

[CaptnTony]

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