Adjust SQL Queries in RDL Reports

Project Description

We need help adjusting SQL queries in RDL reports that obtain data from a Tenrox database and utilize Microsoft Reporting Services. We need 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:


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:


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:

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:

http://server/reports


RDL report properties in Report Manager

RDL report properties in Report Manager


  <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'


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.

You can leave a comment on this project, or post a new project for consideration.