maron, Member
Hi.
It would increase the usability a lot if folders could be favorites.
Also - if there could be a search feature for reports (you have access to) that would search in folders and subfolders, and allow for drag and drop to favorites folders - you would have a cleanup dream for Mydbr.
We have over 300 reports - many of them redundant...
Best regards,
Maron
myDBR Team, Key Master
Having folders as favorites is an obvious choise. We'll add it.
With the user-level search one would need to distinguish between directly executeable reports and linked only reports as you do not want to offer direct access to linked only reports. As for admin level search, one can easily create report for it (can provide samples if needed).
--
myDBR Team
maron, Member
Makes sense with the admin search. User-level search is probably not needed so much.
If you have any hints on how I could locate reports with code in a similar way when I click on a link in the scheduler setup - this would be helpful.
Maron
myDBR Team, Key Master
You can create a report for the search. For example a report for MySQL:
CREATE PROCEDURE sp_DBR_find_report(in_search varchar(101))
BEGIN select 'dbr.parameters.show'; select 'dbr.hidecolumns', 'folder_id';
select 'dbr.url', 'index.php?a=editor', '', '[Name]', 'm=folder_id', 'r=report_id', 'show_link=[is_report]>0';
select 'dbr.url', 'index.php?a=editor', '', '[Name]', 'proc=Name', 'show_link=[is_report]==0';
select 'dbr.url', 'index.php', '', '[Location]', 'm=folder_id', 'highlight=report_id'; select r.name, ifnull(r.proc_name, p.SPECIFIC_NAME) as 'Name', fn_report_location(r.proc_name) as 'Location', p.created as 'Created at', r.folder_id, r.report_id, ifnull(r.report_id,0) as 'is_report'
from information_schema.routines p
left join mydbr_reports r on r.proc_name =p.routine_name
where routine_definition like concat('%', in_search, '%' ) and p.routine_schema=database() and p.routine_name not like 'sp_MyDBR%'
union
select r.name, r.proc_name, fn_report_location(r.proc_name), p.created, r.folder_id, r.report_id, ifnull(r.report_id,0)
from mydbr_reports r
join information_schema.routines p on r.proc_name =p.routine_name and p.routine_schema=database()
where r.proc_name = in_search; END
With function:
CREATE FUNCTION `fn_report_location`(in_procedure varchar(100)) RETURNS text CHARSET utf8
BEGIN
declare v_folder_id int;
declare v_folder_id_orig int;
declare v_depth int;
declare v_breadcrumb text;
declare v_foldername varchar(1024) character set utf8mb4; select r.folder_id, f.name into v_folder_id, v_breadcrumb
from mydbr_reports r
join mydbr_folders f on r.folder_id=f.folder_id
where r.proc_name = in_procedure; while (v_folder_id is not null) do
select mother_id into v_folder_id
from mydbr_folders
where folder_id=v_folder_id; if (v_folder_id is not null) then
select name into v_foldername
from mydbr_folders
where folder_id=v_folder_id; if (v_foldername is not null) then
select concat(v_foldername, ' → ', v_breadcrumb) into v_breadcrumb;
end if;
end if;
end while; return v_breadcrumb; END
myDBR Team, Key Master
Maron,
with the most recent build, you can now add folders to favourites.
--
myDBR Team
maron, Member
Thank you - christmas cleaning ahead....
ajdjackson, Member
Hi
I'm looking for something similar for SQL Server.
I'd like to be able to list all my SPs and identify which report they are in and where in the report group>folder hierarchy they are.
I can get the list of SPs and report name and groups ok but I'm stuck as to how I handle nested folders ie recursive mother_ids
Cheers
Jake
myDBR Team, Key Master
The same with SQL Server where you can use a CTE query (as you can in MySQL >= 8 and MariaDB => 10.2.1):
First the main report:
create procedure sp_DBR_find_report(
@search varchar(max)
)
as
begin select 'dbr.parameters.show'; select 'dbr.hidecolumns', 'folder_id';
select 'dbr.url', 'index.php?a=editor', '', '[Name]', 'm=folder_id', 'r=report_id', 'show_link=[is_report]>0'
select 'dbr.url', 'index.php?a=editor', '', '[Name]', 'proc=Name', 'show_link=[is_report]==0'
select 'dbr.url', 'index.php', '', '[Location]', 'm=folder_id', 'highlight=report_id' select r.name, isnull(r.proc_name, p.SPECIFIC_NAME) as 'Name', dbo.fn_report_location(r.proc_name) as 'Location', p.created as 'Created at', r.folder_id, r.report_id, isnull(r.report_id,0) as 'is_report'
from information_schema.routines p
left join mydbr_reports r on r.proc_name =p.routine_name
where routine_definition like '%'+@search+'%' and p.routine_catalog=db_name() and p.routine_name not like 'sp_MyDBR%' end
And the report location function:
create function fn_report_location( @report_name sysname )
returns varchar(max)
as
begin
declare @folder_id int
declare @breadcrumb varchar(max) select @folder_id = r.folder_id
from mydbr_reports r
where r.proc_name = @report_name; with breadcrumb_cte (folder_id, breadcrumb)
as (
select f.mother_id, cast(f.name as varchar(max))
from mydbr_folders f
where f.folder_id=@folder_id
union all
select f.mother_id, cast(f.name as varchar(max))+' -> '+b.breadcrumb
from breadcrumb_cte b
join mydbr_folders f on f.folder_id=b.folder_id
)
select @breadcrumb=breadcrumb
from breadcrumb_cte
where folder_id is null return @breadcrumb
end
--
myDBR Team
ajdjackson, Member
Hi
Thanks for getting back.
I've tried what you have suggested but I can't get the breadcrumb function to work - therefore no location is returned.
The function always returns null.
Thanks
Jake
myDBR Team, Key Master
You can try again. There was a cut-and-paste error in the original one.
--
myDBR Team
ajdjackson, Member
Hi
Many thanks - works great!
I had wondered what the -6 meant in the original function.
Learnt something new today as well - I didn't appreciate a cte could refer to itself in its creation statement.
Thanks again
Jake
myDBR Team, Key Master
Common Table Expressions (CTE's) are often used when you need recursive queries. To do that, you include the CTE table to the original query via UNION.
--
myDBR Team
You must log in to post.