Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Filling a combo box with a formula field from Crystal Reports

Posted on 2004-08-26
10
Medium Priority
?
373 Views
Last Modified: 2009-07-29
Hi,

     Earlier, a question of mine was answered which caused missing records to no longer be missing when switching from Crystal Reports 10 (where I create my reports) into VB.NET (where I write programs to display and select data to be displayed in these reports for management).  However, this required me to use a formula field for my group field, and in effect, I seem to be having trouble filling my combo box that used to take data from the static field.  Here's what I had before:


        Dim cn As ADODB.Connection
        Dim rs As ADODB.Recordset
        Dim sCombo As String
        ' ---------------------------------------------------------------------
        sCombo = "SELECT DISTINCT Employee FROM Supplies_Trans where Employee is not null"

        cn = New ADODB.Connection
        rs = New ADODB.Recordset

        cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=g:\crmdb.mdb"
        cn.Open()

        rs.Open(sCombo, cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic)

        'now populate the combo box
        cmbEmp.Items.Add("ALL")

        Do While rs.EOF = False
            cmbEmp.Items.Add(rs.Fields("Employee").Value)
            rs.MoveNext()
        Loop

        'clean up
        rs.Close()
        cn.Close()
        rs = Nothing
        cn = Nothing
        ' ----------------------------------------------------------------

        cmbEmp.SelectedIndex = 0


My new main group field, instead of Employee, is @EmpFml, which changes any null employee value into 'Unowned' so that these unowned items will show up in my report, whereas before every record with a null Employee value was vanishing in VB.NET.

I've tried:
            cmbEmp.Items.Add(rs.Fields("@EmpFml").Value)  
            cmbEmp.Items.Add(rs.Fields("EmpFml").Value)  

and a few other less educated attempts, but I'm just not sure how to properly reference the formula field in a statement not formatted as {table.@formula} - I've looked over these boards to keep from posting a stupid question, but here I am, asking, what am I missing?

JP
0
Comment
Question by:gleznov
[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
  • 6
  • 4
10 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 11906532
Yoiu can't get at the report formulas from VB.  The only thing you can do is add the NULL replacing value as you added ALL

        'now populate the combo box
        cmbEmp.Items.Add("ALL")
        cmbEmp.Items.Add("UNOWNED")

mlmcc
0
 

Author Comment

by:gleznov
ID: 11906773
I need to be able to reference the correct records, and the database is constantly updating.  For instance, there may be people on there tomorrow that aren't today, and vice versa.  I could do what you said, and then read all the employee records in I suppose, but then I'd still need to be able to access that formula field.  For instance, what I've been using:


        If cmbEmp.Text <> "ALL" Then
            If cmbEmp.Text = "Unowned" Then
                args_holder = "{supplies_trans.employee} = 'Unowned'"
            Else
                args_holder = "{supplies_trans.employee} = '" & cmbEmp.Text & "'"
            End If
            strtyet = 1
        End If

(args holder builds depending on what's chosen from all combo boxes combined, and is then passed as the selectionformula)

So what I'll need to be able to do (since supplies_trans.employee is no longer the group, and doesn't actually hold the 'unowned' values, but null, which the EmpFml formula field writes out as "Unowned" (if null) or {table.Employee}) is to say

If they choose Unowned from the menu,
selectionformula = "{formula field} = 'Unowned'" then show all records
else, show chosen employee record

       If cmbEmp.Text <> "ALL" Then
            If cmbEmp.Text = "Unowned" Then
                args_holder = "isnull({supplies_trans.employee})"
            Else
                args_holder = "{supplies_trans.employee} = '" & cmbEmp.Text & "'"
            End If
            strtyet = 1
        End If

Isn't working...  It's returning nothing, which was my initial problem - VB.NET not recognizing any rows with the employee field null.  Since I used a formula to insert the text 'Unowned' for null values of employee and the normal value of employee for all others, I need a way to reference this, or else a whole new workaround for the fact that VB.NET won't see records with a null Employee value, whereas Crystal Reports 10 will.  

Here's my original problem and the solution I accepted, which may help too:
http://www.experts-exchange.com/Databases/Crystal_Reports/Q_21108403.html

Thanks!

JP


0
 
LVL 101

Expert Comment

by:mlmcc
ID: 11909083
What query isn't returning the correct values?

If I understand this correctly, the report is working correctly and groupin correctly using the formula.

The problem you are having is how to limit the report to a specific group?  Or is the problem in the VB.Netapplication where the drop down isn't showing the correct information?

mlmcc
0
Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

 

Author Comment

by:gleznov
ID: 11909193
Yes, the problem is with limiting the report to only those records where the employee field is null.  I could see them and use the group bar on the left to access them, but when "Unowned" is chosen from the dropdown box, I don't know how to reference the records in code.  If they choose unowned, then I need everything with formula field = 'Unowned' to be returned, and if I can't in any way access a formula field, then I'm stuck - even with those fields on the report, by trying to access isnull({table.employee}) I seem to have no data...

JP
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 1200 total points
ID: 11909266
I think I understand. You need to build a selection formula that uses the formula field.  VB doesn't do the filter the filter is passed to the report which does the filtering.

       If cmbEmp.Text <> "ALL" Then
            If cmbEmp.Text = "Unowned" Then
                args_holder = "{@EmployeeOwner} = 'Unowned'"
            Else
                args_holder = "{supplies_trans.employee} = '" & cmbEmp.Text & "'"
            End If
            strtyet = 1
        End If

mlmcc
0
 

Author Comment

by:gleznov
ID: 11911720
For some reason I'm back to square one - I have a Crystal Report that, in CR10, shows a "Unowned" on the left shortcut bar, and then when I pull the report up in VB.NET, the "Unowned" isn't there and when I choose the criteria from "Unowned", I still get just a blank sheet.  What in the world am I doing wrong?

JP
0
 

Author Comment

by:gleznov
ID: 11911894
I've rem'd out all the code for the combo boxes and that call the subroutine that deals with selection of things in the combo boxes - now the report shows with the Unowned.  Let me go through this thing piece by piece until I find the problem, and if everything works fine after I find whatever's weeding out that set of records, I'll accept your answer ml.
0
 

Author Comment

by:gleznov
ID: 11911972
I've narrowed it down to this sub:

    Sub RefRep()

        ' Refresh Report depending on fields chosen
        Dim args As String
        Dim args_holder As String
        Dim strtyet As Integer       ' controls whether or not to use 'AND' in the selection formula

        strtyet = 0

        If cmbEmp.Text <> "ALL" Then
            If cmbEmp.Text = "Unowned" Then
                args_holder = "{@EmpFml} = 'Unowned'"
            Else
                args_holder = "{supplies_trans.employee} = '" & cmbEmp.Text & "'"
            End If
            strtyet = 1
        End If

        If cmbItem.Text <> "ALL" Then
            If strtyet = 1 Then
                args_holder = args_holder + " AND "
            End If

            If cmbItem.Text = "Safety Equipment" Then
                args = "{supplies_trans.item} like 'safety*'"
            ElseIf cmbItem.Text = "Janitorial Supplies" Then
                args = "{supplies_trans.item} like 'janit*'"
            Else
                ' something that shows everything but safety* and janit*
                args = "not ({supplies_trans.item} like 'safety*' OR {supplies_trans.item} like 'janit*')"
            End If

            strtyet = 1
        End If

        args_holder = args_holder + args

' for this last section, ALL records EVER displayed on this report must meet the requirement trans_type = dispense
        If strtyet = 1 Then
            args_holder = args_holder + " AND {supplies_trans.trans_type}='dispense'"
            CrystalReportViewer1.SelectionFormula = args_holder
        Else
            CrystalReportViewer1.SelectionFormula = "{supplies_trans.trans_type}='dispense'"
        End If

        CrystalReportViewer1.RefreshReport()

    End Sub


However, I don't know what in there could possibly cause the report itself to actually vanish the "Unowned" set from it's own left-side shortcut list...  And from the data too apparently (?)

I unrem'd the combo box filling part, and Unowned still showed up - then I have 2 subs, one for selectedvaluechanged on each combo box, and they both only say: RefRep() - when I unrem one of these, I lose Unowned...

Thanks!

JP
0
 

Author Comment

by:gleznov
ID: 11912064
I found the problem, but I'm almost to embarrassed to own up to it!  In the database file, every single record that occurs as "Unowned" is currently at status (or trans_type) = 'replenish', so my last clause, for dispense, weeded them all out.  This usually wouldn't be the case but I would be trying to design this report right at the time that the whole set of records is not applicable (LOL!)  Thank you so much ml - you were a lot of help!

JP
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 11918709
Don't feel bad.  I have done that many times where I just knew there was data and I was filtering it out.

GLad I could help

mlmcc
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

719 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