Good morning,
I am looking at the Ice.QueryHdr table and wondering if there is a table that links to show where used, as in the BAQ designer. I’d like to query the locations that all my BAQs are used, so I can export it. Is that possible?
Thanks for your time!
1 Like
I think its the ice.DashBdBAQ table. You can link on the QueryID field and related where used field is ‘DefinitionID’.
Please note never actually used this so please validate!
EDIT: just realised this is only for Dashboards… hopefully some better answers come.
It works! This shows the locations of all dashboards that use a particular BAQ. Awesome! What about all those other where used locations?
I’m actually able to see this in field help (classic)… but only sometimes 
So I guess if you can find a BAQ for all the where used types, then you could find it out. But doesn’t feel a very efficient method. I’m interested this as well so will keep looking when I have a chance, and please could you post if you find all the table names?
1 Like
I use this monster for upgrade prep (It’s not efficient, but it gets the job done):
WITH u AS
(
SELECT DISTINCT QueryID, WhereUsed = 'Dashboard'
FROM Ice.DashBdBAQ
WHERE SystemFlag = 0
UNION ALL
SELECT DISTINCT ExportID, 'QuickSearch'
FROM Ice.QuickSearch
WHERE SystemFlag = 0
UNION ALL
SELECT DISTINCT ExportID, 'BAQReport'
FROM Ice.BAQReport
WHERE SystemFlag = 0
UNION ALL
SELECT DISTINCT QueryID, 'ReportList'
FROM Ice.RptTable
WHERE NOT ISNULL(QueryID, '') = '' AND SystemFlag = 0
UNION ALL
SELECT DISTINCT QueryID, 'Function'
FROM Ice.QueryHdr h, Ecf.EfxFunction f
WHERE f.Body LIKE '%' + h.QueryID + '%'
UNION ALL
SELECT DISTINCT QueryID, 'BPM'
FROM Ice.QueryHdr h, Ice.BpDirective b
WHERE CAST(b.Body AS nvarchar(MAX)) LIKE '%' + h.QueryID + '%'
UNION ALL
SELECT DISTINCT QueryID, 'Customization'
FROM Ice.QueryHdr h, Ice.XXXDef f
WHERE f.TypeCode = 'Customization' AND f.Content LIKE '%' + h.QueryID + '%'
)
,uc AS
(
SELECT QueryID, Usage = STRING_AGG(WhereUsed, '~')
FROM u
GROUP BY QueryID
)
,stats AS
(
SELECT QueryID = Key1, LastUsed = MAX(CAST(Key2 AS date)), Uses = COUNT(*)
FROM Ice.UD20
GROUP BY Key1
)
SELECT Task = CAST(1 AS bit)
,Scope = CASE
WHEN IsGlobal = 1 THEN
'Global'
ELSE
q.Company
END
,BAQType = CASE
WHEN Updatable = 1 THEN
'Updatable'
WHEN ExtQuery = 1 THEN
'External'
ELSE
'Standard'
END
,q.QueryID
,q.Description
,AuthorID
,q.IsShared
,Usage = ISNULL(uc.Usage, '')
,s.LastUsed
,Uses = ISNULL(s.Uses, 0)
FROM Ice.QueryHdr q
LEFT JOIN uc ON q.QueryID = uc.QueryID
LEFT JOIN stats s ON q.QueryID = s.QueryID
WHERE SystemFlag = 0
ORDER BY AuthorID, QueryID;
Note, you can remove the stats portion … I have a BPM that logs when each query is called.
2 Likes
Isn’t there a way to import SQL into a BAQ now? I am not sure how to rebuild all that without just importing it. Do I need to import in the kinetic BAQ editor? I don’t see the place to paste it in.
The export was already written by @klincecum:
Now if you want import too…
Make the software more friendly for DevOps | ERP-I-139
(But I’m sure you have already voted for it.)
1 Like
Unless I am missing something, this doesn’t show where BAQs are used, it just exports them to a ZIP file. Useful? Yes. But not exactly what I needed. A least your comment got me to finally get this setup! Thanks @klincecum, the export tool is great!
1 Like
For what the system can see, there is a BO Method.
Ice.BO.BAQDesigner.GetUsedInBAQList
If you want to look in code, well that’s a bit more involved.
2 Likes
That gets a bit outside of the simple BAQ realm. But glad to know there is a way! Thanks!
Not sure how far you got with this Nate, but for those who do happen upon this thread, two of the other tables used in the Where Used tab of the BAQ Designer are Ice.QuickSearch and Ice.BAQReport.
What I’ve done is this:
DashBdBAQ is linked via QueryID = QueryID, QuickSearch and BAQReport are both linked by QueryID = ExportID. Select all from QueryHdr in each case. Criteria on each table is SystemFlag = 0. I can’t find any BAQs that are used in BAQ List and Report List, so I can’t use Field Help and find the tables for the remaining two. If anybody knows, please do add them.
This is a purely BAQ approach and it won’t find any customizations, BPMs, and etc. that call the BAQ, you’d have to use the SQL approach Doug provided (which I’ve never done, sorry).
2 Likes