Looking to write a BAQ to extract who has access to what Menu Security Codes. When I look in the Menu Security module, the tables listed on some of the fields is not listed in the BAQ options.
Has anyone written something like this?
Dyan
1 Like
Pretty sure there are others here on posts as well
1 Like
I can’t open your BAQ, Can you please post what tables with reference tables and columns displaying on report.
Thanks,
Kumar
/*
* Disclaimer!!!
* This is not a real query being executed, but a simplified version for general vision.
* Executing it with any other tool may produce a different result.
*/
with [CTE] as
(select
[CTEMenu].[Company] as [CTEMenu_Company],
[CTEMenu].[ParentMenuID] as [CTEMenu_ParentMenuID],
[CTEMenu].[MenuID] as [CTEMenu_MenuID],
[CTEMenu].[SecCode] as [CTEMenu_SecCode],
[CTEMenu].[Sequence] as [CTEMenu_Sequence],
[CTEMenu].[MenuDesc] as [CTEMenu_MenuDesc],
(1) as [Calculated_Level],
(CAST(CTEMenu.MenuDesc as varchar(2000))) as [Calculated_MenuPath]
from Ice.Menu as CTEMenu
where (CTEMenu.ParentMenuID = '')
union all
select
[CTE2Menu].[Company] as [CTE2Menu_Company],
[CTE2Menu].[ParentMenuID] as [CTE2Menu_ParentMenuID],
[CTE2Menu].[MenuID] as [CTE2Menu_MenuID],
[CTE2Menu].[SecCode] as [CTE2Menu_SecCode],
[CTE2Menu].[Sequence] as [CTE2Menu_Sequence],
[CTE2Menu].[MenuDesc] as [CTE2Menu_MenuDesc],
(CTE.Calculated_Level + 1) as [Calculated_Level],
(CAST(CAST(CTE.Calculated_MenuPath as varchar(2000)) + CAST(' -> ' as varchar(2000)) + CAST(CTE2Menu.MenuDesc as varchar(2000)) AS varchar(2000))) as [Calculated_MenuPath]
from Ice.Menu as CTE2Menu
inner join CTE as CTE on
CTE2Menu.ParentMenuID = CTE.CTEMenu_MenuID)
select
[CTE1].[CTEMenu_Company] as [CTEMenu_Company],
[CTE1].[CTEMenu_ParentMenuID] as [CTEMenu_ParentMenuID],
[CTE1].[CTEMenu_MenuID] as [CTEMenu_MenuID],
[CTE1].[CTEMenu_SecCode] as [CTEMenu_SecCode],
[CTE1].[CTEMenu_Sequence] as [CTEMenu_Sequence],
[CTE1].[CTEMenu_MenuDesc] as [CTEMenu_MenuDesc],
[CTE1].[Calculated_Level] as [Calculated_Level],
[CTE1].[Calculated_MenuPath] as [Calculated_MenuPath],
[MenuSecurity].[Security_EntryList] as [Security_EntryList],
[MenuSecurity].[Security_NoEntryList] as [Security_NoEntryList],
[MenuSecurity].[Security_SecurityMgr] as [Security_SecurityMgr]
from CTE as CTE1
inner join (select
[Security].[Company] as [Security_Company],
[Security].[SecCode] as [Security_SecCode],
[Security].[EntryList] as [Security_EntryList],
[Security].[NoEntryList] as [Security_NoEntryList],
[Security].[SecurityMgr] as [Security_SecurityMgr]
from Ice.Security as Security
where (Security.ParentSecCode = 'MENU')) as MenuSecurity on
CTE1.CTEMenu_SecCode = MenuSecurity.Security_SecCode
1 Like
Thanks you Michael, I tried to reproduce BAQ with your query but I couldn’t succeeded. Can you please provide info subquery list with diagrams.
Thanks,
Kumar
Here I enclosed my query
Query
with [CTE] as
(select
[CTEMenu].[Company] as [CTEMenu_Company],
[CTEMenu].[ParentMenuID] as [CTEMenu_ParentMenuID],
[CTEMenu].[MenuID] as [CTEMenu_MenuID],
[CTEMenu].[SecCode] as [CTEMenu_SecCode],
[CTEMenu].[Sequence] as [CTEMenu_Sequence],
[CTEMenu].[MenuDesc] as [CTEMenu_MenuDesc],
(CAST(CTEMenu.MenuDesc as varchar(2000))) as [Calculated_MenuPath],
(1) as [Calculated_Level]
from Ice.Menu as [CTEMenu]
where (CTEMenu.ParentMenuID = ‘’)
union all
select
[CTE2Menu].[Company] as [CTE2Menu_Company],
[CTE2Menu].[ParentMenuID] as [CTE2Menu_ParentMenuID],
[CTE2Menu].[MenuID] as [CTE2Menu_MenuID],
[CTE2Menu].[SecCode] as [CTE2Menu_SecCode],
[CTE2Menu].[Sequence] as [CTE2Menu_Sequence],
[CTE2Menu].[MenuDesc] as [CTE2Menu_MenuDesc]
from Ice.Menu as [CTE2Menu]
full outer join as [CTE] on
CTE2Menu.ParentMenuID = CTE.Menu_MenuID)
select *
from CTE as [CTE1]
inner join Ice.Security as [Security]
But I need to finish query diagrams. Just I want to check with you sequence of sub queries correct or not. Please help me,
Thanks,
I figured it out and fixed the query. Now it is working and thanks for your script.
Thanks,