Solved

Filling a combo box with a formula field from Crystal Reports

Posted on 2004-08-26
10
359 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
  • 6
  • 4
10 Comments
 
LVL 100

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 100

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
 

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 100

Accepted Solution

by:
mlmcc earned 300 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 100

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

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…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 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

16 Experts available now in Live!

Get 1:1 Help Now