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
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.
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.
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]