I'm using Crystal 9 with SQL Server database. I have a report that was working fine for the first 8 months. As soon as I add another month, the report fails. I've tried different setting in my indexes with different variations of periods. The minute I have over 8 periods included, the report fails. No errors, just no data. I don't even have a clue where to look for possible answers on the Internet.
My first guess would be that your report is doing something (like a cross tab) that has a "columnarity" to it, and the report definition only provides for 8 columns of data in the result set. This is a common problem for reporting tools like Crystal.
My next guess would be that there is some kind of "query governor" that is putting an artificial limit on the size/resources of the result set. This is something you'd have to ask one of your administrators about, since there isn't any way that I know of for an end-user to reliably detect these.
I've got lots of other guesses, but these two cover about 98% of the situations that I find like this, so lets try the easy answers first, and only worry about the hard stuff if we need to!
I'm not using a Cross Tab, but do have several columns of data. How would I look around for limitations with regards to limitations in the results set?
Also, we don't really have an expert in Crystal. I'm probably the closest we have. Could you point me in the direction of looking for the "query govenor" that may be putting a limit on my size/resources result set?
Also, do you know of any way to set up debugging in Crystal? I miss that from my programming projects. It would be so useful to know of a way of following what is going on with the report behind the scenes.
There is no such thing as a Crystal Reports expert. I've met many who claim to be, but all of them turn out to be absolute noobs at programming. This is because Crystal is a poorly designed product that requires you to programming like an absolute noob to use it.
I'm guessing Pat is right that your report is failing because it is receiving an extra column from a cross-tab style result-set, even if you aren't specifically creating a crosstab query.
Does your report pull directly from a stored procedure of SQL Server view, or does it send an SQL query to the server? If so, the first thing you should do is strip any sort of SQL statements or logic out of Crystal and put it in a stored procedure. Crystal is a sucky sql editor, and you will have a much easier time debugging a store proc. Crystal, and other reporting tools, should be used for displaying the data ONLY, and all data manipulation should be handled on the server.
If it's not practically useful, then it's practically useless.
That is exactly what I've been telling my boss. I keep asking him to send me to training for views and procs because I would rather get the data together using those tools and displaying it with Crystal. Of course, what do I know. My boss doesn't even know what a view is and thus questions why we need one.