Using command parameters

[TXP007]

I am using CRXI against SQL server tables.

I am using an ODBC command as my data source, as -LB suggested in thread767-1523885. It seems to be working correctly. Now, I am trying to figure out how to use the parameters using the command option.

Let's say I have 2 tables: Hair and Sales.

The Hair table looks like this.

ID	Color
1	Black
2	Brown
3	Blonde
4	Red
5	Grey

The Sales table looks like this.

Sales
ID	Date of Sale	Salesperson	Salesperson_Hair_Color	Sale_item	Sale_Item_Category
1	2/4/2008	John	Brown	Apple	Fruit
2	2/5/2008	Mary	Black	Orange	Fruit
3	4/8/2008	Bill	Blonde	Hat	Clothing
4	7/6/2008	Jen	Brown	Apple	Fruit
5	9/5/2008	Tim	Black	Shirt	Clothing
6	9/13/2008	Lisa	Blonde	Radio	Electronics
7	9/22/2008	Dave	Grey	Gloves	Clothing
8	10/3/2008	Bill	Blonde	Calculater	Electronics
9	11/22/2008	John	Brown	Orange	Fruit
10	12/3/2008	Mary	Black	Radio	Electronics

I have a left-outer-join
Hair.Color ----> Sales.Salesperson_Hair_Color. This will establish my hair color groups and allow me to see all hair colors, even if the join produces a null or zero.

How do I run the report for each Sales.Sale_Item_Category? One report per category (Fruit, Clothing, Electronics). I want to define the Sales.Sale_Item_Category(s) I want to see reported on. In other words, I know which Sale_Item_Category(s) I want to be passed as parameters. I want to run one report, using a pre-defined list of Sale_Item_Category(s) that will generate a seperate report for each pre-defined Sale_Item_Category.

Any suggestions?

Thank you for your time,
TXP007

 

[kskid]

I have used the following

(
isnull({sales.Salesperson_Hair_color}) or
(
not isnull({sales.Salesperson_Hair_color}) and
{sales.Sales_Item_Category} in ["Fruit","Electronics"]
)
)

 

[lbass]

Change the command to something like the following, but adding in the additional fields in the select clause:

select hair.color, sales.Salesperson_Hair_Color
from hair left outer join sales on
(
hair.color = sales.Salesperson_Hair_Color and
sales.Sale_Item_Category = '{?SaleItemCateg}'
)

You should create the parameter within the command (on the right) and then double click on it to place it where your cursor is within the from clause of the command.

-LB