nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Report Error when dataset linked to report parameter

k1pp3r

Hello, I have been attempting to create a new report in Birt 4.2 through an SQL database, this is the first time I have attempted to use a report parameter. My query will run fine if I do not use the parameter, however the moment I link the parameter I get an error. I want the report to prompt for a date in string format so the user can select the date they want to run the report for. The query is: select count(*) as TechServicefromTABLENAMEWhereBoard_Name = 'Service Desk' ANDconvert(nvarchar(50), Date_Closed,126) LIKE '?%'Andclosed_by = 'Tech Name' If I use '2013-10-09%' instead of the '?%' and remove the parameter then it runs fine and returns the results. Given the convert date field I should be able to search with a string as the parameter, however I have attempted both string and date formats which both return errors. Any help would be great. Anyone have an idea why the parameter won't work correctly?

Find more posts tagged with

Comments

GLO_FR

Hi, try this in your query and it should work<pre class="_prettyXprint _lang-sql">select count(*) as TechServicefrom TABLENAMEWhere Board_Name = 'Service Desk'AND convert(nvarchar(50), Date_Closed,126) LIKE (? + '%')And closed_by = 'Tech Name'</pre>

bgbaird

We do this with several different methods, depending on the need. I try to stay away from the back/forth type conversions when I can. Another easy method is: Declare @stDate DATESet @stDate = ? SELECT stuffFROM tableWHERE date between @stDate and dateadd(dd,1,@stDate) Brian

GLO_FR

BTW I agree with Brian : Converting a date to string and then use a like function is certainly not the easiest and the most efficient way to write a WHERE clause.
But my previous code works so, depending on your needs, you can still use it.

k1pp3r

<blockquote class="ipsBlockquote" data-author="GLO_FR" data-cid="121285" data-time="1381763775"><div> Hi, try this in your query and it should work<pre class="_prettyXprint _lang-sql">select count(*) as TechServicefrom TABLENAMEWhere Board_Name = 'Service Desk'AND convert(nvarchar(50), Date_Closed,126) LIKE (? + '%')And closed_by = 'Tech Name'</pre>That worked. The date is actually in a date/time format, is there a cleaner where statement to query on just the date portion of the date/time format column?</div></blockquote>

GLO_FR

Hi,

If you want to create a where statement only on the date use

convert(nvarchar(10), Date_Closed,101) LIKE (? + '%')

It will convert the timestamp in date in US format (mm/dd/yyyy)

For other format look here
<a data-ipb='nomediaparse' href='http://msdn.microsoft.com/en-us/library/ms187928.aspx'>http://msdn.microsoft.com/en-us/library/ms187928.aspx</a>