Blank Page, but groups on left pane and plenty of data

[kxramse]

I have the weirdest situation in Crystal 8.

3646621 million rows of data are getting chugged through. The report runs the amount of time it should run, it cycles through the amount of data it should, but, when it’s done, it does not show any data on the page. You also can't cycle through the pages either as if the report didn't have any pages except the first. The groupings are on the left, but when you try to expand them or drill down into them you can't. When you click on a group it says "generating group tree" but then it just doesn't do anything and no drill down occurs.

The report has PLENTY of data, but it’s as if it just decides to give up processing. My gut would have normally of told me to blame it on memory, or the amount of data, but get this....

If I run this report with ANY month long date range BEFORE 4/1/2006, the report RUNS JUST GREAT!!! If I run 2/1/2006 to 2/28/2006, its fine, if I run 3/1/2006 to 3/31/2006, it runs fine. If you run 4/1/2006-4/30/2006, it craps out and displays a blank page. Get this; it chugs through the exact same amount of data when it does those months!!! 5/1/2006 – 5/31/2006, 6/1/2006 – 6/30/2006 also do NOT work.

Here are my guesses:

1. Something in the data changed after 4/1 that the users aren't able to tell me about. I’ve asked and asked and asked and they don’t know of any changes.
2. Crystal Reports is corrupted and needs to be reinstalled.
3. Run this in a newer version to see what happens. I'm waiting for my company to get me some disks.
4. Something on machine that Crystal is running on is messed up. Re-build the machine?

I can’t do guesses 2, 3 nor 4 just yet, while number 1 keeps getting me know where since the users aren’t telling me anything is different, and I can’t find anything in the data to indicate the data is different.

Does anyone have any other ideas? Any suggestions?

Help!!!

Keith

 

[synapsevampire]

I'd guess it's a bad report design.

So if you want solid help, post basic technical information:

Crystal version
Database/connectivity used
Example data
Expected output

Since it "chugs" through the same amount of data, then you aren't passing the filtering to the database, to test this, check the Database->Show SQL Query

What you've placed in the Report-Selection Formula-Record or Group determines whether Crystal has to "chug" through the data, or the database does the work.

So what's in the record selection (filtering you've applied) and what's in the SHOW SQL would be useful as well.

I have a FAQ on this which might help you get a handle on this:

faq767-3825

If you need firther assistance, post the requested information above.

-k

 

[kxramse]

Crystal version: 8.0.1.549 I thought I said 8. Sorry if I missed that.

Database DLL Name=pdsodbc.dll
Server Type=ODBC - crystal
Server=crystal
Database=wolftracs
User Name=cfuser

Here is the "Show SQL". This report wasn't created like I would have liked to create it. It uses multiple extremely complex views, and makes Crystal do all of the work. I was using XI at my last job and would have written this in my SQL command, or as a stored proceedure, or re-wrote a view to write this report. Right now I'm in emergency mode because it stopped working, so I might not have time to do that just yet, plus I'm still waiting on XI here.


Anyway, here is the Show SQL:
SELECT
vComm_Combo."Siteid", vComm_Combo."ReportMonth", vComm_Combo."ReportYear", vComm_Combo."AmountDue", vComm_Combo."AmountReceived", vComm_Combo."DateTimeReceived", vComm_Combo."IncludeDiffNextBill", vComm_Combo."Difference", vComm_Combo."IsCredit", vComm_Combo."SitesName", vComm_Combo."Months", vComm_Combo."AdjRate", vComm_Combo."CompanyName", vComm_Combo."ScaleName", vComm_Combo."GroupNumber", vComm_Combo."RenewDate", vComm_Combo."AgentName", vComm_Combo."AgentNumber", vComm_Combo."TaxID", vComm_Combo."AddressOne", vComm_Combo."AddressTwo", vComm_Combo."City", vComm_Combo."State", vComm_Combo."Zip", vComm_Combo."Rate",
vpremium_PTD."ptd",
vsum_rates."rate", vsum_rates."adjrate",
vsites_max_date."c",
vnext_receive_date."nextrecdate", vnext_receive_date."next_amt_rec"
FROM
{ oj ((("wolftracs"."dbo"."vComm_Combo" vComm_Combo INNER JOIN "wolftracs"."dbo"."vsum_rates" vsum_rates ON
vComm_Combo."GroupNumber" = vsum_rates."GroupNumber" AND
vComm_Combo."ScaleName" = vsum_rates."ScaleName" AND
vComm_Combo."SitesName" = vsum_rates."SitesName" AND
vComm_Combo."ReportMonth" = vsum_rates."ReportMonth" AND
vComm_Combo."ReportYear" = vsum_rates."ReportYear")
INNER JOIN "wolftracs"."dbo"."vsites_max_date" vsites_max_date ON
vComm_Combo."SitesName" = vsites_max_date."sitesname")
INNER JOIN "wolftracs"."dbo"."vnext_receive_date" vnext_receive_date ON
vComm_Combo."Siteid" = vnext_receive_date."Siteid" AND
vComm_Combo."DateTimeReceived" = vnext_receive_date."DateTimeReceived")
INNER JOIN "wolftracs"."dbo"."vpremium_PTD" vpremium_PTD ON
vComm_Combo."SitesName" = vpremium_PTD."sitesname"}
ORDER BY
vComm_Combo."AgentNumber" ASC,
vComm_Combo."GroupNumber" ASC,
vComm_Combo."CompanyName" ASC,
vComm_Combo."SitesName" ASC

The record selection formula is simple actually:
{@rec date}<={@max date}

I'm thinking I'm going to add to it by limiting the months of data that it processes through. In interviewing the user, I've found out that its chugging through ALL data from ALL time and that I can make it look at a maximum of only 24 months of data per run. But I'll try that next week. Let me know if you think that's a great idea that should happen sooner than later.

As for example data, I'm not sure I even understand the data yet; I've been here less that 3 days. Its basically a report that calculates an agents commission for selling something, and does 5 or 6 groupings in order to produce each page of the report. If I can avoid giving out data at this point I'd like to since I do not know my company very well and whether I'm even aloud to diclose scenarios. So-k?

I plan on doing optimization and following your suggestions in your FAQ article after I get through the crisis. Do you think it needs to occur first to get through the crisis?

 

[synapsevampire]

Yeah, the record selection isn't being passed to the database, note there isn't a WHERE clause, hence the report is slow.

Don't assume that we know what's in {@rec date}<={@max date}, we don't. Those are formulas, so you need to be thorough and post what's in them.

Anyway, I suspect that is your issue, a bad design used for filtering.

btw, you still didn't post the database type, ODBC is a type of connectivity, not a database, but it doesn't really matter, the problem is clear, try to avoid using formulas in the record selection, and certainly if you do and need help, share what's in them so we don't have a HUGE thread, such as this...

-k

 

[kxramse]

@rec date = date(left({vComm_Combo.DateTimeReceived},10))

@max Date = maximum({?Report date range})

?report date range is the date range parameter that determines what month this report is for.

This is a SQL Server 2005 database.

You believe the problem is in the filter of the fields I am including in this post?

 

[synapsevampire]

It IS in the filtering to some degree anyway, certainly performance and the excessive rows.

So I gather that a date is stored as a string in the database, and that you've created a date range parameter, however you're ONLY filtering on the maximum side of it. Why?

Instead of your formulas try creating a SQL Expression (another type of formula in Crystal) of:

cast({vComm_Combo.DateTimeReceived} as datetime) DateTimeReceived

I don't have SQL Server here so I can't test this but you should be able to easily figure out the T-SQL CAST or CONVERT syntax to make a REAL datetime out of this string, plenty of examples on the web.

Then in the record selection use:

{%DateTimeReceived} = {?Report date range}

This assumes that if you are asking for a date range that you TRULY want to use a date range, not just the upper bounds.

You might want to reread this thread as some of it appears to confuse you as to why the report is inefficient and potentially incorrect.

The key is to create a record selection formula which is passed to the database, making it simple doesn't help, it needs to pass the criteria to the database.

-k

 

[kxramse]

Ok, I don't get it.

1. I didn't write this report; so just so you know, I understand it wasn't written well; and I have a good idea how to improve its performance.

2. The date range isn't stored in any database table in a format that states, here is the date range to store for today's running of the report. You have to ask the user to input the range, you can't gather that range from the database.

3. The date range is used in many other areas of the report, but in terms of what data is gathered, it gathers the data up to the maximum date in the date range because it is doing processing on data up to 24 months before the date range as well as in the range.

4. I've tried changing the selection criteria and to my amazement, it works, but I'd like to know why. I changed only this one thing.

5. I did things yesturday a little differently than suggested for environmental reasons, but I followed your basic guidance in that it was the selection criteria.

Here is what it use to be:

@rec date = date(left({vComm_Combo.DateTimeReceived},10))

@max Date = maximum({?Report date range})

Then in the record selection use:

{%DateTimeReceived} = {?Report date range}

Where the {?Report date Range} was a user driven parameter asked for at run time.

Now its this way:

DATETIMEVALUE({vComm_Combo.DateTimeReceived}) <= DateTime (2006,07,31,23,59,59)

Then I still pass on the parameter of 7/1/2006 to 7/31/2006 so that the rest of the report knows what range to use.

So basically I've hard coded the formula to see if that would change the way it turns out. It ran successfully!

So I have questions:
1. How could this work?
2. I would have thought it couldn't be the selection criteria... It works for other periods of dates. Other periods of dates aren't formated differently. Other periods and dates have the same number of characters. How did you know it was the selection criteria? What specifically did you see, and what comes to mind when you see that hard coding works? My boss and I are amazed, we keep wondering why dates that use to work and look like the same dates we are using in more recent times aren't able to run through the old criteria.

Thanks for your insights,
Keith