Multiple records on one row 1

[reidtw]

Hi,

Using CR8 thru' ODBC to a SQL server.

I have a table that contains cash balances for various funds.

The layout is: -

FundCode BalDate BalAmt
ABC 31/01 10000
ABC 28/02 10000
ABC 30/04 25000

In my report I want to show the balances on one line, thus: -

FundCode 31/01 28/02 30/04
ABC 10000 10000 25000

I've tried table aliases and get the first balance but zero for the rest. I have no record filtering or suppression so far and no grouping.

I would like to do this without using a cross-tab or sub reports, is it possible?

Cheers
T

 

[reidtw]

Hi lbass,

Yes, the other group is for Fund level. So to recap, the main report has groups 1. Fund 2. Currency 3. Value Date
and the sub report has 1. Currency 2. Price Point.

I DO want to use the Jan result in a group footer #1 formula, which is @ExRate and the formula is

WhilePrintingRecords;
Shared NumberVar ExRate01Sum;

But it returns zero!

Your use of a variable in the main report works a treat and the correct value is returned and my currency has converted to sterling when I add the variable formula in my currency value formula thus: -

Sum ({@Mth01}, {udt_PortfolioValues.sPortfolioCode}) * {@Sum01Sum}

If you think of anything that will render the fxrate directly from the sub-report without your main report variable solution then this would be preferable but I need to kick on with the results I get now.

Many, many thanks for your input to date.

Regards
T

 

[reidtw]

Hi lbass,

Unfortunately I spoke to soon!

The first value for the January fxrate in the group#1 footer (Fund) is correct but subsequent values as one looks thru' the main report are being increased each time by the fxrate value. So if the rate was 0.5 and there are 100 funds, the last value is 50!

Any ideas on how to return the correct value consistently for all funds?

T

 

[lbass]

You need a reset formula:

whileprintingrecords;
numbervar sum01sum := 0;

Place this in the group #1 header.

-LB

 

[reidtw]

That's the one, thanks!

T

 

[reidtw]

Hi again,

Well, my report was developing quite nicely until I have to export to Excel!

I have added another formula, @Value: -

WhilePrintingRecords;
Shared NumberVar Inv_Totals;

the shared variable of which is from another sub report that has totals of a type of investment for each fund. The sub report is in my group#1 header (Fund) and is linked to the main report by fund code. The preview looks fine; each fund that had investments has a value in @Value and those that didn't have zero - great so far.

However, when I extract to excel, where a positive value starts it cascades down until the value changes for the next valid value thus: -

Fund Jan Feb Mar Value

F1 10 20 15 2
F2 15 10 20 2
F3 0 0 0 2
F4 25 60 70 5
F5 4 3 4 5
F6 4 3 2 5

where F1 and F4 should only have positive values and the rest, although they might have a monthly fund value, should be at zero.

If the above is clear, how do I eliminate the duplication of these values?

Cheers
T

 

[lbass]

Try adding a reset formula for that shared variable in the Group #1 footer:

WhilePrintingRecords;
Shared NumberVar Inv_Totals := 0;

-LB

 

[reidtw]

Thanks for that, worked fine.
T