How to get a range starting from lowest to higher values?

[Malik1969]

Hello CR Gurus,

I am beginner to CR 9.0. I have table of items belong to different vendors with different prices. I want make a list based on following pattern:

ITEMS VENDORS
Monitors IBM($60*) NEC($50) DELL($40) COMPAQ($30)
Mouses DELL($2) IBM($1.8) COMPAQ($1) NEC($0.5)

* Vedors shall be arranged from lower to highest values. In exmaple above(record1), IBM has lowest price, while NEC is second to lowest and similary COMPAQ is highest among other. In other words, I just want dispay preferrences based on vendors; i.e. what is least lowest and what are next to it. Values are not required to be mentioned, only vendor's array.

Can someone help me to achieve above?

Thanks in advance.

N. Malik

 

[oj75]

It would be very easy to do something like this in crystal:
Monitors
IBM($60*)
NEC($50)
DELL($40)
COMPAQ($30)
Mouses
DELL($2)
IBM($1.8)
COMPAQ($1)
NEC($0.5)

You would just insert a group with ItemID Or ItemName as the group field. Then add vendor price as a Sort field using the Sort Field Expert. Don't see an easy solution right off to format it the way you stated in your post.



OJ
DB/.Net/CR Developer

 

[Malik1969]

Thanks for the quick answer. I understand that it is very easy the way you explain. But I want to display the data in cross-tab format, e.g.

Monitors $60* $50 $40 $30
Mouses $2 $1.8 $1 $0.5

I tried cross-tab but in summarised field I have default option of showing min. value, while I want write all values starting from minium to highest.

In case I am not clearing my viewpoint, please let me know to send you screen shots.

 

[dgillz]

This is a classic case of needing to use the 3 formula approach with variables.

First, create a report exactly like the one OJ75 menioned, but hide the details section and the group header.

The create the following 3 formulas:

In the group header, to reset the list at each group:
WhilePrintingRecords;
Stringvar List:=""

In the details section, to build the list:
WhilePrintingRecords;
Stringvar List:=List&{SupplierField}&" "&{PriceField}&" "

In the group footer, to display the list:
WhilePrintingRecords;
Stringvar List;

2 things to watch out for:
1) If you are using crystal 8.5, the result of the string can be a maximum of 256 characters. If your string is going to exceed that you will get an error. Crystal 9 is much more forgiving, I believe you get 64,000 characters.

2) If you have your group headers set to repeat on every page, the reset formula above will reset the string in the middle of any group that spans more than one page. To avoid this, add the statement "Not InRepeatedGroupHeader" at the beginning of the formula.

Let me know if you have any questions.








Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[Malik1969]

Dear dgillz,

Thanks for your solution. Perhaps I did not follow correctly. I have an access table "ITEMS" in following manner:

ID Item Vendor Price
1 Monitors IBM $60
2 Monitors NEC $50
3 Monitors HP $40
4 Monitors COMPAQ $30
5 Mouse IBM $2
6 Mouse NEC $3
7 Mouse HP $4
8 Mouse COMPAQ $5

I made general report as proposed by OJ75 menioned above. This is SQL Statement for that;

SELECT `ITEMS`.`Item`, `ITEMS`.`Vendor`, `ITEMS`.`Price`
FROM `ITEMS` `ITEMS`
ORDER BY `ITEMS`.`Item`, `ITEMS`.`Price`

Now I followed your proposed solution and did following:

- Supress Group Header #1: ITEMS.Item-A
- Supress Details

Made 3 formulas as you advised;

@Header Formula

WhilePrintingRecords;
Stringvar List:=""

@Detail Formula

WhilePrintingRecords;
Stringvar List:=List&{ITEMS.Vendor}&""&{ITEMS.Price}&""

@Footer Formula

WhilePrintingRecords;
Stringvar List;

Finally I put @Header in Group Footer #1: ITEMS.Item-A and put @Detail in same Group footer next to header and finally @Footer in Report Footer. This is what I get when I refresed the report;

Monitors <Empty result for @Header> IBM 60.00
Mouse <Empty result for @Header> COMPAQ 5.00

But this is not I want, I want to display all prices starting from lowest value to highest againt each item based on Vendors. For instance like this;

Monitors COMPAQ 30.00 HP 40.00 NEC 50.00 IBM 60.00
Mouse IBM 2.00 NEC 3.00 HP 4.00 COMPAQ 5.00

I think I am missing some slight part to obtain above results. Can you please guide me what to do to obtain desired results?

Thanks for your time.

Nadeem

 

[dgillz]

You missed the formula in the details section:

WhilePrintingRecords;
Stringvar List:=List&{SupplierField}&&quot; &quot;&{PriceField}&&quot; &quot;

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[Malik1969]

Thanks and it is okay now. I want to go one step further, perhaps you can get me good idea. As I wish to represent Vendors with their Codes instead of their real names; so

COMPAQ becomes &quot;1&quot;
HP becomes &quot;2&quot;
NEC becomes &quot;3&quot;
IBM becomes &quot;4&quot;

Finally report will TRANSLATED FROM;

Monitors COMPAQ HP NEC IBM
Mouse IBM NEC HP COMPAQ

TO;

Monitors 1 2 3 4
Mouse 4 3 2 1

This is to avoid long vendor names and to display items with vendor codes for clean list.

Your ideas are welcome to acheieve this. Thanks.

Nadeem

 

[Malik1969]

Waiting for your suggestion.

Thanks.