How to convert column data into row data in crystal report?

[Former Member]

Hi,

Your help is very much appreciated.

I have a report with 3 colmns as follows,

User id Description LVL

Test1 Menu1 2

Test1 Menu2 4

Test1 Menu3 5

Test2 Menu1 3

Test2 Menu2 2 .......

I want the above data ib the following format,

User id | Menu1 | Menu2| Menu3

Test1 | 2 | 4 | 5

Test2 | 3 | 2|

How can this be achieved in crystal report?

Please let me know the solution if you can find one.

Thanks

[Former Member · Accepted solution]

Try inserting a group on the field Test1 and create a formula like this

whileprintingrecords;

stringvar Menu:="";

Place this in group header and create another formula like

whileprintingrecords;

stringvar Menu;

Menu:=Menu{menu field}" | ";

Place this in detail section and create another formula like

whileprintingrecords;

stringvar Menu;

Place this formula in group footer and also place the UserId in group footer

Repeat the same steps for getting the numbers as well. You can suppress the detail section and group header.

Regards,

Raghavendra

[Former Member · Accepted solution]

Hi,

I am getting error message when I use "whileprintingrecords;" statement "Result of selection formula must be boolean".

I have data in db2 database, which I have used in Crystal Report and generated report as,

USERId, DESCRIPTION, LVL

Test1 Menu1 2

Test1 Menu2 4

Test1 Menu3 5

Test2 Menu1 3

Test2 Menu2 2 .......

(USERID, DESCRIPTION, LVL) are the field names in table.

Following are data inserted into table in respective column,

Test1, Menu1, 2

Test1, Menu2, 4

Test1, Menu3, 5

Test2, Menu1, 3

Test2, Menu2, 2 .......

I am able to pull same data in the report in the following format,

User id Description LVL

Test1 Menu1 2

Test1 Menu2 4

Test1 Menu3 5

Test2 Menu1 3

Test2 Menu2 2 .......

But the report has to be generated in the following format,

User id Menu1 Menu2 Menu3

Test1 2 4 5

Test2 3 2

Here, USERID column is retained as it is with only one TEST1, TEST2 values(multiple values of TEST1, TEST2 are removed). DESCRIPTION & LVL Column titles are no more there. The contents of DESCRIPTION & LVL are changed from Column to Row data. It is turned from Vertical to horizontal.

I hope it might be clear to understand. My bad I did not explain correctly.

Thanks

[Former Member · Accepted solution]

You should create formulas on this but you should not use them in record selection.

Regards,

Raghavendra

[Former Member · Accepted solution]

Hi Raghvendra,

I am new to CR. Could you please elaborate more. When I try to add the formula,

whileprintingrecords;

stringvar Menu:="";

in formula editor in "Group Selection" I am getting error" Result must be boolean for teh formula"

If we can give me the steps it will be great. I have pulled the information in Crystal Reports. If you can tell me how to achieve this from here it will be helpful.

If I insert Formula in "Group Sort Order Formula" under "Current Formula" I am getting " Formula cannot be evaluated at time specified".

If I insert Formula in "Select Expert- Group" I am getting "Result must be boolean for the formula".

I am doing it right way?

Thanks

[Former Member · Accepted solution]

Hi Raghvendra,

I did not get what the formaula logic is,

whileprintingrecords;

stringvar Menu;

Menu:=Menu{menu field}" | ";

If I have follwing data how I should chage teh formula, ( To distinguish I am entering Field names and data in braces{ })

{One Menu} {Fund Menu}

{One Menu} {Fund Menu} ......

Thanks

[Former Member · Accepted solution]

Hi Raghvendra,

I was able to follow your steps. But My Description and LVL Columns are not changed into rows.

Data in the DESCRIPTION & LVL Table should be displayed as,

One Menu Fund Menu Chat Menu Menu

Batali 3 2 2 2

Anne 2 7 2 3

[Former Member]

kiran are you using crosstab?

what are you using and what version of CR?

Regards

Jehanzeb

[Former Member]

Hi,

I am trying to use Cross tab to find the solution. I guess we cannot do it in Blank report/Report wizard?

I am using Crystal Report V12.0

If the report can be generated using blank/Report wizard/ Cross tab report, it is fine.

Thanks

[Former Member]

Hi Kiran,

For displaying the column values in the row, you can try interchanging the row and column values in the Cross tab expert. Go to the Cross Tab expert and in the "Cross-tab" tab, interchange the column and row values.

Hope this helps you!

Regards,

Anindita

[Former Member]

Hi,

Thank you for a quick reply.

I have tried Cross Tab. But it does not work. I have 2 columns (Description, LVL) which needs to be changed to Row.

Is there any other way we can achieve this? Kindly let me know.

Thanks