Folders as favorites, report search

(12 posts) (3 voices)

Tags:

No tags yet.

  1. 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

  2. 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

  3. 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

  4. 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

  5. myDBR Team, Key Master

    Maron,
    with the most recent build, you can now add folders to favourites.

    --
    myDBR Team

  6. maron, Member

    Thank you - christmas cleaning ahead....

  7. 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

  8. 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

  9. 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

  10. myDBR Team, Key Master

    You can try again. There was a cut-and-paste error in the original one.

    --
    myDBR Team

  11. 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

  12. 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


Reply

You must log in to post.