Group numbering shown in original order

[allyson1]

I have a table that has these first two columns, Number and Type. The second column 'Type' only will ever contain three different values "MC_STD, MC_BLK or MC_REP". I need to generate the third column, 'FormulaName' as shown. The data must be shown in it's correct Number order but the first Type shown as "STD 1.1". If that type is repeated later in the batch, it is to be shown as same std number with an incremental number.

Number Type FormulaName
1 MC_STD STD 1.1
2 MC_STD STD 1.2
3 MC_BLK STD 2.1
4 MC_STD STD 1.3
5 MC_REP STD 3.1
6 MC_BLK STD 2.2
7 MC_STD STD 1.4
8 MC_REP STD 3.2

Any ideas??????? Any help would be very much appreciated.
Thanks.

 

[lbass]

I'm sure there is a way to simplify the following, but I haven't worked it out:

whileprintingrecords;
stringvar array x;
numbervar y;
numbervar z;
numbervar k;
numbervar m;
numbervar i;
if not({table.type} in x) then (
redim preserve x[3];
i := i + 1;
x := {table.type};
);
if {table.type} = x[1] then
(
y := 1;
z := z + 1;
) else
if {table.type} = x[2] then
(
y := 2;
k := k + 1;
) else
if {table.type} = x[3] then
(
y := 3;
m := m + 1;
)
;
"Std "+totext(y,0,"")+"."+
(
if y = 1 then
totext(z,0,"") else
if y = 2 then
totext(k,0,"") else
if y = 3 then
totext(m,0,"")
);

The above works--it's just more complicated that it needs to be.

-LB

 

[allyson1]

Thank you heaps! That worked exactly.

but....I was wondering, would there be anyway to have the formula work in a crosstab. The report requirements have changed today in that the data is to be presented in a crosstab format - the data in my example would be the rows. Can the formatting be applied somehow so that it shows as STD 1.1 etc.

Thanks again in advance

 

[lbass]

Create a formula {@concat}:

totext({table.number},"0000")+" "+{table.type}

Use this as your row field in the crosstab. In preview mode, right click on the row label->format field->display string->x+2 and enter:

whileprintingrecords;
stringvar array x;
numbervar y;
numbervar z;
numbervar k;
numbervar m;
numbervar i;

if not(split(currentfieldvalue," ")[2] in x) then (
redim preserve x[3];
i := i + 1;
if i <= 3 then (
x := split(currentfieldvalue," ")[2];
));
if split(currentfieldvalue," ")[2] = x[1] then
(
y := 1;
z := z + 1;
) else
if split(currentfieldvalue," ")[2] = x[2] then
(
y := 2;
k := k + 1;
) else
if split(currentfieldvalue," ")[2] = x[3] then
(
y := 3;
m := m + 1;
);
"Std "+totext(y,0,"")+"."+
(
if y = 1 then
totext(z,0,"") else
if y = 2 then
totext(k,0,"") else
if y = 3 then
totext(m,0,"")
);

This worked when I tested it.

-LB

 

[allyson1]

Wow, you are awesome! That worked wonderfully. The only thing, is that it is displaying in reverse order, ie:
1 MC_STD STD 2.4
2 MC_STD STD 2.3
3 MC_BLK STD 3.2
4 MC_STD STD 2.2
5 MC_REP STD 1.2
6 MC_BLK STD 3.1
7 MC_STD STD 2.1
8 MC_REP STD 1.1

The summary data is all in the right order, it is just the way the new description is working. It seems to go from the bottom, up.

I really am very thankful for your help

 

[allyson1]

May I also add, I did change one line of the syntax"

totext({table.number},"000000")+" "+{table.type}

My number is actually a length of 6

 

[ianoctdec]

Hi Allyson1,

If you can write your own SQL command, try the following query:

SELECT A.NUMBER, A.TYPE , COUNT(*) AS TYPE_NUMBERING
FROM
(SELECT NUMBER, TYPE, FROM YOUR_TABLE) A,
(SELECT NUMBER, TYPE, FROM YOUR_TABLE WHERE) B
WHERE
A.TYPE = B.TYPE and A.NUMBER >= B.NUMBER
GROUP BY A.NUMBER, A.TYPE

TYPE_NUMBERING should give the incremental numbering of the type.
Now, in the report you'll have to write a formula to generate your third column:

@NumberingType
select YOUR_TABLE.TYPE
case MC_STD : 'STD 1.' + totext(TYPE_NUMBERING,0)
case MC_BLK : 'STD 2.' + totext(TYPE_NUMBERING,0)
case MC_REP : 'STD 3.' + totext(TYPE_NUMBERING,0)

Dana

 

[lbass]

It was in ascending order when I tested this. Are you sure that you have the row sorted in ascending order?

-LB

 

[allyson1]

Yes, the data values are definitely showing in the correct order - I am comparing them with an original crosstab and everything is the same. It's just that the display of STD etc, seems to be counting from the bottom up! Weird

 

[allyson1]

I have changed the syntax slightly, and can now get the first part of the number to appear in ascending order, but the number after the decimal is still working from the bottom up.

whileprintingrecords;
stringvar array x;
numbervar y;
numbervar z;
numbervar k;
numbervar m;
numbervar i;

if not(split(currentfieldvalue," ")[2] in x) then (
redim preserve x[3];
i := i + 1;
if i <= 3 then (
x := split(currentfieldvalue," ")[2];
));
if split(currentfieldvalue," ")[2] = x[1] then
(
y := 3;
m := m + 1;
) else
if split(currentfieldvalue," ")[2] = x[2] then
(
y := 2;
k := k + 1;
) else
if split(currentfieldvalue," ")[2] = x[3] then
(
y := 1;
z := z + 1;
);
"Std "+totext(y,0,"")+"."+
(
if y = 3 then
totext(m,0,"") else
if y = 2 then
totext(k,0,"") else
if y = 1 then
totext(z,0,"")
);

Many thanks.

 

[lbass]

Can you verify that you used a concatenation formula for the row field? And that you then used the longer formula in the display string area?

If the values were actually in the correct order, the display would be in the correct order, so something is amiss. Did you add a group sort to the crosstab? Where is the crosstab located?

-LB

 

[lbass]

Also, have you defined a sort order in the main report? How does the display formula display if placed in the detail section with a sort ascending on the number field?

-LB

 

[allyson1]

Hi,
Answer is yes, using the concatenation formula for the row field and yes, using the longer formula in the display string for the rows. Also, no sort order defined.

If I use the string display on the @concat field and show it in the Details section, it works! If I leave it there, and unsuppress my crosstab, it reverses!

 

[lbass]

You shouldn't actually have the formula in both places, as the variables are being set twice then. Is the crosstab in the report footer? What happens if you move it to a report header section (or the opposite of where it is now)?

-LB

 

[allyson1]

Crosstab is in report header, tried moving it to report footer, same result :-( Also, have removed all fields from details section.

This is how the crosstab looks in text form:

Std 3.8 ,
Std 3.7 , ,
Std 3.6 ,
Std 3.5 , ,
Std 3.4 ,
Std 3.3 ,
Std 3.2 ,
Std 3.1 ,
Std 2.5 ,0.0149 , , , , , , ,
Std 1.15 ,2.0779 , , , , , , ,
Std 1.14 ,3.45662852 , , , , , , ,
Std 1.13 ,3.0903 , , , , , , ,
Std 2.4 ,0 , , , , , , ,
Std 1.12 ,1.3429 , , , , , , ,
Std 1.11 ,4.1675 , , , , , , ,
Std 2.3 ,0 , , , , , ,0 ,
Std 1.10 ,69.068 , , , , , ,3.7154 ,
Std 1.9 ,87.727 , , , , , ,2.6148 ,
Std 1.8 ,77.348 , , , , , ,3.317 ,
Std 1.7 ,87.331 , , , , , ,1.7782 ,
Std 1.6 ,78.727 , , , , , ,1.7922 ,
Std 2.2 ,0.016 , , , , , , ,
Std 1.5 ,31.019 , , , , , , ,
Std 1.4 ,621.812 , , , , , , ,
Std 2.1 ,0 , , , , , , ,
Std 1.3 ,316.63 , , , , , , ,
Std 1.2 ,1111.1 , , , , , , ,
Std 1.1 ,588.86 , , , , , , ,
Sample Preparation,

The only time it shows in the correct order, is when I use the concat field in the details section.

I'm sorry I am having so much trouble.

 

[lbass]

I think you should try my original syntax again.

Also, if it works, why not place the concat field in the details section and suppress the section? Not sure why that should make any difference at all though.

-LB

 

[allyson1]

I must use a crosstab for this report, so cannot have the @concat field show in the details section.
I tried creating your two formulas over a very simple excel spreadsheet with just a few entries. There is so ordering, no grouping, the @concat field is used as the row line in the crosstab with no ordering but ascending. The long formula is set on the 'display string' for the row item, but I'm sorry to say, that whilst the crosstab is showing the row data values in the correct order, the display string is in the reverse order again :-( The crosstab is in the Report Header, with nothing else in the report, I've also tried moving it to the Report Footer - same result. I have also used your formulas exactly in this test. The result is always showing the count from the bottom, up.

 

[lbass]

Please post the exact formulas you are using.

Also note that you can place the {@concat} field in the details section and suppress the detail section or the formula--if that's what makes the difference.

-LB

 

[allyson1]

Hi,
Here is the formula from the @concat field:

totext({SAMPLE.ORIGINAL_SAMPLE},"000000")+" "+{SAMPLE.SAMPLE_NAME}

This field is used as the 'row' in my crosstab.

The row field is then formated, with the following formula on the 'display string':
whileprintingrecords;
stringvar array x;
numbervar y;
numbervar z;
numbervar k;
numbervar m;
numbervar i;

if not(split(currentfieldvalue," ")[2] in x) then (
redim preserve x[3];
i := i + 1;
if i <= 3 then (
x := split(currentfieldvalue," ")[2];
));
if split(currentfieldvalue," ")[2] = x[1] then
(
y := 1;
z := z + 1;
) else
if split(currentfieldvalue," ")[2] = x[2] then
(
y := 2;
k := k + 1;
) else
if split(currentfieldvalue," ")[2] = x[3] then
(
y := 3;
m := m + 1;
);
"Std "+totext(y,0,"")+"."+
(
if y = 1 then
totext(z,0,"") else
if y = 2 then
totext(k,0,"") else
if y = 3 then
totext(m,0,"")
);

The result is that the data is sorted in the correct order, but the display string is displaying as STD 1.1 on the bottom, and working its way up to the top.

 

[allyson1]

I am on the verge of trying to generate what I need either via Crystal SQL command or trying to create a view.