Cascading Prompts in Enterprise Guide

[shudsoo]

Hi,

I am trying to create a cascading prompt within Enterprise Guide 4.3 without having to use a stored process - I can create a simple prompt using multiple values from a dynamic list and then I can refer to that prompt in the query builder filter but how would I create a cascading prompt that depending on the first measure would only return results from the second measure?

Thanks

Steve

[ChrisHemedinger]

Steve,

Unfortunately, cascading prompts work only with stored processes in EG.  That's the only place you'll see the "Dependency" tab when defining prompts...

Chris

[TimCampbell]

Hi Steve,

I am hitting the same problem as you and Chris is right that the only way to go is stored processes for cascading prompts.

/* Begin Rant */

Thisis a serious design flaw in enterprise guide in my opinion because whilst a stored process prompt in enterprise guide can be cascading, the code that the stored process runs cannot reference any other information from your SAS session because stored processes start their own sessions.

/* End Rant */

But enough of my whining... I have found a way that you can make your dynamic prompts slightly more dynamic without making them cascading depending on your circumstances.

if you want your prompt for values in your dynamic list to react to values selected in a different prompt (or macro variable defined earlier in the project) you can do it by registering views that combine the table containing your data and the sashelp.vmacro table.

for example, I have an enterprise guide project where my users import some scenario data, save it into a library that many users have access to and record the scenario creation in an audit table along with their user id.

In another part of the same project i want the users to be able to pick a scenario that has been imported to use for further processing but i want them to only be able to select scenarios that they have imported themselves.

For this prompt I used proc SQL to create a view which joins my audit table onto sashelp.vmacro where the sashelp.vmacro value variable matches the user id in my audit table and where the sashelp.vmacro name variable is SYSUSERID. In this way the list of scenarios in the view changes depending on the user looking at it. (it is worth checking that the view has the correct data you want in it at this point before you try to register it, been stung by this so many times!)

if you use the Data Library Manager in management console to register the view in metadata (mine usuallyerrors but that is fine) and then DI Studio to update the metadata for the registered table you will probably get an error message again but if you view the details of the error you can find the proc metalib statement you need to run to update the metadata, which should run fine from the EG session that you used to create the view.

If all that worked you can point your program prompt to the view and then the dynamic list will automatically change depending on your connection profile or whatever macro variable values you defined.

Not quite a cascading prompt i know but a little better than a normal prompt.

On a related note, If anyone does know of a way to get a stored process to run in the same session as the open enterprise guide project that would solve no end of issues...

Tim

---Found the answer to my last question.

https://communities.sas.com/message/147006#147006