selection of multiple products

[gagani]

I have a order_product table like this:
orderid productid productname
100 1 A
100 2 B
100 3 C
101 1 A
102 3 C

In the front end,user has a dropdown which has got product names.
It's ok when user makes single selection (like A or B or C).
In the crystal reports, I have taken a parameter field {?product} and is working fine with the following:

if ({?product} = "A") then
(
{ORDER_PRODUCT.PRODUCTID}= 1

)
else if ({?product} = "B") then
(
{ORDER_PRODUCT.PRODUCTID} = 2
)

But when user selects multiple products, then I am unable to implement in crystal reports.

The following one cannot work out and is resulting in the no output as there wont be any orderid with both productid as 1 and 2 at the same time.

If ({?product} ="A+B") then
(
{ORDER_PRODUCT.PRODUCTID}=2 AND {ORDER_PRODUCT.PRODUCTID}=1

)

In the above, writing
If({?product} = "A+B")is ok
but can anyone change the part
{ORDER_PRODUCT.PRODUCTID}=2 AND {ORDER_PRODUCT.PRODUCTID}=1 to fit my requirement.

 

[crystalkiwibruce]

Why not use a Selection of
{table.productname}={?parameter}

That works for single or multiple values.

Bruce Ferguson

http://www.crystalkiwi.com/nlog.htm

 

[lbass]

Just use the product ID as the value in the parameter list, and the product name as the description field. Set it up to allow multiple values as Bruce suggested, and use his selection formula.

-LB

 

[gagani]

In the above table, when the user selects multiple products, lets say A and B, then the output should be only for orderid 100.

With the suggested formula from Bruce, I am getting the output as
100
101

It is infact checking for the condition of 'A' or 'B'.

My requirement is: for the selection of multiple products, it should check for the orderid which has got both the products. From the above table, only orderid 100 has both the products. So the output should only be 100 in the above case.

Could you please help me.
thanks

 

[lbass]

Use the record selection formula mentioned earlier, and set up the parameter the same way, with individual values in the list of values, but allowing multiple values.

Insert a group on {table.orderID} and then go to report->selection formula->GROUP and enter:

distinctcount({table.productname},{table.orderID})=ubound({?Parameter})

-LB

 

[gagani]

it's still resulting in the same result

 

[lbass]

I think you have implemented this incorrectly. Please explain exactly what you did, and show the related data--before and after.

-LB

 

[gagani]

I am sorry, i didnt explain it clearly.

the earlier report has two groups already, one based on the selection of branch
and the other based on the selection of financetype by the user.
and in the group header section of financetype , it has got distinctcount of orderid
it was like this before:

alaska

cash 3
finance 2
others 2

china

cash 2
finance 2
others 2

Your formula has worked when i place the group for orderid in between
branch and financetype groups. but the output is as follows:

alaska

cash 1
cash 1
finance 1
cash 1
others 1
finance 1
others 1
........
your concept is working, but i want the financetype to be combined(instead of mentioning separately for each orderid)
in addition to implementation of orderid grouping.

if i place the group of orderid after financetype, then there is no
effect in the output.
if i place the group of orderid in between branch and financetype
groups, then i am able to get the required thing except that the
financetype is coming out separately as above.

 

[lbass]

I am confused by your fields. Please explain which fields correspond to your original sample of Order ID, Product ID, and ProductName.

In other words, I can't tell from your new layout what value you are using for the parameter, and what group level you are testing for the presence of all parameter values.

-LB

 

[gagani]

I will try to explain the previous situation and the current status of the report.
It is a huge report joining many tables( which are combined to fit the selection of branch and financetype) and it has also got 2 group headers(one for the branch and the other for financetype) already. In the front end, user has to select the branch from dropdown and financetype from the dropdown.
Within the report, based on the selection of the branch and financetype, it will give the distinctcount of orderid of the following table in joining with other tables. I am writing the example of the table which is significant

orderid A B C D
100 1 2 3 4
101 2 0 0 0
102 0 0 3 0

base on the above table, the output earlier was

alaska

cash 3
finance 2
others 2

china

cash 2
finance 2
others 2

......
the number beside the financetype(cash/finance/others) are the distinctcount of orderids for that particular branch and for that financetype.

In the new situation, we are not considering the above table exactly but a modified new table has been created with the same data which is as follows:

orderid productid productname quantity
100 1 A 1
100 2 B 2
100 3 C 3
100 4 D 4
101 1 A 2
102 3 C 3

And there is an introduction of new dropdown in the front end to select the product also. so there would be 3 selections in front end now(for branch, financetype, product). In the report, I have to replace the old table with the new table now.

I have introduced one group based on orderid to fit the multiple selection of products as per previous replies from you.

But since the introduction of new group header in the report, the output is like:

alaska

cash 1
cash 1
finance 1
cash 1
others 1
finance 1
others 1
........

it has split for every orderid as there is new introduction of group.
The above output should be like
cash 3
finance 2
others 2

The cash/finance/others is the group name based on the selection of financetype. In short, Rather than coming the output individually for each orderid, the group for each type should be combined with the count of orderids concerned to it.

 

[lbass]

What are your groups, in order? I would think you would be looking at:

Group #1 Branch
Group #2 Type
Group #3 ID

This assumes that you want to test the presence of all products within the ID group. My suggestion would work for this. It looks to me like you added a group and made it Group #2.

-LB

 

[gagani]

Thank you for your time.

In the header section of Group Type, it has distinctcount(orderid) along with some other summary fields.
If I place Group #3 as ID, it is not giving the required results since it has already did the distinctcount of orderid in the previous group itself. Making Group #3 as ID is making no difference.

Yes, you are right. I made it the ID as Group 2 and type as group 3 and it is resulting in

alaska

cash 1
cash 1
finance 1
cash 1
others 1
finance 1
others 1
........

In short, when I made in the sequence as,

Group #1 Branch
Group #2 Type
Group #3 ID
then
Group #3 ID is not making any differrence as the Group #2 Type header section has distinctcount(orderid) in it.

Is it possible to make the above obtained result to be combined and get the result as

cash 3
finance 2
others 2

with the groups in this order
Group #1 Branch
Group #2 ID
Group #3 Type
since this order is giving me the required results. But I want it to be combined as

cash 3
finance 2
others 2

 

[lbass]

Please go into report->group expert and identify the actual fields being grouped on. If Cash, Finance, etc. are instances of Type, and you are looking for the distinctcount of IDs within Type, then the order must be Branch, Type, ID.

Aside from this, the original problem was how to show groups of IDs that contain all parameter values for Program Name, and you haven't explained at all how the Program Name relates to the Branch, Type, and ID. I am assuming that the Program Name you are trying to test is within each ID group. Please clarify.

-LB

 

[gagani]

yes, the productname which I am trying to test is within each ID
group.
But If I make the group order as
branch
type
ID

Then it is not reflecting anything with changes I need, group header of type has already got a field of distinctcount of orderid.
Group ID is making no difference being in the 3rd place.

 

[gagani]

By following this order,
group#1 branch
group#2 id
group#3 type
Is there anyway I can summarize the results obtained into another group underneath as the results are obtained based on orderid individually.

If I can summarize the obtained results, it will solve my purpose.

thanks

 

[lbass]

Please show detail level data for these fields for multiple detail level rows:

Branch Type Order ID Product ID Product Name

Then show a sample parameter selection using at least two values of Product Name and for the same sample, show the results you are expecting to see.

-LB