Category Archives: Reporting

Reporting and the Edge browser

I recently updated my dev machine to Windows 10. I like it a lot more than Windows 8.1. I was keen to see whether Reporting works in the new Edge browser and it turns out it works just fine. Apart from a strange inability to edit a data source in Report Manager.

When you update anything on a data source you have to re-enter the login’s password but in Edge the page keeps insisting the password is wrong and refuses to save the change.

So I recommend you edit data sources in Internet Explorer for now.

Reporting on report usage revisited

Nearly three years ago I wrote about how to query the ReportServer database and see who is running which reports. Since then I’ve made it a bit more focused. Which reports are being used the most? Which users are the busiest and which reports are they looking at? Here’s what I came up with.

(NB: This is for 2008 or 2008 R2 Reporting Services in standalone mode. I’ve no idea what you get in SharePoint mode.)

Report Heatmap

Which reports are being used the most?

If you click on a report you see…

Report Usage

Who has been using that report?

Report User Heatmap

Who are the busy users?

If you click on a user you see…

Report User History

Which reports has that user has been running?

Feel free to download the RDL files and stored procedures here and use them yourself.

Failing report subscriptions

We had an interesting problem while I was on holiday. (Why doesn’t this stuff ever happen when I’m there?) The sysadmin upgraded our Exchange server to Exchange 2010 and everone’s subscriptions stopped. My Subscriptions showed an error message saying that the email address of one of the recipients is invalid. When you create a subscription, Reporting puts your Windows user name into the To field and most users have no permissions to edit it. By default, Reporting leaves it up to exchange to resolve that into an email address. This only works if Exchange is set up to translate aliases or ‘short names’ into email addresses. It turns out this leaves Exchange open to being used as a relay so it is disabled out of the box. You now have three options:
  1. Open up Exchange. That would be bad.
  2. Give all Reporting users the ability to edit the To field in a subscription. a) They shouldn’t have to, it should just work. b) They don’t really have any business subscribing anyone but themselves.
  3. Fix the report server to add the domain. This looks like the right choice and it works for us. See below for details.
  • A single email domain name.
  • A clear relationship between the Windows user name and the email address. eg. If the user name is joebloggs, then joebloggs@domainname needs to be the email address or an alias of it.
Warning: Saving changes to the rsreportserver.config file will restart the Report Server service which effectively takes Reporting down for around 30 seconds. Time your action accordingly.
Edit the file rsreportserver.config (most probably in the folder ..\Program Files[ (x86)]\Microsoft SQL Server\MSRS10_50[.instancename]\Reporting Services\ReportServer). There’s a setting called DefaultHostName which is empty by default. Enter your email domain name without the leading ‘@’. Save the file. This domain name will be appended to any destination addresses that don’t have a domain name of their own.

How to re-order report parameters

I added a parameter to a report today, which put it the end of the list the user fills in before running the report. I wanted to move it higher up the list but they are presented in a tree structure. You can edit each parameter but there’s no sign of dialogue that lists them (where you might be able to rearrange them). I looked in the help but couldn’t find anything. After a bit of trial and error it turns out you just highlight the parameter and use Ctrl and the up and down arrow keys. Easy when you know how!

URLs to reports with non-default parameters

You can give somebody a hyperlink to get to a Reporting Services report and that’s all they need if there are no parameters or if the default values are what they want. But how do you specify values for parameters? It turns out to be pretty simple but the documentation is a little vague on some of the details.
We have a Sales League report where you can look at a Sales team’s figures and see how the team members are doing. In Report Manager the user has to choose their team and run the report. I was asked to supply links on the intranet home page for each team to go straight to their figures. It turns out all I had to do was grab the URL (NB: the ReportServer URL, not the Report Manager URL) and add the parameters as name value pairs but there are a few things that the documentation doesn’t make clear:
  • The name is the internal name, not the display name. From the example given it could have been either.
  • The value is the actual Value value, not the Label value (ie. again, it’s the internal value not the display value.)
  • You only need to supply name-value pairs for the parameter(s) you want to change. Our report has four parameters and I only needed to change the third one, so I just added the name-value for that one and the rest stuck with their default values.
Help links:

2008 R2 Report Pagination

I thought it was all too good to be true. We upgraded our reporting server in place from 2005 Std to 2008 R2 Std and we’ve hit a major problem. We have a few reports that are about 60 pages. On 2005 the report would render page 1 of 60 pretty quickly. Now it produces page 1 of 2 that is miles long and takes minutes to render. If you have enough RAM and CPU power in your PC to cope. My dev machine can handle it but the average user’s machine just dies. There’s a brief spike on the server and then it’s all your PC that’s doing the work. Seeing as I’m the only person who can run these reports at the moment, guess what I’ve been spending half my time doing?
If you encounter the same problem, please vote this bug report up, or if you know the answer, please let me know. Thanks.

Reporting security audit

Today I set out to produce a list of who has access to each report so I could get this audited by the business management. I didn’t think it would take long. Download a couple of scripts, evaluate them, pick a good one, execute it, and on with the next job. After all, this is crucial stuff. There’s no point agonising over reducing users’s rights in the database engine to the minimum possible if you then go and give them access to reports that summarise all the data in a readable, saveable, printable form. Clearly then, security auditing of Reporting Services must be a common practice. Or so I thought.

Well, I found bits of the puzzle but I didn’t see anything packaged up and ready to go. I’m sure there are tools you can buy but I kind of resent paying serious money for a product that basically just queries my own data. I prefer source code where I can see what’s going on and learn something new.

So I pulled the pieces together, solved a few problems myself and came up with a stored proc that goes through Reporting’s security records for each report, resolves any domain groups recursively down to lists of users, and lists it all out. If you have a look through, you’ll see Reporting doesn’t make this security data easy to get hold of. Also, I had to add a caching mechanism for domain group membership because it took ages to query the domain that many times.

set quoted_identifier on
set ansi_nulls on

if exists (    select     1
        from     dbo.sysobjects
        where     id = object_id(N'dbo.rpt_ITReportsSecurity')
            and objectproperty(id, N'IsProcedure') = 1    )
    drop procedure dbo.rpt_ITReportsSecurity

create proc dbo.rpt_ITReportsSecurity

Version:    1.0
Date:        01/12/2009

set nocount on

declare @FolderName        nvarchar(850)
    , @ReportName        nvarchar(850)
    , @ReportPath        nvarchar(850)
    , @idoc            int
    , @xmlfile        varchar(8000)
    , @GroupUserName    varchar(50)
    , @Role            varchar(50)
    , @GroupNoDomain    varchar(50)
    , @i            int
    , @cmd            nvarchar(2000)

create table #perms
    GroupUserName    varchar(50)
    , Role        varchar(50)

create table #allperms
    FolderName    nvarchar(850)
    , ReportName    nvarchar(850)
    , GroupUserName    varchar(50)
    , [Role]    varchar(50)

create table #temp_domaingroup
    [output] varchar(8000)

create table #group_member
    [Group]        varchar(50)
    , Member    varchar(100)
    , [Role]    varchar(50)

-- Loop through reports
declare report_cursor cursor
local forward_only
select    f.Name as Folder
    , r.Name as ReportName
    , r.Path as ReportPath
from    ReportServer.dbo.Catalog r with (nolock)
    inner join ReportServer.dbo.Catalog f with (nolock) on f.ItemID = r.ParentID
where    r.Type = 2 -- Report
order by f.Name
    , r.Name

open report_cursor

    fetch next from report_cursor
    into     @FolderName
        , @ReportName
        , @ReportPath

    while @@fetch_status = 0

        -- Get the user/groups and their roles from the XML

        truncate table #perms;

        set @xmlfile = (select    sd.XmlDescription
                from    ReportServer.dbo.Catalog c
                    inner join ReportServer.dbo.Policies p on p.PolicyID = c.PolicyID
                    left join ReportServer.dbo.SecData sd on sd.PolicyID = p.PolicyID and AuthType = 1
                where    c.Path = @ReportPath
                    and p.PolicyFlag = 0) --as far as I can tell, this means not a system policy

        exec sp_xml_preparedocument @idoc output, @xmlfile

        insert    #perms
            , [Role])
        select    GroupUsername
            , Role
        from    openxml    (@idoc, N'/Policies/Policy/Roles/Role',2)
            with    (GroupUsername varchar(50) '../../GroupUserName'
                , Role varchar(50) './Name')

        exec sp_xml_removedocument @idoc;

        -- Loop through users/groups
        declare group_cursor cursor
        local forward_only
        select    GroupUserName
            , [Role]
        from    #perms
        where    GroupUserName <> 'BUILTIN\Administrators'
            and [Role] <> 'View Folders Role';

        open group_cursor

            fetch next from group_cursor
            into     @GroupUserName
                , @Role

            while @@fetch_status = 0

                -- Strip the domain off the user/group

                set @GroupNoDomain = @GroupUserName
                set @i = charindex('\', @GroupNoDomain)

                if @i > 0
                    set @GroupNoDomain = substring(@GroupNoDomain, @i + 1, 100)

                -- Users in the groups are cached so we only need to query the domain once for each group

                if not exists (select 1 from #group_member where [Group] = @GroupNoDomain)
                    -- Query the domain

                    truncate table #temp_domaingroup;

                    set @cmd = 'dsquery group -name "' + @GroupNoDomain + '" | dsget group -members -expand';

                    insert    #temp_domaingroup
                    exec xp_cmdshell @cmd;

                    if exists (select 1 from #temp_domaingroup where [output] like 'dsget failed%')
                        -- It's a user, not a group, so add it to the cache

                        insert    #group_member
                            , Member
                            , [Role])
                        values    (@GroupNoDomain
                            , @GroupUserName
                            , @Role);
                        -- Add the users in the group to the cache
                        insert    #group_member
                            , Member
                            , [Role])
                        select    @GroupNoDomain
                            , substring(x.Member, 5, len(x.Member) - 4)
                            , @Role
                        from    (
                            select    substring([output], 1, charindex(',OU=', [output], 1) - 1) as Member
                            from    #temp_domaingroup
                            where    [output] is not null
                            ) x;

                -- Add the user or group users and their roles to the permissions list

                insert    #allperms
                    , ReportName
                    , GroupUserName
                    , [Role])
                select    @FolderName
                    , @ReportName
                    , Member
                    , [Role]
                from    #group_member
                where    [Group] = @GroupNoDomain;

                -- Get the next cursor row
                fetch next from group_cursor
                into     @GroupUserName
                    , @Role


        close group_cursor
        deallocate group_cursor

        -- Get the next cursor row
        fetch next from report_cursor
        into     @FolderName
            , @ReportName
            , @ReportPath


close report_cursor
deallocate report_cursor

select    *
from    #allperms
order by ReportName;