Search report definition

[Garry Flanigan]

Sometimes it becomes necessary to rename a table or a column in a SQL table.
The Search facilities in AA make it easy to find scripts and components that (may) need updating. However, the "Search in all web project files..." does not search reports. When I look at the *.a5rpt it is not human readable (not by me anyway).
Any ideas?

[TheSmitchell]

Re: Search report definition

I was going to suggest using grep or a grep equivalent, but I see that it's a binary file -- probably Alpha's own file format. Without knowing the file format, you're only option seems to be check all the reports manually.

[compuaid]

Re: Search report definition

Another option is to base reports on SQL Views. If you do that then should you ever need to rename a column in the actual table you could still alias the old column name in your views and all reports based on the view would work without modification.

[Garry Flanigan]

Re: Search report definition

Well it is not that difficult as it turns out.
Selwyn gave me a tip of a function:
dictionary.get_singleton_adHocSQL()
So I did some work and came up with the script below. Selwyn initially said there were no plans to add this functionality, but .....

dim vl as P

dim data[1] as c

DIM SHARED cSearch as C
DIM SHARED varC_result as C

vl = local_variables()

Search_button_label = "&Search"
close_button_label = "&Close"
varC_result = UI_MODELESS_DLG_BOX("Search report definitions",<<%dlg%
{can_exit=close}
{wrap=100}
Search for a text string in the adhoc SQL select statement of all reports. Each nodes lists a report name and the branch details the SELECT statement or a truncated string if too long.;
{line=1,0};
{region}
Search for:;
[.40cSearch] <*15=Search_button_label!Search>;
{endregion};
{region}
Results:;
[%S=BLR;D="^"%.100,25node^<data]
{endregion};
<15=close_button_label!Close>
%dlg%,<<%code%
if a_dlg_button = "Search" then
get_data(vl)
end if
if a_dlg_button = "Close" then
ui_modeless_dlg_close("Search report definitions")
end if
%code%)

end

function get_data as C (vars as P)

with vars
dim cFilePattern as c
dim cFolder as c
dim cAllFiles as c
dim nKount as n
dim bSelectSQL as b
dim cPath as c
dim cReportName as c
dim cReportNameFull as c
dim cHits as c = ""
dim nHitKount as n = 0
dim bAdhocSQL as b

'debug(1)

'data = ""

cPath = a5.get_path()

cFolder = cPath + chr(92) + word(cPath,w_count(cPath,chr(92)), chr(92)) + ".WebProjects\Default.WebProject" + chr(92)

cFilePattern = cFolder + "*.a5rpt"
cAllFiles = filefind.get(cFilePattern,FILE_FIND_NORMAL, "N")
nKount = w_count(cAllFiles,crlf())

'dim data[nKount] as C
'dim data as c
if nKount > 0
for i = 1 to nKount
cReportName = word(cAllFiles,i,crlf())
cReportNameFull = cFolder + cReportName
cReportName = stritran(cReportName,".a5rpt")

bAdhocSQL = dictionary.get_singleton_adHocSQL(cReportNameFull)
bSelectSQL = EXTRACT_STRING( bAdhocSQL ,"<SQLSelectStatement=", "<SQLType=" , 1)

if atc(cSearch,bSelectSQL) > 0 then
bSelectSQL = stritran(bSelectSQL,crlf()," ") ' remove crlf's
bSelectSQL = stritran(bSelectSQL,chr(9),"") ' remove tabs
bSelectSQL = stritran(bSelectSQL,"<<%str%","")
bSelectSQL = stritran(bSelectSQL,"%str%>","")
bSelectSQL = stritran(bSelectSQL,"\">","")
bSelectSQL = stritran(bSelectSQL,"\"","")
bSelectSQL = ltrim(bSelectSQL)
nHitKount = nHitKount + 1
data.resize(nHitKount)

if len(cReportName + "^" + bSelectSQL) <= 1024 then
data[nHitKount] = cReportName + "^" + bSelectSQL
else
data[nHitKount] = left(cReportName + "^" + "TRUNCATED SQL: " + bSelectSQL,1024)
end if
cHits = cHits + crlf() + cReportName
end if
next
end if
'debug(1)
end with

end function

[TheSmitchell]

Re: Search report definition

Well it is not that difficult as it turns out.
Selwyn gave me a tip of a function:
dictionary.get_singleton_adHocSQL()
So I did some work and came up with the script below. Selwyn initially said there were no plans to add this functionality, but .....

Ah! Thanks for sharing this! This is awesome. :D

Question: Since there seems to be a way to get at the SQL statements, are there ways to search the report files directly? For, say, some static text/html?

[Garry Flanigan]

Re: Search report definition

Sarah,
Not sure exactly what you are looking for, but take a copy of the *.a5rpt file and rename to *.zip, unzip, take a look with Notepad or whatever. I presume you looked at output from dictionary.get_singleton_adHocSQL() and it did not show what you wanted.
It is amazing how one thing leads to another and being able to search in large projects is a must.

[TheSmitchell]

Re: Search report definition

Sarah,
Not sure exactly what you are looking for, but take a copy of the *.a5rpt file and rename to *.zip, unzip, take a look with Notepad or whatever. I presume you looked at output from dictionary.get_singleton_adHocSQL() and it did not show what you wanted.
It is amazing how one thing leads to another and being able to search in large projects is a must.

I didn't know they were zip files! Thanks. :D

[compuaid]

Re: Search report definition

Looks like they've included this search capability in the latest pre-release. Alpha continues to be the most responsive company to customer feedback I've ever dealt with.

Web Applications - Web Projects Control Panel - 'Search all files in this Project' Command - This command will now search in report, label and letter file (.i.e. .a5rtp, .a5lab and .a5ltr). The data source definition of the report layout (not the report body itself) will be searched.