You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Getting prompts into complex query in PS

4 replies [Last post]
jullman's picture
User offline. Last seen 10 years 35 weeks ago. Offline
Joined: 05/14/2009
Posts: 3

Can you tell me the best practice for this scenario? We want to provide a menu item, that when the user chooses it, will provide job information. The user will enter various prompts such as selected business units, deptids, empl_status, and so on. The result should be similar to query results on the screen, with an option to download.

I could do this strictly with PS Query, but I cannot figure out how to get the complexity I need. For example, there are a few core tables - Job, Compensation, Names, and many tables joined with left outer joins - Address, Email, Phone, and a bunch of code tables to get descriptions. Moreover, we have some criteria for the jobs to choose by date that are not so straightforward.

I wrote this as a regular DB View, but doing it this way doesn't let me provide prompts to narrow down the results.

It looks to me like a Dynamic View might do what I want, but I'm not sure how to fit it in. Since the Query Viewer has the structure I want, it would be nice to embed this DV into a Query, but it does not show up on the available Records to build into a Query.

Any direction would be appreciated. It seems to me this must be a common scenario, so I hope there are ideas out there.

Ashar's picture
User offline. Last seen 4 years 35 weeks ago. Offline
Joined: 03/26/2008
Posts: 227
Re: Getting prompts into complex query in PS

Create a page which will have all criteria (or seach) fields and a grid to display the results.
based on the search criteria entered , populate the grid using Select method of RowSet class.
Enable the download option on the grid.

/Ashar

Ashar's picture
User offline. Last seen 4 years 35 weeks ago. Offline
Joined: 03/26/2008
Posts: 227
Re: Getting prompts into complex query in PS

You can also populate the grid using RunToRowSet method of query class if you want to use the PSQuery that you have created already.

jullman's picture
User offline. Last seen 10 years 35 weeks ago. Offline
Joined: 05/14/2009
Posts: 3
Re: Getting prompts into complex query in PS

These are very good answers, but they made me realize I made a mistake. I forgot that I NEED the code to be in a Query, because users need to use the Query directly in Crystal Reports and XML Publisher.

So is there a way to do that?

Thanks in advance.

Ashar's picture
User offline. Last seen 4 years 35 weeks ago. Offline
Joined: 03/26/2008
Posts: 227
Re: Getting prompts into complex query in PS

I'm not sure about doing it in PS Query. I don't have much experience with Query. May be there are someone else out there who can help you.