Equivalent of WHERE clause in Crystal Reports

Question

This Crystal Report program was dumped on my lap and I'm brand new to it. I understand the concept, but I'm hitting a wall here.

There is another question that's similar: Crystal Reports equivalent of 'WHERE'

But it and the answers are directed towards (it seems) "totaling" the column, whereas I want to simply only show the row if one of the columns (in this case, if "CostType" is equal to 0).

I have two data tables I'm reading from to create this report. They're joined via a common column name (ItemKey). Unfortunately, in the second table, there are multiple records of the same item. I only want the item(s) with a CostType of 0.

This is stupidly easy in SQL, even for a beginner like me, but I can't figure out how to effectively say: "only show an item if the CostType is 0" with Crystal Reports.

Item_Cost Table (example):

ItemKey      Special         Cost         CostType
001          TRUE            55.00        0
002          FALSE           10.00        0
003          TRUE            20.00        1

Item Table (example):

ItemKey      Description     Mode  
001          Whatever        EE 
002          Random          AB 
003          Unimportant     GE

---

What shows up:

Item         Description     Mode  
Box          Whatever        EE 
Square       Random          AB 
Circle       Unimportant     GE

What I want to show:

Item         Description     Mode  
Box          Whatever        EE 
Square       Random          AB 

---

I hope this all makes sense. Basically, if this was a SQL query, I'd just say "WHERE CostType = 0" at the end and I wouldn't get all of the other items that have different cost types.

I should mention that this is entirely handled by Crystal Reports. I'm not using a SQL string and passing it in or something. The function for showing the window is as follows:

    Dim PartsDemandReport As CrystalDecisions.CrystalReports.Engine.ReportDocument = New crptIndentedBomXLBom
    PartsDemandReport.SetDataSource(GLOBAL_VARIABLES.GlobalBomIndented)

    Dim CrystalReportViewerForm As New frmCrystalReportViewer
    CrystalReportViewerForm.CrystalReportViewer1.ReportSource = PartsDemandReport
    CrystalReportViewerForm.Show()
    PartsDemandReport = Nothing

If there's a way to simply force Crystal Reports to add that "WHERE CostType = 0", that'd be great. Thanks in advance.

Solution

There are couple of ways to do this:

1. If you don't need the CostType not equal to 0 in whole report then you can directly give in select expert which acts as where clause for crystal reports.

Example:

go to Report ---> Selection Formula ---> Record this open formula window in that add below line

CostType = 0

This will ensure only cost type = 0 will be retrived from database

2.If you require cost type <> 0 any where in report. Then add below condition for Item, Description and Mode Columns

Example

@Item:

if cost type = 0 
then <<Database Item field >>

Place it in detail part of Item column.

In the same way do for other columns as well.

Comments

[Keith]

Excellent. Thank you very much. Today is a day off, but as soon as I can get back into the office, I will try this.

[Siva]

Np @Keith... if you face any issue let me know.

[Keith]

I finally got back at the office after a long weekend. I tried your solution, but with one tweak: putting "CostType = 0" under 'Group Selection' as 'Record Selection' wouldn't work. You have no idea how much time you've saved me. Thank you very much! I'd up-vote if I had the 15 required to do so. :)

[Siva]

@Keith... No problem... I am glad that problem was solved... Happy to help always... don't place under Group but place your conditon on another option available that is Record

[Keith]

Any idea why Group would be doing what is desired and not Record?

[Keith]

Okay. I will keep fiddling with it and keep an eye out. Thanks again, Siva