Breakdown Column

[OT2]

Wondering if you can help with the following: I am using CR9 and have been asked to put together a stale prices report. I have accessed the price table in our port admin database called mhfspric, the fields I will need to use from this table are

mhfspric.secid
mhfspric.pricedate
mhfspric.price
mhfspric.source

By dropping mhfspric.pricedate and price date into the details section of the report I get a repor that is 2k pages. What I need to see is 7 clumns, one for each of the last 7 business days, ie:

{mhfspric.secid} {pricedate-1} {pricedate-2} {pricedate-3}

etc...

Any recommendations... Thank you= OT2

 

[synapsevampire]

If you place the following into the Report->Selection Formulas->Reocrd you'll limit the rows to 7 days:

mhfspric.pricedate in currentdate-7 to currendate-1

Now use Insert->Cross-tab and place the pricedate in the column, mhfspric.secid
in the rows, and mhfspric.price (set it to maximum as the aggregate) into the summary field.

Should be close to what you want.

Otherwise post example data and expected output, your example doesn't even show where the price would be displayed...

-k

 

[OT2]

Hi synaps, thanks for your help. The crosstab looks good, but not really what I want.

Here is my example data *with only 4 dates for simplicity
SecID Date Price
1234567XYZ 20050512 0.0989
1234567XYZ 20050513 0.0989
1234567XYZ 20050516 0.0989
1234567XYZ 20050517 0.0989
186782XYZ 20050512 0.0995
186782XYZ 20050513 0.0995
186782XYZ 20050516 0.0995
186782XYZ 20050517 0.0995


Here is my expected data

SecID 20050512 20050513 20050516 20050517 Stale
1234567XYZ 0.0989 0.0989 0.0989 0.0989 true
186782XYZ 0.0995 0.0995 0.0995 0.0995 true

does this help?

 

[synapsevampire]

Ahhh, the stale column...

Since it doesn't exist in the data, how can I know what is to be done?

Is this a formula?

I suspect that you'll need to build a manual crosstab.

One means is to group by the SECID and use a formula in the details such as:

whileprintingrecords;
numbervar date7:=0;
numbervar date6:=0;
numbervar date5:=0;
numbervar date4:=0;
numbervar date3:=0;
numbervar date2:=0;
numbervar date1:=0;
if currentdate-{table.date} = 7 then
date7:= {table.price}
else
if currentdate-{table.date} = 6 then
date6:= {table.price}
else
... you get the idea

Now display the columns in the Group Footer, and suppress the details and the group footer.

This gives you the last 7 days (not including today).

-k

 

[OT2]

Hi Synaps,

I have put together the following formula, whileprintingrecords;
numbervar date7:=0;
numbervar date6:=0;
numbervar date5:=0;
numbervar date4:=0;
numbervar date3:=0;
numbervar date2:=0;
numbervar date1:=0;
if currentdate-{mhfspric.PriceDate} = 7 then
date7:= {mhfspric.Price}
else
if currentdate-{mhfspric.PriceDate} = 6 then
date6:= {mhfspric.Price}

I get an error message indicating me that "A date is required here" where the 7 and 6 are....