Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2009-04-01
8
Medium Priority
?
721 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 101

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 101

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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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
 
LVL 101

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 101

Accepted Solution

by:
mlmcc earned 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

610 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