Seperating/positioning sets of values taken from 1field

[d44]

One table in my report is called property_descriptor.This table has numerous
fields,but the ones in question are 'descriptor' and descriptor_value.

'descriptor' describes information on certain properties and there is a
large selection. Below is a few examples of what this field can contain:

e.g. DATB: is Build Date of Building
BEDS: number of bedrooms
WNDS: no of windows....etc

The descriptor_value is the associated value with regards to the descriptor.

e.g. the DATB of certain property,the descriptor_value would be a date field
such as. 01/12/1921.

Using Selection
({property_descriptor}="DATB" or ({property_descriptor}="BEDS&quot

I am able to select the DATB and the BEDS and output the
corresponding values. This outputs as shown below(with some other fields).

ID Address Descriptor Value
001 122 High St DATB 1945
001 122 High St BEDS 4
002 325 Main St DATB 1956
002 325 Main St BEDS 3

Is it possible to create the report along the lines of

ID Address DATB BEDS
001 122 High St 1945 4
002 325 Main St 1956 3

Or something along those lines so that I
dont have repeated info.

Thanks

 

[rogar]

You can do this with a Group and global variables.
Insert a Group on ID. In the Group Header, insert a formula
(call it Reset):
StringVar sDatB := "";
StringVar sBeds := ""
This resets the two variables for each ID.

In the Detail section, insert another formula (call it Update):
StringVar sDatB;
StringVar sBeds;
If {property_descriptor.descriptor} = "DATB" then
sDatB := {property_descriptor.descriptor_value;
If {property_descriptor.descriptor} = "BEDS"
then sBeds := {property_descriptor.descriptor_value;

In the group footer, place the ID and address data fields and two more formulas:
(call one ShowDatB): StringVar sDatB
(call the other ShowBeds): StringVar sBeds

Now suppress the group header and the details.

Note the use of ":=" to assign values and ";" to separate statements in the formulas.

I'm assuming the descriptor_value field is a string here. If it is a number, do this to reset:
NumberVar nDatB := 0; NumberVar nBeds := 0
and change the other formulas the same way.

 

[synapsevampire]

You might try using the next() function if you only have 2 detail records per row to be displayed, and the column descriptor always contains DATB and BEDS.

Group by: ID and sort by descriptor descending

Create a formula called @Beds containing:

next({Value})

In the Page Header place the labels:

ID Address DatB Beds

In the Group Header place the following fields:

{ID} {Address} {Descriptor} {@Beds}

Beds will always be the next({Value}) field.

Suppress the Detail and Group Footer sections.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[synapsevampire]

Ooops, should have checked my work:

In the Group Header place the following fields:

{ID} {Address} {Descriptor} {@Beds}

Should be:

In the Group Header place the following fields:

{ID} {Address} {Value} {@Beds}

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

My approach would be more generic since you probably have many descriptors...not just beds and Date. I would do it as follows:

Group 1 : ID
Group 2 : Descriptor

the results will be printed in the Group 1 footer

@initalize ( Suppressed in Group 1 header )

WhilePrintingRecords;
if not inRepeatedGroupheader then
(
//create 20% more elements than the current max possible
stringVar array descriptor := ["","","","",""....""];
stringVar array value:= ["","","","",""....""];
numberVar icount := 0;
);

In the detail section place this formula

@StoreDescriptor (suppressed in detail section)

WhilePrintingRecords;
stringVar array descriptor ;
stringVar array value;
numberVar icount;

icount := icount + 1;
descriptor[Icount] := {Table.descriptor};
value[icount] := {Table.value};


now in the footer for Group 1 you will place a series of display formulas....this can be tedious but the result will be perfect

For each pair of element of the descriptor/value

@DispDecript1 (placed in Group footer 1 - "Can Grow&quot

WhilePrintingRecords;
stringVar array descriptor ;
stringVar array value;

descriptor[1] + chr(13) + chr(10) + value[1];

Now your footer will look like this

{table.ID} {table.address} {@DispDecript1) {@DispDecript2} ....adding the rest.

if you have more formulas than can fit on l line create subsections and enable "suppress Blank Section" on them so they will look like this

ID Address Date Beds Windows Doors
28/06/2002 1 2 1
Sofa Fridge Stove
2 1 1

Hope this helps Jim Broadbent