Display stock for different conditions

[adhhealth]

crystal 8 pervasive sql 8
i want a report like

id desc stock-abc stock-def stock-xyz
1023 item1 10 11 127

stock-abc is the stock whose item number is F-abc-1023
stock-def -- f-def-1023 and so on..
so first i did a grouping by 1023.
then by the 2nd segment, ie by "abc"
i inserted 2 sections for the 2nd group and gave the suppress formula as if {@SEGMENT2}='abc' THEN FALSE ELSE TRUE
if {@SEGMENT2}='def' THEN FALSE ELSE TRUE

grpheader 2a - id desc stock-abc
grp header 2b stcok-def
grp header 2c stock-xyz

and checked "underlay following sections" option, but it does not display in a single line..it displays like

1023 item1 10
11
127.

pl. advise.
thanks

 

[synapsevampire]

Rather than explain what you did, try to focus first on describing your requirements, someone might have anotehr way of doing it.

Post example data and the expected output as a result of that data. You did an OK job of showing what you want, but what does the data look like now?

Why is this complicated? Also keep in mind the language barrier issues, so examples will lend themselves well to commincating requirements.

-k

 

[adhhealth]

Yes mam/sir thats what I did,i thought i gave an example clearly, i guess it was not understood properly.

this is where i imply what the data looks like:

"it displays like following:

1023 item1 10
11
127".
but i want it to look like:

1023 item1 10 11 127.

 

[adhhealth]

traditionally if i create a report grouped by 1023
it would look like this

id item# stock
1023
f-abc-1023 10
f-def-1023 11
f-xyz-1023 127

but my need is:

id desc stock-abc stock-def stock-xyz
1023 item1 10 11 127

hope it is clear now..

 

[synapsevampire]

I think I get it now, you have multiple rows which you'd like to display on a single row. Your first post stated something about that being the result of groupings, and references formulas that weren't posted ({@SEGMENT2}=)...

Also having one example may not be clear, for instance do they all have stock-abc, stock-def, and stock-xyz, or does it vary? Is that what's stored in the database? And you use the term segment, does this mean a part of the data within column, or? The tables and examples of what's in them is your example data, not how you've currently laid things out on a report that isn't functioning correctly.

Anyway, you might group by the id and then the desc (assumed, again your second example data posted doesn't have the same thing as the first, and you keep stating 1023, that's the contents of the column, not the column that's being grouped), and then create formulas to get the values.

@formula1
whileptrintingrecords;
stringvar TheValues;
if not(instr(TheValues, {table.field}) = 0) then
TheValues:=TheValues+{table.field}+","

Then you can use the unique values later using:
whileprintingrecords;
Stringvar TheValues:=left(TheValues,len(TheValues)-1);
split(TheValues,",")[1]

Iterating the [1] to [2] and so on to get each value.

I doubt this is correct, but I really don't fully understand yet, but I'm trying to...

-k

-k

 

[lbass]

You might want to try inserting a crosstab in the report header or footer. You would use a formula like the following for your row field:

//{@row}:
split({table.field},"-")[3]

And for your column, field use {@column}:

split({table.field},"-")[2]

Not sure what your summary is, so you'll have to supply that--something like sum of {table.amt} or count of {table.field}.

-LB

 

[adhhealth]

Thanks for the prompt replies k and lbass.
by 1023(id)i mean the third segment
item number is of the format f-abc-1023
so i write a formula like mid(item,7,4) and do a grouping by that.. 1023 can be any thing..
i have to first group by this formula ..
ex:
id item# desc stock.
1120
f-abc-1120 item2 123
f-def-1120 item2 112
f-xyz-1120 item2 1270
1023
f-abc-1023 item1 10
f-def-1023 item1 11
f-xyz-1023 item1 127

and so on..
most of the times description for all these under an id will be the same,each item is differentiated by abc,def,xyz which are the customers.

they always have stock-abc, stock-def, and stock-xyz and no more
always the report structure would look like:

id desc stock-abc stock-def stock-xyz
1023 item1 10 11 127
1120 item2 123 112 1270
1324 item3 1123 45 343
and so on..

lbass, i dont want to use cross tab as i dont have to summarize any thing..

k,pl. forget about my formula(segment2)..i think it will be easier if you suggest me what to do..

sorry that i have not communicated my need to you efficiently..i really hope above example clears what i need..

 

[lbass]

If there is only one value that meets each row-column combination, then you can insert a crosstab and use the maximum function to display that value. A crosstab would seem to meet your presentation requirements. You can always suppress row and column totals.

It certainly would seem to be worth a try. If you want to display both the item and description, you can concatenate that in a formula to use as your row.

-LB

 

[adhhealth]

lbass,
i am going to try crosstab, i never created a crosstab before,this will give me a chance to understand it..
thanks