Export Report via CognosScript 1

[bebbo1986]

Hallo,
I have managed to control the dimensionline of my report with a macro.
Now I want to export the resulting report in one Excel-worksheet. Then I want to change the dimensionline again and export the report with the new values in the same workbook in a further worksheet.
I've tried the command
objPPRep.SaveAS "test", 4
but it won't work. So my trials fail just at the beginning.

I'm thankful for your help.

 

[bebbo1986]

Ok this command works. You just have to write the total path, so
objPPRep.SaveAS "C:\Documents and Settings\User\Desktop\test", 4

But so I need a new workbook for every report and I would like to have them in one workbook.

 

[drlex]

Chap,
Almost there.
Your "test" needs to specify path and file extension.
Try "C:\test.xls" instead.


soi la, soi carré

 

[drlex]

Too late.
Best way to have one workbook for multiple reports is to automate Excel to combine them as sep. tabs within a new workbook.


soi la, soi carré

 

[bebbo1986]

Thanks you for your answer.
Now I probably will programm the macro that it opens the single workbooks and copy them in one.
I just thought there must be an easier way.

Thanks again.

 

[drlex]

AFAIK, the only way that PP will save separate tabs in one worksheet is when a report has layers.

soi la, soi carré

 

[bebbo1986]

So instead of changing my dimensionline and then export the report I could add a new layer for every dealer (thats what I change) and then export the whole report.
But I don't know the command for creating a new layer with a specific parameter.

And one other questions besides. I made a Directory named "data" with MkDir "...\data" where I store the reports.
Whenn I know start the macro again this command makes trouble because the directory is already existing. How is the command to check, if a directory exists. (DirExists doesn't work)

 

[drlex]

Have a look at the Layer object in CognosScript Help or the documentation. Remember that PowerPlay operates in two modes - Explorer and Reporter. To have only certain layers, you'll need to be in Reporter mode.
Try Dir ("C:\reports\dealer_reports") to check for directory existence. ("Dir" in the help file)


soi la, soi carré

 

[bebbo1986]

I'm sorry I can't find anything helpful in the documentation. There's described how to read from specific layers, but not how to create one.

Besides I have again one further short question. Is it possible to create a layer automatically that contains the average values from all layers in the report.

I'm sorry that I have so many questions. But it's really important and I trying hard.

 

[bebbo1986]

The solutions seems to be connected with
objPPRep.Layers.Add objCubeCategories

But right now I can't manage to select the right CubCategories.
But the problem with the average values stays.

 

[drlex]

Might be easier to start with all layers, switch to reporter, remove the unwanted layers and then apply an average.
There's an example at the bottom of the Layers Object entry in my help file that is instructive. I've amended it to:

Sub Main()
Dim objPPRep AS Object
Set objPPRep = GetObject("C:\Sample.ppr" )
objPPRep.Visible = 1
objPPRep.ExplorerMode = 0
objPPRep.Layers.Subset(1,2).Remove
objPPRep.Layers.Subset(1,2).Subtraction
objPPRep.Layers.Average
objPPRep.Save
Set objPPRep = Nothing
End Sub

to show the ease of adding an Average layer against a report on the root of C:\.

soi la, soi carré

 

[bebbo1986]

Thanks a lot.

 

[bebbo1986]

The solution with the layers is working very good so far. But when it comes to the saving of the report as an Excel-Worksheet with
objPPRep.SaveAS CurDir & "\data\dealers_ref.xls", 4, True
then all Layers are written in one worksheet one below the other.
Is there a solution to write the layers in separated worksheets without using Excel-functions. So it could be managed directly with the export.

 

[drlex]

What version of PowerPlay and Excel are you using? When using 7.4 with Excel 2002, a layered report is saved as each tab containing a layer. Whilst the excel file shows as .xls in Explorer, it is shown as a .mht when chosing save as from within Excel. See thread401-1448642 for a way of switching defaults.

Happy Friday.

BTW, thanks for the star.

soi la, soi carré

 

[bebbo1986]

I'm using Excel 2002 and PowerPlay 7.3.
Thanks for the help. I've got some VBA macro from a colleague, who had similar problems.
Perhaps with that I find a even better way. We'll see.