Showing details of the same dimension on column level in Crystal Reports.

[ashrafsharaf]

Hello,

I've been looking for a solution for the below case:

I am using Item Code as my main dimension and we want to put let's say 8 text boxes, each box contains Item Code and its details(Item name, bin location etc...), the specific item codes will be determined with parameters that the user will select when running the report.

Problem is we want them on column level next to each other, not in row level under each other.

I have tried duplicating my command several times, looked into creating a temp table but i haven't been successful so far.

Regards,

Ashraf

[DellSC]

What type of database are you connecting to? Can you post the command that you're using? I may have some thoughts about how to re-write it to get what you need.

-Dell

[ashrafsharaf]

Hi

I am using an MSSQL database, here is the command:

Select 
t0.Row_ID,
t1.ItemCode, 
t1.ItemName, 
(t1.[10]+', '+t1.[9]+', '+t1.[8]+', '+t1.[7]) as Bin 
from 
(Select * from 
(
Select ROW_NUMBER() OVER(partition by t0.ItemCode ORDER BY t0.itemcode) as Row_ID ,
* from 
(Select distinct 
a.ItemCode,
a.ItemName,
d.BinCode from oitm a 
join OITW b on  a.ItemCode = b.ItemCode 
join OWHS c on c.WhsCode = b.WhsCode 
Join OBIN d on d.WhsCode = c.WhsCode
)
T0 
)T1
PIVOT
(
    Max(Bincode)
FOR
[Row_ID]in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS a
) t1
join (Select ROW_NUMBER() OVER(partition by ItemCode ORDER BY itemcode) as Row_ID, ItemCode from itm1) t0 on t1.ItemCode = t0.ItemCode

And i suppress on CR based on Row ID, but all items have to be the same quantity.

[DellSC]

Could you, by chance, post a small data sample of the results from the "Select *..." subquery? Just a few rows will do.

Thanks!

-Dell

[ashrafsharaf]

Problem with this query is that if you suppress let's say row id 1, you suppress 1 of each item, so they all need to have even quantities and we have to set the max number manually based on duplicates:

1	ItemCode1	ItemName1	Bin 1, Bin 2, Bin 3, Bin 4, Bin 5
2	ItemCode1	ItemName1	Bin 1, Bin 2, Bin 3, Bin 4, Bin 5
3	ItemCode1	ItemName1	Bin 1, Bin 2, Bin 3, Bin 4, Bin 5
4	ItemCode1	ItemName1	Bin 1, Bin 2, Bin 3, Bin 4, Bin 5
5	ItemCode1	ItemName1	Bin 1, Bin 2, Bin 3, Bin 4, Bin 5
6	ItemCode1	ItemName1	Bin 1, Bin 2, Bin 3, Bin 4, Bin 5
7	ItemCode1	ItemName1	Bin 1, Bin 2, Bin 3, Bin 4, Bin 5
8	ItemCode1	ItemName1	Bin 1, Bin 2, Bin 3, Bin 4, Bin 5
9	ItemCode1	ItemName1	Bin 1, Bin 2, Bin 3, Bin 4, Bin 5
10	ItemCode1	ItemName1	Bin 1, Bin 2, Bin 3, Bin 4, Bin 5
1	ItemCode2	ItemName2	Bin 1, Bin 2, Bin 3, Bin 4, Bin 5
2	ItemCode2	ItemName2	Bin 1, Bin 2, Bin 3, Bin 4, Bin 5
3	ItemCode2	ItemName2	Bin 1, Bin 2, Bin 3, Bin 4, Bin 5
4	ItemCode2	ItemName2	Bin 1, Bin 2, Bin 3, Bin 4, Bin 5
5	ItemCode2	ItemName2	Bin 1, Bin 2, Bin 3, Bin 4, Bin 5
6	ItemCode2	ItemName2	Bin 1, Bin 2, Bin 3, Bin 4, Bin 5
7	ItemCode2	ItemName2	Bin 1, Bin 2, Bin 3, Bin 4, Bin 5
8	ItemCode2	ItemName2	Bin 1, Bin 2, Bin 3, Bin 4, Bin 5
9	ItemCode2	ItemName2	Bin 1, Bin 2, Bin 3, Bin 4, Bin 5
10	ItemCode2	ItemName2	Bin 1, Bin 2, Bin 3, Bin 4, Bin 5

[ido_millet]

Right-click the detail section handle area.
Section Expert
Turn on the checkbox option for 'Format with Multiple Columns'

[ashrafsharaf]

Thanks, i appreciate the answer but it's not what i am looking for :).

I don't wanna just split my data into multiple columns instead of multiple rows, i wanna be able to list on column level selectively.

For example:

I want to select details for items A (quantity 3), B(quantity 2), E, F(quantity 4), and Z

So the display should be like this:

AAABBEFF
FFZ

With the details of each item showing below.