Reporting - All Enabled titles including extras

[sara_b]

Is there a way to pull a report of All Enabled titles, their long and short descriptions, meta tags and pricing information? Obviously there's not a canned report that does this. Do you know what tables/sources would be needed for this? 

[CharlesW]

Most, if not all of what you are looking for should be in the WD_WebPackages table as this is where most of the catalog item data is stored... A query similar to the following should give you what you need:

select Packagetitle, PackageDesc, BriefDescription, CatalogCost from WD_WebPackages where PackageVisible = 1

Not sure what you were looking for with regards to "meta tags".

[sara_b]

---

@CharlesW wrote:

Not sure what you were looking for with regards to "meta tags".

---

meta tags = keywords. Sorry about that.

[CharlesW]

Not sure of the best way to write the query, but the keywords are stored in the WD_WebPackageKeyword table. There will be one row for every keyword.

[jdempsey · Accepted solution]

Perhaps something like this...

SELECT DISTINCT
    wp.Packagetitle AS [Title],
    wp.BriefDescription AS [Brief Description],
    wp.PackageDesc AS [Full Description],
    wp.CatalogCost AS [Cost],
    STUFF(
        (
            SELECT ', '+kw.KeyWord  AS [text()]
            FROM WD_WebPackageKeyword kw
            WHERE kw.PackageID_FK = wp.PackageID
            ORDER BY kw.KeyWord
            FOR XML PATH ('')
        ), 1, 2, '') AS [Keywords]
FROM WD_WebPackages wp
WHERE wp.PackageVisible = 1

Keep in mind that PackageDesc can contain HTML, so if you are trying to display this in an HTML-formatted report, that may not work so well.  Either limit your full description entries to text only, encode the results from your SQL query before rendering it, or exclude that column from your report.