Crystal Enterprise - Query Builder -

[BlurredVision]

Hello All,

One thing I dislike about CE9 is how hard it is to pull information from it.
Seems silly that you cannot easily report on your own information. I hear the next release will be somewhat better, but not by much.


I have posted several questions regarding this issue on this forum, without any success.


After much time and energy I have come up with a way to pull anything from
the Query Builder (IE: Select * from CI_INFOOBJECTS) and put that information into
a spread sheet or database so that it can be reported on.

I can now report on what reports I have in CE9, who receives them, when they last ran, location, etc..


I am putting the finishing touches on this little project of mine. Once completed I will post it here.

If you could, please post any queries that you are currently using within the query builder along with a description. I would like to ensure I am capturing all necessary information.

Thanks

 

[synapsevampire]

That sounds great, please post what you have.

And I agree, since you need to go through the CE objects for some data elements, CD has done it again, developing a data source that their own product (CR) can't report on - they must be very proud

Perhaps the BO folks will slap some business sense into them.

-k

 

[Turkbear]

Hi,
Not to defend the way CD has done it, but the APS data (CI_INFOOBJECTS, etc.) is designed for optimum speed in returning info needed by the system, not in being accessable to CR - it is not a data source in that sense..

That does not mean that they could not have created a System Management utility that would give you the type of reports about the CE system that you need..( 3rd party folks have done it, so why not CD making it a part of the package ?)
The Launchpad Administrative samples give some idea about how to create useful information pages but don't go far enough..


Just my 2c..

 

[cutiger93]

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=&quot;#FFFFFF&quot;>
<!--#INCLUDE FILE=&quot;INCLUDE/ADOVBS.INC&quot; -->
<%
dim dataConn, sSql
dim FirstName, LastName

FirstName = &quot;Nathan&quot;
LastName = &quot;Pond&quot;

set dataConn = Server.CreateObject(&quot;ADODB.Connection&quot
dataConn.Open &quot;DSN=CE_TEST;uid=user;pwd=password&quot; 'make connection

sSql = &quot;sp_folder_information&quot;

dataConn.Execute(sSql) 'execute sql call
%>


<%
connectme=&quot;DSN=CE_TEST;uid=username;pwd=password&quot;
sqltemp=&quot;select * from folder_information order by folder,sub_folder1,sub_folder2,sub_folder3,report&quot;

' 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:
'

http://www.learnasp.com/learn/connectioninfo.asp

' 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(&quot;whichpage&quot
If mypage=&quot;&quot; then
mypage=1
end if
mypagesize=request(&quot;pagesize&quot
If mypagesize=&quot;&quot; then
mypagesize=10
end if
mySQL=request(&quot;SQLquery&quot
IF mySQL=&quot;&quot; THEN
mySQL=SQLtemp
END IF

set rstemp=Server.CreateObject(&quot;ADODB.Recordset&quot
rstemp.cursorlocation=aduseclient
rstemp.cachesize=5
tempSQL=lcase(mySQL)
badquery=false
IF instr(tempSQL,&quot;delete&quot>0 THEN
badquery=true
END IF
IF instr(tempSQL,&quot;insert&quot>0 THEN
badquery=true
END IF
IF instr(tempSQL,&quot;update&quot>0 THEN
badquery=true
END IF
If badquery=true THEN
response.write &quot;Not a SELECT Statement<br>&quot;
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 &quot;Page &quot; & mypage & &quot; of &quot; & maxpages & &quot;<br>&quot;
response.write &quot;<table border='1'><tr>&quot;

'Put Headings On The Table of Field Names
FOR i=0 to howmanyfields
response.write &quot;<td><b>&quot; & rstemp(i).name & &quot;</b></td>&quot;
NEXT
response.write &quot;</tr>&quot;

' Now loop through the data
DO UNTIL rstemp.eof OR howmanyrecs>=maxrecs
response.write &quot;<tr>&quot;
FOR i = 0 to howmanyfields
fieldvalue=rstemp(i)
If isnull(fieldvalue) THEN
fieldvalue=&quot;n/a&quot;
END IF
If trim(fieldvalue)=&quot;&quot; THEN
fieldvalue=&quot; &quot;
END IF
response.write &quot;<td valign='top'>&quot;
response.write fieldvalue
response.write &quot;</td>&quot;
next
response.write &quot;</tr>&quot;
rstemp.movenext
howmanyrecs=howmanyrecs+1
LOOP
response.write &quot;</table><p>&quot;

' 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=&quot;&quot;
scriptname=request.servervariables(&quot;script_name&quot
response.write &quot;<table rows='1' cols='1' width='97%'><tr>&quot;
response.write &quot;<td>&quot;
response.write &quot;<font size='2' color='black' face='Verdana, Arial,Helvetica, sans-serif'>&quot;
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=&quot;<a href='&quot; & scriptname
ref=ref & &quot;?whichpage=&quot; & 1
ref=ref & &quot;&pagesize=&quot; & mypagesize
ref=ref & &quot;&sqlQuery=&quot; & server.URLencode(mySQL)
ref=ref & &quot;'>First</a> : &quot;
Response.Write ref


ref=&quot;<a href='&quot; & scriptname
ref=ref & &quot;?whichpage=&quot; & (counterstart - 1)
ref=ref & &quot;&pagesize=&quot; & mypagesize
ref=ref & &quot;&sqlQuery=&quot; & server.URLencode(mySQL)
ref=ref & &quot;'>Previous</a> &quot;
Response.Write ref
end if
Response.Write &quot;[&quot;
for counter=counterstart to counterend
If counter>=10 then
pad=&quot;&quot;
end if
if cstr(counter) <> mypage then
ref=&quot;<a href='&quot; & scriptname
ref=ref & &quot;?whichpage=&quot; & counter
ref=ref & &quot;&pagesize=&quot; & mypagesize
ref=ref & &quot;&sqlQuery=&quot; & server.URLencode(mySQL)
ref=ref & &quot;'>&quot; & pad & counter & &quot;</a>&quot;
else
ref=&quot;<b>&quot; & pad & counter & &quot;</b>&quot;
end if
response.write ref
if counter <> counterend then response.write &quot; &quot;
next
Response.Write &quot;]&quot;
if counterend <> maxpages then
ref=&quot; <a href='&quot; & scriptname
ref=ref & &quot;?whichpage=&quot; & (counterend + 1)
ref=ref & &quot;&pagesize=&quot; & mypagesize
ref=ref & &quot;&sqlQuery=&quot; & server.URLencode(mySQL)
ref=ref & &quot;'>Next</a>&quot;
Response.Write ref


ref=&quot; : <a href='&quot; & scriptname
ref=ref & &quot;?whichpage=&quot; & maxpages
ref=ref & &quot;&pagesize=&quot; & mypagesize
ref=ref & &quot;&sqlQuery=&quot; & server.URLencode(mySQL)
ref=ref & &quot;'>Last</a>&quot;
Response.Write ref
end if
response.write &quot;<br></font>&quot;
response.write &quot;</td>&quot;
response.write &quot;</table>&quot;
end sub
%>
</body></html>

 

[jbullman]

I've been thinking of doing the same. I will be most interested to see what you come up with. Thank you for offering it.

Judith Ullman

http://www.innovativeinformation.com

Metro New York Crystal Decisions Training and Consulting Partner

 

[BlurredVision]

This is just one part of it. I am still working on some of the finer points and will post everything once I am completed. If you take this and make changes, please share them with the rest of us. Beats having to pay for a 3rd party utility

The following will pull the report name, destination (email), and the parent folder.

1) Run the following query from the Query Builder:

SELECT SI_PARENT_FOLDER, SI_SCHEDULEINFO FROM CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Report' and SI_INSTANCE=0

2) From your browser, save this page as an html document.

3) Open your html document in Excel.

4) Insert a new sheet, and name it sheet2

5) Press Alt-F11 and paste the following code:

Sub copyInfo()
Dim nFound As Boolean
counter = 1: nFound = False
For a = 1 To Range(&quot;E65535&quot.End(xlUp).Row
Cells(a, 1).Activate

If ActiveCell.Value = &quot;SI_PARENT_FOLDER&quot; Then
nFound = True: Cells(a, 2).Copy Sheets(2).Cells(counter, 3)
End If

If ActiveCell.Value = &quot;SI_NAME&quot; Then
nFound = True: Cells(a, 2).Copy Sheets(2).Cells(counter, 1)
End If

If InStr(1, Cells(a, 5).Value, &quot;@&quot <> 0 And nFound = True Then
Cells(a, 5).Copy Sheets(2).Cells(counter, 2): counter = counter + 1
End If

If ActiveCell.Value = &quot;Properties&quot; Then nFound = False
Next a
End Sub

6) Run the macro, then view sheet2

That is it.
Let me know what you think!

Brian

 

[synapsevampire]

Turkbear: Going through an object interface and THEN querying the database seems faster to you???

It simplifies programming and storing data, sometimes, faster? not bloody likely...

-k

 

[Turkbear]

Hi,
It may be faster if it allows for storage of the information in an optimized format..hard for humans, but easy for machines ( and the object's methods) to decode..Not that I am sure CE does this, but using hashed values can be much faster than using 'standard' storage methods..