How would you like your reports to be automatically run, exported to a PDF and delivered to your Email InBox every Monday morning at 6am? The Crystal Reports designer doesn’t provide a way to do this (unless you upgrade to CR Server or BO Enterprise). But if you look at third party products like those on my LINKS page you will find several reasonably priced or free tools that do this. Some do even more. So every March I go through the list and publish a feature comparison on my blog.

Reportal Software has just released rePORTAL 6.1, a new version of their web portal for Crystal Reports.  They are offering a 10% discount on all server license fees through April 15, 2017.  I don’t see the server price on their web site but the price was $3,000 per server in January when I did my latest annual review.  That link is also a great place for more information on rePORTAL and the other third party web portals.

One of my customers has a report that he can run for one Sales Rep or all. He now wants the Sales Reps to run the report themselves, but doesn’t want them to be able to run each other’s reports. He wanted the report to be restricted based on the user’s windows login. The term for this is row-level security, because certain users are allowed to access only certain rows of data.

There are a few different ways to do this. Unfortunately, none of them would work in this environment:

1) You can set this up in some databases, but this client doesn’t have access to the database to make changes.
2) You can set this up in some web portals and viewers, but this customer runs all of their reports from an integrated viewer built into their software application.
3) There are even ways to do this using formula functions, but because their integrated viewer is installed at the client level it would have been difficult to deploy and maintain the required dll’s.

So I suggested a method that I will call “poor man’s” row level security, and that is the option the customer chose. Each of his sales reps and their managers got to pick a password specific for this one report. Each time they run the report from the integrated viewer they select their name in the original parameter and enter their password in a new parameter. These values are checked in a formula called {@criteria} that is part of the report’s selection formula. The {@criteria} formula looks something like this:

if {?User} = “Tom”  and  {?pw} = ‘cats’ then true else
if {?User} = “Kelly” and {?pw} = ‘dogs’ then {rep} = [‘Joe’, ‘Sam’] else
if {?User} = “Joe”   and   {?pw} = ‘bugs’ then {rep} = ‘Joe’ else
if {?User} = “Sam”  and  {?pw} = ‘mice’ then {rep} = ‘Sam’ else
false

Tom with his password sees all Sales Reps. Kelly with her password sees the two reps that she supervises. Joe and Sam only have access to their own data.  The selection formula ends with:

……. and {@criteria}

This may not seem very secure but is protected by two factors in their environment.

1. The users don’t have a copy of Crystal
2. The connection to the database is made only from within the application.

Years before I started my blog I was creating online content in forums like Tek-Tips.com. I am still an occasional contributor there but years ago I was spending an hour or more every day answering questions and generating thousands of posts. It isn’t unusual for me to do a web search and find my own answers from long ago in the results.

Recently, I was trying to see if I had ever posted my formula for taking a date value and finding the last day of that month. I found one example in a TT post from 2001 and reading that thread made me smile. The thread is all about finding the last day of the month and the answers are pretty complex. They were even discussing the 100 and 400 year exceptions for leap year.

I was late to the party, but my formula only had 4 lines. This is the same formula in 2 lines:

Local DateVar Last := Date(DateAdd( 'm', 1 , {Orders.Order Date} ));
Last - Day(Last)

You put any date value (database field, parameter, CurrentDate function, etc) in place of the field {Orders.OrderDate} and the result is the last day in that month. The comments below show two alternate formulas submitted by some of my colleagues that take different approaches.

This tip was included in Vol. 4 of my Expert Techniques series. Each volume in the set costs $10 has 30 similar tips. The full list of topics is here.

I haven’t written many posts on SQL topics. But recently it seems that much of my work involves writing reports based on complex SQL queries. Part of this involves converting Crystal formula logic into SQL syntax. The fun part is that the syntax varies from one flavor of SQL to another, especially for date calculations.

So today’s post is specific to SQL Server syntax, one of the most common flavors. I often need date calculations that are relative to today’s date. In a Crystal formula I would use CurrentDate. In SQL Server syntax the closest equivalent function is GetDate(). I have used it for quite a while assuming that it is the same as CurrentDate. But I just recently discovered that GetDate() includes both the date and the time, which changes things. If you run a report at 2pm on March 8th and the WHERE clause says:

WHERE orders.Date >= GetDate() -2

you might expect to get all the records on March 6th, but you probably wouldn’t. If your Orders.Date field doesn’t store times you would not get any records from the 6th. If that field does have times you would get records, but only those after 2pm on the 6th. So if you want the calculation above to behave like the CurrentDate function in Crystal you have to remove the time from GetDate().

I found two ways to strip off the time off any DateTime value. The one I see listed most often is this one:

DateDiff(d, 0, GetDate())

or this one submitted by Ralph Wahlert:

Cast(GetDate() as Date)

These works great in a WHERE or ON clause, but have one flaw. If you include them in your SELECT clause so that you can show the date on the report the first will appear as a number in Crystal and the second will appear as a string. To use them in the SELECT and have them as dates, you have to convert them to DateTimes:

cast(DateDiff(d, 0, GetDate()) as DateTime)
cast(cast(GetDate() as Date) as DateTime)

These two work in the WHERE/ON clauses as well as the SELECT. In each case they will return a DateTime value but with the time portion set to 12:00am.