What I really would like to do is to be able to enter the name of the individual and find out what reports they have access to as what their right are, instead of having to go to each report and find out.
I am using CE 8.0. I have done the following to be able to identify folders with reports. Hope this helps.
/*--SQL Script to dump data into a table.
1. Create the table in the database first.
2. Run the Stored Procedure.
3. Set up the asp page to view from an intranet site.
--Create a table for the stored procedure
Create Table folder_information
(
Folder varchar(50),
Sub_Folder1 varchar(50),
Sub_Folder2 varchar(50),
Sub_Folder3 varchar(50),
Report varchar(50)--,[Description] varchar(100)
)
*/
Create Procedure sp_Folder_information
as
delete folder_information
Set nocount on
-- Cursor with loop
declare @ci_parentid float
declare @exec1 varchar(255)
declare @ci_id float
declare @ci_name varchar(50)
declare @ci_id_fold1 float
declare @ci_name_fold1 varchar(50)
declare @ci_parentid_fold1 float
declare @ci_id_fold2 float
declare @ci_name_fold2 varchar(50)
declare @ci_parentid_fold2 float
declare @ci_id_fold3 float
declare @ci_name_fold3 varchar(50)
declare @ci_parentid_fold3 float
declare @Folder varchar(50)
declare @SUBFolder1 varchar(50)
declare @SUBFolder2 varchar(50)
declare @SUBFolder3 varchar(50)
declare @SUBFolder4 varchar(50)
declare @REPORT varchar(50)
declare PrimaryContact_cursor1 cursor for
SELECT CI_ID,CI_NAME,CI_PARENTID
FROM CI_INFOOBJECTS where ci_type=2
open PrimaryContact_cursor1
fetch next from PrimaryContact_cursor1 into @ci_id,@ci_name ,@ci_parentid
while @@fetch_status <> -1
begin
SELECT @Folder=''
SELECT @SUBFolder1=''
SELECT @SUBFolder2=''
SELECT @SUBFolder3=''
--select @ci_parentid,@ci_id
--select @ci_name
If @ci_parentid>0
Begin
SELECT @ci_name_fold3=ci_name,@ci_id_fold3=ci_id,@ci_parentid_fold3=ci_parentid
FROM CI_INFOOBJECTS
where ci_type=1 and CI_ID=@ci_parentid
--select @ci_name_fold3,@ci_id_fold3,@ci_parentid_fold3
If @ci_parentid_fold3=0
Select @folder=@Ci_name_fold3
End
If @ci_parentid_fold3>0
Begin
SELECT @ci_name_fold2=ci_name,@ci_id_fold2=ci_id,@ci_parentid_fold2=ci_parentid
FROM CI_INFOOBJECTS
where ci_type=1 and CI_ID=@ci_parentid_fold3
--select @ci_name_fold2,@ci_id_fold2,@ci_parentid_fold2
If @ci_parentid_fold2=0
Begin
Select @folder=@Ci_name_fold2
Select @SUBFolder1=@Ci_name_fold3
End
End
If @ci_parentid_fold2>0
Begin
SELECT @ci_name_fold1=ci_name,@ci_id_fold1=ci_id,@ci_parentid_fold1=ci_parentid
FROM CI_INFOOBJECTS
where ci_type=1 and CI_ID=@ci_parentid_fold2
--select @ci_name_fold1--,@ci_id_fold1,@ci_parentid_fold1
If @ci_parentid_fold1=0
Begin
Select @folder=@Ci_name_fold1
Select @SUBFolder1=@Ci_name_fold2
Select @SUBFolder2=@Ci_name_fold3
End
End
--SELECT @Folder
--SELECT @SUBFolder1
--SELECT @SUBFolder2
--select @ci_name
Insert into folder_information values (@folder,@subfolder1,@subfolder2,@subfolder2,@ci_name)
Fetch next from PrimaryContact_cursor1 into @ci_id,@ci_name ,@ci_parentid
end
close Primarycontact_cursor1
deallocate PrimaryContact_cursor1
set nocount off
select * from folder_information
order by folder,sub_folder1,sub_folder2,sub_folder3,report
-------------------------ASP PAGE ------------------------
<html><head>
<TITLE>Folders and Reports</TITLE>
</head><body bgcolor="#FFFFFF">
<!--#INCLUDE FILE="INCLUDE/ADOVBS.INC" -->
<%
dim dataConn, sSql
dim FirstName, LastName
FirstName = "Nathan"
LastName = "Pond"
set dataConn = Server.CreateObject("ADODB.Connection"
dataConn.Open "DSN=CE_TEST;uid=user;pwd=password" 'make connection
sSql = "sp_folder_information"
dataConn.Execute(sSql) 'execute sql call
%>
<%
connectme="DSN=CE_TEST;uid=username;pwd=password"
sqltemp="select * from folder_information order by folder,sub_folder1,sub_folder2,sub_folder3,report"
' Troubleshooting TIP:
' if you use this code and get an error, for example:
'
' ADODB.Recordset error 800a0cb3
'
' The operation requested by the application is not
' supported by the provider.
'
' You may have a driver that is out of date, see:
'
' for code that will identify what your driver version is
' this script works with Access, SQLserver and Oracle
' with up-to-date drivers
mypage=request("whichpage"
If mypage="" then
mypage=1
end if
mypagesize=request("pagesize"
If mypagesize="" then
mypagesize=10
end if
mySQL=request("SQLquery"
IF mySQL="" THEN
mySQL=SQLtemp
END IF
set rstemp=Server.CreateObject("ADODB.Recordset"
rstemp.cursorlocation=aduseclient
rstemp.cachesize=5
tempSQL=lcase(mySQL)
badquery=false
IF instr(tempSQL,"delete"
>0 THEN
badquery=true
END IF
IF instr(tempSQL,"insert"
>0 THEN
badquery=true
END IF
IF instr(tempSQL,"update"
>0 THEN
badquery=true
END IF
If badquery=true THEN
response.write "Not a SELECT Statement<br>"
response.end
END IF
rstemp.open mySQL,connectme
rstemp.movefirst
rstemp.pagesize=mypagesize
maxpages=cint(rstemp.pagecount)
maxrecs=cint(rstemp.pagesize)
rstemp.absolutepage=mypage
howmanyrecs=0
howmanyfields=rstemp.fields.count -1
response.write "Page " & mypage & " of " & maxpages & "<br>"
response.write "<table border='1'><tr>"
'Put Headings On The Table of Field Names
FOR i=0 to howmanyfields
response.write "<td><b>" & rstemp(i).name & "</b></td>"
NEXT
response.write "</tr>"
' Now loop through the data
DO UNTIL rstemp.eof OR howmanyrecs>=maxrecs
response.write "<tr>"
FOR i = 0 to howmanyfields
fieldvalue=rstemp(i)
If isnull(fieldvalue) THEN
fieldvalue="n/a"
END IF
If trim(fieldvalue)="" THEN
fieldvalue=" "
END IF
response.write "<td valign='top'>"
response.write fieldvalue
response.write "</td>"
next
response.write "</tr>"
rstemp.movenext
howmanyrecs=howmanyrecs+1
LOOP
response.write "</table><p>"
' close, destroy
rstemp.close
set rstemp=nothing
' Now make the page _ of _ hyperlinks
Call PageNavBar
sub PageNavBar()
' Thanks to Jeff Emrich <jeff.emrich@datafuse.com>
pad=""
scriptname=request.servervariables("script_name"
response.write "<table rows='1' cols='1' width='97%'><tr>"
response.write "<td>"
response.write "<font size='2' color='black' face='Verdana, Arial,Helvetica, sans-serif'>"
if (mypage mod 10) = 0 then
counterstart = mypage - 9
else
counterstart = mypage - (mypage mod 10) + 1
end if
counterend = counterstart + 9
if counterend > maxpages then counterend = maxpages
if counterstart <> 1 then
ref="<a href='" & scriptname
ref=ref & "?whichpage=" & 1
ref=ref & "&pagesize=" & mypagesize
ref=ref & "&sqlQuery=" & server.URLencode(mySQL)
ref=ref & "'>First</a> : "
Response.Write ref
ref="<a href='" & scriptname
ref=ref & "?whichpage=" & (counterstart - 1)
ref=ref & "&pagesize=" & mypagesize
ref=ref & "&sqlQuery=" & server.URLencode(mySQL)
ref=ref & "'>Previous</a> "
Response.Write ref
end if
Response.Write "["
for counter=counterstart to counterend
If counter>=10 then
pad=""
end if
if cstr(counter) <> mypage then
ref="<a href='" & scriptname
ref=ref & "?whichpage=" & counter
ref=ref & "&pagesize=" & mypagesize
ref=ref & "&sqlQuery=" & server.URLencode(mySQL)
ref=ref & "'>" & pad & counter & "</a>"
else
ref="<b>" & pad & counter & "</b>"
end if
response.write ref
if counter <> counterend then response.write " "
next
Response.Write "]"
if counterend <> maxpages then
ref=" <a href='" & scriptname
ref=ref & "?whichpage=" & (counterend + 1)
ref=ref & "&pagesize=" & mypagesize
ref=ref & "&sqlQuery=" & server.URLencode(mySQL)
ref=ref & "'>Next</a>"
Response.Write ref
ref=" : <a href='" & scriptname
ref=ref & "?whichpage=" & maxpages
ref=ref & "&pagesize=" & mypagesize
ref=ref & "&sqlQuery=" & server.URLencode(mySQL)
ref=ref & "'>Last</a>"
Response.Write ref
end if
response.write "<br></font>"
response.write "</td>"
response.write "</table>"
end sub
%>
</body></html>
![[profile]](/data/assets/smilies/profile.gif "[profile] [profile]")
