Behind the scenes with SSRS and Vision, Part 3

In this last installment, I will briefly discuss the interaction between vision and the SSRS service layer. So far I have discussed the interaction between Vision and the .rdl report file in part 1 of this series, and vision/report file and the reportserver database in part 2.

Assuming you have permissions, log into your reporting server via the following url.

http://YOUR_REPORT_SERVER_NAME/reportserver. Follow the link by clicking on each link (folder) you encounter. More than likely, each link (folder) will represent the structure of your installation. For example, it will more than likely be the following, install_directory/web_link_name/list_of_all_users_who_have_run_reports/Report_folders/ Report_instance_name./

Below is an example of when you finally navigate to the report instance you ran. Click on the image to take a closer look. You'll recognize the path of the report because it existed in several calls to the database I documented in my previous post.

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

SSRS has been instructed by Vision to create and save the AR Ledger Report that was created through the Vision interface. This report is re-viewable through the native SSRS interface from outside of Vision. If you remember back in the actuate days, you could log into the Actuate Report Server/Report Volume through a java application and actuate provided the same functionality.

From a high level, I have tried to show you the interaction between Vision and the Report file, the reportserver database, and the SSRS service layer. Please feel free to respond to this post or email me directly if you have any questions or objections. Enjoy....

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.

Behind the scenes with SSRS and Vision, Part 1

In my previous post, I documented how Deltek Vision integrated with Actuate through Actuate's web service API. As many of you are happy to know, Deltek Vision 6.1 now uses SQL Server Reporting Services as it's reporting platform. My hope with this article is to give you a brief insight into the technical workings between Vision and SSRS.

The first thing I always do when investigating a new feature available from the Vision menu, is I look at the menu in design mode to see what's being called from behind the scenes.

Click on the image to take a closer look. BTW, The A/R reporting in Vision is my personal favorite.



You'll notice within the actions box that the open action Deltek.Vision.Report.Client.VisionOpenReport is called. The Deltek.Vision.Report.Client.VisionOpenReport Object and it's methods are defined within the Deltek.Vision.Report.Client.dll. The dll is probably downloaded when you log into Vision for the first time as you see something similar to the images below.



Or...



Because Deltek has create a reporting hierarchy that closely mimics the reporting menu structure, it's easy to guess where the Accounts Receivable files are. While searching for those files, you'll be happy to realize that the reporting folders contain actual Visual Studio .Net solutions. No more actuate report designer. Yeah!

If you have trouble locating the solution, do a search for the file Deltek.Vision.AccountsReceivable.Reports.sln on your Deltek Vision server or on your local machine.

Because the A/R reporting in Vision is my favorite, naturally the AR Ledger report is my favorite report. So I wanted to take a closer look at the rdl file for the AR Ledger report, and I looked through the XML definition.

You'll notice two more calls to long object names.
Deltek.Vision.ReportingAccountsReceivable.Client.dlgRepOpt_ARLedger and
Deltek.Vision.ReportingAccountsReceivable.Server.ARLedgerBO.

In my next post, I'll discuss how the report file interacts with these objects within their respective dll's.