on 07-17-2014 11:12 AM
Hi Hu,
What's wrong? I see that it breaks at every 6th column.
-Abhilash
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
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.
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.
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.
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.
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.