Pass params and get user interactive report

system · August 11, 2004, 6:49am

Hi all,

Using Macros in VBA i have created a userinteractive form( where in users selects report criteria fields) in a document called “rep1”
Further to this i want to pass these values as parameters and wanted to generate an interactive report.

Here i have struck up in creating a report by passing those parameters.

How can i pass the params from the vb form and get the report based it.

Any help is highly appreciated.

Thanx

asurendraz (BOB member since 2004-08-11)

system · August 11, 2004, 2:22pm

First, a warm welcome to BOB!

Second, give us a bit of background on the purpose / functionality of the VBA form. What sort of parameters are you gathering? Which fields to include on the report? How to sort? What value(s) to use for a condition? Some sort of pre-query integrity check?

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

system · August 12, 2004, 5:05am

Hi

The report is a user interactive type. where in user passes/selects values as report where condition.

VBA form has five drop downs

drop down -> some static values filled like Regions (Fixed values)
drop down -> all distinct dates in the table1/date1 field
based on date field get transaction type ( here i need to sort based on 2nd drop down.)
based on value in dropdown 4 get this value
this is again some static value.

User selects the top five columns as per his choice

data provider here is “UNIVERSE” and only one data provider

report columns -> Region, Transactions, Date, amount, etc

Help required for::

populating values in drop down box 3 based on date on dropdown 2
how to use multi conditions using “OR” operator (by default it takes and when i use multi criteria.

preferable i need the classes to use for these requirements.

Thanx

asurendraz (BOB member since 2004-08-11)

system · August 12, 2004, 2:58pm

Can all of that be done? Yes probably, but it will require intermediate to advanced SDK skills. You’re mixing standard LOV functionality (list of values from a table) with customized logic enforcing relationships between fields. You’re also wanting to dynamically choose which columns to display, AND dynamically change the parenthetical nesting of conditions.

[soapbox]For an ad hoc need, teach people to use the tool themselves. The effort you put into development (which will likely be obsolete in no time) would have a better ROI if applied to training instead.[/soapbox]

OK, I feel better now . With all that said, here are a couple of nudges. Set up prompts in the query panel (or universe objects) for the five prompts. You can set them via VBA with something like this:

ThisDocument.Variables("Prompt1 Text").Value = yourVBAVariable

Set Interactive = False before refresh, and Interactive = True after refresh to keep the dialog box from actually appearing.

As far as dynamically placing objects in the data provider, and controlling the parenthetical nesting, take a look at the Copy Data Providers / Copy Report Variables utility for ideas on how to manipulate data providers. You have some work ahead of you … good luck!

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

system · August 12, 2004, 6:52pm

I love it when Dwayne gets on the soapbox!

Go Dwayne, go!

dirmiger (BOB member since 2002-08-28)

system · August 12, 2004, 7:13pm

And I’m not even a consultant, so I don’t even get paid to have opinions like that, LOL! My only “client” is my own company … which is probably the worst kind to have!

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

system · March 22, 2005, 7:27pm

Dwayne Hoffpauir:

With all that said, here are a couple of nudges. Set up prompts in the query panel (or universe objects) for the five prompts. You can set them via VBA with something like this:
ThisDocument.Variables("Prompt1 Text").Value = yourVBAVariable
Set Interactive = False before refresh, and Interactive = True after refresh to keep the dialog box from actually appearing.

Is it possible to pass multiple selection using the above mentioned way? Can I have a prompt in my report with “in list” and let the user select more than one value from the VBA listbox and pass the value back to the prompt in the report?

Thanks

broadcast (BOB member since 2004-11-18)

system · March 23, 2005, 1:51pm

broadcast:

Dwayne Hoffpauir:
With all that said, here are a couple of nudges. Set up prompts in the query panel (or universe objects) for the five prompts. You can set them via VBA with something like this:
ThisDocument.Variables("Prompt1 Text").Value = yourVBAVariable
Set Interactive = False before refresh, and Interactive = True after refresh to keep the dialog box from actually appearing.
Is it possible to pass multiple selection using the above mentioned way? Can I have a prompt in my report with “in list” and let the user select more than one value from the VBA listbox and pass the value back to the prompt in the report?

Certainly. Loop through the choices, build a semi-colon delimited string with the ones that are selected, and pass that to the prompt. I know the actual SQL will use apostrophes and commas, but the prompt syntax is no apostrophes or quotes and semi-colon delimited.

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