Discovering which Views are Available for Reports

1 minute read time.

A customer wanted to find out which Views were available for use in Reports. When I looked at the list of views for a particular entity (e.g. Company) there was no obvious way of seeing which view could be used to create a new report. To discover that I found I needed to enter each view definition screen in turn to see if the view can be used.

I didn't want to do that, so I created a simple SQL statement that would return the names of the views I needed.

[code language="sql"]
SELECT TOP (100) PERCENT dbo.vCustom_Captions.Capt_Code, dbo.vCustom_Captions.Capt_Family, dbo.vCustom_Captions.Capt_IntegrationId,
dbo.vCustom_Captions.Capt_UK, dbo.Custom_Views.CuVi_Entity
FROM dbo.vCustom_Captions WITH (NOLOCK) INNER JOIN
dbo.Custom_Views ON dbo.vCustom_Captions.Capt_Code = dbo.Custom_Views.CuVi_ViewName
WHERE (dbo.vCustom_Captions.Capt_Family = N'ReportViews')
[/code]

The next step was to create a view using this, then create a report based on that view.

I created this new view under the System entity within the customization screens.

I had to make this new view available for reports!

I could then create a new report based on this view.

Running the new Report provided me with the information I needed

I have addressed a similar subject in a previous article "What Reports have Charts?" In that article I discussed how I could find out which reports included Charts that could potentially be included in Interactive Dashboard gadgets.