cancel
Showing results for 
Search instead for 
Did you mean: 

Help me with this crosstab

former_member198639
Participant
0 Kudos

I design a cross-tab, If I write no formula, it shows:

Then I write a formula to let ot grow every 6 columns a tab.

whilereadingrecords;

numbervar c1; numbervar c2;

if remainder(c1,6) = 0

then (c1 := c1 + 1; c2 := c2 + 1;)

else (c1 := c1 + 1; c2;)

But it shows

How to correct? Thanks.

Accepted Solutions (0)

Answers (1)

Answers (1)

abhilash_kumar
Active Contributor
0 Kudos

Hi Hu,

What's wrong? I see that it breaks at every 6th column.

-Abhilash

former_member198639
Participant
0 Kudos

Yes,it breaks at every 6th column.

But, the rows also break.

I want the rows together like picture 1.

Thanks.

former_member198639
Participant
0 Kudos

i.e.

JWiseman
Active Contributor
0 Kudos

hi Hu Po,

in your grouping formula you use a limit of 6. this means that only 6 records will appear in each group which then means that you can only have 6 columns and 1 row.

if you want 6 columns and 4 rows you need to use a number of 24 for your group. you also must ensure that the proper data is in each group. please see the second half of my blog post here for an example.

it is very important that you ensure that your groups have the proper data in them in order to produce the cross-tab formatting.

steps to try to get this to work in your report:

1) change the number in your grouping formula to 24

2) order the report by the C1,C2,C3 field in the report sort expert

3) order the report by the date field in the report sort expert

-jamie

former_member198639
Participant
0 Kudos

Hi Jamie,

The rows may grows to many rows, but the column is fixed to 6.

JWiseman
Active Contributor
0 Kudos

yes i know. if you have a look at my blog post it explains in detail on how to create a cross-tab with N rows & M columns.

if you are not able to bring all of the correct data into your report and in the correct order as per the blog post then this style of cross-tab will not be possible to create.

please read the section Formatting a Cross-tab to Have N Rows & M Columns


if you do not want N rows & M columns then this technique (grouping on a certain number of records) will not work for you. i.e. you must also fix / set the number of rows.

JWiseman
Active Contributor
0 Kudos

the only way to do exactly what you want to do is to use sql to assign data to particular groups. you'd need to ensure that every 6 C values (e.g. c1, c2, c3, c4, c5, c6) have a unique group identifier and then the next 6 C values have another unique group identifier. you would probably need to write a stored procedure to do that.

after you created that stored proc then create a new report using that new group value as the main group in your report. your crosstab would then be placed on that group.

former_member198639
Participant
0 Kudos

Hi, Jamie

It takes me a bit long time to understand the blog, since it's written in English.

And I think in my case, i should add a unique group identifier to my mdb, and then use it to group.

But it may take me some time to program code for my mdb.

JWiseman
Active Contributor
0 Kudos

hi Hu Po,

you can also add the unique identifier at the report level should you be able to use a simple Case / If Then Else methodology.

look into using the Switch() function in a new formula in crystal reports which will be the unique group identifier.

e.g.

switch (

{your field} in 'C1' to 'C6', 'group 1',

{your field} in 'C7' to 'C12', 'group 2',

{your field} in 'C14' to 'C18', 'group 3',

True, 'group 4'

)

this will work as long as you know what all of the values for the "your field" will be in the database.

JWiseman
Active Contributor
0 Kudos

p.s. as you've been noticing there are limitations with using a cross-tab object. have you looked into creating a grid style / multi-column report instead? please see this blog post for an idea of how these work.

former_member198639
Participant
0 Kudos

Yeah, but the database created by my customer is changing, it can be C1, C2, C3, or M8, M9, M100, or ZC-1, ZC-2......

This is crazy, ha.

former_member198639
Participant
0 Kudos

OK, thanks, Jamie.

JWiseman
Active Contributor
0 Kudos

that's what i was afraid of. so you'd either need to use a stored proc or Command in crystal to create a running total which increments a group identifier integer or see the grid style report method above.

with a grid style report a subreport is used and the combination of page size and multi-column sizing dictates the number of columns that will be in the report. for example in the first screen shot i needed 7 columns. the number of rows is determined by the data only. so in your case you always need 6 columns. and the  number of rows you need can change. using a grid can accomplish this and you don't need to change your database or create a stored procedure.

you would still need to ensure that the proper data came into your report and was in the correct order as well.