BusinessObjects Board

Prompts using VB for a report with source as excel

I have to create a report using an excel file as source with dat say as follows

C1 C2 C3
India Assam Mumbai
India Assam Pune
India Goa Surat
England Kerala Panjim
England Kerala Thane

And in that I need to create prompts in the following manner.

The report should first prompt me for C1, if I select India then it should
prompt me for Assam or Goa and upon selection of one of these it
should show the resp. data.,i.e, if I select India and then Assam it should
show 2 rows in the report

C1 C2 C3
India Assam Mumbai
India Assam Pune

Is it possible to do so using a VB macro,if so I would be grateful if u could send me such a macro.I hope somebody could help me out.

Thanks in advance


rupam (BOB member since 2005-05-04)

hi,

you can use drill mode… So you can filter by colmun names, and in the combo box you can select India , and than it show 3 rows

India Assam Mumbai
India Assam Pune
India Goa Surat

than you can add column 2 to drill mode and after you can select Assam or whater you want…
i think this is what you want…
Hope this helps.


ssusantez :turkey: (BOB member since 2005-12-22)

Hi susan,
Thanks for your help.The drill option does work but my client wants prompts to be incorporated , that is why I asked if maybe the same can be done using vb or something.

Thanks for your help again

Regards
Rupam


rupam (BOB member since 2005-05-04)

I think you are looking for something like cascading prompts.IF so please check out this


Rajasekar :india: (BOB member since 2004-07-16)

First, you can’t use VBA to manipulate data providers of any type other than those based on a Universe. Second, even if you could, you can’t use prompts in a personal data provider. So…

I think your first step is going to have to be creating a universe based on your Excel spreadsheet. There are a whole set of challenges there. From that point you can consider writing some VBA code that manipulates the data provider prompts.

It’s not the most difficult thing in the world, but it’s not trivial.

Now if you’re truly talking about using Visual Basic (VB) rather than Visual Basic for Applications (VBA) then that’s another wrinkle to deal with as well. :slight_smile:

I don’t have such code to share. I expect that it’s more than a few minutes worth of work.


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