Max_Inlist_Value

Hi , Where can I set the Max_InList_Value , please reply with the specific answer. I have a requirement where I am dynamically through VBA addition InList Condition where in the values are more than 3000 values
e.g

User Object “InList” … values (“1;2;3…3589”)


jatinr (BOB member since 2004-05-27)

Depends on your version.

In version 5 you do this in the PRM file (or is it the SBO file).

In V6, I believe you can do in on the parameters tab of the universe properties.


Steve Krandel :us: (BOB member since 2002-06-25)

True, but I still think this is just the limit allowed by the user interface. There is still a database limit, which for Oracle I believe is 1,000.


Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

The max_inlist_values only has to do with the number of values that can be interactively picked by the user. It has nothing to do with the number of values that can be added via VBA. There must be something else going on.

Try adding all of your required condition values (your “in list” values) to a semi-colon delimited string stored in a variable. Then use that variable - once you are done adding values to it - to assign the condition. I have added thousands of items to an inlist condition using that technique. And that was without having to adjust the “max” value in the parameter file.


Dave Rathbun :us: (BOB member since 2002-06-06)

Hi , I am doing exactly what you have written , Basically I have to pass result of column from DP1 to other Data prodivers in VBA ,I have all the values that get conconacted into a variable which I use Conditions Class - Add Method., then I refresh my Dataprovider .

When I see the report using Edit Dataprovider , i find not all the values of the column from DP1 have been used in the inlist condition that has been added dynamically by VBA.

Please respond.

Thanks
Jatin


jatinr (BOB member since 2004-05-27)

Databases do not allow for an unlimited number of items to be used in an in-list. You need to find out what the limit is for your system.


Steve Krandel :us: (BOB member since 2002-06-25)

Moving to SDK.

Post your code and we’ll see if someone can help.


Dave Rathbun :us: (BOB member since 2002-06-06)

In the code below , I pass the results of Column from DP1 concatenated into a string delimited by “;”. (e.g 2000323;23232434;2323232;…)

There can be upto 3000-4000 such values.

thanks

Private Sub Document_BeforeRefresh(Cancel As Boolean)
Dim DP1 As DataProvider
Dim DP2 As DataProvider
Dim Doc As busobj.Document
Dim DP1Values As String

Set Doc = ThisDocument
Application.Interactive = False
Doc.DataProviders.Item("DP1").IsRefreshable = True
Doc.DataProviders.Item("DP1").Refresh
DP1Values = GetDP1Values
If (DP1Values <> "NODATA") Then
CreateConditionForDP2 (DP1Values)
Doc.DataProviders.Item("DP2").IsRefreshable = True
Doc.DataProviders.Item("DP2").Refresh
'Doc.DataProviders.Item("DP2").IsRefreshable = False
End If
Application.Interactive = True
End Sub

Private Function GetDP1Values() As String
Dim MyConditionList As Variant
Set Doc = ThisDocument
Set DP1 = Doc.DataProviders.Item("DP1")
If (DP1.Columns(1).Count <> 0) Then
For i = 1 To DP1.Columns(1).Count - 1
'We get all the values for the first column
MyConditionList = MyConditionList + CStr(DP1.Columns(1).Item(i)) + ";"
Next
MyConditionList = MyConditionList + CStr(DP1.Columns(1).Item(i))
GetDP1Values = MyConditionList
'MsgBox GetDP1Values
Else
GetDP1Values = "NODATA"
End If
End Function

Sub CreateConditionForDP2(ListOfValues As String)
Dim MyConditions As Conditions
Dim MyCondition As Condition
Dim DistinctLOV As String
Dim i As Integer
On Error GoTo DPError2

DistinctLOV = ListOfValues
Set Doc = ThisDocument
Set DP2 = Doc.DataProviders.Item("DP2")
'We get the conditions for the first query
DP2.Load
Set MyConditions = DP2.Queries(1).Conditions
If MyConditions.Count <> 0 Then
'We remove the conditions that we don't want anymore, in the example, I removed everything...
For i = 2 To MyConditions.Count
MyConditions.Remove (i)
Next
End If
'We had now the condition
Set MyCondition = MyConditions.Add("Plan Billed Rev Cust MBN BTN Detail", "Customer MBN BTN Detail Key", "in List", DistinctLOV, "Constant")

'MsgBox DP2.SQL

DP2.Unload
Exit Sub
DPError2:
DP2.Unload
End Sub

[Edit - Please use the “Code” formatting option when posting code samples. It preserves the indenting and makes the post easier to read. Thanks. - Dave]


jatinr (BOB member since 2004-05-27)