[b]Best practice[/b] 7

[moonknightt]

A lot of us are either Crystal experts or new. For those that are considered experts are there any Crystal Best practices

 

[synapsevampire]

One would think that a Crystal Expert would understand best practices by definition.

The answer is that there are lots and lots of them. One of the interesting aspects of report writing is that you are generally expected to know how to best extract and present data from numerous sources.

As for a list of guidelines, they vary from shop to shop, but there are lots of threads here discussing this ever expansive topic, use the search.

-k

 

[moonknightt]

By definition a best practice is an activity or procedure that has produced outstanding results. Even though practices differ from shop to shop, there are BEST practices required for every shop or every shop should know

 

[moonknightt]

Best practices differ from solutions

 

[synapsevampire]

Alrighty then...

My point is that there are lots of threads, and the topic covers such an expansive area that there is NO single best practices.

If you have Crystal 8.5 and an Oracle backend, then you'll find a completely different set of best practices from a shop running CR XI with a Sybase back end.

Also the requirements dictate everything.

Check out the FAQ areas for a few articles along these lines, but again, it's more expansive than trying to cover application development, it's closer to integration.

-k

 

[rhinok]

SV's right - there's no comprehensive list of 'best practices' for report writing, because the subject is highly subjective. If you were compiling a list of best practices, however, there are certain things I would recommend:[ol][li]Optimize your reports for processing on the database server, not the client - 9 out of 10 report writers I interview have no idea what this means or how to accomplish it[/li][li]Generally avoid subreports if at all possible, but definitely avoid them in the detail section[/li][li]Learn SQL - it's your friend, no matter what flavor[/li][li]Get to know SQL Expression fields[/li][li]Learn to create SQL Command objects[/li][li]Adopt, implement and enforce design standards specific to your project or environment. Use the same type of comments, code and 'look-and-feel' so that your finished product is more professional[/li][li]Write release notes documents (using a template) for each report for turnover to the testers/users[/li][li]If you're using Crystal Enterprise or another scheduler, design your reports so that they can be scheduled automatically on a recurring basis and run on-demand. Almost all of my date-driven reports have a {?Schedule Type} parameter. If the user selects 'ON DEMAND' as the value, the report will run using additional data parameter values. If the default value is set to 'RECURRING', then the report will automatically calculate the appropriate date range based on the record selection criteria and/or additional parameter values such as 'Last Quarter', 'Last Month' and 'Last Week'.[/li][/ol]

~Kurt

 

[Mike Lewis]

If I may add my EUR0.02 worth:

- Devise standards to cover such issues as date formats (long vs. short), whether to use thousand separators in numeric columns, currency symbol in column header vs. in the column itself, and so on. Once these standards have been agreed, enforce them consistently.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[moonknightt]

Thank you Kurt and Mike, that is the kind of information I was trying to gather, not just for me but for everyone who might not be aware of certain best practices. Even though as i said before, certain things differe from shop to shop, best practices will cover all shops. If everyone adds their two cents, there could be certain standards that can be carried from shop to shop, making it easier to transfer knowledge and help those travelling this road.

Thanks again, really appreciated

 

[moonknightt]

For those working on sensitive information or where security might be an issue add disclaimers to your report

 

[synapsevampire]

OK, let me address these best practices using Kurt's as an example:

1) Optimize your reports for processing on the database server, not the client - 9 out of 10 report writers I interview have no idea what this means or how to accomplish it

Often the case, but it doesn't make sense in many shops. The cost in designing for performance may far outweigh the cost of just getting it done. Certainly this is the case with many ad hoc reports, and on small databases where performance isn't an issue, as well as some very large data warehouses as the complexity in understanding how to optimize that SQL may be a good deal more complicated than simply passing the SQL.

So you might qualify this by saying that when designing production reports for databases that will net significant performance gains.

2) Generally avoid subreports if at all possible, but definitely avoid them in the detail section

Most of the time. Subreports are the cost of a query. If you're building a dashboard type of report, which encompasses numerous queries, the cost of using subreports, versus building a process to normalize the data into one data set should lean heavily towards using the subreports in most instances, and perhaps at a details level.

3)Learn SQL - it's your friend, no matter what flavor
Unless you're using databases or in shops where you are either not allowed to create data objects, or the database is not a SQL database. Certainly not a best practice nor a requirement in many shops.

4) Get to know SQL Expression fields
See 3. Plus SQL Expression fields are somewhat less important with the advent of SQL Command Objects, which I also try to avoid.

5) Learn to create SQL Command objects
I would advise using Views or Stored Procedures whenever possible instead, they provide better reusability and maintenance.

I'd go on, but you get the point. Crystal is used as an integration tool across many industries with varying requirements and tools being integrated. I'd liken it more to picking a database or hardware than a software development tool. Back in my days of C, Pascal, COBOL, C++ and VB coding, I could have easily built a best practices that was relatively bulletproof, with Crystal it's not so straightforward, which is one of the great challenges about BI in general.

My best practices are to get a second opinion during design (rather than afterwards when something doesn't work), try to set standards within each shop, although these often get overidden later, then measure twice and cut once.

Of course common sense practices are applicable, such as quality documentation and extensive comments, otherwise you'll find that the exception quickly becomes the rule with Crystal.

Fun stuff.

-k

 

[Madawc]

As well as what's been said, I'd add:

a) If possible, get rid of unwanted records in the select statement rather than suppressing them

b) If the report is going to be run a lot, take a look at the SQL generated by your code. If part of the selection isn't there, that means it's not being done on the Server and will be done slowly. I had a report that speeded up vastly when someone replaced a selection based on several date-fields with the same written in SQL. E.g.SUBSTRING("INAM01"."Kfield", 1, 3) gets sent to the server and thus produces a much faster return than LEFT({INAM01.Kfield}, 3)

c) Regarding subreports, the SQL Query (Database->Show SQL Query) will execute once. However, if that same report contains any subreports, then the SQL Query of the subreport(s) will execute as many times as it is triggered depending on what section the subreport(s) is/are placed.

d) On the other hand, Subreports make a lot of work for computers, but save time for humans. You decide which is more important for your particular circumstances.

e) Learn the best uses of running totals and summary totals. Running totals allow you to do clever things with grouping and formulas. They also accumulate for each line, hence the name. The disadvantage is that they are working out at the same time as the Crystal report formats the line. You cannot test for their values until after the details have been printed. You can show them in the group footer but not the group header, where they will be zero if you are resetting them for each group. Summary totals are cruder, but are based directly on the data. This means that they can be shown in the header. They can also be used to sort groups, or to suppress them. Suppress a group if it has less than three members, say.

Madawc Williams (East Anglia, UK)

 

[IdoMillet]

The item at the top of the list should be:

Develop a report only after you clearly know WHY the report is needed and how it will be used.

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[Turkbear]

Hi,
THANKS, Ido...That one piece of advice will prevent more stress than most other things we could say to a new developer ( or an experienced one, for that matter).
As much as I hate structure ( I would be an anarchist, but the wouldn't let me be in charge ), pre-planning and communication with the report requester is the only way to go, unless you like re-doing everything because what you heard was not what they meant..

 

[Madawc]

Regarding pre-planning, I've always found it easy to start with a hazy idea in Crystal and adapt it to something else. Of course that may be relative, the approach of someone whose more used to mainframe languages.

Crystal is also a language that can be trusted to non-prigrammers, anyone who can write their own Excel spreadsheets. They'll still need advice for the more complex stuff, of course.

When you're a beginner, it is a good idea to trust the 'Expert' options. Discard them once you have a clearer idea of what's going on.

Always remember that Crystal is a report-writing tool, not a language. As others have said already, there are things best done in SQL, which can be attached to Crystal reports. (See threads on 'Stored Procedurtes').

Madawc Williams (East Anglia, UK)

 

[moonknightt]

If one was to make a choice between stored procedures and views. what choice will be the most beneficial?

 

[vidru]

The requirements usually dictate the answer to Views vs Procs. A well-written Record Selection Formula against a well-written View (if the View contains JOINs) can perform just as well as a stored procedure. Stored Procs should be used if the requirements of a report are too complex to write an effective Record Selection Formula that will pass-through to the server for processing.

I say this while I'm working for a company that uses stored procedures for 99% of its reports when at best 10% of them actually need to be. I've tried to talk them out of it, but there's a bit of a 'resitance to change' problem around here...

-dave

 

[moonknightt]

but dave if you have too many formulas, selections and code in your report doesn't that make it harder to trouble shoot or change the report as the business changes?

 

[vidru]

My decision process comes down to this:
If I use Views/Tables, am I going to have to jump through hoops to get the desired result (subreports to link non-linkable fields/showing normally unrelated data on the same line)? If so, then can I create a stored procedure that does the hoop-jumping? Nine times out of ten, I don't need the procedure - it's when the requirement and the data structure butt heads that I'll go with a stored proc.

As far as code/formulas are concerned, documentation is the key. If you keep up with documentation, and comment your code, it'll make your life much easier if something breaks.

If you're dealing with a large number of reports, you should also condider using some form of version control software to keep track of any changes that were made. You as the report developer can be your own best friend, or worst enemy.

-dave

 

[synapsevampire]

SP's can be much faster than Views, it depends on the database and the execution plan. SPs tend to be atomic in nature, and therefore experience less reusability. Maintenance is probably similar betweeen them.

moonknightt: You keep slapping your mallet into that round peg hoping to make it fit in the square hole.

It alwasy depends on the requirements, the dtaabase, and the long term plan.

It's the equivalent to asking which vehicle is the best to drive.

Depends, I prefer submarines when under water, but if it's the only chance I'll ever get to drive a Maserati MC12, I'm going under

-k

 

[moonknightt]

Well I would really like to shape that round peg into a square in the hope that it would eventually fit my square hole. The reason for best practices is to aim at
Improved efficiency
Reduced costs
Better product quality
Greater customer satisfaction.

There is a lot of garbage out there and customers start looking for other solutions because they feel some other tool can do a better job in producing the the efficiency and quality they expected and rightly deserve after shelling some $$$'s on the crystal tool. In essence, it is not the tool but the developers.
Best practices gets developers thinking on a straight line not developing reports all willy nilly.