Novice User Saving as csv

[Robhkent]

I am able to save a report as a csv file in Impromptu 7.1. What I need to do is have an auto created report also saved as .csv file, and I need to have this happen automatically nightly. I believe that I can use the script editor to do this, but I cannot get a script configured to do this.

Thanks to anyone who can help.

Rob

 

[drlex]

Rob,
Something like this:

Sub Main()
   Dim objImpApp As object
   Dim objImpRep As Object 

   Set objImpApp = CreateObject("CognosImpromptu.Application")
   objImpApp.Visible 1
   objImpApp.OpenCatalog "C:\catalogues\mycat.cat","Creator",,,,1

   Set objImpRep = objImpApp.OpenReport ("k:\Reports\Costex.imr")
    Set objImpRep = objImpApp.ActiveDocument
    [b]objImpRep.ExportAscii "C:\Excel Reference Files\Costex.csv",0 [/b]
   objImpRep.CloseReport

   objImpApp.Quit
   Set objImpApp = Nothing
   Set objImpRep = Nothing   
End Sub

soi la, soi carré

 

[griffindm]

Check out the .ExportASCII method as documented in the script editor. You have control over the delimiter character, include or exclude headers, quotes on text strings, and the type of EOL indicator. Use this in a macro and then schedule the macro and you should be there.

Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[Robhkent]

DRLEX:

Thanks for the script, being a novice a have a couple more questions,

1. After creator you have a few coms and a 1. I understand that this is probably a pause for action on the computer. I have to log in to my database, how do i set this.

2. I get an error "object is set to nothing" I believe this pertains to the two lines above End sub in the script, what do I set these tow to avoid this error.

Thanks
Rob

 

[drlex]

Rob,

A1. The commas are to skip optional parameters for the command. Open Macro Help and look at the entry for "OpenCatalog Method".

Syntax is
Application.OpenCatalog CatalogName ,[UserClassID] ,[UserClassPassword] ,[DatabaseID] ,[DatabasePassword] ,[ConnectToDatabase] ,[DistributedCopyFilePath]

So in my example, the CatalogName is the path and filename of my catalogue, the UserClassID is "Creator", then I skip the next 3 fields, which you probably need to complete and then the 1 corresponds to the ConnectToDatabase parameter.

(I skip the passwords and IDs, as I use OS Authentication with Access Manager, which sets the Cognos Windows logon when the server is booted.)

A2. The fact that you get an 'Object is set to nothing' error is probably because you are failing to open the catalogue. Step through the macro using the F8 key and you can see where you have a problem

lex

soi la, soi carré

 

[Robhkent]

drlex:

Thanks for the previousposts, have been a help here is the line for login that I still am having trouble with.

objImpApp.OpenCatalog "n:\Prime\Cognos\catalogs\daily.cat","Creator",,"sa",

This returns an error Error number -9:

DMS-E-GENERAL, A general exception has occurred during operation 'attach database'.
[Microsoft][ODBC SQL Server Driver]Dialog failed
I know I do not have the connect to database parameter in at all, but I have extende the above line.. "sa" with ,1 or ,,1 and it still failes with the same error.

Tahnks again

Rob

 

[drlex]

Rob,
Assuming "sa" is the appropriate user for your SQL server DB, try following it with your password in double quotes ("" if it's blank), then a comma and a 1.
Otherwise, I've found that if you don't require a log-on to use a catalog or access manager is linked to your OS signon, you can comment out the OpenCatalog line.

HTH,
lex

soi la, soi carré