Crystal report SelectionFormula is not being applied

PenningtonCounty
PenningtonCounty used Ask the Experts™
on
After going through the Knowledgebase, I am still stumped I have a Crystal Reports that pulls information from a SQL 2008 database.  In an application that is written using VB.NET 2008 I am trying to filter the report so that it will show a user specificied record instead of all the records.

Below is the code in question.  Instead of applying the filter, I am still seeing all the records.


If sSelectionFormula.Length > 0 Then
   'objReport.RecordSelectionFormula = sSelectionFormula
   objReport.DataDefinition.RecordSelectionFormula = sSelectionFormula
End If

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
As a side note. Every time I do any form of refresh on the report, I get a dialog box asking for a user name and password.  Neither of which exists as I am using Integrated Security to avoid this requirement.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
WHat is the rest of the code?

Is this a WEB application?

mlmcc

Author

Commented:

This is not a web application.

Below is the code for the function in question.
    Friend Function ViewReport(ByVal sReportName As String, Optional ByVal sSelectionFormula As String = "") As Boolean
        'Declaration of local variables
        Dim intCounter As Integer
        Dim intCounter1 As Integer
        Dim objReport As New CrystalDecisions.CrystalReports.Engine.ReportDocument

        Dim ConInfo As New CrystalDecisions.Shared.TableLogOnInfo

        Dim mySubReportObject As CrystalDecisions.CrystalReports.Engine.SubreportObject
        Dim mySubRepDoc As New CrystalDecisions.CrystalReports.Engine.ReportDocument

        Dim sFileName As String
        Dim index As Integer

        Try
            sFileName = sReportName

            objReport.Load(sFileName)

            intCounter = objReport.DataDefinition.ParameterFields.Count
            If intCounter = 1 Then
                If InStr(objReport.DataDefinition.ParameterFields(0).ParameterFieldName, ".", CompareMethod.Text) > 0 Then
                    intCounter = 0
                End If
            End If

            'Set up parameter values----------------------------------------------------------
            crParameterFieldDefinitions = objReport.DataDefinition.ParameterFields
            crParameterFieldLocation = crParameterFieldDefinitions.Item("UpdateType")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = "Cash Turn In"
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            crParameterFieldLocation = crParameterFieldDefinitions.Item("DesiredDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = "01/27/2010"
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            crParameterFieldLocation = crParameterFieldDefinitions.Item("DesiredRecordID")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = 162
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)
            '---------------------------------------------------------------------------------

            'Setup connection information-----------------------------------------------------
            ConInfo.ConnectionInfo.IntegratedSecurity = True
            ConInfo.ConnectionInfo.ServerName = "SERVERNAME"
            ConInfo.ConnectionInfo.DatabaseName = "DATABASENAME"

            For intCounter = 0 To objReport.Database.Tables.Count - 1
                objReport.Database.Tables(intCounter).ApplyLogOnInfo(ConInfo)
            Next
            '---------------------------------------------------------------------------------

            'Apply connection information-----------------------------------------------------
            For index = 0 To objReport.ReportDefinition.Sections.Count - 1
                For intCounter = 0 To objReport.ReportDefinition.Sections(index).ReportObjects.Count - 1
                    With objReport.ReportDefinition.Sections(index)
                        If .ReportObjects(intCounter).Kind = CrystalDecisions.Shared.ReportObjectKind.SubreportObject Then
                            mySubReportObject = CType(.ReportObjects(intCounter), CrystalDecisions.CrystalReports.Engine.SubreportObject)
                            mySubRepDoc = mySubReportObject.OpenSubreport(mySubReportObject.SubreportName)
                            For intCounter1 = 0 To mySubRepDoc.Database.Tables.Count - 1
                                mySubRepDoc.Database.Tables(intCounter1).ApplyLogOnInfo(ConInfo)
                            Next
                        End If
                    End With
                Next
            Next
            '---------------------------------------------------------------------------------

            'Apply desired filtering----------------------------------------------------------
            If sSelectionFormula.Length > 0 Then
                'objReport.RecordSelectionFormula = sSelectionFormula
                objReport.DataDefinition.RecordSelectionFormula = sSelectionFormula
            End If
            '---------------------------------------------------------------------------------

            rptViewer.ReportSource = Nothing
            rptViewer.ReportSource = objReport
            rptViewer.Show()

            Application.DoEvents()

            Me.Text = sReportName
            MyBase.Visible = True
            Me.BringToFront()

            Return True

        Catch ex As System.Exception
            MsgBox(ex.Message)
        End Try
    End Function

Open in new window

OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
What selection formula are you passing in?

mlmcc
Most Valuable Expert 2012
Top Expert 2014

Commented:
Even though its about Oracle in the below link, i think same would apply to SQL Server

http://www.discussweb.com/vb-net-programming/1140-crystal-report-vb-net.html

Author

Commented:

mlmcc:  At the moment, I am not using any as I couldn't get anything to be accepted.  The last parameter that I have listed is used by the report to determine which record to view.

At this point. I can even get the report to be viewed as it keeps asking for a login and password.  Neither of which exists as I am using Integrated Security(this option has to be used per IT).  Below is an updated version of my code.
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared

Public Class frmViewReport

    Private report As CashBuy
    Private crParameterDiscreteValue As ParameterDiscreteValue
    Private crParameterFieldDefinitions As ParameterFieldDefinitions
    Private crParameterFieldLocation As ParameterFieldDefinition
    Private crParameterValues As ParameterValues


    Private Sub frmViewReport_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        report = Nothing
        report = New CashBuy

        rptViewer.Refresh()
        ConfigureCrystalReports()

    End Sub

    Private Sub ConfigureCrystalReports()
        crParameterFieldDefinitions = report.DataDefinition.ParameterFields

        crParameterFieldLocation = crParameterFieldDefinitions.Item("UpdateType")
        crParameterValues = crParameterFieldLocation.CurrentValues
        crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
        crParameterDiscreteValue.Value = "Cash Turn In"
        crParameterValues.Add(crParameterDiscreteValue)
        crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

        crParameterFieldLocation = crParameterFieldDefinitions.Item("DesiredDate")
        crParameterValues = crParameterFieldLocation.CurrentValues
        crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
        crParameterDiscreteValue.Value = "01/27/2010"
        crParameterValues.Add(crParameterDiscreteValue)
        crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

        crParameterFieldLocation = crParameterFieldDefinitions.Item("DesiredRecordID")
        crParameterValues = crParameterFieldLocation.CurrentValues
        crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
        crParameterDiscreteValue.Value = 165
        crParameterValues.Add(crParameterDiscreteValue)
        crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

        rptViewer.ReportSource = report
    End Sub

End Class

Open in new window

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Comment out the REFRESH line

mlmcc

Author

Commented:

mlmcc,

Unfortunately, there was no change.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
What selection formula are you trying to pass to the report?

mlmcc

Author

Commented:
mlmcc,

at this point I'm not passing any selection formula at all.  As I couldn't get it to be used by the report, regardless what I tried, I gave up and switched to what I am currently using.  Inside the report itself, under Select Expert->Record I have the following formula: {CashDrawer.id} = {?DesiredRecordID}
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
If the database doesn't have security why are you trying to use Integraed Security which passes the Windows user name to the database but that user doesn't have any permissions to the database .

mlmcc

Author

Commented:

mlmcc,

I think I might have misspoke earlier.  I am using Integrated Security so that the Windows username and password is passed to the database.  The access permissions are set based on these logins.  Using myself as a test case, as I have full rights to the database, I am still not getting anywhere.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
DO you have groups set up in the database based on the Windows logins?

Is the database set for Windows Authentication?

mlmcc

Author

Commented:
mlmcc:

First Question: yes, my log in may not be in any of the groups, but it is listed as the owner with full rights
Second Question: the database was setup to use either Windows Authentication or SQL Server Authentication.
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
You might add it to a group though that shouldn't matter.

Are you using the Crystal in VS2008 or a standalone version?

mlmcc

Author

Commented:
mlmcc,

I've tried both standalone and the one in vs2008.  No difference that I've found.

Author

Commented:

I've discovered what my issue was and it had nothing to do with Crystal Reports.  One of the DLLs that I had created a while back to handle security and encryption was named Security.  I had forgotten that that name had already been used in one of the standard classes that the application was using else where.  This naming confusion is what caused the error.  Once I redid the DLL and gave it a nonconflicting name, everything seems to be working.

Thank for the assistance, though as it allowed me to take a better route in coding.

Author

Commented:
I felt the points were deserved for the help that was given.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial