using cognosscript to add columns together

[steever]

Hi,

Curious as to how i might take a range of periods (eg. April 2003 to June 2003) as input by the user, and, using Cognosscript, add up each column in each period to produce an output summary report. The periods exist as dimensions. Can i temporarily store each period, as a separate report in an array, do my math on the columns in each one, and produce one final summary report? Is this possible within
Cognos's script language.

Any ideas are greatly appreciated!!

Steever

 

[drlex]

Steveer,
If I understand what you want to do, it should be possible.
What product are you using for the reports? Impromptu or Powerplay? (I assume you mean to do this via the Client version, and not the Web)
lex

"Time flies like an arrow, but fruit flies like a banana."

 

[steever]

I'm using the PowerPlay client version, yes.

Some more detail:

I have an existing macro that creates an output report for a single monthly period. The user enters the period, the macro filters down on that dimension, saves the report as PDF, done. I need to modify this to accomodate a range of periods, so i need to use the existing functionality to go to the FIRST period, loop through to the last period in the range, and in between i need to add each column in each period together to produce one final output and save to pdf. I'm just not sure how to manipulate data via cognoscript across these different periods/dimentsions.

thx!

 

[drlex]

Steever,
Without getting into specifics, here's some code I knocked up against a cube. You can amend it to run against your report by changing the ".New" to ".Open {path and report}" and making it tie in with the rest of your macro.

It creates a Reporter report from the cube, removes the columns, adds in the columns that match the contents of the prompt array using ReportQueries and then puts a summary column in that is the total of the selected columns.

There's probably a more elegant way of doing this, but this might get you going.

HTH,
lex

Sub Main()
Dim strCubePath As String
Dim Prompt(10) as String
Dim objPPRep As Object
Dim objFind As Object
Dim objAdvanced As Object
Dim objNewCol as Object
strCubePath = "K:\cubes\Invoiced sales.mdc"
Set objPPRep = CreateObject("CognosPowerPlay.Report&quot
objPPRep.New strCubePath, 1
objPPRep.ExplorerMode = False
objPPRep.Visible = True
objPPRep.Columns.Item(1).Activate
objPPRep.Columns.Active.Select
objPPRep.DeleteSelected
objPPRep.Columns.Item(1).Activate
objPPRep.Columns.Active.Select
objPPRep.DeleteSelected
Prompt(1) = "May 2003"
Prompt(2) = "Aug 2003"
Prompt(3) = "Oct 2003"
For x = 1 to 3
Set objFind = objPPRep.ReportQueries.Add(1)
With objFind
.Name = "Find date"
.Dimension = "All dates"
.SearchShortName = False
.SearchText = Prompt(x)
.Pattern = 2
End With
Set objAdvanced = objPPRep.ReportQueries.Add(3)
With objAdvanced
.Name = "Selected Dates"
.Dimension = "All dates"
.Level "Month"
.Find objFind.Name
.Execute
.AddToReport 1,1,5
End With
Set objAdvanced = Nothing
Set objFind = Nothing
Next x
For x = 1 to 3
objPPRep.Columns.Item(Prompt(x)).Select
next x
Set objNewCol = objPPRep.Columns.Addition
Set objPPRep = Nothing
End Sub

"Time flies like an arrow, but fruit flies like a banana."