Running Brio from within VB

[MacroManII]

I am using the following code to run a simple Brio Query from VB. I would like to set up and run a a Brio Query that needs to connect using a DataBase Connection File (.oce), and supply a username and password to gain access. Does anyone know how to do this ?

MacroManToo


Option Explicit
Private Sub Main()

On Error GoTo Err_Trap

Dim bqApp As BrioQry.Application
Dim bqDoc As BrioQry.Document
Dim bqSec As BrioQry.Section

Dim strDoc As String

Set bqApp = New BrioQry.Application
bqApp.Visible = False
Set bqDoc = bqApp.Documents.New("MacroManII.Bqy&quot
bqDoc.Sections.ImportDataFile "C:\mymail.csv", bqImportFormatCommaText
bqDoc.Sections("mymail.csv&quot.Activate
With bqApp.ActiveSection
.Columns.AddComputed "DATE", "ToDate ( Substr ( DATE_STAMP, 2, 10) )"
.Columns.AddComputed "TIME", "ToDate ( Substr ( DATE_STAMP, 13, 8 ) )"
.Columns.AddComputed "MONTH", "ToChar ( DATE, ""yy - mm"&quot"
.Columns.AddComputed "WEEKDAY", "NextDay ( DATE, 'Monday' )"
.Columns.AddComputed "DIFF", "WEEKDAY - DATE"
.Columns.AddComputed "DAY", "Decode ( DIFF, 7 ,'Mon' ,1,'Sun',2,'Sat',3,'Fri',4,'Thu',5,'Wed', 6,'Tue' ,'Xyz' )"
End With
bqDoc.Sections.Add bqPivot, "mymail.csv"
bqDoc.Sections("Pivot&quot.Activate
bqApp.ActiveSection.Name = "MyMail Pivot"
With bqApp.ActiveSection
.TopLabels.Add "ACTION"
.SideLabels.Add "MONTH"
.SideLabels.Add "DATE"
.SideLabels.Add "DAY"
.Facts.Add "BOX COUNT"
.Facts.Add "COST"
.Facts("COST&quot.DataFunction = bqDataFunctionSum
.SideLabels("MONTH&quot.Totals.Add
.SideLabels("DATE&quot.Totals.Add
.TopLabels("ACTION&quot.Totals.Add
.HTMLExportBreakRowCount = 10000
.Export "C:\costs.htm", bqExportFormatHTML, True, False
End With
bqApp.Quit

Exit_Main:
Set bqDoc = Nothing
Set bqApp = Nothing
Exit Sub

Err_Trap:
MsgBox Err.Description, vbCritical, Err.Number
Resume Exit_Main

End Sub

 

[prachisharma]

Hi,

Even i am looking for database connection to Brio from VB. Do you know...let me know if possible.

thanks,
prachi

 

[MacroManII]

This is how to connect to an OCE in Brio within VB



Dim bqApp As BrioQry.Application
Dim bqDoc As BrioQry.Document
Dim bqSec As BrioQry.Section
Dim bqCon As BrioQry.Connection
Dim strOCE As String

strOCE = "C:\Program Files\Brio\BrioQuery\Program\Open Catalog Extensions\Ship_reporting.oce"

Set bqApp = New BrioQry.Application
bqApp.Visible = False

Set bqDoc = bqApp.Documents.New("Ger.Bqy")

Set bqSec = ActiveDocument.Sections.Add(bqQuery)

Set bqCon = bqSec.DataModel.Connection

With bqCon
.Open (strOCE)
.Username = "shipping"
.SetPassword ("shipping")
.Connect
End With

If bqCon.Connected Then

'here you process what you want

End If

bqApp.Quit

 

[prachisharma]

Hi,

That works when creating new brio qry but i want to just process the existing brio qry through VB.The report should be generated with latest data.Any idea how we can do this thru VB.It should work the same as when we click on process button in brio query designer.

thanks,
prachi

 

[MacroManII]

This is how to open an existing Brio Query

Set bqDoc = bqApp.Documents.Open("C:\ExistingQuery.bqy")

Regards

MacroManII