Subreport selection - formula matching part of Column Value

[sajisher]

Hi All,

Using CR9. Passing a formula to a subreport and trying to match this formula with part of a column value.

But there is no data returned though each Account.Name has 1 or 2 rows in table. Is my record selection in subreport proper because in 'Show SQL' I do not see this criteria in 'WHERE' clause. Can anyone point me in right direction please.

formula: @ActName
mid({Account.Name},3,10)

Subreport link:
matching @ActName to table.column (Account.Name)

In subreport-> Record selection
mid( {Account.Name},3,5 ) = {?Pm-@ActName}
and mid({Account.Name},1,1) = "S"

thanks

 

[lbass]

You need to show us how the account.name field displays.

This is confusing also because you appear to be using the same {table.field} in the subreport as in the main report, but are expecting a 10-character version to match a 5-character version.

-LB

 

[sajisher]

Thanks for the response.

Sorry, it is actually 10. While writing here my mistake. The formula is as follows and Account.Name is like S-1234567890-50

In subreport-> Record selection
mid( {Account.Name},3,10 ) = {?Pm-@ActName}and mid({Account.Name},1,1) = "S

 

[Turkbear]

Hi,
As a subreport link,
this:

mid({Account.Name},3,10) //middle 10 chars

will NOT match

table.column (Account.Name)//15 char string

unless you have described it incorrectly.

What values are in the sub's table.column (Account.Name)?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[sajisher]

Account.Name has value

S-1234567890-50
L-1234567890-50

S-1234567891-50
L-1234567891-50 and so on

 

[lbass]

Looks like your formulas need to be:

mid({account.name},3,9)

-LB

 

[Turkbear]

Hi,
So you are actually linking on the

mid({Account.Name},3,10) formula
as shown by this part of the sub's selection formula:
mid( {Account.Name},3,10 ) = {?Pm-@ActName}

I missed that part, sorry .

Where is the formula in the main report and where is the sub inserted?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[sajisher]

@ActName is the group formula and placed in the GF1a.

The subreport is also placed in the same GF1a.

For each mid( {Account.Name},3,10 ) i.e. '2682980500' there are 2 rows - one starting with L and one with S.

and the report needs to look like

AccountName "GL" Balance 'OT' Balance 'IA' Balance
@ActName @GL_Balance @OT_Balance @IA_Balance

Main report links 2-3 tables and so AccountName is grouped

'OT' and 'IA' balance made to come from subreport2 and 'GL balance from another subreport 1.

@OT_Balance in subreport2
if mid( {Account.Name},1,1 ) = "S" Then
If {AccountBalance.Text1} = "OT" Then
{AccountBalance.Balance} else 0
else
0

@IA_Balance in subreport2

if mid( {Account.Name},1,1 ) = "S" Then
If {AccountBalance.Text1} = "OT" Then
{AccountBalance.Balance} else 0
else
0

@GL_Balance from subreport 1
------------------------------
if mid( {Account.Name},1,1 ) = "L" Then
If {AccountBalance.Text1} = "GL" Then
{AccountBalance.Balance} else 0
else
0

--- Sorry about Account.Name values. these are the values--
L-2682980500-050
S-2682980500-050

L-2885139000-050
S-2885139000-050

L-2885161000-050
S-2885161000-050

 

[lbass]

It looks like the linking is correct, so I'm wondering whether you are merely trying to display the subreport results or whether you are trying to pass shared variables to the main report? The subreport selection criteria are not showing up in the SQL because of the functions you are using in the formula, but it doesn't mean rows aren't being returned.

-LB