I have many (tens of thousands) of stored procedures I am looking to expose to a trained user base via IA.

For some stored procedure input parameters I would like to offer a listbox that is datas driven, coming from a master that is sourced by a view or table.

I am looking to find a series of steps I can document for my IA users to do this, but I cannot find a way to make it happen.

I would appreciate any advice,

Thank you

This message has been edited. Last edited by: Kerry,

Hi

1. I would recommend you to open a new feature request for it at IBI (we also requested the same, defining dynamic prompts list of values on the master file level)

2. The only way I know you can accomplish it:

2.1 Using developer studio create a reporting object for the master file which describes the stored procedure

2.2 Using dev studio GUI in the reporting object create the WHERE or optional Filters

2.3 now you have the full GUI of the Where creation wizrad including defining a parameter which is dynamic list of values based on a different master file (for example a master file which describes the ref table)

2.4 Then in dashboard users should be able to create reports on this reporting object and when they run the report they will get the prompts- list of values

quote:

defining dynamic prompts list of values on the master file level

Eran:

Thank you. Our new feature request is in. I think I follow your recommended workaround, but right now I am just document and training IA users in what they can do without access to DS and without requiring DS tasks to be done. (We are a SaaS providers, so tasks they require us to do in DS can cost them, so I am helping the user base by providing collateral of task example to be completely done in IA without support from us.)

Hi

1. You can see the new features we requested in cases: 80082507 , 80242511

2. I don't know whether the following will be acceptable by your IA users but since you rely only on IA you can try the following in the BI dashboard:

2.1 give the IA user a role of developer
2.2 then the IA user is allowed to create a reporting object in the BI dashboard environment
(its a partial java applet in 7703)
2.3 when creating the reporting object you can define a Where, you have the option to click on EDIT and type the command directly
You can use the syntax which populates the dynamic list box
for example:
WHERE PROD_CODE EQ '&PRCODE.(FIND PRODUCTKEY,ENGLISHPRODUCTNAME IN pele_dimproduct).COUNTRY.'

the syntax for populating the dynamic list:
FIND CODE_FIELD,DESCRIPTION_FIELD IN MASTER_NAME

2.4 then when creating the report using InfoAssist this dynamic list of values will be populated before submitting the report

Thanks, Eran.

Case #80952553 is for our request of the feature.

By the way, will you be coming here for Summit in Florida this year? I will be there with one or more of my colleagues. Maybe we could compare notes.

Thanks for the addiitonal steps, we may consider making partners developers in order to give them access to the WF code, but right now I am just looking to document for our users the capabilities of just the IA GUI.

We are a SaaS ERP provider to a broadbase of customers with a multi-tenant back-end. I would have to do serious security penetration testing to make sure users won't be able to see other customers' data before I could let them have code access. Also, we have our custom tools that allow them to do that through a secure combination of ad hoc SQL authoring, custom scripting language and WYSIWYG HTML authoring.

We are just rolling out to the customer base IA without Editor (no develpoer role) this spring and that is what I am preparing for.

Hi

I manage IBI\WebFOCUS technical services here in Israel (at IBI Agent company: SRL products \MalamTeam).
I plan to attend the Summit this year also with one of my colleagues and maybe one of our customers will also join.
It will be interesting to hear about your implementation.

Your application sounds very interesting, we have also customers who have internet based customer facing applications , successfully passed external security penetration testing, however they deployed a self service application.
Good Luck with the deployment in the spring,
Yours

Eran

Tom,

Please allow me to re-phrase your objective so you can tell me if I understand it correctly.

You want your InfoAssist users to have the capability to run a report from one master file (based on your stored procedure) and populate their filter controls using a different master file (a lookup table of some sort) is that correct?

I assume you know that your users can create prompts for the report now, yes? I am also going to assume that dynamically populating the prompts takes way too much time which is why you want to fetch the values from a second source. If not, what is the reason for not fetching the values from the same master that the report is going to run on?

Either way, here is a possible solution:
Let's assume you want to prompt the user to select a Country value when they run your report.
1) Create a report with only the COUNTRY column in it as a SORT
2) Set the output format of that report to Excel. (Note: Excel 2000 worked for me in step 11 whereas Excel 2007 did not)
3) Run the report and save the Excel file to the local hard disk
4) Repeat steps 1 thru 3 for every column you may want to use as a filter (Yeah, I know)
5) Create your actual report
6) Right-click a column for which you want to create a filter and select the Filter option from the context menu
7) In the Filter dialog box click on the Prompt menu item and select "Prompt using Selection (Static)"
8) Click on the Values menu item and select "Fetch Values from Disk File"
9) Click on the Browse button and locate the file containing the values for the column you are working with
10) Click the Open Button
11) Click the OK Button
12) Move some or all of the items in the Data Values list over to the Selection Values list
13) Click the Allow Multiple Values for Prompt button (which really should be a checkbox) if you want the user to select more than one item from the filter list
14) Change the Prompt text if you so desire
15) Click the OK Button
16) Run your report and you should be prompted with your selection list

Not exactly simple and certainly not dynamic but it accomplishes what I think you want.

Thoughts?

Dan

quote:

Please allow me to re-phrase your objective so you can tell me if I understand it correctly.

You want your InfoAssist users to have the capability to run a report from one master file (based on your stored procedure) and populate their filter controls using a different master file (a lookup table of some sort) is that correct?

I assume you know that your users can create prompts for the report now, yes? I am also going to assume that dynamically populating the prompts takes way too much time which is why you want to fetch the values from a second source. If not, what is the reason for not fetching the values from the same master that the report is going to run on?

Either way, here is a possible solution:
Let's assume you want to prompt the user to select a Country value when they run your report.
1) Create a report with only the COUNTRY column in it as a SORT
2) Set the output format of that report to Excel. (Note: Excel 2000 worked for me in step 11 whereas Excel 2007 did not)
3) Run the report and save the Excel file to the local hard disk
4) Repeat steps 1 thru 3 for every column you may want to use as a filter (Yeah, I know)
5) Create your actual report
6) Right-click a column for which you want to create a filter and select the Filter option from the context menu
7) In the Filter dialog box click on the Prompt menu item and select "Prompt using Selection (Static)"
8) Click on the Values menu item and select "Fetch Values from Disk File"
9) Click on the Browse button and locate the file containing the values for the column you are working with
10) Click the Open Button
11) Click the OK Button
12) Move some or all of the items in the Data Values list over to the Selection Values list
13) Click the Allow Multiple Values for Prompt button (which really should be a checkbox) if you want the user to select more than one item from the filter list
14) Change the Prompt text if you so desire
15) Click the OK Button
16) Run your report and you should be prompted with your selection list

Not exactly simple and certainly not dynamic but it accomplishes what I think you want.

Thoughts?

Dan

Yes, I do need for me InfoAssist users to have the capability to run a report from one master file that is based on a stored procedure while populating at least some filter controls using a different master file (a lookup table of some sort). That is that correct.

How can they create prompts for the report now from a master that is not the one based on the stored procedure? I want to fetch the values from a second source since the values are not in the first source (one case), or not
obtainable (second case).

Consider the stored procedure in more detail. Greatly simplifying it, the stored procedure takes “Workcenter Group” as an input parameter and in the return columns includes “Workcenter Name”, and several measurable, such as “Efficiency”, “Production Rate”, etc.
The desire is to have a filter control of a multi-select listbox of currently available Workcenter Groups. Note that the stored procedure returns 0 records until it receives a list of Workcenter Groups, so it cannot supply the data for the filter control. However, there is a minor table of Workcenter Groups ready to use.

I appreciate your Excel steps and saw that option there in IA, but as your already implied that is not a very desirable solution. We are OEM’ing BI to our customers as part of their ERP solution and they already can do BI sourcing their filter controls off of the DB, so it would be real step backwards in technology to have the subscribers need to use local files for filter controls. Also, when sharing reports (customer to customer or partner to customer) or running the report later in the day from thin clients or mobile devices may not be reliable if the filter controls require. We are a SaaS provider sending out this functionality through the cloud, so architecture that requires local files is rather anathema to us.

Simpler for a current workaround is I just have them doing a text prompt, copying the values they want out of the ERP and paste in a comma-delimited list to feed into the stored procedure. That will be more current perhaps than any local file and I think it will take less steps.