How to display 3 different names in 1 line? 1

[foru11]

Hi all, I faced a difficulty here. I'm using CR 10, Oracle.

I would like to have my report to have below results:

Item Desc | Qty | Price | Vendors |
-----------------------------------------------------------------
Notebook | 1 | 4300.00 | Dell | HP | Computer |
| | | 4300.00 | 4200.00 | 4200.00 |
-----------------------------------------------------------------
The report will show the top 3 vendors from the table.

The data in table is as below:

Company_Code Division Req_Number Vendor_Name Price
2 10 01 Dell 4300.00
2 10 01 HP 4200.00
2 10 01 Computer 4200.00

Now my report is showing like this:

Item Desc | Qty | Price | Vendors |
----------------------------------------------------------
Notebook | 1 | 4300.00 | Dell | | |
| | | 4300.00 | | |
----------------------------------------------------------
Notebook | 1 | 4300.00 | HP | | |
| | | 4200.00 | | |
----------------------------------------------------------
Notebook | 1 | 4300.00 | Computer | | |
| | | 4200.00 | | |
----------------------------------------------------------

Appreciate for any help. Thx alot!!!

PS: Sorry for the table display. I do not know how should I make it easy to view. Hope it is still understandable.

 

[Madawc]

If you group by Vendor, do a summary total of sales and then use the TopN function, you could show just the top three vendors. But on three successive lines, group header or footer with the rest suppressed.

I can't see a way to get them all on one line, unless you have SQL and used a Stored Procedure to turn the 'sales by vendor' details into individual records.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

Insert a crosstab that uses vendor as the column field, and item description and quantity as the row field, with price as the summary field. If you want this per company code or division, then insert the crosstab in a group section.

-LB

 

[badword]

I am having a hard time trying to understand what it is you are trying to accomplish. see if you can help me understand. what are the top 3 vendors from the table are based on? In other words, what makes them the top 3?

I am also trying to understand the required layout results. I see that you want the file in pipe-delimited format with the headers. Do you also want the data to be on each line with the headers? And if so, do you want the second record to be on next line,if so, what should be use to create the next record, or do you want everything to be on one line?

 

[lbass]

I missed the top3 Requirement. You can still use the crosstab approach, but you might want to start out by using a command as your datasource that limits the data to the top3. I don't have access to Oracle right now, but in Access the syntax would be like this:

select distinct top 3
(
select sum(A.`Price`)
from table A
where A.`Vendor`= table.`Vendor`
) as grpsum, table.`vendor`, table.`other fields`
From table
Order by 1 desc

-LB

 

[foru11]

Thank you for all the guidelines.

LB, I will have a try on your suggestion. Will update after my try.

 

[foru11]

Hi LB, the crosstab works just perfect!! Just for the top 3 records, where should I put the command at? Is it at Record Selection?

Pls advice. Thanks.

 

[lbass]

You create the command by going to "Add Command", above the list of tables in the database expert. You should use the command as the sole datasource for your report or add it in a subreport. Note that the syntax for the command would be different for Oracle.

-LB

 

[foru11]

Oh, okok. Got it. Sure, I will amend the syntax accordingly.
Thx a million LB!!