Crosstab - Sequence column in the front 1

[smsaji]

Hi,

Crystal report 10 with sql server stored procedure as datasource.

Is it possible to add a 'Sequence no column' as First column to a crosstab, something like a running total.

something like this:

Col_1
1 Row_1 3%
2 Row_2 4%

I tried to insert another crosstab with some sequence column in front of existing crosstab. The sequence numbers are showing. Using a formula for the sequence column.

formula is like:

If ProcName.Row_Name = 'aaa' then
1
else
If ProcName.Row_Name = 'bbb' then
2

The problem is then 'aaa' does not show up for some parameter, the sequence column does not show up 1, then it starts with 2. Likewise in the middle some number is missing.

Or if can force to bring all Row_Name, it will be good solution, but I am not able to bring all rows in the resultset. I tried different join in the stored procedure.

 

[lbass]

If you want the sequence number as a separate column, you can create a formula like the following:

whilereadingrecords;
numbervar x;
stringvar y;

if instr(y, {ProcName.Row_Name}) = 0 then
(y := y + {ProcName.Row_Name} + ", ";
x := x + 1);
x

Add this as your first row field in the crosstab and then go to the customize style tab and while the formula is highlighted, check "suppress subtotal". Then while in preview, right click on the field->format field->number->
-1123.

This will ONLY work if you sort your main report by {ProcName.Row_Name} or group by that field. If you have to use a different group or a different sort field, then insert a subreport and follow the above steps, and in the subreport, be sure that the subreport is only sorted on the row_name field.

-LB

 

[smsaji]

Wonderful, lbass! The sequence numbers just rolls up down!

One very samll question. However much, I try, I am not able to bring line alignments between the two crosstabs. (The column horizontal lines are little not getting aligned). Crosstab I is with the sequence number. Crosstab two having the Row_Name, Col_Name with the summary value.

Is there any magic you have to achieve this, ..

-saj

 

[lbass]

Why are you creating the sequence numbers in a separate crosstab? You should be able to add the formula as a row field in the same crosstab. While in the crosstab expert, click on the new rowfield and drag it to the topmost position.

-LB

 

[smsaji]

Comes out wonderful! Thank you, lbass!!!

 

[ReillyC]

Oh my that works beautifully. One small question how do I get the sequence of numbers as whole numbers rather than with digital

Currently
1.00
2.00
3.00

Desired
1
2
3

Thanks! Thanks!

 

[lbass]

If you are using the formula exactly as displayed above, then in preview mode you can format it like you would any number. Just click on the icon for reducing decimals until there are none.

If you have amended the above because you want to include the numbering in a string, then change totext(x) to totext(x,0,"").

-LB

 

[smsaji]

This is with relation to the thread : thread149-1130706

This time, I am creating a sequence for a crosstab. The crosstab row is numeric like 12.0, 9.0, 9.0, 5.0 ... In this case will the seqence be generated

lbass had suggested this formula for crosstab string row and it worked great!:

whilereadingrecords;
numbervar x;
stringvar y;

if instr(y, {ProcName.Row_Name}) = 0 then
(y := y + {ProcName.Row_Name} + ", ";
x := x + 1);
x

Now, since the crosstab rowname is numeric, I changed

instr(y, {ProcName.Row_Name}) TO instr(y, totext({ProcName.Row_Name})) AND
(y := y + {ProcName.Row_Name} TO (y := y + totext({ProcName.Row_Name})

but the rows are getting repeated. For example the first three rows are the output. But after the first three rows get displayed, they get repeated. In the duplicated rows, the next sequence shows (no.4. here)

Seq Row_Name_1 Row_Name_2
1 MMM 12.0%
2 DDD 9.0%
3 GGG 9.0%
4 MMM 12.0%
DDD 9.0%
GGG 9.0%

But when I remove the ‘Seq’ from the crosstab rows, it shows up only the three rows, which is correct,

Seq Row_Name_1 Row_Name_2
1 MMM 12.0%
2 DDD 9.0%
3 GGG 9.0%

Will this same formula work to generate a sequence column for crosstab numeric rowname or is there any other way.

thanks

 

[lbass]

First, when you use the instr function with numbers, you should format them to have the same number of digits, so you might format your sample data with:

totext({table.number},"00.0")

Are you saying this is occurring when you are trying to use two row fields, instead of one? Make sure your main report is sorted first by RowName1 and then by RowName 2, also.

-LB

 

[smsaji]

Awesome!!!!!

Just before leaving, saw your suggestion and used totext({table.number},"00.0") and like magic everything all correct!!!

Wonderful job, lbass!!!! How to thank you. Whole day, I was trying different means. (even some other days)