Solved

Problems running Crystal Report from VB.Net when Parameters / Record Selection not set programmatically

Posted on 2009-04-01
8
709 Views
Last Modified: 2013-11-26
I have a VB.Net application that allows users to browse, select, edit and run Crystal Reports.  The program design is very simple: when the Run option is selected, the program does not dynamically set the RecordSelectionFormula neither does it set any Parameter values.  Instead, the users simply key in the Parameter values in the pop up displayed by the Crystal Report Viewer and rely on the record selection formula that is already set in the Crystal Report.

The problem is that, when the report is run using the attached code the Parameters window is not displayed and so the users are not able to set the parameter values.  If, however, I uncomment out the oDoc.Refresh line, then the parameter window is displayed but the reports RecordSelection formula does not work.

This should be easy but it is giving me a lot of grief!



Dim crDatabase As Database

Dim crTables As Tables

Dim crTable As Table = Nothing

Dim crConnInfo As New ConnectionInfo

Dim crLogonInfo As TableLogOnInfo

 

oDoc = New ReportDocument

oDoc.Load(_ReportFilePath)

crDatabase = oDoc.Database

crTables = crDatabase.Tables

 

 'set connection info  ---------------------------------------------------------

 For Each crTable In crTables

       With crConnInfo

               .ServerName = gServer

               .DatabaseName = gDatabase

               .UserID = gUserName

               .Password = gUserPassword

       End With

       crLogonInfo = crTable.LogOnInfo

       crLogonInfo.ConnectionInfo = crConnInfo

       crTable.ApplyLogOnInfo(crLogonInfo)

       crTable.Location = gEnterpriseMRM_Database & ".dbo." & _

       crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1)

Next '------------------------------------------------------------------------

 

 crViewer.ReportSource = Nothing
 

     ' when commented out Parameters window not displayed

     ' when left in, Parameters window is displayed but ReportSelectionFormula does not work

    'oDoc.Refresh()              
 

crViewer.ReportSource = oDoc

Open in new window

0
Comment
Question by:ccravenbartle
  • 4
  • 4
8 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 24040575
What version of Crystal?

Is the report saved with data?

mlmcc
0
 

Author Comment

by:ccravenbartle
ID: 24047655
Crystal Reports XI Release 2

If I have the [Save Data with Report] setting ticked in the Crystal Report then the Parameters window does not open unless I manually click the Refresh button in the viewer or uncomment out the oDoc.Refresh.

If I do not have the [Save Data with Report] ticked then the Parameters window does display.

In neither case does it appear to evaluate the Record Selection Formula
{PubManuscript.PaperAccepted}=true
so it prints all Manuscripts not just the ones which have been Accepted.
If I do a msgbox (odoc.RecordSelectionFormula) immediately before the
crViewer.ReportSource = oDoc line
then it shows
( {PubManuscript.PaperAccepted} )
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 24052756
Can the field be NULL?

If so you need to test for it.  Crystal returns an unknown value when it evaluates a NULL value.  In my experience in the record selectioin it returns TRUE

Try
Not(IsNull({PubManuscript.PaperAccepted})) and ({PubManuscript.PaperAccepted}= TRUE)

mlmcc
0
 

Author Comment

by:ccravenbartle
ID: 24057471
The field can be null and I modified the report accordingly but it still failed.
However, I have now discovered the root of the problem - it is to do with the Group Selection and Record Selection formulas.  I do not understand what is happening though.

The Report has been written to use this Group Selection Formula
{PubManuscript.PublicationType} = {?Publication type} AND
{PubManuscript.PublicationVolume} = {?Volume} AND
{PubManuscript.PublicationIssue} = {?Issue}
and this Record Selection Formula
({PubManuscript.PaperAccepted}= TRUE)

Scenario 1 - Using Group and Record Selection as above
Run from Crystal Reports Designer - works OK
Run from VB Application - Group Selection is evaluated but not Record Selection

Scenario 2- Using Record Selection only (i.e. place all 4 formula elements within Record Selection)
Run from Crystal Reports Designer - works OK
Run from VB Application -  Neither the Group Selection or the Record Selection is evaluated - all records are returned.

Scenario 3- Using Group Selection only (i.e. place all 4 formula elements within Group Selection)
Run from Crystal Reports Designer - works OK
Run from VB Application -  works OK

Any ideas?


0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 100

Expert Comment

by:mlmcc
ID: 24062777
Can any of those fields be NULL?

mlmcc
0
 

Author Comment

by:ccravenbartle
ID: 24078383
Yes: {PubManuscript.PublicationType} ,{PubManuscript.PublicationVolume}  and PubManuscript.PublicationIssue}  can all be NULL
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 24083883
You need to test for NULL.  What do you want to happen if a field is NULL?

Use IsNull

Something like this
(Not IsNull({PubManuscript.PublicationType} ) AND {PubManuscript.PublicationType} = {?Publication type})
AND
(Not IsNull({PubManuscript.PublicationVolume}) AND {PubManuscript.PublicationVolume} = {?Volume})
 AND
(Not IsNull({PubManuscript.PublicationIssue}) AND {PubManuscript.PublicationIssue} = {?Issue} )
({PubManuscript.PaperAccepted}= TRUE)

mlmcc
0
 

Author Closing Comment

by:ccravenbartle
ID: 31565314
Thank you for your persistence.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now