BO VBA 6.5.1: Getting Document Refresh Parameter Values

[Former Member]

Hi guys.

Please help a novice in BO 6.5.1 development. I've been tasked to write a macro to iterate through a number of legacy documents and to export data for all the possible values of the different parameters. I figured out how to hardcode a value and how to get the current value, how to get the data provider SQL query (generally huge and ugly), but could someone point me to an example of how to get other possible values? The Variable.PossibleValues property call leads to an error stating that the Variable does not have a list of possible values, but when a report is opened, the user can select a value from a list. Do I have to somehow perform a SQL query directly to get these values? Is a general purpose solution possible or do I have to hardcode the parameter values (getting them from a predefined list or file)?

Thank you in advance

Follow-up: What is done

I prefer using the collections rather than cycles and external list files rather than hardcoded values.

...
Const folder = "C:\...\BusinessObjects\97\"
Const listFile = "C:\...\list.txt"
Sub Get()
Dim x As Variable
Dim dp As DataProvider
Dim c As Column
Dim rep As Report
Dim doc As Document
Dim docNames As New Collection
Dim name, path$, f#, str$

f = FreeFile
path = Left(folder, Len(folder) - 3)
On Error GoTo ErrHandle1
Open listFile For Input As f
While Not EOF(f)
  Line Input #f, str
  docNames.Add(str + ".rep")
Wend
Close
On Error GoTo 0

For Each name in docNames
  On Error GoTo ErrHandle2
  With Application
    .Documents.Receive name, path
    Set doc = .Documents.Open(path + name, True, True)
    For Each dp In doc.DataProviders
      Debug.Print dp.SQL
      ' Here it is also possible to view the data already filtered with the last used values of the variables
      ' using dp.Columns, c.Item(index), etc.
      ' Getting the unfiltered data might also help
      ' The problem is in the "general purpose" part, because inferring the possible values from an ugly 
      ' SQL query might require an unsurmountable amount of logic
    Next dp
    For Each x In doc.Variables
      If InStr(1, x.name, "Year") > 0 Then x.Value = 2008
      '...
    Next x
    .Interactive = False
    doc.Refresh
    .Interactive = True
    For Each rep In doc.Reports
      On Error GoTo ErrHandle3
      rep.ExportAsExcel folder + rep.name + ".xls"
    Next x
  End With
Next name


ErrHandle[1-N]:
End Sub

Edited by: Alexey Krasheninnikov on Oct 2, 2008 10:00 AM

Edited by: Alexey Krasheninnikov on Oct 2, 2008 10:21 AM

Edited by: Alexey Krasheninnikov on Oct 2, 2008 10:43 AM

[Former Member]

That's a nice one to get the values of DocumentVariables (not just Variables, which are different). But at least in BO 6.5.1 the Documents are only numericaly indexed, so you can't call for DocumentVariables("Year"). Again the general purpose stands in the way because the assertion that the DocumentVariable and the Variable have identical or even comparable names may fail in the real world (like in the case of localization of variable and document variable names). So knowing the V-DV mapping in advance is a must...

added [Oct 5, 2008 12:31 AM]: by the way, from what I've seen, I presume that the values of DocumentVariables will be filtered to what the corresponding Variable holds.

Am I wrong somewhere?

PS. Apparently the technique doesn't work in my real world case: there is no DocumentVariable even containing "Year" in the name, but thanks anyway.

Edited by: Alexey Krasheninnikov on Oct 3, 2008 12:46 PM

[former_member11696]

Hi Alexey,

I'm moving your question to our [Legacy|; Forums group.

Thank you

Don

[Former Member]

Thanks Don. I didn't find the forum before posting. Any ideas on the topic?

[dan_cuevas]

Hi Alexey,

Just to clarify, you have a data provider that is based off a universe and you want to extract the values for an object in that data provider.

If this is the case then you can try using the following:

ThisDocument.DocumentVariable("Year").Values(boUniqueValues)

Hope this helps.

Regards,

Dan