macro to move a column in powerplay 1

[collierd2]

Hello

I've looked everywhere for this with no success and I really can't see it being that difficult

I am building a macro which does various things

If I have a report that has 6 columns what code would take column 1 and move it to the end column

When you perform calculations on columns within a macro it doesn't put the column in the right place so I am looking to control where it goes

Thanks

Damian.

 

[drlex]

Damian,
I beleive you can only do this in Reporter mode.

This short macro might assist you; it opens a report and moves the first column to the end before exiting. The Activate command is required to move the focus of the paste operation.

Note that the effect of removing a column has an immediate effect on the total number of columns - so I need to deduct 1. Alternately, the count could be performed after column removal so as to remove the need to reduce the value.

Sub Main()
   Dim objPPRep as Object
   Set objPPRep = CreateObject("CognosPowerPlay.Report")
   objPPRep.Open "C:\Report.ppr"
   objPPRep.ExplorerMode = False
   objPPRep.Visible = True
   TotalColumns = objPPRep.Columns.Count
   objPPRep.Columns.Item (1).Select
   objPPRep.Cut   
   objPPRep.Columns.Item (TotalColumns-1).Activate   
   objPPRep.Paste
   objPPRep.Close
   Set objPPRep = Nothing
End Sub

soi la, soi carré

 

[drlex]

Damian,
I believe you can only do this in Reporter mode.

This short macro might assist you; it opens a report and moves the first column to the end before exiting. The Activate command is required to move the focus of the paste operation.

Note that the effect of removing a column has an immediate effect on the total number of columns - so I need to deduct 1. Alternately, the count could be performed after column removal so as to remove the need to reduce the value.

Sub Main()
   Dim objPPRep as Object
   Set objPPRep = CreateObject("CognosPowerPlay.Report")
   objPPRep.Open "C:\Report.ppr"
   objPPRep.ExplorerMode = False
   objPPRep.Visible = True
   TotalColumns = objPPRep.Columns.Count
   objPPRep.Columns.Item (1).Select
   objPPRep.Cut   
   objPPRep.Columns.Item (TotalColumns-1).Activate   
   objPPRep.Paste
   objPPRep.Close
   Set objPPRep = Nothing
End Sub

soi la, soi carré

 

[collierd2]

That very nearly works

As I am putting this in the middle of some other code the preceeding code seems to select column 2.

When I execute objPPRep.Columns.Item (1).Select it selects column 2 and column 1 creating an index out of bounds errors when it tries to cut and paste

How do I select column 1 only

This is my code:

Sub Main()
   Dim objPPRep as Object
   Dim objBranchList as Object
   Dim objMeasuresList as Object
   Dim objNewCol as Object
   Set objPPRep = GetObject(, "CognosPowerPlay.Report")
   
   Set objBranchList = objPPRep.CategoryList
   Set objMeasuresList = objPPRep.CategoryList
   objPPRep.Visible = True

   objBranchList.Add 1, "All Reps Customer Branch", "09 M6 Papers"
   objPPRep.Rows.Add objBranchList

'   objBranchList = ""
   objMeasuresList.Add 1, "MEASURES", "SV"
   objMeasuresList.Add 1, "MEASURES", "GM"
   objMeasuresList.Add 1, "MEASURES", "GM%"  
   objMeasuresList.Add 1, "MEASURES", "BSV"
   objMeasuresList.Add 1, "MEASURES", "BGM"
   objMeasuresList.Add 1, "MEASURES", "BGM %"
   objPPRep.Columns.Add objMeasuresList

   Set objNewCol = objPPRep.Columns.Item(1).Subtraction _
      (objPPRep.Columns.Item(4))
   'Set objNewCol = objPPRep.Columns.Item(1)
   objNewCol.Name = "Variance SV"
   
   TotalColumns = objPPRep.Columns.Count
   objPPRep.Columns.Item (1).Select
   objPPRep.Cut   
   objPPRep.Columns.Item (TotalColumns-1).Activate   
   objPPRep.Paste
   
   Set objCatList = Nothing
   Set objPPRep = Nothing
End Sub

Thanks Damian.

 

[drlex]

Damian,
For reasons best known to Cognos programmers, it appears that the Select function is acting in a cumulative fashion until action or deselection. Perhaps it is hinted at by the use of the plural 'columns'...

Anyhow, put the command "ObjPPRep.Columns.Unselect" prior to the line "objPPRep.Columns.Item (1).Select" and you should get the required result.

lex

soi la, soi carré

 

[collierd2]

Fantastic, thanks