The Crystal Reports Underground News - Volume 2017.03
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for March 2017:
** Comparison of desktop-based schedulers (2017 update)
** Security vulnerabilities in Crystal Reports web apps
** My library of Crystal Reports materials
** Visual Cut adds RPT management
** Save money on rePORTAL
** Let me create your Crystal Reports
** Finding the last day of the month
** "Poor man's" row level security
** Open enrollment Crystal classes in Frederick, MD
** Today's date in SQL Server
** Using the NthSmallest() Function
** A simpler way to maintain formatting conditions
Gems from the Archives:
Date parameters in a command with DateTime Fields July 2006
One parameter for 2 different commands July 2006
Comparison of desktop-based schedulers (2017 update)
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.
There are 12 products in the list this year. The page linked above provides a brief description of each product and lists the features that set it apart. Then there is a detailed feature matrix that shows the key specifics for comparison including prices. To clarify the matrix terminology I have written a feature glossary to explain what each feature means. Finally there are links to the vendor websites so that you can get more information on each product. In May I will be updating a separate article that compares server based scheduling tools. If you think one person can manage all of your scheduling you are probably fine with one of the desktop tools, regardless of the number of people receiving the scheduled output. But if you plan to have multiple people scheduling reports then you may want to consider a server based tool.
Security vulnerabilities in Crystal Reports web apps
A Crystal Reports web development team has posted in the SAP forums about some security weaknesses in the .NET web deployment model. They are trying to get the attention of SAP, without success so far.
It appears that this team deployed a .NET web application using the Crytal Reports runtime engine. During a security audit they uncovered some serious vulnerabilities. It appears that they weren't able to get the SAP support team to look at the problem because they do not have a support subscription, so they posted it to the forum as three question. From what I have read they are trying to walk the line between highlighting the seriousness of specific vulnerabilities, while not making these same vulnerabilities easier to exploit.
Those of you who do .NET web development might want to check out the following links (some of which appear to have been deleted as of 3/6).
Link 1 (131436) (deleted)
Link 2 (130250) (deleted)
Link 3 (133449)
And thanks to Ido Millet of Millet Software for pointing these posts out to me.
My complete library of Crystal Reports materials
Do you want to really understand Crystal formulas? Do you know when to use the four different methods to add totals? Why not let me explain these Crystal Topics to you with one of my Expert's Guides. Each guide comes with clear explanations and sample files to illustrate the concepts.
Expert's Guide to Formulas ($18)
Expert's Guide to Subreports, Parameters and Alerts ($14)
Expert's Guide to SQL Expressions, Options and Commands ($13)
Expert's Guide to Totals ($12)
Expert's Guide to Cross-Tabs ($11)
Expert Techniques Vol. 1 - 4 ($10 each)
Quick Reference to Crystal Reports in Visual Basic ($8)
Quick Reference to Crystal Reports in .NET ($7)
You will find these on the LIBRARY page of my site.
Visual Cut adds RPT management
Visual Cut has always been a solid desktop scheduler. I have used it for years to send out my invoices.
But Ido Millet of Millet software has recently added some new features that allow you to scan multiple reports. The scan reads in the database fields, text objects, SQL expressions and all formulas (including selection formulas and property expressions). You can group, sort, search, and export the results. You can also find & replace text and expressions and save the updated versions of these reports. Ido has posted a video demo of this.
Another video demo shows how you can deploy an entire series of new formulas in Excel and import these new formulas into multiple reports. With these new features, Visual Cut will now be included in my annual comparison of RPT management utilities.
Save money on rePORTAL
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.
Let me create your Crystal Reports
There aren't many people who know Crystal Reports better than I do. It is what I do all day, every day. So if you need a tough report created why not leave it to an expert? Let me show you how I can mix and match techniques to create the reports you need - even the ones that "can't be done". And since I am also a teacher I am happy to explain to you how the techniques work together.
I can also review existing reports that break, or run slowly, or seem overly complex. Let me have a look at them and see if there is a more elegant solution.
Finding the last day of the month
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 on my blog post 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 30 articles with similar tips and sample reports.
"Poor man's" row level security
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.
The users don't have a copy of Crystal
The connection to the database is made only from within the application.
Today's date in SQL Server
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())
It works great in a WHERE or ON clause, but it has one flaw. If you include it in your SELECT clause so that you can show the date on the report it will actually appear as a number. To get it in the SELECT as a date you can use either of these:
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.
Open enrollment Crystal classes in Frederick, MD
Stop struggling with Crystal Reports and learn how to use it fully. Come join me in one of my January classes and learn how to make Crystal work for you. My Intro class makes sure you know all of the basics. We even include material on cross-tabs, charts and formulas. The Advanced class shows you how to solve reporting problems with running totals, subreports, parameters and commands. The material is good for any version. See my web site for course outlines and dates.
So what makes my classes different? I have written my own course materials and have used them to teach over 2,500 satisfied students. And, I give you a toll-free number so you can call me after class with questions at no charge.
Or, if you want to schedule a class at your office, using your data, that is my specialty. I have found some stellar instructors in the the US and Canada that deliver my class for a very competitive price. They are based near Spokane/Seattle, LA, Omaha, Detroit, Tampa, Philadelphia, New York City, Boston, Vancouver and Montreal. Call for details.
Using the NthSmallest() Function
In the ideal world, customers write perfect specs up front, and requirements never change. In my work reports tend to evolve through several iterations. Some customers don't realize what would work best until they start to see drafts.
Last week a customer presented me with what is usually a fairly simple request: print only the first 40 widgets. So I sort the records into the correct order (in this case date and item number) and then suppress all rows with a record number greater than 40.
Then the customer asks if the chosen 40 records can be sorted by location. Well they can, but changing the sort to location would change the chosen 40 records. So we go to plan B.
I created a formula field called {@Rank} that combines the Date and the Item Number into one string. The combined value needs to sort by date and then by Item Number so I used this:
Totext ({Date} , 'yyyyMMdd' ) & '-' & Totext ({ItemNumber} , '00000')
This converts the date and item number into a value that still sorts in the correct order. But I didn't actually sort on this formula. Instead, I calculated the 40th value using the NthSmallest() function. It is like the Minimum function but allows you to specify not just the very smallest value (minimum) but any number, like the second smallest value in the column. You use the function like this to get the 40th value:
NthSmallest (40, {@Rank})
There is also an NthLargest() which is like Maximum(), and NthMostFrequent() which is like Mode(). If this were a formula field sitting on the report it would display the 40th value on the report, and it can do this without requiring an actual sort. Just like the minimum can find the lowest value without requiring a sort.
So with this calculation I could sort the records by location and still limit the report to 40 records. The limit is done with a group selection formula that only includes values less than or equal to that 40th value. The group selection formula would look like this:
{@Rank} <= NthSmallest (40, {@Rank})
The report works correctly.
But then another wrinkle appears. The number 40 needs to be a calculation instead of a literal number. Here is where I learned something new. In just about every place that Crystal requires a numeric argument (like 40) Crystal allows you to use a calculation instead. But the NthLargest, NthSmallest, and NthMostFrequent functions are exceptions to that rule. You can't put a calculation, a formula field, a database field or a parameter as an argument in that function. You can't even put in (1+1). It has to be a literal number.
So to meet the new requirement I had to completely scrap the NthSmallest() function and go to Plan C, using a subreport. I inserted the report I had created back into itself as a subreport. In the main report I sorted the records based on {@Rank}, and then wrote a formula that said:
WhilePrintingRecords;
StringVar RankN;
if RecordNumber = {@Adjusted 40 target}
then RankN := {@rank};
RankN
The formula called "adjusted 40 target" is the calculation that replaces the original fixed target number 40. So the formula above ignores all records until it gets to that target number record and then it stores the value of {@rank} from that record into the variable RankN. That will be our cutoff value and will get passed as a parameter into the subreport. The subreport then selects all {@Rank} values that are less than or equal to the parameter. So the subreport only returns the records I need. But the subreport can be sorted by location without affecting the chosen records.
Of course subreports are a last resort, but the customer gets exactly what they want (at least for now).
A simpler way to maintain formatting conditions
I have written before about the advantage of using CurrentFieldValue when applying a formatting condition. This is especially true when applying a similar format to multiple fields, because it allows you to 'paint' the format properties from one field to another using the format painter. Related to this method is a way to make it easy to update all of these formulas at once, without having to change them individually.
For instance, say you have 12 columns and the font color formula for all of them is:
if CurrentFieldValue > 90
then CrGreen
else CrYellow
When someone wants to change the value from 90 to 95 or tweak one or both of the colors it would require updating one and repainting all the others. Instead you could create some feeder formulas for the different literal values. I would create three formula fields:
{@target} which contains the number 90.
{@LowColor} which contains the function CRYellow
{@HighColor} which contains the function CRGreen
If you have those three formulas, your formatting formula would be:
If current field value > {@target}
then {@HighColor}
else {@LowColor}
You can apply this formula to all 12 fields. Then when someone needs to change yellow to orange you just change the {@LowColor} formula from CrYellow to Color(255 , 165 , 0). This way the change affects all the formatting formulas in one stroke.
Gems from the Archives:
Date parameters in a command with DateTime Fields July 2006
One parameter for 2 different commands July 2006
Removal instructions:
I have sent you my newsletter because you or your company are one of my consulting or training customers. If you don't wish to receive the newsletter you can use the link below.
Contact Information
Ken Hamady, MS
525K East Market St.
PMB 299
Leesburg, VA 20176
(540) 338-0194
ken@kenhamady.com
http://www.kenhamady.com
Copyright 2017 by Ken Hamady
All rights reserved - Republishing this material requires written permission