Hello all,
Been lurking on these boards for a few years now, and have gleaned a lot of good information from many great Admin/Dev’s out there. I feel it is only right to return the favour.
Note: This was created in part from elements of others work, unfortunately I was working on a timeline and hadn’t created a list of authors to give credit to. If you see some code that you may have created, I thank you for great work, and for sharing that knowledge with others.
I had been looking into ways to access CMS repository data and have this scheduble and distributable in desktop intelligence in the enterprise.
While very basic with no error handling, I feel that other more advanced enterprise coders out there may be able to use this foundation for greater works of art that will allow for scheduable and distributable CMS repository reports.
(For all you expert coders out there, I am fairly new at coding in the BO SDK’s so I may not be able to answer more advanced questions)
environment:
BO XI 3.1 prem on 32 bit Win2K3 servers
SDK installed on client and on server
Desktop Intelligence installed on client
1)I created a report, and on the “specify data access” screen, I used the option “others” and chose “visual basic applications for procedures”
2) I called it getRepoUsers, and click create
3) it will open to the Microsoft Visual Basic page, and will Display the following code:
Public Sub getRepoUsers(dpInterface As DpVBAInterface)
' TODO: Add VBA Data Provider code here.
' For information, refer to DpVBAInterface online help.
End Sub- Go to Tools|References
- Add the following libraries:
-Crystal Enterprise InfoStore Library 12.0
-Crystal Enterprise Framework Library 12.0
click Ok - Copy the following code, and paste it in the window above the ‘end sub’
(Remember to change the (“Adminacct”, “AdminPass”, “CMS server name”, “secEnterprise”) to your system information)
Dim oSessionMgr As CrystalEnterpriseLib.SessionMgr
Set oSessionMgr = New CrystalEnterpriseLib.SessionMgr
Dim oEnterpriseSession As CrystalEnterpriseLib.EnterpriseSession
Set oEnterpriseSession = oSessionMgr.Logon("Adminacct", "AdminPass", "CMS server name", "secEnterprise")
Dim oInfoStore As CrystalInfoStoreLib.InfoStore
Set oInfoStore = oEnterpriseSession.Service("", "InfoStore")
Dim oInfoObjects As CrystalInfoStoreLib.InfoObjects
Set oInfoObjects = oInfoStore.Query("SELECT top 10000 SI_NAME FROM CI_SYSTEMOBJECTS WHERE SI_PROGID='CrystalEnterprise.USER' order by SI_NAME")
Dim oInfoObject As CrystalInfoStoreLib.InfoObject
Dim newCube As DpVBACube 'the microcube we're filling
Set newCube = dpInterface.DpVBACubes.Item(1) 'there is only one cube
Dim Cols As DpVBAColumns 'the columns of the microcube
Set Cols = newCube.DpVBAColumns 'reference the columns of the cube
Cols.SetNbColumns (1) 'how many columns in the cube?
Dim Col As DpVBAColumn 'a column in the microcube
Set Col = Cols.Item(1) 'look at the first column in the cube
Col.Name = "UserNames" 'the name that appears in the report
Col.Type = boCharacterObject 'what sort of information is it?
Col.Qualification = boDimension 'dimension, detail or measure?
Dim UserCount As Integer
UserCount = oInfoObjects.ResultCount
Dim i As Integer 'loop index
For i = 1 To UserCount 'for each row (item)
Col.Item(i) = StrConv(oInfoObjects.Item(i).Title, vbLowerCase) 'oInfoObjects.Count
Next
oEnterpriseSession.Logoff
- Save the VBA code, name the report, close any vba windows and on the “Access Data From VBA” window: Highlight “ThisDocument.getRepoUsers” and run the the code.
It should build a list of all the user in the CMS.
This report is now refreshable and schedulable on the enterprise.
Thanks all,
James Duffy
jduffy 
(BOB member since 2006-04-21)
(BOB member since 2003-11-27)
(BOB member since 2007-11-07)