Jump to content
  • 0

Using Sql Select In A Datapage


JRoss

Question

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

 

Link to comment
Share on other sites

4 answers to this question

Recommended Posts

  • 0

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!

Link to comment
Share on other sites

  • 0

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

Link to comment
Share on other sites

  • 0

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

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...