New to Script Editor - Variable Dates

[AndrewKent]

Hi there everyone,

I'm new to the forum and indeed new to using Cognos! I was looking for some help. I have written a very simple bit of code (using VB6) to open Impromptu, login and extract a report to .csv. Now this seems to work however I'm not sure that I have declaired my variable dates properly as the extract report comes back blank (ie the only thing in the csv file is the column headers, no data).

Sub Extract()
Dim objImpApp As Object
Dim objImpRep As Object
Dim StartDate As String
Dim EndDate As String
StartDate = txtStartDate.Text
EndDate = txtEndDate.Text
Set objImpApp = CreateObject("CognosImpromptu.Application")
objImpApp.OpenCatalog "F:\Cognos\db_local.cat", "", "", "kentaa", "novem999"
Set objImpRep = objImpApp.OpenReport("G:\MI\Cognos Reports\New Cognos Reports\E-Leads.imr", "StartDate|EndDate")
objImpRep.RetrieveAll
objImpRep.ExportASCII ("F:\Cognos MI Suite\Ya Bam.csv")
objImpRep.CloseReport
objImpApp.Quit
Set objImpRep = Nothing
Set objImpApp = Nothing
End Sub

Can anyone help please?

Cheers,

Andy

 

[drlex]

Andy,
Your prompt will not work in that format. Substitute startdate + "|" + enddate for "startdate|enddate", as Cognos is interpreting the prompt as one long string, not two date strings.
HTH,
lex

soi la, soi carré

 

[AndrewKent]

Hi Lex,

Thanks for your help, I have updated my macro to...

Sub Extract()
Dim objImpApp As Object
Dim objImpRep As Object
Dim StartDate As String
Dim EndDate As String
StartDate = txtStartDate.Text
EndDate = txtEndDate.Text
Set objImpApp = CreateObject("CognosImpromptu.Application")
objImpApp.OpenCatalog "F:\Cognos\db_local.cat", "", "", "kentaa", "novem999"
Set objImpRep = objImpApp.OpenReport("F:\Cognos MI Suite\E-Leads.imr", StartDate + "|" + EndDate)
objImpRep.RetrieveAll
objImpRep.ExportASCII ("F:\Cognos MI Suite\E-Leads.csv")
objImpRep.CloseReport
objImpApp.Quit
Set objImpRep = Nothing
Set objImpApp = Nothing
End Sub

...however it is still coming back having not retrieved anything as part of the filter. Since posting this thread I have worked out how to declare filters (if they are text based eg loooking for records with specific words) so it seems that looking for dates between two ranges eludes me. The formatting in the database is dd/mm/yyyy hh:mm could this have something to do with it?

Cheers,

Andy

 

[drlex]

With the use of Prompt Manager (under "Available components" on the LHS) in the Filter tab, you should be able to set up a date range filter. The date format will be dependant on your system set-up. All should need to do is make sure that you format your 'StartDate' and 'EndDate' variables to match. So if your Prompt Manager shows dates in yyyy-mm-dd format, make sure that your variables are the same. Check that Impromptu is seeing your DB data as date format by either formatting the field or check the type under the 'Profile' tab of the report ('Query columns' radio button).

soi la, soi carré

 

[AndrewKent]

Hi Lex,

Okay I've checked and the although when you run a report in Cognos the date can be entered dd/mm/yy, the prompt manager has it stored as yyyy-mm-dd I've tested it and it works perfectly! So a BIG thank you for helping me here!

The only other question I have is around export format.if I wanted to export to PDF or XLS format, how would this...

objImpRep.ExportASCII ("F:\Cognos MI Suite\E-Leads.csv")

...change? Many many thanks again for your help, you've saved me a lot of time and effort.

Andy

 

[drlex]

Andy,
Glad you're getting somewhere; you just have to bear in mind that CognosScript is not exactly the same as VBScript; the help file in CognosScript Editor is good and has examples for many of the functions.

Your save as Excel will depend on the version of Impromptu that you are using.
IIRC, in versions 7.1 and above,
objImpRep.ExportASCII ("F:\Cognos MI Suite\E-Leads.csv")
can be changed to
objImpRep.ExportExcelWithFormat "F:\Cognos MI Suite\E-Leads.xls"

Note that in current versions (7.3+, I think), you can set the Excel export version under Report>Excel>Version as either Excel 2000 or Excel 2002 & higher. Options about what to save are also available.

Lex

soi la, soi carré

 

[AndrewKent]

Hi again Lex,

The line...

objImpRep.ExportExcelWithFormat "F:\Cognos MI Suite\E-Leads.xls"

...works a treat however I still can't figure out how to extract into ".pdf" format? I've had a look through ther manuals I have but can't seem to locate anything, sorry to be a pain!

Andy

 

[drlex]

Andy,
You'll be wanting PublishPDF; set an Object and use that. On the basis that you're still using objImpRep as your report object and you've put "Dim objPDFPub as Object" in prior:

Set objPDFPub = objImpRep.PublishPDF
objPDFPub.Publish "F:\Cognos MI Suite\Evil_Plan.pdf"

HTH,
Lex

soi la, soi carré

 

[AndrewKent]

Hey Lex,

Seems to work fine HOWEVER (and I'm sure you're sick of hearing that!) if I run this report manually and export to PDF, all columns are shown on the same page in the PDF document, however running the same report through the macro and some of the columns are on separate pages!

Cheers,

Andy

 

[drlex]

Andy,
Not had that before. Given that the 'Publish' command lacks options, I don't know of a way to force it, as the 'Fit to page' check-box in 'Page Set up' doesn't appear to affect PDF creation. If you have your report in 'Page Layout', are all the columns on the same page? If not, you may just have to change the margins and/or paper size to get it all on one sheet. Since PDF readers tend to scale to the page width, using a scaled page size in Impromptu ought to fix it for you (until such time users want to print the report).
Lex
(Answering Qs until the Eastern Seaboard joins in)

soi la, soi carré