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.
Client is using a custom time tracking and invoicing solution that utilizes the following components:
All RDL reports were created originally in Visual Studio 2010. During report creation process, a machine was set up with the following parts:
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.
Once the query is written and debugged, the procedure of creating an RDL report is simple:
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.
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
<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'
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.