Suppress "No Data to Fetch" with used input prompt

building reports desktopintelligence
1

Hi,

We have a report with 3 data providers.
When I refresh a report,there is one data provider,A, that fetches no data while the other two do.
Hence the report displays "No Data to fetch " for data provider,A and then goes on to display data
fetched by the other two data providers.

What I need to do is:
Display the message "No Data to fetch " only when all three data providers fetch no rows and at the same time ask for user input too.

I searched this forum the whole day long but was unable to find a solution to such a situation .
This is because if I set Application.Interactive = False then I do not get the prompt for user input .

Please give me suggestions to do it

Regards
anusha

anusha (BOB member since 2004-08-03)

2

Brief sketch:

  • Use a Union query for every single data provider, bring in one dummy row using Union. This will guarantee that each data provider brings back at least one row (the dummy row).

  • After the document has refreshed count of the number of rows for each data provider, subtract 1 for each data provider (the dummy row) and display an alert accordingly.

Andreas :de: (BOB member since 2002-06-20)

3

Thanks for the reply.

The problem is I do not have a dummy table in the database and hence will have to use VB controls.
Can you please provide me an alternative code that can help in this case?

Regards
anusha

anusha (BOB member since 2004-08-03)

4

Sketch:

Create a data provider (call it Prompts) whose only purpose is to gather prompts … dummy result object, conditions that are always true (like prompt = prompt). You still have all LOV’s, and so forth.

In the Before Refresh event:[list]1. Refresh ONLY the Prompts data provider. This will display the prompts dialog box.
2. Set Interactive = False.
3. Refresh the entire document. You will not see the prompt box, or any “no data” messages.[/list]In the After Refresh event:[list]1. Check the record count for the “real” data providers, and display your own “no data” message if needed.
2. Set Interactive = True (critical).[/list]

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

5

Hi Anusha,

As suggested by Andreas, you just have to modify your query for each data provider to add a UNION and a query like ‘select dummy from dual’. You do not need a dummy table for this. Make sure you check the ‘Do not generate SQL’ option.

Having done this, you will now have to use the variables to count the number of rows in each data provider - 1 and if the total count is 0 then display ‘No data to fetch’.

But Andreas, in this solution we would also need to hide the dummy row from getting displayed in the report, right? :?:

ajaymjain (BOB member since 2004-05-18)

6

Hi,

Thanks for your solutions.

Ajay,The hitch is that the database I use is Sybase and hence a union query like ‘select dummy from dual’ does not work. :nonod: .
Is there some other way?

The solution provided by Dwayne is fine but I have no clues as to how I should go about it.
Dwayne ,can you please let me know the code if possible.

Regards
Anusha

anusha (BOB member since 2004-08-03)

7

When I have the opportunity, I’ll put it together … may be a day or so.

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

8

Thanks Dwayne,
Looking forward to the solution,

Regards
anusha

anusha (BOB member since 2004-08-03)