Problem with prompts and datasets

[Clairvoyant1332]

I have a report that selects a list of customer transactions entered into the system on a certain date such that the customer has no other transactions entered within a year of that date. I use a dataset to select customers that have transactions within a year of the date, and my main report selects all customers that are not included in the dataset.

Here's the problem: I have to prompt the user for the target date. I first placed prompts in each report, but that results in the user being prompted twice for the same date. I then defined the prompt in the catalog. Now I only get prompted once, but after publishing the report to the web and attempting to run it from there, I get the following error after entering in the prompt value:

The report request failed.
Unable to execute report \\sbkntsfap01\cognos\server\QA\iwr\Initial Load of QA ODS Reports\apps\EasOdsIr Advertisers Advertising in Category Rate Card AA01030 (prompted) V1.imr. Reason: Error: 0, Generic; Process ID: 18600; Thread: 17588; Host: SBKERQAAS2; Text: This report need prompt information; BackTrace Info: 1. File Name: Y:\src\impromptu\ImpromptuServer\app.exe\ole.lib\APPAUTO.CPP; Line#:8628; Desc.:This report need prompt information; 2. File Name: Y:\src\impromptu\ImpromptuServer\app.exe\ole.lib\APPAUTO.CPP; Line#:9155; Desc.:CAppAuto::ExecuteRunReport; .

The report runs fine with the two report-based prompts, although they come up in a different order on the web than they do in Impromptu.

Any idea why this might be happening? Is there another way to go about doing this? I'm using Impromptu 7.1.

Thanks,
Dennis

 

[nagrajm]

Another method, without having to use a dataset, would be to pull a report of all transactions for the period (TARGET DATE minus ONE YEAR) and then use the summary filter to weed out customers who had transactions before the target date.

In order to accomplish that, group the report on CUSTOMER_ID and add the following condition in the summary filter;

minimum(SALE_DATE) for CUSTOMER_ID = TRAGET_DATE

That should do the trick but the performance will not be as good as the dataset method because the latter involves a sub-query that is executed at the database level.

Hope that helps.