Archive for June, 2012

Slow performance caused by a “split” DSN

Friday 29 June 2012 @ 9:42 am

I was troubleshooting a report that was taking a long time to get data from the database.  The report used the same table configuration as many other reports, and none of those reports had performance problems.  But when going in to check the links I noticed that there were two different connections.  Then I got an info message from CR that said the report was using two different data sources, which was strange.  So I went into “Set DataSource Location” and, sure enough, one of the tables was listed separately from the others, with a separate connection.  The funny thing was that the two connections seemed identical.  Same name, same properties, etc.

So first I took lone table Continue Reading »
Slow performance caused by a “split” DSN

How CR adjusts date values for DateTime fields

Monday 25 June 2012 @ 4:42 pm

When people enter date criteria they almost always expect to enter just the date value without times.  But most modern databases use a DateTime data type rather than a date.  If your data stores dates with times you have to be sure that you enter your date ranges correctly or you take chance of losing the data for the last day of the range.

The good news is that in most situations CR will make the adjustment automatically, but I found one situation where the adjustment doesn’t happen.  Say you have a transaction date that is a DateTime field, and you are trying to select the transaction for  January.  You might go into the select expert and  add a rule that says  Continue Reading »
How CR adjusts date values for DateTime fields

Using ‘Set Datasource Location’ with a SQL command

Wednesday 20 June 2012 @ 10:23 pm

If your report uses tables, it is usually straight forward to point the report from one data source to another.  You use the menu item:

Database > Set DataSource Location.

Once in that window, you highlight the existing connection in the top half of the window.  Then you highlight the new connection in the bottom half of the window.  When you click “Update” the properties of the current connection (top window) should change to reflect the new connection.

However, this is not as simple when the report is based on a command, because with a command there is no existing table called ‘command’ for CR to find in the new connection.  The command only exists in CR.   So here are two workarounds that you Continue Reading »
Using ‘Set Datasource Location’ with a SQL command

Compliance percentages in a cross-tab

Friday 15 June 2012 @ 6:47 am

I was asked to create a cross-tab that showed a percentage of assignments completed on time.  The row was a technician name and the column was the month.  After working through several more complex ways to do this, I found a simpler method.  I wrote a formula that said:

If {data.Ontime} = 'Y' then 100 else 0

I then set up the cross-tab with the date as the column field (option set “for each month”) and the tech name as the row field.  I used my formula as the summary field and used “average” as my summary.  This gave me a Continue Reading »
Compliance percentages in a cross-tab


Recrystallize Pro

Crystal Reports Server