Using Sql Select In A Datapage

[JRoss]

I have a calculated field that uses SQL, I plan on using this datapage as a child called from a parent that passes in MemberID

 

When I run the child datapage by itself that uses the select below It does not seem to pass in the parameter entered on the first sceen,  I always get the same number , Its like the @field:MemberID value is the last row in the dataset or something.

 

select sum(b.CCO_Place_Points)
from JRRESULTSTABLENEW a
join Lookup_Places_2 b on b.CCO_Place_ID = a.CCO_PlaceID
where a.MemberID = [@field:MemberID]

 

Thanks,

-John

 

[casyana]

Hi JRoss!

 

I hope, I have a workaround for you. I believe, your DataPage is based on the table JRRESULTSTABLENEW, so field MemberID is from the same table. The workaround is to create a view, based on this table, you can include all fields from the table into the view, you even can name them the same, except of the MemberID, you should rename this field (for instance, name it as Member_ID). After that, change the data source of your DataPage to the just created view, and update your formula to the following:

 

select sum(b.CCO_Place_Points)

from JRRESULTSTABLENEW a
join Lookup_Places_2 b on b.CCO_Place_ID = a.CCO_PlaceID
where a.MemberID = [@field:Member_ID]

 

Hope it helps!

[ShWolf]

Hi JRoss,

 

In your case MemberID in the second DataPage static value? Or it will be dynamic value?

 

You can try to use external parameter.

In first DataPage for the MemberID field select "Pass field value to the next page" on Advanced tab.

In second DataPage replace [@field:MemberID] with [@MemberID]

 

NOTE: Check that parameter name is the same in both DataPages

 

Let me know if this helps

[JRoss]

Update : I changed the replaceable parameter [@MemberID] with a static value and the select works as expected, I also created a calculated field that only contained [field:MemberId] which returned the MemberID for each row.

 

I would  think that this type of calculated field should work w/o needing to embed it into a separate datapage, Its like the  replaceable parameter is not being correctly set by the db .

 

Yes the @MemberID is dynamic.  - I'll check that each of the parameter names are identical between datapages.

 

I'm really only trying to produces a distinct list of Members, count(*), or sum(amt) .

 

Thanks in advance.

 

-John

[bahar_vm]

John, If you are passing MemberId as parameter from first screen when you open the DataPage then you need to use [@MemberID] in your SQL code instead of [@field:MemberID].