sara_b
Level 4

Reporting on active Catalog Items

Jump to solution

I'm looking at the standard, canned, reports in App Portal 2016 (11.0.4.0). There is a report titled "All Catalog Items with Category and Description". This seems to report every item ever built in App Portal, including all the archived items. Is there a report that would just list all the currently active (not necessarily enabled) catalog items?

0 Kudos
1 Solution
jdempsey
Moderator Moderator
Moderator

You can modify the SQL query behind that report to be more appropriate to the data you're looking for.  In the <App Portal>\Web\ReportDefinition folder, you'll find a file called AdminStudio.Reports.xml.  I recommend copying this file to another folder as a backup before modifying it.  Once you've done that, you can edit the original file and search for "All Catalog Items with Category and brief Description".  Under that section of the xml file, in the DataSource element, you'll see the following SQL select statement:

SELECT P.PackageTitle AS [Catalog Item],C.CatName AS [Category],P.PackageDesc AS [Description] FROM dbo.WD_WebPackages AS P left outer join dbo.WD_Package_Category AS PC on P.PackageID = PC.PackageID left outer join 
dbo.WD_Category AS C on PC.CategoryID = C.CatID Order By [Catalog Item]

To retrieve only the currently active (i.e. not archived) catalog items, you would simply add a WHERE clause onto the end as follows:

SELECT P.PackageTitle AS [Catalog Item],C.CatName AS [Category],P.PackageDesc AS [Description] FROM dbo.WD_WebPackages AS P left outer join dbo.WD_Package_Category AS PC on P.PackageID = PC.PackageID left outer join 
dbo.WD_Category AS C on PC.CategoryID = C.CatID WHERE P.Deleted = 0 Order By [Catalog Item] 

Please note that the title of this report indicates that it retrieves the "Brief Description"; however, the SQL query is actually retrieving the "Full Description".  If you're populating brief descriptions in your catalog items, I would recommend changing the query to pull that instead of the full descriptions, since the rich HTML formatting of the full descriptions doesn't lend itself well to a table-based report.  If you want to make that change, just change P.PackageDesc to P.BriefDescription.

Hope that helps.  You can find more information about managing reports in the App Portal Admin Guide on HelpNet (https://helpnet.flexerasoftware.com/appportal2019r1/default.htm#helplibrary/AP_Reports.htm).

Anything expressed here is my own view and not necessarily that of my employer, Flexera. If my reply answers a question you have raised, please click "ACCEPT AS SOLUTION".

View solution in original post

This thread has been automatically locked due to inactivity.

To continue the discussion, please start a new thread.

1 Reply
jdempsey
Moderator Moderator
Moderator

You can modify the SQL query behind that report to be more appropriate to the data you're looking for.  In the <App Portal>\Web\ReportDefinition folder, you'll find a file called AdminStudio.Reports.xml.  I recommend copying this file to another folder as a backup before modifying it.  Once you've done that, you can edit the original file and search for "All Catalog Items with Category and brief Description".  Under that section of the xml file, in the DataSource element, you'll see the following SQL select statement:

SELECT P.PackageTitle AS [Catalog Item],C.CatName AS [Category],P.PackageDesc AS [Description] FROM dbo.WD_WebPackages AS P left outer join dbo.WD_Package_Category AS PC on P.PackageID = PC.PackageID left outer join 
dbo.WD_Category AS C on PC.CategoryID = C.CatID Order By [Catalog Item]

To retrieve only the currently active (i.e. not archived) catalog items, you would simply add a WHERE clause onto the end as follows:

SELECT P.PackageTitle AS [Catalog Item],C.CatName AS [Category],P.PackageDesc AS [Description] FROM dbo.WD_WebPackages AS P left outer join dbo.WD_Package_Category AS PC on P.PackageID = PC.PackageID left outer join 
dbo.WD_Category AS C on PC.CategoryID = C.CatID WHERE P.Deleted = 0 Order By [Catalog Item] 

Please note that the title of this report indicates that it retrieves the "Brief Description"; however, the SQL query is actually retrieving the "Full Description".  If you're populating brief descriptions in your catalog items, I would recommend changing the query to pull that instead of the full descriptions, since the rich HTML formatting of the full descriptions doesn't lend itself well to a table-based report.  If you want to make that change, just change P.PackageDesc to P.BriefDescription.

Hope that helps.  You can find more information about managing reports in the App Portal Admin Guide on HelpNet (https://helpnet.flexerasoftware.com/appportal2019r1/default.htm#helplibrary/AP_Reports.htm).

Anything expressed here is my own view and not necessarily that of my employer, Flexera. If my reply answers a question you have raised, please click "ACCEPT AS SOLUTION".