Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    126

    Unanswered: Crystal Reports Features [missing] in Access

    Our ERP software uses Crystal Reports to report off of a SQL Server database. We also use Excel spreadsheets WAY too much.

    We are now moving all of our Excel spreadsheets into Access Data Projects, and Access has it's own report writing capapbilities which I see are incredibly powerful due in large part to VB automation and the benefits of using one application to both enter data and report on it.

    This brings me to the question of:

    Is there any pressing reason I would continue to use Crystal Reports?

    While converting several reports from Crystal to Access, I have run into a few features that I found very useful in Crystal that are "missing" in Access. By "missing" I simply mean that I haven't figured out simple ways of replicating them [yet].

    Does anyone have any suggestions on replicating the following Crystal Reports features in and Access 2003 Data Project? I am very open to using VB, I just haven't found a very good starting place on these things yet.

    1. Multiple Sections Crystal lets a user create multiple versions of each section which can then each be turned on or off independtly using conditional formatting. For example, on a customer statement, I might want different fields to display for a payment than an invoice.
    2. Conditional Visibility [of Sections] Similar to the above feature, Crystal allows one to VERY easily turn on or off the visibility of a section based on any conditional expression. It allows the user to select which of the multiple sections to display as well as a method of filtering which records get displayed. I have certain formulas on my reports that prevent me from filtering the query, I need to simply filter the visibility of the records in the report.
    3. Specified Grouping I want a little more control over my grouping than simply sorting by equal periods or the first 3 letters of a field. Crystal offers the ability to specify a group based on any expression, and even give it a name. For example, I want to create two groups on my report: Previous Activity and Current Activity. Since the previous activity can occur more than a month or even a year ago, I cannot group on either of those things. I simply want to specify Group "Previous Activity" as those records with dates before the statement's start date, and "Current Activity" for records with dates after the start date.

    Any help would be GREATLY appreciated. I look forward to overcoming these minor hurdles with the help of those Access experts out there.

    Thank you,
    Mike

  2. #2
    Join Date
    Feb 2004
    Posts
    199
    Very actual theme!
    before everything, I'm trying to find cos & pros too and i want to discuss about it
    one story: There where in our office a Report done in Crystal runing on SQL data whole night to get results next morning. I've done same report in Access and it's runs in 20 seconds. Of course this is not only Crystals fault, I've seen how the report been done, designer that done it, used all Crystals features and forgot about optimizing, he just made simple select query and than use agregate functions in cristal to calculate sums , to filter rows (by hiding rows that doesn't meet criteria), etc...

    now about Access
    1) Multiple Sections - nice feature, in Access CONDITIONAL FORMATING may help.
    2) Conditional Visibility [of Sections] - say the true, I'd never use this feature even if I could, I'd filter rows before report receives it, why I should load client computer and network with junk data?!
    3) Specified Grouping when I need a speciall grouping, i just a column in query with expression I need to group. again why I should load client's comp with extra calculations?

    My post doesn't mean - "Access Reports better than Crystal Reports"
    I have just simple logic - in 99% cases Access does what I need, and If I work with MS SQL or Jet, why should I use third company's tool rather than native tool.
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    The conditional formatting and visibility can all be accomplished with code. It might not be as easy as it is in Crystal (no experieince with it) but once you get a feel for how to do it, it isn't that hard. I have written several reports that use the Tag property to determine when to show and when not to show. You can loop through all of the controls on a report and if the Tag field is x then show it. If it is y then hide it. All of the code would be put in the Format event. Whether it is the Details section or the Header or a Footer.

    As for specified grouping, I am not sure I understand your example but Access allows you to put several subreports on your main report. I haven't run into a situation that I couldn't figure out, with either queries or subreports.

  4. #4
    Join Date
    Feb 2004
    Posts
    126

    Expressions

    I got a suggestion in another forum to simply add a column to my query where I say something like "If Doc_Date <@startdate then 0 else 1" and then group on that column. I can then put anything in place of that conditional expression in the query and it will always do what I want on the report.

    My problem now though is what @#$@# syntax do I use to enter that expression as a column in the Design view when creating my function or stored procedure. I can't use IIF because it's a Jet-only expression. I need to use some sort of T-SQL statement, but I have been unable to figure out just what I need to put in the column field.

    Please help?

  5. #5
    Join Date
    Feb 2004
    Posts
    199
    in T-SQL there is CASE statement and you can use it instead Jet's IIF, and again - don't try to avoid T-SQL usnig Crystal's nice but not optimized features instead optimised stored procedures and views!
    use what you want, Access reports or Crystal but use SQL's features too!
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •