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

 

[lbass]

Your layout IS a crosstab. You can either insert a crosstab and use BalDate as the column, fundcode as the row and BalAmt as the summary, or you can create a manual crosstab (no need to do this unless you have left out other features of your final report), where you insert a group on fundcode and then create formulas like:

//{@1/31}:
if {table.date} = date(2005,01,31) then {table.BalAmt}

//{@2/28}:
if {table.date} = date(2005,02,28) then {table.BalAmt}

You would place these in the detail section and insert summaries on them and then suppress the detail section.

-LB

 

[reidtw]

Thanks lbass,

I've done this already but I cannot get the max date for each month to summarize properly. Here is the data: -

FundCode BalDate BalAmt
ABC 31/01 10000
ABC 28/02 10000
ABC 30/04 25000
ABC 01/05 21000
ABC 02/05 15000
ABC 15/05 14000

So I would want to return: -

FundCode 31/01 28/02 30/04 15/05
ABC 10000 10000 25000 14000

Apologies for not posting this requirement originally.

Cheers
T

 

[lbass]

You can use a SQL expression to solve this. First create a SQL expression {%maxdate}:

(select max(AKA.`date`) from Table AKA where
AKA.`FundCode` = Table.`FundCode`)

Substitute your exact field names for "date" and "FundCode" and your table name for "Table". Leave "AKA" as is, since it is an alias table name.

In the record selection formula, add:

{table.date} = {%maxdate}

Then you could proceed to insert a crosstab or create a manual crosstab as I outlined earlier, since only the maximum record per FundCode will be returned to the report.

-LB

 

[reidtw]

Hi lbass,

Thanks for the suggestion but I think the SQL synatx is slightly wrong, I believe it should be: -

(select max(AKA."dtValueDate") from udt_PortfolioValues as AKA, udt_PortfolioValues where AKA."sPortfolioCode" = udt_PortfolioValues."sPortfolioCode")

which is working. However, bringing in the last record for each fund code is not the desired result; this will only bring in May. I must bring in the last record for each month and fund.

Any ideas?

Cheers
T

 

[lbass]

The syntax for SQL expressions differs with different datasources. I think mine should have worked if you had just replaced the ` with ". The following adds in the requirement that it is the most recent per month (my fault for omitting that earlier):

(select max(AKA."dtValueDate") from udt_PortfolioValues AKA where AKA."sPortfolioCode" = udt_PortfolioValues."sPortfolioCode" and
{fn Month(AKA."dtValueDate")} = {fn Month(udt_PortfolioValues."dtValueDate")} and
{fn Year(AKA."dtValueDate")} = {fn Year(udt_PortfolioValues."dtValueDate")})

-LB

 

[reidtw]

Hi lbass,

The most recent expression is still only bringing in the last date of the sequence; am I missing something?

I have replaced the previous expression with the above but nothing else.

I there something else I need to do? A date parameter perhaps or changing the record selection?

T

 

[lbass]

Did you set it equal to the date field in the record selection formula?

{udt_PortfolioValues.dtValueDate} = {%maxdate}
//where {%maxdate} is the SQL expression

-LB

 

[reidtw]

Yes, I had done that previously.

T

 

[lbass]

It should work. Did you use my expression exactly as shown? It tests out here.

-LB

 

[reidtw]

I had to enter it as: -

(select max(AKA."dtValueDate") from udt_PortfolioValues as AKA, udt_PortfolioValues where AKA."sPortfolioCode" = udt_PortfolioValues."sPortfolioCode" and
{fn Month(AKA."dtValueDate")} = {fn Month(udt_PortfolioValues."dtValueDate")} and
{fn Year(AKA."dtValueDate")} = {fn Year(udt_PortfolioValues."dtValueDate")})

because it did not recognise the field udt_PortfolioValues."dtValueDate"

T

 

[lbass]

Your expression will not work correctly. Please change it to:

(select max(AKA."dtValueDate") from udt_PortfolioValues AKA where AKA."sPortfolioCode" = udt_PortfolioValues."sPortfolioCode" and
{fn Month(AKA."dtValueDate")} = {fn Month(udt_PortfolioValues."dtValueDate")} and
{fn Year(AKA."dtValueDate")} = {fn Year(udt_PortfolioValues."dtValueDate")})

I tested your syntax--doesn't work-- but the above does, at least with my datasource.

What error message are you getting when you try the above? Are you sure that you have the correct punctuation for your datasource? If you double click on the date field in the SQL expression expert, does it appear exactly as above?

-LB

 

[reidtw]

Hi lbass

That worked! Not sure what happened before.

Many thanks for your help.

T

 

[reidtw]

Hi again,

Following on from the above, I now need to convert the fund values from USD to GBP.

I have another report that looks at an exchange table and uses a sql expression similar to that in the fund values report where I am returning the maximum date for each calendar month. The data returns as follows: -

Jan Feb Mar etc, up to Dec
0.53 0.54 0.52 0.50

Each rate is a separate calculated field along the lines of: -

Shared NumberVar ExRate01Sum;
ExRate01Sum:=
Sum ({@ExRate01}, {FXRates.sCurrency})

where 01 is replaced with 02,03,etc to reflect each calendar month.

The report is grouped 1. Currency (although only GBP present) 2. @Price Point [ToText({FXRates.dtPricePoint},"MMMM")]

The fund values report is grouped by 1. Fund 2. @Value Date [ToText({udt_PortfolioValues.dtValueDate},"MMMM")]

I have linked the exchange rates sub report to the fund values report using @Price Point and @Value Date which are both string values of 25 characters in length.

I placed the sub report in group header 2 and a formula field @exrate01 [Shared NumberVar ExRate01Sum;]
in group footer 1 (because the fund value sums are here).

A zero value is returned for Jan but I know there are values for each month.

Any ideas on what I could be doing wrong?

Cheers
T

 

[reidtw]

Further info: -

The sub report looks like this when placed in the group 2 header (@value date): -

April
0.52438
February
0.53115
January
0.53107
March
0.52173
May
0.52438

Not sure if this helps but I am wondering if the fact I have no currency grouping for GBP in the main report is skewing the data and not allowing a return in any formula fields in the main report.

Would I be able to force Crystal to accept a made up grouping as I do not have an equivalent field to currency in the main report.

T

 

[lbass]

After creating the shared variables in the subreport, did you place the formulas on the subreport canvas? This is a must.

Are you just trying to display each month's value in the group#1 footer?

-LB

 

[reidtw]

Yes, all the formulas are in the subreport canvas.

No, I need to be able to place a formula in group footer 1 for each month along the lines of: -

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

where

{@ExRate01} = Shared NumberVar ExRate01Sum;

but I get a zero value for Jan.

T

 

[lbass]

Are you certain that you used "shared" in both the subreport and main report formulas and that the variable name is exactly the same?

And there is definitely January data in the main report?

I can't recreate your problem here. I don't think it will make a difference, but try adding "whileprintingrecords;" at the beginning of each shared variable formula.

-LB

 

[reidtw]

Are you certain... exactly the same?" YES

"And... in the main report?" YES

"I... each shared variable formula" No difference.

It's not that the fxrates are not coming through to the main report it's that I cannot get the fxrate that HAS come through for Jan to be part of my conversion formula.

If I place a formula - [Shared NumberVar ExRate01Sum;] - in the main reports group header 3 (Value Dates) this is what I get for each fund: -

0.00000
0.00000
0.00000
0.53107 (Jan)
0.00000

(The zeros are the other months up to May.)

However, I tried to sum this in another formula - [Sum(Shared NumberVar ExRate01Sum)] - in Group Footer 1 (Funds) but it would not let me and got the usual "The summary... not be created".

T

 

[lbass]

I guess you added another group? I thought you wanted to use the January result in a group footer #1 formula. If so, then does the January result appear correctly in the GF#1 when you just use the following there?

shared numbervar ExRate01Sum;

This should work without needing to sum, and I see no reason to sum. However, if you did need to sum a shared variable, you would need to use a variable in the main report with formulas like:
//{@reset} to be placed in the GH#1 header:

whileprintingrecords;
numbervar sum01sum;

//{@accum} to be placed in the GH#3
whileprintingrecords;
shared numbervar ExRate01Sum;
numbervar sum01sum := sum01sum + ExRate01Sum;

Then in the GF#1 footer you would use:

whileprintingrecords;
numbervar sum01sum;

-LB