Adjust SQL Queries in RDL Reports

Project Description

We needed to adjust SQL queries in RDL reports that obtain data from a Tenrox database and utilize Microsoft Reporting Services. Specifically, we needed to extend the date range for some reports.

Completion Notes

Client is using a custom time tracking and invoicing solution that utilizes the following components:

• A Tenrox database and data entry mechanisms.
• A set of custom-developed RDL reports that obtain the data from the database.
• External Windows server that utilizes Excel automation to build the final result. RDL reports are exported as Excel documents and sent by email. They are used as input for Excel automation tasks.

More on RDL Reports

All RDL reports were created originally in Visual Studio 2010. During report creation process, a machine was set up with the following parts:

• MSSQL server.
• Tenrox database imported locally.
• Visual Studio 2010 with Business Intelligence - Reporting Services - Report Server Project Wizard template. This wizard automates creating an RDL file that can later be used with Microsoft Reporting Services.

Creating a Query

The critical part in the report creation process is figuring out which SQL query to use with each report. As Tenrox is a closed source solution, there was some reverse-engineering and guesswork involved, but what helped greatly was a Tenrox collection of RDLC reports (client-side reports), that their web application uses.

The process roughly looked like this: Experiment with the local database to construct a query. Use example code from the RDLC collection from Tenrox, which uses stored procedures available in our backup database. Basically, we tried to find a closely matching example for a report, then re-engineer its code.

Creating an RDL Report

Once the query is written and debugged, the procedure of creating an RDL report is simple:

• Start Visual Studio 2010 and create a new project using this template: Business Intelligence - Reporting Services - Report Server Project Wizard.
• In it, use a local data source, with the query. Finish the wizard, it generates an .RDL file.
• Upload the file on Tenrox reporting website. In there, change report properties to use the Shared data source available. At this point the report is ready to run.
• To use as data feed, create a scheduled subscription that sends a report as Excel document.

Problem Definition

Once the above was done, the development machine was decommissioned. However, the problem arose next fiscal year, because of a data range filter that some of the SQL queries used.

Apparently, if we had the development machine and a fresh database, the fix would be simple just as described above. But can we do something without it and the usual woodoo dance installing things? Fortunately, for this particular project, the solution was easy.

Edit RDL

As our queries are already working, we only need to adjust the date range in them, and then redeploy modified RDL. Here is how:

• Login to Report Manager on the Reporting Services server. The URL to it is usually like shown below, where the server part identifies the system where Reporting Services are installed.

http://server/reports    

• Navigate to the report that needs adjusting.
• Click on the Properties tab.
• In the Report Definition section, click the Edit link - it will allow you to download the existing RDL.

• Open the downloaded RDL file in text editor, and find the SQL query in it. In our case, the query looked like below.

<DataSets>
    <DataSet Name="DataSet1">
      <Query>
        <DataSourceName>DataSource1</DataSourceName>
        <CommandText>USE [NameOfDatabase];

SELECT CLIENTNAME
  , TMSHTPERIODSTART
  , TMSHTPERIODEND
  , ENTRYDATE
  , TIMESHEETSTATE
  , TIMESHEETAPPROVED
  , TIMESHEETCOMPLETED
  , TIMESHEETREJECTED
  , TIMESHEETNOTES
  , PROJECTCODE
  , PROJECTNAME
  , PROJECTTRACKINGNUMBER
  , TASKNAME
  , TASKACCESSTYPE
  , USERFULLNAME
  , RESTYPE
  , ISLEAVETIME
  , WORKTYPENAME
  , TOTALTIME
  , NONBILLABLETOTALETIME
  , BILLABLETOTALETIME
  , TOTALBILLING
  , REGTIME
  , BILLABLEREGTIME
  , INVOICEID
  -- , TPROJECTCUSTFLD_VIEW_Opportunity_Organization_Name -- this does not work, therefore trying hard-core with a join.
  , dbo.TCUSTLSTDESC.VALUE as PROJECTDISPOSITION
  , CHARGENAME
     
  FROM [dbo].[VIEWTIMEENTRY]

  /* Project disposition joins. 89 is UNIQUIED for Project Disposition in TOBJCUSTFLD table. May have to eliminate magic number. */ 
  LEFT JOIN dbo.TOBJCUSTFLDVAL on (dbo.TOBJCUSTFLDVAL.OBJECTID = dbo.VIEWTIMEENTRY.PROJECTUID AND dbo.TOBJCUSTFLDVAL.OBJCUSTFLDID = 89)
  LEFT JOIN dbo.TCUSTLSTDESC on (dbo.TCUSTLSTDESC.CUSTLSTID = dbo.TOBJCUSTFLDVAL.VALLONG AND dbo.TCUSTLSTDESC.LANGUAGE = 0)

  WHERE ENTRYDATE BETWEEN '2015-01-01' AND '2015-12-31'
  
  ORDER BY CLIENTNAME
  , ENTRYDATE
  , PROJECTNAME</CommandText>
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>

As you can see, the result set is filtered down to only 1 year worth of entries by the WHERE clause:

WHERE ENTRYDATE BETWEEN '2015-01-01' AND '2015-12-31'  

We can edit the query in RDL file so that it says:

WHERE ENTRYDATE BETWEEN '2015-01-01' AND '2099-12-31'

Or:

WHERE ENTRYDATE >= '2015-01-01'

• Upload updated RDL to the Report Manager.
• After uploading, optionally adjust its data source in Properties - Data Sources to use a shared one available on the server.
• Test the modified report to see if it is working as expected.

Summary

For our simple date range adjustment, we managed to fix the problem easily by editing SQL query in RDL definition and re-uploading an adjusted RDL to the server. However, more complex tasks may require local debugging with all the tools and a database copy installed.