ImpAdmin.exe error when using Dataset

[amykrage]

I need to select distinct records with a recordset that contains a text field. In order to do so, I am creating one catalog report with all columns needed except the text field. With a second catalog report, I am selecting the same columns including the text field I need where the company id also exists in the first report I created. I am using a filter on the second report of 'where Company Id in (Dataset)' where the (dataset) is the first report. When I initially run this, it runs just fine and I get the results I need. When I save it and come back to run it after closing out of it, I get the following error:
ImpAdmin.exe has generated errors and will be closed by Windows. You will need to restart the program. An error log is being generated.

Now, I am unable to access the second report without getting this error and having Impromptu shut down. Any input as to the reason for this...or a solution to not using the dataset? Thanks in advance.

-Amy

 

[Prasad678]

Amy,
I am sure that u r using Cognos 7.1 coz, this is a bug in that release. contact cognos & they'll provide u a hotfix for this.

Cheers....


Prasad
RPrasad1@Chn.Cognizant.com

 

[saikat123]

Hi

You can try this

1. Create a blank report using the first dataset (select only the id which you will use in where condition)

2. Now go to the insert --> sub report --> list frame

3. Choose the fields needed (with the id which is needed for where condition) from 2nd recordset

4. Go to the filter and select Report Coulmns.

5. You can find there main query, clicking on it you can find the id which you have selected from 1st dataset

6. Now join that main query id with the id from the 2nd recordset. i.e mainquery.id = id of 2nd dataset

If it not works you have to convert the text field to varchar during selection of dataset

 

[amykrage]

Thanks for the input - I have submitted an incident with Onyx (since we purchased Cognos through them). As far as the suggestion from saikat123, it appears like your suggestion should work. My first recordset has 86 id's which is correct, but when I create the sub-report and link the id's the way you suggested, I only get information for the first record in the first recordset rather than all 86. Any ideas? Thanks.

 

[saikat123]

Hi

I think both the recordsets are using the data from the same table. Only in this case the last record will be shown in the report otherwise it should show all the records that are matching by company id in the first and second recordset

 

[amykrage]

OK - I figured out how to scroll through the data with the suggestion saikat123 gave me. Is there a way to see all the data at once versus record-by-record?

 

[saikat123]

Could you tell me what is the query you have used for the first report.

For that open the first report.

Go to Report --> Query --> Select the Query --> Profile --> Sql --> Edit. If you can copy the sql and send me then I probably I can suggest something

Regards
Saikat

 

[amykrage]

Here is the sql for the main query - a listing of company id's that meet specific criteria. Let me know if you need the sql for the sub-report (the detail information that I need for all id's in this main query). Thanks.



Snapshot was created using the following SQL:

select distinct T1."iOwnerId" "c1"
from "vCustomerproduct" T7, "vCustomerproduct" T1 LEFT OUTER JOIN "vReferenceParameters" T4 on T1."iSiteId" = T4."iSiteId" and T1."iStatusId" = T4."iParameterId", (("vCompany" T8 LEFT OUTER JOIN "vReferenceParameters" T2 on T8."iSiteId" = T2."iSiteId" and T8."iCompanyTypeCode" = T2."iParameterId&quot LEFT OUTER JOIN "vReferenceParameters" T3 on T8."iSiteId" = T3."iSiteId" and T8."iCompanySubTypeCode" = T3."iParameterId&quot LEFT OUTER JOIN "OnyxRpt"."dbo"."vSDtcComments" T6 on T8."iCompanyId" = T6."iOwnerId" and T8."iSiteId" = T6."iSiteId", "vCustomerProductDetail" T9 LEFT OUTER JOIN "vReferenceParameters" T5 on T9."iSiteId" = T5."iSiteId" and T9."vchUser3" = T5."vchParameterId"
where T7."iSiteId" = T1."iSiteId" and T7."iProductId" = T1."iProductId" and T7."iSiteId" = T8."iSiteId" and T7."iOwnerId" = T8."iCompanyId" and T1."iSiteId" = T9."iSiteId" and T1."iProductId" = T9."iProductId" and T2."vchParameterDesc" = 'Customer' and T3."vchParameterDesc" = 'Regular' and T4."vchParameterDesc" in ('Current', 'Grace Period (Expiring)') and T5."vchParameterDesc" in ('Purchase', 'Trial', 'Non-Production ') and T6."vchCommentDesc" = 'Ship Memo'
order by 1 asc

 

[saikat123]

Ya that will be helpful

Thanks
Saikat

 

[amykrage]

Here is the sql for the sub-report. The id from the main report appears to be hard-coded in the sub-report every time I scroll through the id's in the main report - 985 changes to the next id when I scroll. Not sure this is helpful.

select T1."iCompanyId" "c1"
from "vCompany" T1
where 985 = T1."iCompanyId"

 

[saikat123]

Whether the ownerid in first recordset is the company id of the 2nd?

 

[amykrage]

Yes - that's correct.

 

[saikat123]

Do the following. This is not a good solution I believe but solve your purpose for the time being

1. Choose new report
2. Choose Simple List
3. Select Profile from top menu
4. Click the radio 'Sql'
5. Click on Edit and copy the sql written below and paste there
6. Verify it and OK

select iCompanyId
from vCompany T1
where iCompanyId in
(
select distinct T1.iOwnerId c1
from vCustomerproduct T7, vCustomerproduct T1 LEFT OUTER JOIN vReferenceParameters T4
on T1.iSiteId = T4.iSiteId and T1.iStatusId = T4.iParameterId,
((vCompany T8 LEFT OUTER JOIN vReferenceParameters T2 on T8.iSiteId = T2.iSiteId
and T8.iCompanyTypeCode = T2.iParameterId) LEFT OUTER JOIN vReferenceParameters T3
on T8.iSiteId = T3.iSiteId and T8.iCompanySubTypeCode = T3.iParameterId) LEFT OUTER JOIN
OnyxRpt.dbo.vSDtcComments T6
on T8.iCompanyId = T6.iOwnerId and T8.iSiteId = T6.iSiteId, vCustomerProductDetail T9
LEFT OUTER JOIN vReferenceParameters T5 on T9.iSiteId = T5.iSiteId and T9.vchUser3 = T5.vchParameterId
where T7.iSiteId = T1.iSiteId and T7.iProductId = T1.iProductId
and T7.iSiteId = T8.iSiteId and T7.iOwnerId = T8.iCompanyId
and T1.iSiteId = T9.iSiteId and T1.iProductId = T9.iProductId
and T2.vchParameterDesc = 'Customer' and T3.vchParameterDesc = 'Regular'
and T4.vchParameterDesc in ('Current', 'Grace Period (Expiring)')
and T5.vchParameterDesc in ('Purchase', 'Trial', 'Non-Production ') and T6.vchCommentDesc = 'Ship Memo'
)

 

[amykrage]

Thanks for your help - I really appreciate it. I would really like to avoid the sql you suggested. The issue I have is that we have a BUNCH of stored procedure reports - using Access as our main reporting tool to call the stored procedures. We have purchased Impromptu and are in the process of migrating all of our stored procedure reports to Impromptu Catalog reports wherever possible. If I am not able to use the 'in (dataset)' functionality of Impromptu, then I will probably just leave the report as a stored procedure and make it a sp report within Impromptu. Thanks again for your help - you actually showed me how to do sub-reports which I will need in the future.