I have a report that has 7 prompts. The prompts are defined as filters in designer and then I included them in the report.
I have 3 Data Providers (MAIN, MAIN2 and REPORT) in this report. MAIN and MAIN2 DPs are using the 7 prompts. The prompts are then filled with values using VBA code. Basically I refresh REPORTS universe through VBA and based on values returned, the 7 prompts are filled with certain values. I then refresh MAIN and MAIN2 universe.
The report is then scheduled to run via BCA. Everything works just fine.
However, now I am facing a situation where not all 7 prompts are needed to be answered every time. For example, if prompt_1 asks for a list of selected departments to run the report for, and the user wants all departments included, then I would need to omitt answering prompt_1 in the MAIN and MAIN2 DPs. How can I do this? The prompts are defined in the universe and included on the report and they are expecting to be answered. If I don’t answer all 7 of them every time, then I get ‘Error in refresh’ which sort of makes sense.
So, the question is ‘is it possible to define and answer these prompts within SDK?’ If this is possible, I would be able to, based on certain criteria, define and answer a different number of prompts every time the report is run.
Thanks for reply. I think your solution will partially work for me. It will work for situations where there is a list of values to chose from. However, there can also be a situation where the prompt is not needed at all. For instance, if prompt_2 ask for a ‘user_type’ and the ‘user type’ is not available for every report, then I would need to entirely drop prompt_2 from being answered.
Thanks for your suggestion though. I think I will still use it for situations where a user can select *.
It is not possible to leave a prompt blank or otherwise make it optional. They all have to be filled in. That said, if you are comfortable that the “ALL” trick will work for prompts that ARE needed, it should also work where the prompt is NOT needed. In either case, it effectively makes the prompt optional by creating a condition that is always true.
So, if I understand this correctly, then I should put ALL in both cases, where the prompt is needed and wants all values to be included, and where the prompt is not needed, by supplying ALL, it will also returned all values. Correct?
I think I understand that if you define prompts in universe and then include them in the report, they have to be answered. But if I need a different number of prompts to be answered each time the report is run, then ideally, I would want to dynamically define and answer these prompts within VBA. Anybody knows how to define prompts in VBA?
The following would simulate adding a Year prompt to an eFashion query:
Dim Conds As Conditions
Set Conds = ThisDocument.DataProviders(1).Queries(1).Conditions
Call Conds.Add("Time period", "Year", "Equal to", "Enter Year (YYYY)", "Prompt")
Yes, this is what I am looking for – to define and fill in prompts in VBA. I tried your code but it’s giving me error that the object doesn’t exist.
Here is my macro. What I need to do is to add a prompt to ‘MAIN’ data provider then answer that prompt with a value retrieved from "FILTERS’ data provider for Code_ID.
Sub burst_reports()
' Set up Data Providers as data sources (cubes)
Dim bo_burst As DataProvider
Dim bo_main As DataProvider
Dim bo_main2 As DataProvider
Dim bo_filters As DataProvider
Dim bo_var1 As Variable
Dim log_file As String
Dim log_file2 As String
Dim ctr As Integer
Dim I As Integer
Dim N As Integer
Dim rowCountMAIN As Integer
Dim rowCountMAIN2 As Integer
Dim rowCountRPT_DIST As Integer
Dim rowCountFilters As Integer
Log "***********" & "Start of macro..." & Now & "***********"
' Interactive mode default is true.
Application.Interactive = True
log_file = "C:\BOBCALog\BCA_email_error_log.txt"
log_file2 = "C:\BOBCALog\BCA_error_log.txt"
On Error GoTo ErrorHandler
' Refresh FILTERS data provider which is a stored procedure
Set bo_filters = ThisDocument.DataProviders.Item("FILTERS")
bo_filters.Refresh
Log "bo_filters DP refreshed"
rowCountFilters = bo_filters.NbRowsFetched
Log "Numbers of rows fetched in FILTERS: " & rowCountFilters
For N = 1 To bo_filters.Columns.Item(1).Count
Log "Report_Name: " & bo_filters.Columns.Item("Report_Name").Item(N)
Log "Report_Name: " & bo_filters.Columns.Item("Code_ID").Item(N)
JumptoNextRecord_in_FILTERS:
Next N
'DEFINE A FILTER ON 'MAIN' DATA PROVIDER THEN ANSWER IT WITH Code_ID from FILTERS DP.
Dim Conds As Conditions
Set Conds = ThisDocument.DataProviders("MAIN").Queries(1).Conditions
Call Conds.Add("Code ID", "Code", "Equal to", "Enter Code ID", "Prompt")
' Set BO Back to Interactive Mode
Application.Interactive = True
' Release memory by setting variables to nothing
Set bo_burst = Nothing
Set bo_main = Nothing
Set bo_main2 = Nothing
Set bo_filters = Nothing
Log "Macro successfully executed!"
Exit Sub
EmailErrorHandler:
' Open a log file to record the error
Open log_file For Append As #1
' Write the Date, Document Name, Server Name, and Error Message to the log
Print #1, Now
Print #1, "Error #: " & Str(Err.Number) & " was generated by " & Err.Source
Print #1, "Error Message: " & Err.Description
Print #1,
Close #1
'Resume JumptoNextRecord:
ErrorHandler:
' Open a log file to record the error
Open log_file2 For Append As #1
' Write the Date, Document Name, Server Name, and Error Message to the log
Print #1, Now
Print #1, "Error #: " & Str(Err.Number) & " was generated by " & Err.Source
Print #1, "Error Message: " & Err.Description
Print #1,
Close #1
End Sub
Sub Log(logMsg As String, Optional errObj As ErrObject)
If gLogFile = "" Then
gLogFile = "C:\BOBCALog\" & ThisDocument.Name & "-macro.Log "
End If
Dim intFile As Integer
intFile = FreeFile()
Open gLogFile For Append As intFile
If errObj Is Nothing Then
Print #intFile, logMsg
Else
Print #intFile, logMsg
Print #intFile, "----------------------------------------------------------------"
Print #intFile, "Error Number: " & errObj.Number
Print #intFile, "Error Description: " & errObj.Description
End If
Close #intFile
End Sub
The part where it says DEFINE A FILTER ON ‘MAIN’ DATA PROVIDER THEN ANSWER IT WITH Code_ID from FILTERS DP’ is where I am getting errors.
I don’t see where you are then setting the value to the prompt. You write all of the FILTERS values to a log, and define the prompt, but never tie them together otherwise.
In any case, is it possible you are making things more complex than is needed? Why create a prompt if you are going to fill it in with VBA behind the scenes. If you know what you want the value to be, just set that in the condition … no prompt needed. Say the CODE_ID is ABC:
(BOB member since 2003-11-27)
(BOB member since 2002-09-19)
!