Condition field non-existant

[clevelandjwb]

I am using MS Sql Server 7.0 and Crystal Reports 8.5.

I put this in the data access area because the problem has its root in data access even though the error message does not necessarily point to a data access problem.

I developed a rather complex query which involves the use of temporary tables before creating the final output table. To make testing of the both the query and report easier, I developed the query in Query Analyzer and simply created an output table. This same stored procedure is also going to have a life providing data to a WEB server so it was necessary at some point to make it a stored procedure.

I wrote and tested the report using the table created in Query Analyzer. Now I am attempting modify the report to used the stored procedure rather than the table that used to be created.

I went through "Set Location" for the database both in the report and a sub report. I fixed the linkages between the report and the sub report to use the new name. Everything looks right. The stored procedure seems to return the right data which I can see on the outline tree to the left of the report.

Before any real formatting of the report is complete I get the message: Group #1 ? - A <new line>
This group section can not printed because its condition field is non-existant or invalid. Format the section to choose another condition field <end of message>

Group #1 pulls together various reports that are going to the same person. It does not print either a header or a footer. It does not seem to have any selection formulae. Both the header and footer have "Free Form", "Hide" and "Keep Together" checked. All of the "X-2 pencil"'s are blue.

I would imagine that somehow I am still trying to reference the original data rather than the data coming back from the stored procedure.

Any help would be appreciated.

This gets worse. I did a little more testing as I proofread my post and have found that the problem might be data dependent although I can not determine what the data problem might be. In order to section a very long report into several shorter sections for printing, one pair of paramenters gives the starting letter and ending letter to select on the first character on the recipient's last name. When I run this report "a to a" I get the error message. When I run it "b to b" I get the error message. Just for fun I tried "c to c" as well as "d to d" and both of those produce a proper report ( e and f fail and g works).

John

 

[lyanch]

The group is probably grouping on a now non-existant field (pointing to the old table) it could also be a sort on a now non-existant field. Go to "change group" and check the field that it is grouping on. Also check sorting.

Lisa

 

[clevelandjwb]

This is an addendum to the previous post. I have continued to play with this, trying different things. I finally found out that the selection for the sub report did not have the proper field name. I went back and selected it and that made the first message go away. Unfortunately it was replaced with an ODBC message telling me that the stored procedure is an "invalid object name".

This can't really be an invalid object name because the correct data is being returned and changing the parameters changes what is found in the left hand column (even though it can not format any part of the report). It also shows approximately the right number of records on the status bar. It would appear that the error is on the second pass through the report.

Since the last thing I changed had to do with the linkage between the report and the sub-report the problem might be there. I changed that in the formula editing window. Since the sub report references the same stored procedure as the report the conditional expression looks a little strange and I am not aware of how Crystal would differentiate between the two instances of the same name.

Thanks for any help.

John

 

[clevelandjwb]

Lisa:

It would appear you have the first part right. Your post came to me right after my second one.

The following is what I have for the selection formula for the sub report:

{TestStatement.ownerfundid} = {?Pm-TestStatement.ownerfundid} and
{TestStatement.fundid} = {?Pm-TestStatement.fundid}

I am not familiar with the "?Pm-" notation but assume it is the field from the primary report. Both the report and the sub report use the same stored procedure. I wonder if it is trying to call the procedure for each execution of the sub report or if it "knows" to use the data stream created when the main report was run.

Thanks.

John

 

[lyanch]

Ah... yes.. it will call the SP every time the subreport is called. The ?Pm is the "add on" that crystal does for subreport parameters.

Lisa

 

[clevelandjwb]

That sounds frighteningly inefficient since the stored procedure will return the same data each time. I guess with cache it will start doing it a little more quickly. The sub report would probably be printed about 700 times (all different, it is a summary of what immediately proceeds it) in a full report.

Is there a better access method that that you can suggest. I am not familiar with using the actual Crystal access methods. Would one of them actually run the stored procedure only once and then allow both the report and the subreport to access a temporary file?

John

 

[lyanch]

The easier answer would be to return all the data the way you want it from the SP instead of needing to call it twice. If you show the data coming back, and why you need the same data twice, we can probably show you how to make it work.

Lisa

 

[clevelandjwb]

The final query is rather long but please let me describe why I need it twice and see if that helps.

The report is a statement of activity. It is organized by account and each account has multiple sub accounts, typically 8-10. In the detail section of the report, transactions are grouped by sub account and a header shows the beginning balance and a footer the ending balance.

The subreport appears in the footer for the project (after all detail sub accounts are printed). Basically the sub report produces what would match the subaccount footer in the main report, a listing of each subaccount and its balance.

The way the SP works, both the beginning and ending balance are available on every detail record and the report and subreport combination can really do with only one version of the input file. The sub report does no calculation, when the subaccount footer prints, it simply grabs the ending balance from the last detail record in the subacocunt group.

The whole reason I went from using a separately run SP creating a table for the report was the need to have the SP return records for our WEB application which will only be doing one project at a time. It will not be using Crystal for presenting the information. The reporting system will typically do about 800 at a time.

My mind is now going in the direction of writing a second stored procedure that will call the main one and simply build a database table from the output of the SP. This would then not get in the way of the main SP doing its thing. Is there a way to force Crystal to run a SP that is not actually going to return any data before Crystal starts accessing the primary data table?

 

[lyanch]

How is the data in the subreport different than the data in the main report? Can't you just use the data that is there? (or add it up etc)

 

[clevelandjwb]

That is a good question. The subreport gets all of the data that is available to the main report but really only needs a very small subset of it. The subreport data could be drawn out by a much more specific query that would deal with less data. The account balances are stored in a separate table. At the moment, the SP calculates the time period to report on but that information could be passed to the main report and I assume somehow passed to the subreport (although I don't at this point know how). Although efficiency is really not a concern in this application (when I was reporting using our accounting application this takes about 2 hours to run, the Crystal version with a separate running of the query runs in 7 minutes and if it was 15 it really would not be a problem) it would be good if the filtering of these queries could be pushed to the server. I have never developed a Crystal query (being rather a newbie at this) but the process looks like something I can handle.

If you know the answer could you tell me one thing from the last post - - is there a way to make Crystal call a stored procedure exactly once before producing the report.

Also, the description of the Crystal SQL Designer seems to indicate that it will build a file, contacting the database only once. Would this be true when executing a sub report using the same referenced Crystal Query or would the sub report cause multiple executions?

Thanks for the continuous help you have been although at the moment we have not yet solved this problem I am sure we will.

John

 

[lyanch]

If the stored procedure is used *only* for the main report, or for a subreport that is only used once, then it will only be called once.

I would not recommend using the crystal query tool, but rather create the query directly in the visual linking expert.

I still am not sure that you need a subreport at all. If you could give an example of the data the SP returns, and what you need the output to be, we can probably show you how to get that without using a subreport, especially since the same stored procedure gives the data for both.

Lisa

 

[lbass]

If your goal is to show the subaccount balances in the project footer, you could insert a crosstab in the project footer. You would use subaccount as the row, and account balance as the summary, no column. Don't know how well this would work for your Web reporting requirement though.

lyanch has repeatedly requested that you supply a sample of the data returned and the report you would like to achieve. Providing concrete information like that would probably result in a quicker solution.

-LB

 

[clevelandjwb]

Thanks for the advise. In the end, I have decided to build a file in the stored procedure and access it that way. It works but I still want to improve my knowledge of how these things are done.

I am sorry about not providing an example it is hard to do but I will make the attempt.

___________________________________________________

Project xyz (833204)

Begin Bal Subaccount 001 Support $100.00
06/01/04 Description 50.00
06/03/04 Description2 (25.00)
06/04/04 Description 3 24.00
Ending Bal Subaccount 001 Support $149.00

Begin Bal Subaccount 011 Passage 200.00
06/01/04 Description2 25.00
Ending Bal Subaccouint 011 Passage 225.00

Begin Bal Subaccount 140 Transmission 26.00
06/04/04 Description 4 25.00
06/15/04 Description 5 (51.00)
Ending Bal Subaccount 140 Transmission 0.00

Subaccount Summary
001 Support 149.00
011 Passage 225.00
140 Transmission 0.00

End of first project.
___________________________________________________
The transaction lists are much longer and the typical number of subaccounts per project is around 8.

I am interested in the crosstab concept but presume it too would require a separate pass over the data, correct?

John