Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Bronze Super Contributor
vivek
Posts: 1,820
Country: United_Kingdom
Message 1 of 2 (495 Views)
Accepted Solution

Dashboard Datachart Date problem

Hi I hope someone can help point out what I am doing wrong or if there is a problem with the Datachart tool. I have the following SQL query:

 

 

SELECT O.OPPORTUNITY_FIELD_7 AS "R-Rebate",
     O.ESTIMATED_CLOSE_DATE AS "Estimated Close Month" 
   FROM dbo.OPPORTUNITY AS O
   WHERE (O.ESTIMATED_CLOSE_DATE >={Start Period|DATE|NOW-YEAR})
   ORDER BY O.ESTIMATED_CLOSE_DATE ASC
   ;1;FALSE

 

SELECT O.OPPORTUNITY_FIELD_7 AS "R-Rebate",    

DATENAME(mm,O.ESTIMATED_CLOSE_DATE) AS "Estimated Close Month"   

FROM dbo.OPPORTUNITY AS O  

 WHERE (O.ESTIMATED_CLOSE_DATE >={Start Period|DATE|NOW-YEAR})  

 ORDER BY O.ESTIMATED_CLOSE_DATE ASC  

 ;1;FALSE

 

The query runs without any issues, except that users can not alter the filter date! What I mean by this is that the Filter dialog pops up and the user can change the date but it does not update the WHERE clause.

 

I do have a second problem with regards to filtering dates and that is the <= operator. The XML parsing reads the '<' as a tag opener but also has a problem with &lt. Originally I had hoped to use the following WHERE clause:

 

WHERE ( O.ESTIMATED_CLOSE_DATE >={Start Period|DATE|NOW-YEAR}

AND O.ESTIMATED_CLOSE_DATE <={Start Period|DATE|NOW-YEAR})

 

I've actually given up trying to work that one out!

Vivek Gargav
Caldere Associates Ltd.
www.caldere.com
vgargav@caldere.com
My Blog
Please use plain text.
Bronze Super Contributor
vivek
Posts: 1,820
Country: United_Kingdom
Message 2 of 2 (450 Views)

Re: Dashboard Datachart Date problem

Ok finally worked it out. Posting it here in case anyone else gets stumped by this...

 

WHERE (O.ESTIMATED_CLOSE_DATE &gt;='{Start Period|DATE|NOW-YEAR}')

 

It was those poxy apostrophes that I was missing out initially! Once I put them in WHERE clauses magically start working...hurrah!

Vivek Gargav
Caldere Associates Ltd.
www.caldere.com
vgargav@caldere.com
My Blog
Please use plain text.