Solved

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

Posted on 2009-04-01
8
707 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

760 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

20 Experts available now in Live!

Get 1:1 Help Now