SQL Reporting Services

“There is no data in the database for @catalogitemid” in Power BI Report Server

After updating from the May 2020 release of Power BI Report Server to the January 2021 release, we started to receive this error during our report refresh schedules:

An error has occurred. There is no data in the database for @CatalogItemId

This was happening when multiple reports were refreshing at once, during our overnight schedules, as well as when multiple reports were manually refreshed at the same time. I found a post that seemed to confirm this (https://community.powerbi.com/t5/Report-Server/Scheduled-Refresh-error-quot-There-is-no-data-in-the-database/m-p/1505277) and suggested that you change your schedules so multiple reports don’t refresh at once – since were using shared schedules (including a schedule that we trigger via the API when our ETL finishes), that wasn’t an option for us.

To work around it, we had to refresh our reports one at a time, waiting until the currently one had completed before starting the next one – if you started the next one before the previous was complete, it seemed like about a 50/50 chance that the second report would fail with this error. This left a 0% chance that the overnight schedules that were attached to dozens of reports would even refresh 2-3 successfully.

I came across a post in the Power BI Community forum (https://community.powerbi.com/t5/Report-Server/There-is-no-data-in-the-database-for-catalogitemid/m-p/1721489) where somebody shared that it was caused by a code change to the code that loads the data model from the SQL database in the October 2020 release – switching a query hint in a Stored Procedure (GetCatalogExtendedContentData) from READPAST to NOWAIT (it was even annotated with a comment from the PBIRS developer that made it – nice work, whoever this was! This is a great example of a comment that has the “Why” and not the “What”, so it’s actually useful):

FROM [CatalogItemExtendedContent] WITH (NOWAIT) -- DevNote: Using NOWAIT here because for large models the row might be locked for long durations. Fail fast and let the client retry.

You can read more about query hints in the Microsoft Docs, but at a high level, READPAST (the previous code) causes a query to skip past and locked rows as if they’re not even in the table – NOWAIT, however, causes a query to fail with a lock timeout error as soon as it encounters any locked rows that it wants to read. The code change results in a failure any time we attempted to refresh a report while a previous refresh has any data model contents locked.

I couldn’t force this error to happen to one of our test environments, no matter how many reports I refreshed at once or large the data models were – given this, I wasn’t sure that fixing this code would resolve our error since the test environment had the code with the NOWAIT hint. I wanted to be sure, so I ran a SQL Profiler trace while refreshing some reports in production so I could see the error happen (WARNING – Profiler in production is usually a bad idea as it drags down performance, so proceed with caution here). Here were the three lines I saw that confirmed it:

SQL Profiler trace showing Lock Request Timeout exception followed by the SP call that’s failing

This was exactly where the forum said the code had changed – I was seeing the stored procedure throw an immediate Lock Timeout, which was the confirmation that I needed. I ran the following script with SQL Management Studio in the Power BI Report Server database to revert the code to the May 2020 query hint (red is the code I commented out and blue is the code I added):

 USE [PBIReportServer]
 GO
 ALTER PROCEDURE [dbo].[GetCatalogExtendedContentData]
     @CatalogItemID UNIQUEIDENTIFIER,
     @ContentType VARCHAR(50)
 AS
 BEGIN
     SELECT
         DATALENGTH([Content]) AS ContentLength,
         [Content]
     FROM
         [CatalogItemExtendedContent] WITH (READPAST) -- Reverting this back to May 2020 code. Commented line below is original in January 2021 version.
 --        [CatalogItemExtendedContent] WITH (NOWAIT) -- DevNote: Using NOWAIT here because for large models the row might be locked for long durations. Fail fast and let the client retry.
     WHERE
         [ItemID] = @CatalogItemID AND ContentType = @ContentType
 END
 GO

With this change deployed, we didn’t have a single report refresh failure over the weekend when our shared schedules ran! I’m not sure why I couldn’t force the issue in our lower environments, though I suspect it has something to do with either lower total data model size stored there, or index maintenance, or something else related to the data storage. I checked and all the SQL Server settings I could see where the same (as they should be) so I can’t confirm what additional situation causes this error (since clearly it doesn’t happen in every installation).

If you are able to use this successfully or have issues, please weigh in on the community post above or in the comments below!

Getting a list of Power BI reports and Data Sources from the Power BI API

In changing our reports to point from an older database server to a newer one, we needed a way to tell which reports we’d migrated so far and which ones still had data connections to the old server. Keeping a list of reports would be the easiest way to do this, but why do that when you can write a query to track it for you!

I came across this post that provides the outline of what I was looking for:

https://community.powerbi.com/t5/Report-Server/Get-all-connection-string-for-reports-from-power-bi-report/td-p/920392

It provided the following M-code, which uses the Power BI API to fetch a list of all reports on the server and all the datasource details (it doesn’t fetch the contents of the actual M-code behind the data sources – that would be pretty amazing). Nonetheless, pretty neat on its own (you need to replace “<YOUR URL>” in a couple of spots):

let

    Source = OData.Feed("https://<YOUR URL>/pbireports/api/v2.0/PowerBIReports", null, [Implementation="2.0"]),

    #"Expanded DataSources" = Table.ExpandTableColumn(Source, "DataSources", {"Id", "ModifiedBy", "ModifiedDate", "ConnectionString", "DataModelDataSource"}, {"DataSources.Id", "DataSources.ModifiedBy", "DataSources.ModifiedDate", "DataSources.ConnectionString", "DataSources.DataModelDataSource"}),

    #"Expanded DataSources.DataModelDataSource" = Table.ExpandRecordColumn(#"Expanded DataSources", "DataSources.DataModelDataSource", {"Type", "Kind", "AuthType", "Username", "ModelConnectionName"}, {"DataSources.DataModelDataSource.Type", "DataSources.DataModelDataSource.Kind", "DataSources.DataModelDataSource.AuthType", "DataSources.DataModelDataSource.Username", "DataSources.DataModelDataSource.ModelConnectionName"}),

    #"Added Conditional Column" = Table.AddColumn(#"Expanded DataSources.DataModelDataSource", "DS.Connection_String", each if [DataSources.ConnectionString] = null then "No Data Source" else [DataSources.ConnectionString]),

    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"CacheRefreshPlans", "AccessToken", "Roles", "ContentType", "Content", "ParentFolder", "Properties", "Comments", "AlertSubscriptions", "AllowedActions", "Policies", "DependentItems","Id", "ParentFolderId", "DataSources.Id", "DataSources.DataModelDataSource.ModelConnectionName"}),

    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"DataSources.ConnectionString", "DS.ConnectionString"}, {"DataSources.DataModelDataSource.Type", "DS.Type"}, {"DataSources.DataModelDataSource.Kind", "DS.Kind"}, {"DataSources.DataModelDataSource.AuthType", "DS.AuthType"}, {"DataSources.DataModelDataSource.Username", "DS.Username"}, {"DataSources.ModifiedBy", "DS.ModifiedBy"}, {"DataSources.ModifiedDate", "DS.ModifiedDate"}, {"Name", "Report"}, {"Path", "Report Path"}}),

    #"Add Report URL" = Table.AddColumn(#"Renamed Columns", "Report URL", each "https://<YOUR URL>/PBIReports/powerbi" & [Report Path] & "?rs:embed=true")

in

    #"Add Report URL"

This was a good framework, but I wanted to add a few things and combine multiple different servers together (My example shows a dev, qa, and prod server being combined, but they could be any number of servers or even both Power BI Server and SSRS if you wanted):

The M-code shown earlier became the highlighted item above – I converted it to a function (“PBI Data Sources” at the top of the “PBI Data Sources Function” group) that accepted both a “Server URL” (replacing the “<YOUR URL>” portion in the initial query) and a “Server Zone” allowing you to name the server in the results. Once you have the function, we move up to the last three objects in “Each Server”, which each call the function with different parameters (for each of my server zones), and then combine them into a single dataset (“Combined Data Sources”). Finally, the results are split into a list of Reports and a list of Data Sources that you can pull into your Power BI model.

Once of the issues I ran into was that I didn’t have access to all the reports on my server – those broke my dataset with the following message:

DataSource.Error: ODate Request failed: The remote server returned an error (500) Internal Server Error

Interestingly, it wasn’t a “Permission Denied” error, but a 500 server error. I got around it by adding some code to drop any rows where I received an error by using the “try otherwise” error handing in M, something I’d never used before:

= Table.SelectRows(#"Sorted Rows", each ((try [DataSources]{0}[Id] otherwise null) <> null))

This code checks to see if it can view the very first value within the “DataSources” field, and if it can’t, it returns null and then the row gets filtered. This isn’t the most elegant way to do this – it drops any reports I don’t have access too, rather than calling them out, but it got the job done and allowed my dataset to move forward without errors.

Once the data is in the model, you can use some simple visualizations to see what types of connections you have, where you’re connecting, and what user those connections are set up to use (so you can see if every one of your connections is using the correct proxy users).

I hope this helps you get a handle on all the data sources you’re using, and make sure that everything is using the correct settings. I’ve included the empty PBIT file at the end of this post for anybody that wants to connect to their data and see what it looks like (when you open the file, it prompts you for two variables – you can put whatever you want there, but it’s because I’ve left the source query in the file).

If you do end up using this to create something, please let me know what as I’d love to see it in action!

Download the PBIT file

Viewing active (and missing) permissions in SSRS and Power BI Server

To see who has folder/report level permissions in SSRS and Power BI Server, you have to join a couple of tables together – I started with a query I found at StackOverflow and built onto it – here’s my query (it pulls a bit more detail and shows not just permissions granted, but those not granted):

select  case E.Path
		when '' then '/'
			else E.Path
		end as ItemPathAndName,
		concat(LEFT(E.Path, case
			when len(E.Path)-len(E.Name) = 0 then 0
				else len(E.Path)-len(E.Name)-1 end),'/'
		) as ItemPath,
		E.Name as ItemName,
		case E.Type
			when 1 then 'Folder'
			when 13 then 'PBI Report'
			else concat('Unknown (',Type,')')
		end as ItemType,
		case e.PolicyRoot
			when 0 then 'Inherited'
			when 1 then 'Customized'
			else 'Unknown'
		end as PermissionSource,
		C.UserName,
		D.RoleName,
		--D.Description, -- Uncomment for role description
		convert(bit, case when
				a.id is null then 0
				else 1
		end) as ValidPermission
from dbo.Catalog E
cross join dbo.Users C
cross join dbo.Roles D
   left
   join dbo.PolicyUserRole A
     on E.PolicyID = A.PolicyID 
	and A.RoleID = D.RoleID
	and A.UserID = C.UserID
order by Path, C.UserName

Rather than just selecting the permissions, this query actually shows all possible permissions, and shows where users have or don’t have them. In my case, I wanted to see a list of users on the server that lacked access to specific reports and the “show me the permissions” query wouldn’t do that – additionally, this query can show you all the places that your permissions are manually set, rather than inherited.

To interpret them, I copied the results to Excel, created a pivot table, and then filtered using PermissionSource=Customized (to see all the custom permissions) or ValidPermission=0 (to see the places where people didn’t have permission to view an item).

The crossjoins can make the resultset a bit large, but it worked for me. If you want to filter things down a bit, you can add a WHERE clause near the end to folder those columns for the specific situation you’re looking for.

Passing multi-value report parameters to SSRS using the URL querystring

Passing parameters to reports in SSRS using the URL can be really helpful, especially when you want to link to a report with some of the data aleady filled out. We had an email alert that some accounts in active directory were incorrect, and we wanted a way to link to a report that provided some additional detail for those accounts. Querystring parameters aren’t very well documented (and I couldn’t find anything on multi-value parameters), and I hope this saves you some time if you’re in the same situation.

There’s a pretty good reference on Technet, but it’s got some gaps (for me, the biggest was even mentioning multi-value/multi-select parameters). The basic syntax is pretty straightforward – normally, a report is called with a URL like the following:

http://YourSsrsServer/Reports/Some%20Folder/YourReportName

From there, you can select the parameters and run the report. To provide a link to the report with the parameters already filled out (if they’re blank or if you gnat to override the defaults), just append this to the end of the URL:

?ReportParam1=SomeValue&ReportParam2=SomeOtherValue

You can pass any type of parameter with this same syntax – text, datetime, integer, float, or boolean (use Yes/No). Your full url would look like this:

http://YourSsrsServer/Reports/Some%20Folder/YourReportName?ReportParam1=SomeValue&ReportParam2=SomeOtherValue

To do a multi-value parameter, you repeat the name of the parametr to provide it multiple values:

http://YourSsrsServer/Reports/Some%20Folder/YourReportName?ReportMultiParam=SomeValue&ReportMultiParam=SomeOtherValue

If this is the only parameter on the report and it doesn’t need any other user input, the report will automatically execute because it has everything it needs. If you want the report to wait for the user to hit “View Report”, you need to leave at least one parameter empty.

Two quick notes if you’re having trouble:

Use the parameter name, not the prompt text. If your parameter is called “Office” but the prompt says “Which office?”, use “Office”. Parameter names aren’t always intuitive and you may need to open the report in Visual Studio to get these names if you can’t guess them.
In the same way, you need to use the field’s value, not the label. If the dropdown contains the full name of the office, but the value behind the list is the office number, you’ll need to provide the numbers on the querystring.

Good luck!

Microsoft Connect item for an SSRS pre-report canvas

Current state:

About six months ago, I submitted a connect request for SQL Server (my first one) to suggest an improvement to the SSRS interface – a customizable canvas that’s displayed before an SSRS report renders, while parameters are being entered. My issue was that there was information I wanted report users to have (like expected wait time, suggestions for the parameters, the report title and a short description that might help them, etc.) and there was no way to get it to them.

With a giant empty screen staring at them, it seemed like a great place to add that information:

The suggestion:

Ideally, while the user is considering parameters, I’d love to see something like this (ignoring that it’s not a super-photogenic report):

This would be a great place for details about the report that help the user before (or while) they run it, like:

The name of the report (not always clearly visible in the URL or browser title bar, especially when you’re Sharepoint-integrated. Especially helpful if they’ve left multiple report tabs open or if report generation fails, they’re often left with a white screen and no easy way to tell the tabs apart.
A short description of the report, including when you’d use it or any notable caveats.
An estimated runtime – this isn’t always possible exactly as parameters vary, but even a general estimate (or better yet, an average or a 95% confidence range, pulled from the report server database) would be better than the complete blank they get today.
Suggestions/restrictions on the parameters – If a report can’t be run for all offices at once, you can say so here. If running it for everybody at once means you can’t export to Excel because it’s too large, let the user know.
A mock-up/screenshot of the report – so they user can see if it’s the one they want. They may be looking for a report with a specific chart, and waiting for 3 minutes for each one to render may not be ideal.
A link to any other documentation/resources, or related reports, or pretty much anything else.

It wouldn’t need to be anything fancy – I’d even settle for text you can format, or some kind of additional panel on the report that you can design, but isn’t rendered with the report itself. Access to data (to pull estimated on execution times or dynamic owner/description details, for example) would be nice, but not required – I’d rather have a design-only, no-data canvas than nothing at all.

The plea:

Thanks to the motivation from the folks at Brent Ozar Unlimited, I’m asking for your help in voting for this suggestion (if you think it would be beneficial, of course). As part of T-SQL Tuesday, they’ve asked people to find a connect request that they’d like to see filled and post about it, so here I am asking humbly for your support. : )

If you like the idea, please vote for it – if you don’t, please comment and let me know why not (I’m always open to understanding the opposition). There are a ton of great Connect items out there and this is only one, but I think it would help out (especially for our longer-running reports).

Thanks for your help!

Direct URL for the Connect item: https://connect.microsoft.com/SQLServer/feedback/details/2809098/ability-to-customize-report-splash-screen-during-parameter-submission

Side note:

I’ve attached the two images (current/proposed) to my connect item three different times and they’re still not showing up there – if anybody knows how to get those images published, people can see what I’m talking about when I describe my suggestion! : )