Dynamic Population of Dropdown List

[wfdev]

I am new to Actuate report development. I have only had the beginner’s class in this.

We have a project assignment to build front-ends to be accessed through the web for reports that have been developed as SQL Server stored procedures.

I would like to come up with code which will display dropdown lists based on a prior selection.

For example, a dropdown list of a category is displayed. Depending on the value of the category, a corresponding subcategory should be displayed showing those values that relate to the category value. What is the most appropriate way to build/populate ‘dynamic’ dropdown lists based on a prior value being selected? What method is utilized (e.g. BrowserCode, etc?)

I have not been able to find any examples of this so far. If someone can offer any ideas, I’d appreciate it.

Thank you.

 

[milton747]

See the BrowserScriptingControl.rod sample in the product.

Use a BSC component and insert code in the OnRow()
eg

Sub OnRow( row As AcDataRow )
Super::OnRow( row )

Dim sCustomState As String
' Add additional option that the user can select from

sCustomState = row.GetValue( "customers.state" )
If ( InStr ( BSC_OptionsCode, sCustomState ) = 0 ) Then
if sCustomState = DataSource::customers_state then
BSC_OptionsCode = BSC_OptionsCode & "<option selected value='" & sCustomState & "'>" & sCustomState & " </option>"
else
BSC_OptionsCode = BSC_OptionsCode & "<option value='" & sCustomState & "'> " & sCustomState & " </option>"
end if
End If
End Sub

 

[jfdev]

Was this sample code from Actuate really helpful. I don't think it goes far enough with what your requirement is. I have the same type of requirement. Dynamic dropdown lists could be used in many of our apps. I can also populate the lists from our database but cannot get the lists to be linked logically, meaning the choice in the 1st list allows the filtering for the 2nd.

Any help would be greatly appreciated. I have seen other forums looking for the same solution.

 

[gdwood]

jfdev,

can you elaborate on what you need? You have a dropdown that is populated dynamically from a datastream, then you want the user to select a value and populate another dropdown based on that selection from the same or a different datastream? and so on?

is that it?

 

[jfdev]

gdwood,

Yes, you have it. My current approach was to use 3 separate report sections within a sequential section. One for each dropdown list (BSC) with its own data connection and SQL. My plan was that if I could capture the selection from the first list, I could use it to modify the WhereClause (using ObtainSelectStatement) for the 2nd list and so on. The closest sample that comes with Actuate is QuinnCustomers that uses states, cities and customers. I tried the exact same approach but was not successful.

thanks for the reply.

 

[gdwood]

OK, sounds simple enough, I won't look at the examples as that is not like anything that we do, and only confuses me to heck as to why it would be done that way.

Can you verify that the value is being passed when the user selects something from the first drop down?

If that's the case then you should be able to utilize that in the where clause of the next select.

If you have 3 report sections in one sequential, what 'stops' the report from generating the 2nd before a value is picked? From your description, it sounds like the report would just keep pulling data from the database and not actualyl wait for the user to select a value. Therefore the report doesn't have a value to use in the second and subsequent selects. Does that make sense?

You'd have to code the report to 'wait' for the input. Do you have that type of code in there?

 

[jfdev]

gdwood,

thanks again for the reply. I agree that this should also be rather simple. I do these things using MS Visual Basic all the time. Knowing how to integrate it within Actuate has been more of a challenge than I expected. I cannot verify that the value is passed. How can I do this? As for 3 report sections, I tried this in order to create a separate data stream for each dropdown list so that I could modify the WhereClause at run time. Maybe I should just use one report section? I will need to learn how to do a "wait" in Actuate.

thanks

 

[gdwood]

put in a break and debug to see if the value is passed in the SQL statement.

I think the issue is that the report is just running through the sections and not waiting for the variable to be populated for the where clause, of course that's a guess.

do you build the SQL line by line and issue an open or execute command? If so place a break before and look at the SQL in the second report section.

In VB your getting the information and then doing a second and subsequent fetch to the DB, whereas the report runs without waiting for your input.