Collect Top Level Folders and Report Names from SQL Server

[Hypermommy]

Hi all,

I'm sure hoping someone can help me with this becuase I'm pretty stumped. I'd like to write a SQL Server Stored Procedure that will return two recordsets.

1) A listing of all the top level folders -- i.e., those available to administrators when they select public folders -- and a count of all reports in those folders. Not the instances of the report but just the report itself (i.e., if someone's run Report1 6 times and has run Report2 12 times, the number associated with the top level folder that reports 1 and 2 are found in should only be 2, not 18).

2) A listing of each of the reports within each top level folder, regardless of how many sub folders stand between the report and the top level folder.

So.. if I can see this in ePortfolio:

Folder 1
Subfolder 1
Report1
Report2
Report3
Subfolder 2
Report4
Report5

Folder 2
Report6
Report7
Report8
Report9

Folder 3
Subfolder 3
Subfolder 5
Report10
Report11
Subfolder 4
Report12

I'd like Recordset #1 to have the following information
Folder1-Name 5
Folder2-Name 4
Folder3-Name 3

And I'd like Recordset #2 to have the following information

Folder1-Name Report1-Name
Folder1-Name Report2-Name
Folder1-Name Report3-Name
Folder1-Name Report4-Name
Folder1-Name Report5-Name
Folder2-Name Report6-Name
Folder2-Name Report7-Name
Folder2-Name Report8-Name
Folder2-Name Report9-Name
Folder3-Name Report10-Name
Folder3-Name Report11-Name
Folder3-Name Report12-Name

Thanks in advance for any assistance you can give me on figuring out the query/logic to put in the stored procedure.

-= Hypermommy =-

 

[Turkbear]

Hi,
AFAIK, no external Sql query ( that is one against the APS database directly ) will work due to the specialized way the data is stored..Queries against the CI_INFOOBJECTS within a csp page can get it , but I have not seen a way to get it otherwise..

 

[Hypermommy]

Okay, in that case, does anyone have a query that will work to achieve the same results within a CSP page? I can make that work, I think.

-= Hypermommy =-

 

[CrystalEnterprise]

Try this, but you need to point this to ce90 database.

select a1.ObjName as FolderName, a2.ObjName as ProgramName
from aps_infoObjects2 a1 inner join aps_infoObjects2 a2 on a1.ObjectID = a2.ParentID
where a1.typeID = 1 and a1.ObjFlags = 0

 

[Hypermommy]

I tried that in the CSP Query builder but changed aps_infoObjects2 to ci_infoobjects (to follow the example given) and was told that it was not a valid query. Do I need to use the APSName_infoobjects2 in place of aps_infoobjects2 in your example above?

Here's what I put... maybe I'm not understanding:
select a1.ObjName as FolderName, a2.ObjName as ProgramName
from ci_infoobjects a1 inner join ci_infoobjects a2 on a1.ObjectID = a2.ParentID
where a1.typeID = 1 and a1.ObjFlags = 0

Thanks for the help.

-= Hypermommy =-

 

[cutiger93]

Try the following and see if it does what you want it to do.



--select * from folder_information
--delete folder_information
--drop table folder_information
/*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)
)
*/
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