1. 

   anthonyb, Member

   Hi Guys,

   We renewed our MyDBR license, and subsequently had to update to 4.0 as well.
   We run PHP/Apache on a Windows Server with MS SQL Server Express.

   The update and subsequent switch from php_mssql to Microsoft's php_sqlsrv was done, and MyDBR runs fine after this update.

   All except for one issue: Reports that use date filtering return no results.

   To run through it we take input by using
   @vStartDate date,
   @vEndDate date,
   

   'vStartDate=-1','vEndDate=-2' as required user parameters, the usual stuff

   in the report query's where-clause we use
   where (v.ReportingDate between @vStartDate and @vEndDate)
   

   Dates in the database are formatted like so: 2013-06-01 00:00:00.000
   And this has always worked in the past, and still seems to match the new date picker formats.

   For some reason since the 4.0 update, the date filtering is not working.

   Other reports with passed parameters are functional, so i'm ruling out mssql/sqlsrv differences... so I suspect the issue is with date formatting?

   Any assistance will be appreciated!

   Thanks
   Anthony
2. 

   myDBR Team, Key Master

   Anthony,
   there should be no changes in the date handling. Where does you setup actually lose the date? In the original report or in the linked report?

   If you set your original report to show the dates it receives:

   select @vStartDate as 'StartDate', @vEndDate as 'EndDate'

   and do the same in the linked report, what do you get? Do the dates appear correctly in the date picker?

   You can also see the dates used in the report by taking a look at the URL parameters.

   Btw, when you do references to the input parameters, instead of using old -1, -2 format, you can use: 'vStartDate=(vStartDate)'. It makes report maintenance easier.

   --
   myDBR Team
3. 

   anthonyb, Member

   Hi myDBR Team,

   I had gone over the reports again, and noticed that I had pasted in example linked-report code that was irrelevant to the problem at hand.

   The results dropped off not on the linked report, but on the main report once a user had submitted input to return results. Turns out I could get correct dates back using your suggestion to select @vStartDate/@vEndDate as output to check formatting and ensure it was passed properly. All date formatting was ok.

   The report also allowed for user input keyword search which was linked to a LIKE '%%' statement. For some reason the problem is actually residing here, as when I comment out the user keyword input and LIKE clause the reports work as expected.

   I suspect it has something to do with sqlsrv drivers and varchar/nvarchar differences?
   Maybe you know something about this... Weird, anyway - for now I have commented out the keyword search entirely and the issue is resolved, as it was not a utilized feature.

   Thanks for the feedback and suggestions.
4. 

   myDBR Team, Key Master

   Anthony,
   glad the problem is fixed.

   If you want, you can send us an example (just add &export=sql to the URL) so we could take a look what is the reason behind the behavior. We'll just to check if it is something we should take into account. You can use the support email for this.

   --
   myDBR Team

Reply

You must log in to post.