Skip to Content
0

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

Nov 05, 2017 at 06:57 AM

136

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Ido Millet Nov 06, 2017 at 03:10 PM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

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.

0
Dell Stinnett-Christy Nov 07, 2017 at 04:24 PM
0

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

Show 3 Share
10 |10000 characters needed characters left characters exceeded

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.

0

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

Thanks!

-Dell

0

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

0