Is there a better way than using Hot Files? 3

[sharkatek]

Hello all,

I am using Cognos Impromptu v 7.1.339.0
Please answer either or both questions:

1. Is it better to use a report as a query and pull information from there rather than creating a hot file from the report and then using it?

2. How can you save a dataset and use fields from it in another report?

Thank-you,
Sharkatek

 

[DoubleD]

1. This depends on your requirements:
A hot file is static (just a glorified flat file) so if you need to always capture the most current data, you'll have to constantly refresh your hotfile.
If it takes a long time to run the hotfile, but by running it once you have all the data you need for the day because your source only updates daily, then it may be worthwhile to create a hotfile.
If the hotfile runs quickly, you may be just as well to write a report and filter against that dataset.

2. This depends on whether you actually need to bring the fields into your report, or if you just need them for filtering purposes.
I would try to build the whole thing as a single report first. If performance became an issue, and I needed to bring the additional fields into my report, I would probably create a hotfile.

I am what I am based on the decisions I have made.

DoubleD

 

[sharkatek]

DoubleD - thanks for the information although it is a disaster for me to learn this.

My biggest problem is that I am working with two catalogs and some information is needed from both. If I write a report in catalog 2 to pull out the information I need, then I want to do a left outer join from catalog 1 so that all the basic units show up from cat1 and the applicable information for those some of those units from cat2, I do not see the REPORT .imr file in the Join screen. I only see reports from cat1 and hot files from cat1 and cat2. What do you suggest?

Thanks!
Sharkatek

 

[DoubleD]

What is the need for separate catalogs? Are they completely disconnected datasources? If so, you have limited options.

If both catalogues are built against the same datasource, I would suggest creating a new catalog to get what you need.

If the two catalogs really are against separate datasources, here are some options:
1. Create a hotfile and join against the hotfile as you suggested above.
2. Have IT combine the two datasources in a single database. Then write a catalog against that database.
3. Join your two sources using Microsoft Access, then build a single catalog against the Access database.


I am what I am based on the decisions I have made.

DoubleD

 

[sharkatek]

DoubleD

Unfortunately, I am only a contract programmer writing ad-hoc reports that need to work when I'm gone. The database is in multiple catalogs and IT at the company cannot combine them. It is a purchased program and it is constructed that way.

aiiiieeeeee!

 

[GJParker]

One way or the other you are going to have to modify one of the catalogs, whether this be adding in hotfiles or linking in new tables.

If the catalogs use the same datasource then I would first look at linking in the required tables direct from the db.

If they use different datasources, then either use hotfiles or use MS Access as a gateway to join the 2 datasources and build a new catalog.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[sharkatek]

DoubleD or Gary (YTH),

Is there a way via vb code to refresh a hot file or a bunch of them. I am thinking of creating a routine that could be run by the users as needed to update the report hotfiles.

I save all my hot files as reports first, then save them as a hot file. I just need an automated way to re-save the reports at hot files so I don't need to open the reports and manually re-save them.

thank-you!
Sharkatek

 

[DoubleD]

At Least Two Options here:
1. You can use Cognos Scheduler in combination with Cognos Script to accomplish this.
2. Write VB code and use Access as your scheduler.

My suggestion would be to use Cognos Scheduler. It comes with Impromptu Admin. Just read up on CognosScript to learn how to automate this. The documentation provides lots of examples. We can help you if you have specific questions. You would also be able to write a CognosScript (macro) that the users could run to refresh the hotfiles.

I am what I am based on the decisions I have made.

DoubleD

 

[drlex]

The command in CognosScript that you will want is

objImpRep.ExportHotFile "k:\hotfiles\myhotfile.ims"

(assuming that you've defined and set objImpRep as an object that is the report you've run).

soi la, soi carré

 

[sharkatek]

thank-you, I will try this out. It may take me a while - I've never used CognosScript. I'll come back when I have something put together.

 

[DoubleD]

sharkatek,
If you're familiar with VB, CognosScript will be a breeze. I've ported code from CognosScript to VB and back many times without any problems. In fact, any time I want to write CognosScript, I write it in VB Editor first, then paste it into ScriptEditor. I find the VB Editor to be a much more user friendly interface.

I am what I am based on the decisions I have made.

DoubleD

 

[sharkatek]

DoubleD and drlex - thank-you both!

Here's what I put together and it works! I did just a sample run to save a hot file on my hard drive. I ran the macro and it worked the first time! I noticed that after the run though, there was a .mcx file with the same name as my .mac file. I clicked on it and it ran the macro. What is this? Is it a compiled macro?

One question - is there any way to make this run without the user being prompted to log in to Cognos? Can I make it log in using userid and password variables? This is just a "nice-to-have". What I have so far will do the job.

Sub Main()

Dim strReportName As String
Dim strHotFileName As String

Dim objImpApp As Object
Dim objImpRep As Object

strReportName = "C:\Documents and Settings\cognos\My Documents\Hot Files\Hot_File_Data_Available.imr"
strHotFileName = "C:\Documents and Settings\cognos\My Documents\Hot Files\Hot_File_Data_Available.ims"

Set objImpApp = CreateObject("CognosImpromptu.Application")
objImpApp.Visible 1
objImpApp.opencatalog "\\Salem-nt\enrich\enrv5620\Catalog\AMLR.CAT"

Set objImpRep = objImpApp.OpenReport(strReportName)
Set objImpRep = objImpApp.activedocument
objImpRep.exporthotfile strHotFileName
objImpRep.closereport

objImpApp.Quit
Set objImpApp = Nothing

End Sub

 

[DoubleD]

An mcx file is the compiled executable Macro. The mac file is the editable version of the macro.

A couple things:
1. Adding the Database Signon and Password to the OpenCatalog line will turn off the requirement to login.
2. Changing the Visible line to 0 will hide the process from the user. You could then just provide them a msgbox when the process is complete.
3. What happens when one user is connected to the catalog and running a report against the hotfile and another user attempts to update the hotfile? You'll probably need to account for this.
4. This line is not necessary:
Set objImpRep = objImpApp.activedocument
5. Add this line:
Set objImpRep = Nothing


I am what I am based on the decisions I have made.

DoubleD

 

[sharkatek]

DoubleD - I'd give you ten stars if I could..

You have given me more help for FREE with various questions than the book I paid $64.00 for (Impromptu Startup! - what a waste of money!)

thank-you!
Sharkatek

 

[DoubleD]

Glad to help. Good luck with everything.

I am what I am based on the decisions I have made.

DoubleD

 

[drlex]

Sharkatek,
DoubleD as usual has been exemplary. I would add that the Help files in CognosScript are useful and check out 'cgspmac.pdf' (Macros and cognos script language) and 'schd.pdf' (Cognos Scheduler) in the documentation folder of your installation for the times that TT is off line or DoubleD is asleep...

soi la, soi carré

 

[sharkatek]

thanks to you both - I have just one more question.

I am able to get the macro to sign in but it gives me an error on the password and asks if I want to enter it. I do so, but I need it to work correctly. Here is my code - what is the correct syntax?

objImpApp.opencatalog "\\Catalog\Cat1.CAT", "Creator", "UserID", "Password

 

[GJParker]

Try

objImpApp.opencatalog "\\Catalog\Cat1.CAT", "Creator","", "UserID", "Password"

Gary


Gary Parker
MIS Data Analyst
Manchester, England

 

[DoubleD]

Also read Imp_mac.pdf. This specifically covers automating Impromptu tasks. To further explain the syntax Gary provided:

objImpApp.OpenCatalog "CatalogHere", "CatalogUserId","CatalogPassword", "DatabaseUserID", "DatabasePassword"

I am what I am based on the decisions I have made.

DoubleD

 

[sharkatek]

ok - I hope this is the last question - I'll start a new thread if I need help on automating this monster..

I am getting an error within the "for/next" loop - it happens in the first instance of "i"

Error = Cannot convert between these object types
then I tried to run it again to see what the specific error number was and got a different error:
error code: -1003 "Error exception not handled by program"

Sub Main()
Dim i As Integer
Dim strReportNameCat1(8) As String
Dim strHotFileNameCat1(8) As String

Dim objImpApp As Object
Dim objImpRep As Object

strReportNameCat1(1) = "\\Reports\Development\HFUnitSort.imr"
strReportNameCat1(2) = "\\Reports\Development\Hot File Unit Inventory Equipment Category.imr"
strReportNameCat1(3) = "\\Reports\Development\HF Work At Non Domicile Facility Assignment.imr"
strReportNameCat1(4) = "\\Reports\Development\HFItemAverageCost.imr"
strReportNameCat1(5) = "\\Reports\Field\HF EMMS Field Stock Status Report Vendor Item Price.imr"
strReportNameCat1(6) = "\\Reports\Field\HF EMMS Alias Unit Customer Link.imr"
strReportNameCat1(7) = "\\Reports\Field\HF EMMS Unit Pool.imr"
strReportNameCat1(8) = "\\Reports\Field\HF EMMS Field Unit Facility.imr"

strHotFileNameCat1(1) = "\\Reports\Development\HFUnitSort.ims"
strHotFileNameCat1(2) = "\\Reports\Development\Hot File Unit Inventory Equipment Category.ims"
strHotFileNameCat1(3) = "\\SReports\Development\HF Work At Non Domicile Facility Assignment.ims"
strHotFileNameCat1(4) = "\\Reports\Development\HFItemAverageCost.ims"
strHotFileNameCat1(5) = "\\Reports\Field\HF EMMS Field Stock Status Report Vendor Item Price.ims"
strHotFileNameCat1(6) = "\\Reports\Field\HF EMMS Alias Unit Customer Link.ims"
strHotFileNameCat1(7) = "\\Reports\Field\HF EMMS Unit Pool.ims"
strHotFileNameCat1(8) = "\\Reports\Field\HF EMMS Field Unit Facility.ims"


Set objImpApp = CreateObject("CognosImpromptu.Application")
objImpApp.Visible 0
objImpApp.opencatalog "\\Salem-nt\enrich\enrv5620\Catalog\Cat1.CAT", "Creator", "", "Cognos", "Cognos"

For i = 1 To 8
Set objImpRep = objImpApp.OpenReport(strReportNameCat1(i))
Set objImpRep = objImpApp.activedocument
objImpRep.exporthotfile strHotFileNameCat1(i)
objImpRep.closereport
Next i

objImpApp.Quit
Set objImpApp = Nothing
MsgBox "All Hot Files Updated"
End Sub