Behind the scenes with SSRS and Vision, Part 2

In my previous post, I covered the communication between the Vision application and and the SSRS Report File (.rdl). I discussed the AR Ledger report specifically, because that is my favorite. With this post, I hope to discuss Vision's interaction with the SSRS reporting server and focus on the reportserver database.

When the AR Ledger report is configured and executed using the Vision interface, many calls are made to the SSRS layer, and many things are happening within the reportserver database.

From a high level, the following stored procedures are called within the reportserver database. I may have these a little out of order, but understanding the big picture is the intent here. In addition, I've captured what each call looks like, but I have ommitted or simplified parameters passed to the stored procedures.

1) CreateObject
2) AddDataSource
3) ObjectExists
4) LoadForDefinition
5) GetReportParametersForExecution
6) GetAllProperties
7) GetCompiledDefinition
8) ObjectExists
9) CreateSession
10) InsertUnreferencedSnapshot
11) SetSessionData

CreateObject
The CreateObject stored procedure is used to create a specific version of the report for your session that you are unknowlingly creating behind the scenes. A record is created within the report catalog (catalog table) that includes a unique identifier, the name of the report, the path of the report and the location as well.

The path of the report contains the Vision installation directory along with the version of vision, the weblink name used and the username you are running the report as. IE, @Path = '/vision/weblink name/username/....'

exec CreateObject @ItemID='61A90D67-9B14-466E-98F0-3E967F646B86',@Name=N'Accounts Receivable Ledger',@Path=N'/Vision/WEB_LINK_NAME/USER_NAME/AccountsReceivable/AR Ledger__BAA6DDF0E57149E09925692F96EF950A

AddDataSource
Every report can have at least 1 data source. While the report is being created via the CreateObject Stored Procedure, the AddDataSource stored procedure stores the appropriate data sources used within the report along with the unique identifier created in the CreateObject Stored Procedure.

exec AddDataSource @AuthType=1,@DSID='A5CB6F20-519F-4863-8505-3B16EE341FCB',@ItemID='61A90D67-9B14-466E-98F0-3E967F646B86',@Name=N'DefaultDataSource',

ObjectExists
Checks the catalog to make sure that the report exists along with the appropriate authorization level.

exec ObjectExists @Path=N'/Vision/WEB_LINK_NAME/USER_NAME/AccountsReceivable/AR Ledger__BAA6DDF0E57149E09925692F96EF950A/Accounts Receivable Ledger',@AuthType=1

LoadForDefinitionCheck
According to Microsoft, this stored procedure is used "For loading compiled definitions to check for internal republishing, this is done before calling GetCompiledDefinition or GetReportForExecution".

exec LoadForDefinitionCheck @Path=N'/Vision/WEB_LINK_NAME/USER_NAME/AccountsReceivable/AR Ledger__BAA6DDF0E57149E09925692F96EF950A/Accounts Receivable Ledger',@AcquireUpdateLocks=0,@AuthType=1

GetReportParametersForExecution
This stored procedure grabs the current parameter set or a recent parameter set from the reporting cache.

exec GetReportParametersForExecution @Path=N'/Vision/WEB_LINK_NAME/USER_NAME/AccountsReceivable/AR Ledger__BAA6DDF0E57149E09925692F96EF950A/Accounts Receivable Ledger',@AuthType=1

GetAllProperties
Retrieves all properties for a given report including server information, user information, creation date and xml options.

exec GetAllProperties @Path=N'/Vision/WEB_LINK_NAME/USER_NAME/AccountsReceivable/AR Ledger__BAA6DDF0E57149E09925692F96EF950A/Accounts Receivable Ledger',@AuthType=1

GetCompiledDefinition
This procedure is used for creating and calling up a snapshot of a report.

exec GetCompiledDefinition @Path=N'/Vision/WEB_LINK_NAME/USER_NAME/AccountsReceivable/AR Ledger__BAA6DDF0E57149E09925692F96EF950A/Accounts Receivable Ledger',@AuthType=N'1'

CreateSession
Creates and updates the session for a given report run.

exec CreateSession @SessionID='kehsjb55qpdklmj4xsiantjt',@ReportPath=N'/Vision/WEB_LINK_NAME/USER_NAME/AccountsReceivable/AR Ledger__BAA6DDF0E57149E09925692F96EF950A/

InsertUnreferencedSnapshot
This stored procedures either writes a snapshot to the snapshot table or temporarily saves the snap shot in the reportservertempdb database.

exec InsertUnreferencedSnapshot @EffectiveParams=N'declare @p20 xmlset @p20=convert(xml,N'20.....

SetSessionData
Similar to the CreateSession procedure, this creates or updates the session.

exec SetSessionData @SessionID='kehsjb55qpdklmj4xsiantjt',@ReportPath=N'/Vision/WEB_LINK_NAME/USER_NAME/AccountsReceivable/AR Ledger__BAA6DDF0E57149E09925692F96EF950A/Accounts Receivable Ledger',@Timeout=600,@AutoRefreshSeconds=0,@OwnerSid=@OwnerName=.......


So far we have covered Vision's interaction with the .RDL file, and with this post we have covered the interaction between the Vision, the .RDL file and the reportserver database. In my next post, I will complete this series tying the entire picture together.