Community
Showing results for 
Search instead for 
Do you mean 
Reply

Custom Dashboard by editing XML file

Copper Contributor
Posts: 6
Country: United Kingdom

Custom Dashboard by editing XML file

Please help,

I have spent hours (+++) trying to solve what should be a simple thing.

I'm attempting to code a custom dashboard by editing the xml file. I have a little knowledge of sql query statements and have studied the example dashboards and the notes at the top. Entering a date variable is covered and have got it to work, however, getting a date selection to work by adding a second variable is so far, problematic as my use of the standard sql statement BETWEEN ....AND , does not.

the statement: WHERE O.OPEN_DATE = '{Open Date|DATE|NOW}'

returns data when the input date matches a record's date and that suggests the syntax is ok.

The XML file suggest using two dates for a selection, great, but no examples unfortunately.

The statement: WHERE O.OPEN_DATE = BETWEEN '{Start Date|DATE|NOW}' AND '{End Date|DATE|NOW}'

allows the input of the two dates but returns no data.

It would be fantastic to know which variant of SQL is applicable so that the exact syntactical requirements are known, I have only managed marginal success through hours of painstaking trial and error.

any help would be appreciated, thank you, Les

Nickel Elite Contributor
Posts: 937
Country: USA

Re: Custom Dashboard by editing XML file

For the most part Transact SQL commands and syntax is supported.

 

Check out the SQL Chart control on the developers download forum - this tool will really help you develop your SQL statements (there's a graphical builder in there) and those statements will work in the datachart control.

 

For the statement you have: 

  WHERE O.OPEN_DATE = BETWEEN '{Start Date|DATE|NOW}' AND '{End Date|DATE|NOW}'

 

The correct SQL Syntax would be:

 

 WHERE O.OPEN_DATE >= '{Start Date|DATE|NOW}' AND O.Open_Date <= '{End Date|DATE|NOW}' 

 

 FYI: 

< is less than

<= is less than or equal to 

Copper Contributor
Posts: 6
Country: United Kingdom

Re: Custom Dashboard by editing XML file

Thanks for the help regarding user date range entry, I must be closer to success now but I need some further clarification please.

I pasted the t-sql statement

WHERE O.OPEN_DATE >= '{Start Date|DATE|NOW}' AND O.Open_Date <= '{End Date|DATE|NOW}'  

into my DataChart script which had the result of the template disappearing from the DataChart list.

I tried entering a cut-down statement to see what would happen

WHERE O.OPEN_DATE >= '{Start Date|DATE|NOW}'

The template is still available and this allows user input and returns data accordingly. the whole statement causes the template to disappear from the list.

I have also tried this t-sql function with the activequerybuilder with no success.

 

I would appreciate any more help and I apologise that I didn't get it,

 thanks,

Les

 

Copper Contributor
Posts: 6
Country: United Kingdom

Re: Custom Dashboard by editing XML file

A quick update.

my user input dates via '{start date|DATE|now}' present input via a regular calendar so the first of January gives me 01/01/2010. But if I select the first of April 01/04/2010 the sql treats this as the the fourth of January i.e. the format is treated as american style with the month first.

the date selection statement then kind of works, but the results are very odd. I'm a bit closer but no cigar..................

Copper Contributor
Posts: 6
Country: United Kingdom

Solved: Custom Dashboard by editing XML file

ByJove, I think I've done it!

Coding for user date selection as a datachart Filter does use the BETWEEN AND statement , my use of the "<" symbol removes the template from the list.

 

Item 2. in the Help section of the XML Custom Dashboard Datachart file, {ParamName|PICKLIST:LNAME|Value} allows you to set a filter based on any(?) field that is defined as a dropdown list. ParamName can be anything, LNAME is the string of characters you enter when naming the dropdown list.

the following query allows me to produce a monthly total for opportunities filtered for Status and a dropdown filter of my specification and to narrow the results between two user-defined dates. whoohoo.

<item>
  <key>
  <string>YourTemplateName</string>
  </key>
  <value>
  <string>
   SELECT convert(varchar(7),O.OPEN_DATE,111) AS "Year-Month",
     SUM(O.TOTAL)    AS "Total"

     FROM dbo.OPPORTUNITY AS O
   
   WHERE O.OPEN_DATE BETWEEN '{Start Date|DATE|NOW}' AND '{End Date|DATE|NOW}'

 

   AND(((O.STATUSNUM = 0) AND ({Status - &amp;Open|CHECKINT|1}=1))
   OR  ((O.STATUSNUM = 1)  AND ({Status - Closed &amp;Won|CHECKINT|0}=1))
   OR  ((O.STATUSNUM = 2)  AND ({Status - Closed &amp;Lost|CHECKINT|0}=1))
   OR  ((O.STATUSNUM = 3)  AND ({Status - &amp;Inactive|CHECKINT|0}=1)))

   AND O."YourDropdownlistField" IN ({AnynameforyourFilter|PICKLIST:YourDropdownListname|%})
   

   GROUP BY  convert(varchar(7),O.OPEN_DATE,111)
   ORDER BY convert(varchar(7),O.OPEN_DATE,111)
   ;7;FALSE</string>

  </value>
 </item>

Cheers,

Les