Crystal Reports Features [missing] in Access 2003

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Crystal Reports Features [missing] in Access 2003

Author

Topic

grrr223
Starting Member

30 Posts

Posted - 2004-02-23 : 12:51:05

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:

If I am going to be using Access to enter data anyway, 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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-02-23 : 12:58:15

1 -- I showed you how to do this in another post. just repeat the expression in your list of groups. i.e., in "sorting and grouping", list "customer" twice to have 2 customer sections.

2 -- on each section's "Print" event, you can just use some VB:

private sub Detail_Print()
detail.visible = { some boolean expression }
end sub

the boolean expression can be something like

me.amount > 0

or something like that, where Amount is a textbox on the report.

if you need to hide 4 sections depending on the value when you are in the 1st group header, you can do it all at once:

sub Groupheader1_Print()
dim b as boolean
b = {some boolean check}
detail.visible = b
groupheader1.visible = b
groupheader2.visible = b
end sub

3 -- you can group by any expression you want. you can put an valid vb/JET SQL expression in the group by's. For example, you can have a grouping of

=Format(Date, "yyyy-mm")

just put an equals before it . plus you can put any expression in the query the report is based off of, and use the expression from the query as your grouping.

- Jeff

grrr223
Starting Member

30 Posts

Posted - 2004-02-23 : 13:15:10

THANK YOU!

1. Yes, you're right, you did answer this question in the other post, I just figured different people looked in the Access forums and figured it wouldn't hurt to mention that one thing again. Sorry about that, I know double-posting gets very annoying to frequent contributors.

2.This is exactly what I think I needed to get started on making Access replicate what I was able to do in Crystal. It is also the key part in making your very helpful answer for #1 work as well since I can now create multiple sections and turn them on or off at will.

3. Hmm, I don't think I explained what I wanted very well. Please note that I am working in a Data Project, I don't think I can use Jet operators here.

What expression might I use to have two groups, one before @startdate and one after @stardate? I pretty much want to have current month activity in one group and all previous activity in another group if it makes any more sense to explain it that way.

grrr223
Starting Member

30 Posts

Posted - 2004-02-23 : 14:17:52

Back to 2.

I tried the example code you gave me to control the visibility of a section, and I could not get it to work, I eventually settled on this:


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If (Me.txtAmount <> 0) Then
        Me.Detail.Visible = True
    Else
        Me.Detail.Visible = False
    End If
End Sub

Which works wonderfully to hide all $0 invoices from the statements.

If I change the above code to act on the Detail_Print event, it acts very weird. It hides all records on some statements, and shows some records on others, even ones that are $0.

While I was testing the Detail_Print example you gave me, I had changed my test field to Doc. Number. because I figured it would have fewer possible rounding errors or who knows what going on behind the scenes.

When I tested this code:


Private Sub Detail_Print(Cancel As Integer, PrintNumber As Integer)
     Detail.visible = (Me.doc_no <> 1872)
End Sub

It also actedd weird. My invoices are in order by doc_no, and if i used the above code, it would display all the records with doc_no's <=1872, and then hide the rest. You wouldn't be able to offer any insight on any of this behaviour would you?

Thanks again though, you pointed in me in the right direction, and now it works :)!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-02-23 : 15:27:49

ooops --- yow know what, try the detail_format() event, not the print. i think once you make it invisible, the print() event won't fire any more ! so try it on that one. i usually use the format() event, not sure why I put down the Print one.

as for your groups, again, you can put any expression, or create any expression in your View or SQL statement that your report is based on and use that. but i'm not sure about ADP projects i will let you know.

i.e., if your SQL is:

select *
from table

then you can say

select *, case when date <@startDate then 1 else 0 end as BeforeStart
from table

and then use "BeforeStart" as a group expression.

- Jeff

grrr223
Starting Member

30 Posts

Posted - 2004-02-23 : 17:32:56

Okay, I think I get it now, so you're saying to just add a new column to my query with an expression that evaluates to a simple numeric value and then group by that column? That way I can make the expression give me anything I want and I don't have to change the report.

[edit after bright idea]

OR, did you mean...

I guess I can add a calculated field on my report layout and group on that, right? I think I'll try that because it's a lot easier to use VB to change that formula than it is to change an expression in the query.

[edit after proving bright idea wrong]

Damn, I guess I have to add a column to the query because you can only group by columns in the record set. Which I guess is fine, it just doesn't provide that extra little bit of flexibility that is always nice. :)

Thanks, you've been a great help :)!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-02-23 : 17:40:31

yup either or. again, in the "sorting and grouping" box, you can type in any expression you want to group by or sort your report by. the only question is, for ADP's, what kind of expression does it want: a sql expression or a VB expression. I've barely used ADP's, so I'm not sure!!

EDIT: just checked, it wants a VB expression in the grouping/sorting box.

so just put it in with an equals sign !

= iif(Amt < 1,0,1)

or

= Amount * rate

or whatever you want. not sure if you can use parameters, though. test it out or check HELP ....

- Jeff

grrr223
Starting Member

30 Posts

Posted - 2004-03-09 : 13:07:50

I ended up adding a column I call GroupByColumn to my query with a case statement that returns simple integer values and then I sort my report on that column, it works great! It gives me all the control I could imagine in sorting my report because I can just change the Case statement to return different values based on different criteria.

Thank you for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources