Crystal Reports with Access 2000 1

[cmmrfrds]

I am considering using Crystal Reports for my Acess 2000 application, since it is an industry standard and robust report writer. Also, the customer wants to use it.<br>
<br>
Is Crystal Reports easy to integrate with Access. I will have a menu in Access where the reports will be selected and executed from.<br>
<br>
Any show stopper limitations? <br>
<br>
Should I treat the Crystal Reports as a separate application?<br>
<br>
Anybody integrating Access and Crystal Reports?

 

[JimHorton]

I will just give an opinion here. I believe that Access' report writer is easier to use and more flexible than Crystal. However, you can't create canned report objects in Access--if that's a need, use Crystal. If the data is already Access data, and the user will be running the reports direct from an Access mdb, I see little reason to use Crystal. Otherwise, you can set crystal to using an access.mdb as a datasource.<br>
--Jim

 

[elizabeth]

I would also be interested in hearing about any advantages of Crystal over Access, as I've been asked to use Crystal for a combination of Access & Oracle dbs. Re: canned reports, when I was using Access 97, I was able to create canned reports that were viewable with a freely-distributable Access report viewer. I think it may have been called Snapshot Viewer.

 

[DougP]

Hey Elizabeth,<br>
Do you know where to get that groovey Access report viewer?<br>
&gt;&gt;&gt;&gt;&gt; &quot;I was able to create canned reports that were viewable with a freely-distributable Access report viewer. I think it may have been called Snapshot Viewer&quot;<br>
Also<br>
Have you used the Developer to make a standalone Access database's that do not need Access?<br>
If so how is the performance? <p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[elizabeth]

DougP,<br>
<br>
Snapshot Viewer available as download:<br>
<A HREF="

http://support.microsoft.com/support/downloads/DP2826.ASP"

TARGET="_new">

http://support.microsoft.com/support/downloads/DP2826.ASP</A><br>

<br>
Now that I have the Developer Edition of Office2000, I have no need need to use that capability. Well, no complaints, as not needing what I've got is less stressful than not getting what I need...

 

[cmmrfrds]

Thank you for all the quick responses. The customer has Crystal Reports and is pulling data from a proprietary accounting database and we may need to integrate the data with Access data for reporting -- not sure yet. At this point, I will probably start with some reports in Access and see what the response is and if any limitations develop.<br>
<br>
Again, thank you.

 

[elizabeth]

cmmrfrds, please post if you find any limitations. thanks!

 

[FunkyMonkey]

I have found an advantage to using Crystal reports!

I had been at a loss to find any significant advantages of Crystal reports over natively produced Access reports. However, the mechanism for building a report based on a crosstab query is completely different in Crystal reports than Access.

In Access, if you design a report based on a crosstab query, you fix the number of columns. Given that both the number of rows and columns in a crosstab is fluid, the fixed format of the Access reports will generate an error message for each erroneous column if there is any deviation from the original report design. I have yet to figure out a method of emulating what Crystal does effortlessly...

In Crystal, You base the report on the crosstab and in the design view, you only see the basic format of column and row headers, not the number of columns you may have. The number of columns that appear in your crosstab query is automatically catered for with this feature. It is rather useful, but the rest of the time, I somehow prefer bloody Access.

Peace.

 

[JimHorton]

Funky,
< I have yet to figure out a method of emulating what Crystal does effortlessly >
'Effortlessly' is the key word here. In access, it's fairly easy to dynamically build reports.

I wouldn't say it's 'effortless', so it might not fit what you're looking for, but I've never done crosstab in Crystal so I don't know what to compare it to--one reason I've never done Crosstab in Crystal is that in Access, the CreateReportControl method has worked very, very well for me. If using the CreateReportControl seems like too muich coding, it may even be easier to just create a report with the controls already there (if you know of a reasonable Maximum number of columns that will be generated) and just toggle the .Visible Property.

As far as the underlying query, in Access' crosstab sql, if you know a max number of columns, you use the IN(col1,col2, etc) clause to force Access to generate the columns in the base query--if the column data is there, it's there, if it's not, the column/row intersection is Null--no big deal. Either way, dynamic crosstabe query/reports in Access are not a high hurdle.

In my opinion, this is far less effort than worrying about crystal .ocx issues on the client machine, which, like many .ocx issues, can be a headache, especially since Access has no 'Setup.exe' generator.
--Jim
-

 

[FunkyMonkey]

As I am not a programmer, I am easily lost when delving into technical issues such as what you are discussing.

when I use the word 'effortless', of course this can only be viewed subjectively. Apologies for clumsiness / lack of clarity in that regard. What I was referring to was that I have thus far been unable to do (without resorting to coding) what Crystal provides a visual interface for,
that which is within my knowledge. That does not, in this case, extent to visual basic.

Even if the number of columns I could control, I would still, by definition, want to change the column headings. The reports I use are open orders for the last 5 days. So in that regard, it is not the column count that is the problem, but the labels attached to the columns. Can I 'easily' create a dynamic report based on a crosstab of this nature?

 

[B827]

I don't know what the latest versions of Crystal are like but I find, from past experience, Access does produce better looking reports more easily and quickly. If a Crosstab is involved then it is easier to put the data into Excel and use a Pivot table. You can then use all of Excels facilities on Pivot Tables to evaluate the data.

Even crosstabs in Access can cope with &quot;missing data&quot; for instance you force a column header for every month of the year whether data is there or not. So if you know what all the column headers are going to be then you can create an Access crosstab and report. Sandy

 

[JimHorton]

Funky,
Ok, put the below code into a module. Make sure your query name is replaces the one below in the Set qd = db.Querydefs.... statement, and make a report and replace that name in the below OpenReport statement. There is another short block of code that goes into the report
Sub CrtCross()
'alters PIVOT clause in existing query to reflect current date and and the previous 4 days (5 days)
Dim db As Database, qd As QueryDef, strSQL As String, strPVT As String
Dim iPos As Integer, dtStart As Date
Set db = CurrentDb
dtStart = Date 'for now, the date we look at for the 'last 5 dates' is today, but you could pass this in
'set an object for the query
Set qd = db.QueryDefs(&quot;qryTestCross&quot
'get base sql
strSQL = qd.sql
'Find the word 'PIVOT'
iPos = InStr(strSQL, &quot;PIVOT&quot
'strip everything after pivot, but first make sure it was found
If iPos = 0 Then
MsgBox &quot;Invalid Crosstab sql&quot;
Exit Sub
End If
strSQL = Left$(strSQL, iPos - 1)
'now add the new pivot statment, reflect todays 'Last 5 dates' in the Pivot clause. Assume for this case the field is named &quot;ord_date_d&quot;
strPVT = &quot; PIVOT ord_date_d IN(#&quot; & dtStart - 4 & &quot;#,#&quot; & dtStart - 3 & &quot;#,#&quot; & dtStart - 2 & &quot;#,#&quot; & dtStart - 1 & &quot;#,#&quot; & dtStart & &quot;#)&quot;
'put the old statement together with the new PIVOT clause
strSQL = strSQL & strPVT
'Update the query itself
qd.sql = strSQL
'Open the report
DoCmd.OpenReport &quot;rptTestCross&quot;, acViewPreview
End Sub

************* now the below goes into the report, in the OPEN event:
Private Sub Report_Open(Cancel As Integer)

'VERY IMPORTANT: You must name the 5 labels in the following manner:
'lblDateMinus0 , lblDateMinus1, lblDateMinus2, etc, etc
'AND NAME THE TEXTBOX's as follows:
'txtDateMinus0,txtDateMinus1,txtDateMinus2 etc.
'format labels
Dim i As Integer
For i = 0 To 4
'the labels I just used a different date format, you could use any
Me(&quot;lblDateMinus&quot; & i).Caption = Format(Date - i, &quot;MMM/DD/YYYY&quot
Next i
'set textbox controls
For i = 0 To 4
Me(&quot;txtDateMinus&quot; & i).ControlSource = &quot;=[#&quot; & Date - i & &quot;#]&quot;
Next i
End Sub

In the first block of code, I used the variable dtDate, which is set to Date(), meaning today's date. An enhancement would be to change it to, say, a date on a form that you enter. IF that is done, then the code in the open event must fetch the date from that textbox.
--Jim