Automating Queries using VBA

[system]

Hi all, does anyone know if the Business Objects VBA object model can be used to automate queries.

I have been able to write some VBA to export reports and such, but I cant figure out how to alter my query.

What I would like to do is quite simple, just change a date range using a form, ie have a Userform which has each month on it for the user to select which month to run the report for, then update the queries start and end dates to correspond.

Any assistance would be appreciated.

Adam

---

adam@luton (BOB member since 2004-08-20)

[MarkP]

Given there is user interaction involved, how much automation are you after? I would suggest using prompts for your dates and automating the output if that’s what you intend.

[system]

I was wanting to make up a simple userform and use VBA to do the work behind the scenes to alter the dates.

below is the current query

SELECT
  initcap(O_PERSONS.PER_SURNAME || ', ' || O_PERSONS.PER_FIRST_NAMES),
  O_PERSONS.PER_ID,
  initcap(nvl(Categories2.CAT_DESC,'Unknown')),
  initcap(nvl(Categories.CAT_DESC,'Not Completed')),
  initcap(O_SERVICE_TYPES.STY_NAME)
FROM
  O_PERSONS,
  O_CATEGORIES  Categories2,
  O_CLASSIFICATIONS  Ethnicity,
  O_CLASSIFICATIONS  Persons_Classes,
  O_CATEGORIES  Categories,
  O_SERVICE_TYPES,
  O_AGREEMENT_DETAILS
WHERE
  ( O_PERSONS.PER_ID = Persons_Classes.CLA_SUBJECT_ID(+) and Persons_Classes.CLA_SUBJECT_IND(+) = 'P'  )
  AND  ( Categories.CAT_ID(+)=Persons_Classes.CLA_CAT_ID and Categories.CAT_TOP_CAT_ID(+)=Persons_Classes.CLA_TOP_CAT_ID  )
  AND  ( O_PERSONS.PER_ID = O_AGREEMENT_DETAILS.ADE_SUBJECT_ID(+) and O_AGREEMENT_DETAILS.ADE_SUBJECT_IND(+) = 'P'  )
  AND  ( O_AGREEMENT_DETAILS.ADE_STY_ID=O_SERVICE_TYPES.STY_ID  )
  AND  ( Categories2.CAT_ID(+)=Ethnicity.CLA_CAT_ID and Categories2.CAT_TOP_CAT_ID(+)=Ethnicity.CLA_TOP_CAT_ID  )
  AND  ( O_PERSONS.PER_ID= Ethnicity.CLA_SUBJECT_ID(+) and Ethnicity.CLA_SUBJECT_IND(+)='P'  )
  AND  ( Ethnicity.CLA_TOP_CAT_ID(+) = @Prompt('Select the top level classification for ETHNICITY','A','Personal Details (Usually Client)\Ethnicity Top Level Cat',MONO,CONSTRAINED)  )
  AND  ( Persons_Classes.CLA_TOP_CAT_ID(+) =@Prompt('Enter Top Level Category','A','Personal Details (Usually Client)\Top Level Categories',mono,constrained)  )
  AND  (
  trunc(months_between(SYSDATE,O_PERSONS.PER_BIRTH_DATE)/12)  >=  65
  AND  Persons_Classes.CLA_DATE_NOTIFIED  BETWEEN  '01-01-2004 00:00:00' AND '31-01-2004 00:00:00'
  AND  Ethnicity.CLA_END_DATE  IS NULL  
  )

It is set up to provide a report for those people aged over 65 recieving “equipment” between 01-01-2004 and 31-01-2004.

What I would like is a userform (no big deal, easy to make, have been doing them in Excel for ages) which users can select the month, say February, and I can alter the date in the query to 01-02-2004 to 29-02-2004 by using the userform code.

I have been playing around with VBA in Business Objects, and stumbled accross this sort of code, but I dont know how to use it, and its not documented anywhere.

Dim newquery As busobj.Query
Set newquery = New busobj.Query

newquery.Conditions.Add(????????)

---

adam@luton (BOB member since 2004-08-20)

[system]

I’m going to echo Mark’s comments. You’re already using prompts for other things. Why not add Begin Date and End Date prompts? I realize that users have to be "protected from themselves " sometimes, but I don’t think asking them to enter dates correctly is too much to ask.

That said, if you’re wanting to understand the details of data providers and the SDK, check out this utility. The purpose of the utility is to copy a data provider, therefore it touches every part of a data provider. Review the code, and you can see how you might be able to adapt it to your purposes.

---

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

[system]

… moved to SDK forum. Thanks.

---

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

[system]

Thanks everyone for your assistance. Sorry I havent got back sooner, was in Munich for Beerfest.

---

adam@luton (BOB member since 2004-08-20)