Columns object not Drillingdown after 5 levels in PowerPlay

[ggiridhar]

am writing a macro in PowerPlay to extract the hirarchy structure of a cube. The problem which am facing is that the column obj is not drilling down after 4 levels even though it can be drilled-down.
plz help me
here is the code...

Declare Function Tree_Extractor(PPCol as Object)
Sub Main()
On Error GoTo Debu
Dim objPPRep as Object
Dim objCatList as Object
Dim objPPDim as Object
Dim objPPCol as Object
Dim s1,s2 as String
Set objPPRep = CreateObject("CognosPowerPlay.Report")
objPPRep.New "xxx.mdc", -1
Open "tree.xml" for Output as #1
Write #1, "<root>"
For x = 1 to objPPRep.DimensionLine.Count - 1
Set objPPDim = objPPRep.DimensionLine.Item(x)
s1 = objPPDim.Name
MsgBox s1
Write #1, "<" & s1 & ">"
For y = 1 to objPPDim.Count
Set objCatList = objPPRep.CategoryList
s2 = objPPDim.Children.Item.Name
Write #1, "<" & s2 & ">"
objCatList.Add 1,s1,s2
objPPRep.Columns.Add objCatList
Set objPPCol = objPPRep.Columns
Call Tree_Extractor(objPPCol)
objPPDim.ChangeToTop
objCatList.Remove
Set objCatList = Nothing
one:
Write #1, "</ " & s2 & ">"
Next y
Write #1, "</ " & s1 & ">"
Next x
MsgBox "Finished"
Write #1,"</ root>"

Exit Sub
Debu:
Resume one
End Sub

Function Tree_Extractor(PPCol as object)
On Error GoTo Debugger
Dim i,f as Integer
Dim CatList as Object
f = 0
For i = 1 to PPCol.Count
If PPCol.Item(i).Children <> 0 Then
f = 1
Write #1,"<" & PPCol.Item(i).Name & ">"
Call Tree_Extractor(PPCol.Item(i).Children)
End If
done:
If f = 1 Then
f = 0
Write #1,"</ " & PPCol.Item(i).Name & ">"
Else
Write #1, PPCol.Item(i).Name
End If
Next i
Exit Function
Debugger:
Resume done
End Function

 

[drlex]

I tried your code and note that there are some errors, plus it never gets to iterate. That would appear to be due to the fact that since you never change to the child of each level in the dimension, you never track through the lower levels of the hierarchy.

One thing to consider is whether you're trying to records the levels of the cube or the children (the actual members of each level). If it's the former, you want to use the Dimension.Level property; the latter the Dimension.Children property.

I have some incomplete code that attempts to record both the Children and Level names in a cube; feel free to experiment with it. The presence of calculated & special categories, together with alternate drill-downs make it rather ugly, and probably a little erroneous. However, it might be useful.

Declare Sub getlevels(objPPDim, level)
   Dim x
   Dim Levelloop(9) as integer
   Dim level
   Dim objPPRep as Object
   Dim objPPDim as Object
   Dim objPPMeasure as Object
   Dim strItem as String
   Dim AuditFile as string
   Dim strCube as string
   
Sub Main
   strCube = "E:\cubes\Acme sales.mdc"
   AuditFile = "C:\CubeAudit.csv"
   Open Auditfile For APPEND As #1
   Write #1, "Analysing " + strCube
   Set objPPRep = CreateObject("CognosPowerPlay.Report")
   objPPRep.New "E:\cubes\" + strCube, -1
   'objPPRep.Visible = 1
      For x = 1 to objPPRep.DimensionLine.Count-1 'Last dimension is the MEASURES folder
         Set objPPDim = objPPRep.DimensionLine.Item(x)
         Write #1, objPPDim.Name & " Dimension"
         level = 0
            call getlevels(objPPDim, level)   
         Write #1, Chr$(10)
      Next x  
      Set objPPDim = objPPRep.DimensionLine.Item(objPPRep.DimensionLine.Count)
      objPPDim.ChangeToParent
          For x = 1 to objPPDim.Children.Count
             Set objPPMeasure = objPPDim.Children.Item(x)
             Write #1, "Measure " + Cstr(x) + " = " + objPPMeasure.Name
             Set objLevel = Nothing
          Next x
   objPPRep.Close    
   Set objPPRep = Nothing
   Set objPPDim = Nothing
End Sub
'
Sub getlevels(objPPDim, level)
   Dim z
   Dim objLevel as Object
      Set objLevel = objPPDim
         If objLevel.Children.Count = 0 then goto skip 'No children, so leave subroutine
         level = level + 1
         levelloop(level) = objLevel.Children.Count
            For z = 1 to levelloop(level)
               strItem = objLevel.Children.Item(z).Name
               If Level = 1 then strtext = "Level 1 "  
               If Level <> 1 then strtext = "Level " + Cstr(level) + " Name: " + objLevel.Level.Name 
               Write #1, strtext + "; item no." + Cstr(z) + " of " + Cstr(levelloop(level)) + " = " + strItem
               objLevel.Change(strItem)
               test = 0
               If objLevel.IsAlternate = True then Write #1, "Alternate/Calculated/Special - skipping"
               If objLevel.IsAlternate = True then test = 1
               If objLevel.IsCalculatedCategory = True and z = levelloop(level) then test = 1   'set flag for last calculated item in level
               If objLevel.IsCalculatedCategory = False and objLevel.IsAlternate = False Then call getlevels(objLevel, level)  'iterate   
               If objLevel.IsCalculatedCategory = True then objLevel.ChangeToParent 'Up a level for calculated   
               If test = 1 then objLevel.ChangeToParent     'Last Calculated/Alternate/Special - going to change up again"        
            Next z   
         level = level - 1        
skip:    If objLevel.HasParent <> 0 then objLevel.ChangeToParent 'Up a level if required
End Sub

soi là, soi carré

 

[ggiridhar]

drlex tanks for ur code its the one which i wanted.Its working fine
u told that i dint change to the child of each level in the dimension thats the reason it dint drill down.
so can u tell me how to do that

 

[drlex]

I have just posted slightly amended code in your other thread.

Within the iteration subroutine, the command objLevel.Change(strItem) takes the dimension objLevel down to the category strItem; the command objLevel.ChangeToParent takes it back up one level (to the parent).

(Please look at the 'cleaner' code in the other thread.)

soi là, soi carré

 

[ggiridhar]

i have seen that...but in my previous code am using Colums object and not the Dimension Object...so how can i change in that

 

[drlex]

I don't think the Change functionality applies to Column/Row/Layers; from the Help file on the Children method:

Use this method to obtain the children for the Column, Dimension, Layer, and Row objects. The Children method accesses the different children for the current category. For example, the 1998 Q1 category has three children; 1998/January, 1998/February, and 1998/March.

For Dimensions, use the Change method to point to a specific category, the ChangeToTop method to point to the top level, and the ChangeToParent method to point one level higher in the hierarchy.

I read that to mean that the Change methods apply to Dimensions only; the ChangeToTop/Parent pages mention only applicability to Dimensions.



soi là, soi carré

 

[ggiridhar]

thats my question drlex...am using Column.Item.Children to access the children of the column obj in my code.
but still am not getting the column's children obj if the level where it lies is above 4

 

[drlex]

I'm surprised you're even getting that - running your code produced just a list of the children of each of the first levels in every dimension; your "PPCol.Item(i).Children" command in the subroutine results in an error, which supports the interpretation of the Cognos Help screen as only one level of children on columns. In contrast, using Dimension allows one to drill all the way through the levels.

soi là, soi carré