Creating an Atom Feed from Vision reports

Vision 6.1 SP4 now supports SSRS 2008 R2. R2 has many new features that include Atom rendering extensions. This means a report can be a data feed. What this really means is that a report can be a data source that can be used by any application that can consume a feed.

For example, Excel 2010 with PowerPivot can consume an Atom Feed. For the most part, anything that can consume XML can also consume an ATOM feed.

I tested this out by going to the ReportManager URL for our SSRS 2008 R2 reporting server that is used by Vision. The default URL for most R2 installations usually looks something like this: http://YOUR_REPORT_SERVER_NAME/Reports_SQL2008R2

Find your Vision report and run the report. You should notice an orange icon in which I have highlighted with a red box around it. This is the export to data feed icon.

See the screen shot below.

When you click the orange icon, you will be prompted to save the atom feed. An improtant note about the screen shot below is if you have Excel 2010 installed from the machine you are exporting the data feed to, you will have an "Open" button to open directly into excel.

I chose to save the Atom file, which you then can open in any text editor to view, or you can view directly in your browser like the screen shot below.

Although it may be hard to detect based on my poor screen shot, you will see a URL listed in the collection XML tag. If you replace the "&" to "&" in the URL, copy and paste it into your browser, you will get an enormous XML data dump of the contents of the report. This is the XML output from the data feed.

From here, you can choose to open the XML or just the .atomsvc file directly in Excel 2010, or in any other data feed consumer and your Vision reports can be a datasource for the Web 2.0 revolution.

Enjoy!!!

Vision 6.1 SSRS Reports

As I’ve mentioned in previous posts, when Deltek announced that they were switching to SSRS for their reporting platform, I was ecstatic. I thought how much more wonderful the world was going to be by moving off of a heavy I/O dependent reporting architecture like Actuate and moving to a memory hungry architecture like SQL Server Reporting Services.

In previous versions of Vision that use actuate, administrators would have to crawl through the report log grid in report administration to cough up SQL that was being run by reports. Or, if you were clever enough, you could piece together some breakpoints and look through the SQL variables in the actuate designer.

Now, The SSRS report .rdl file themselves contain enough of the SQL that you can gather what the reports are trying to do. Deltek mentioned a couple of years back, that this was purposeful in order to help users understand what reports were trying to do and to allow easier custom development. For those of you who have authored custom actuate reports, it was not an easy task.

It’s great that the SQL is readily available and viewable in the Vision .RDL files, but I started wondering why such complicated SQL was nested in the Reporting Layer and kept out of the database in the fashion of Stored Procedures.

I reached out to some folks in the know and they gave me a pretty reasonable answer as to why Deltek may have coded it in this fashion. In short, there’s a lot of dynamic query manipulation that occurs when various grouping, sorting and columns options are chosen. So, it’s easier to manipulate the SQL within the .RDL file. In addition, if the Vision .RDL files used stored procedures, any bug fixes to the reports themselves would require both an .RDL file change and a database change for the stored procedures if it was needed.

If you open the .RDL files in the visual studio .net toolset, you will be happy to see tons of SQL that you can copy out of the xml definition and into your SQL client tool or your own custom report to change how you wish.

Happy customizing!!

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.

Deltek Vision Actuate Reporting

I think we were all excited when Deltek announced that Vision 6.1 would use SSRS as it's reporting technology. But I was also frustrated because I had worked so hard to understand the Actuate reporting framework and had even dug deep into the way Vision integrated with the Actuate Reporting Server. So with this post, I wanted to focus on how Vision works with Actuate at the code level before following up with a post celebrating how Vision works with SSRS.

From a high level, the Vision application goes through the following steps when calling a report.
1) Construct a Vision State Object
2) Instantiate a Vision Authenticated Vision Object
3) Consume the Actuate Web Service
4) Create an XML String that defines the report to be called along with column and grouping definition
5) Define the output of the Actuate report.
6) Pass the object returned from actuate to the Vision Actuate Report Viewer.

1) and 2) Deltek.Vision.Ancestors
The vision state object is the object Deltek carries around in memory after the authentication process. The authentication process is handled by through a call to the GetNewSessionID() method on the Vision State object.

3) Actuate Web Service
The next part of the process is that the Vision application consumes the Actuate Web Service. You can find the definition for the actuate web service by browsing the actuate web server URL, http://servername:portnumber/wsdl

Below is a screen shot if I were trying to add the Actuate web service as a web reference to a web project within Visual Studio.




So a new Actuate Web Service API class is constructed. Vision defines a couple of properties such as Time out settings, header definition, Actuate report server URL, Locale and the targeted Actuate Report Volume.

Next Vision creates an Actuate loginRequest and loginResponse and populates various properties required for authentication to the actuate report server such as the Actuate ID, the Actuate Admin password, the Actuate Report Volume..

Next Vision encrypts the active Portal Session information and the report Session Portal Information.

4) Create XML string defining report, column and grouping information.

Vision internally builds an xml string that either contains xml stored in the database as your favorite or information from your setup within the dialogue box that contains information such as which period to run it for and columns to include and grouping.

You can find the xml string used by reports run in your system under utilities > report administrator > report logging

An Report Request and Response object is created properties about how Actuate should handle the request and ultimately how the response should be handed back. Things like output type, file archival rules, progressive viewing and most importantly, the report options for the report being requested at hand. It's important to note that the report options will be defined and ultimately part of the xml string that is passed between Vision and Actuate.

5) Define the output of the Actuate report.
Will the report be an excel file, a pdf file, or an actuate report file?

6) Pass the object returned from actuate to the Vision Actuate Report Viewer.

The response object is internally passed to a viewing page within the Vision code structure and that is where the actuate report is rendered.

Now that we understand the connection between Vision and Actuate, my next post will discuss the integration between vision and ssrs. You can't understand where you are headed if you don't know where you came from.

Enjoy...