Community
Showing results for 
Search instead for 
Do you mean 
Reply

Help with SQL in Dashboard

Copper Contributor
Posts: 25
Country: United_Kingdom

Help with SQL in Dashboard

Hi,

 

Using Act 18.1.103.0 Update 3. I am attempting to customise the Act Dashboard by editing Act.Dashboard.DataChart.XML but getting stuck and could do with some SQL advice.

 

I am using the 'Recent;ly Edited Contacts' item as starting point. This looks like...

 

  <item>
    <key>
      <string>Recently Edited Contacts</string>
    </key>
    <value>
      <string>
		SELECT	C.CONTACTID,
			C.CONTACT		AS "Contact",
			C.COMPANY		AS "Company",
			C.[ID/STATUS]		AS "ID/Status",
			C.PHONE			AS "Phone",
			DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()), C.EDIT_DATE)		AS "Edit Date",
			C.LAST_EDITED_BY	AS "Last Edited By",
			C.RECORD_MANAGER	AS "Record Manager",
			DATEDIFF(DAY,C.EDIT_DATE,GETUTCDATE()) AS "Days Since Edited"
		FROM	dbo.CONTACT AS C
		WHERE	DATEDIFF(DAY,C.EDIT_DATE,GETUTCDATE()) &lt; {&amp;Days Edited|STRING|30} 
		AND	C.RECORD_MANAGER IN ({Record Manager|USERS|%})
       ;0;TRUE;HIDE=[CONTACTID],LINK=[CONTACT_DETAIL,Contact,CONTACTID]</string>
    </value>
  </item>

 

I want to remove some columns, change the date diff from 30 to 7 days, change the filter to be based on "LAST_EDITED_BY" rather than "RECORD_MANAGER" and crucially exclude two users from the lookup. What I have come up with is 

 

  <item>
    <key>
      <string>Recently Edited Contacts</string>
    </key>
    <value>
      <string>
		SELECT	C.CONTACTID,
			C.CONTACT		AS "Contact",
			C.COMPANY		AS "Company",
			C.[ID/STATUS]		AS "ID/Status",
			DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()), C.EDIT_DATE)		AS "Edit Date",
			C.LAST_EDITED_BY	AS "Last Edited By",
			DATEDIFF(DAY,C.EDIT_DATE,GETUTCDATE()) AS "Days Since Edited"
		FROM	dbo.CONTACT AS C
		WHERE	DATEDIFF(DAY,C.EDIT_DATE,GETUTCDATE()) &lt; {&amp;Days Edited|STRING|7} 
		AND	C.LAST_EDITED_BY IN ({Last Edited By|USERS|%})
       ;0;TRUE;HIDE=[CONTACTID],LINK=[CONTACT_DETAIL,Contact,CONTACTID]</string>
    </value>
  </item>

There are two problems with my code

1. It doesn't work. I'm not sure why. I just get a grey box when I add it to my dashboard - If I attempt to 'preview' the results first, the preview doesn't even appear. It is as though there are 0 results for this query.

2. I don't know how to exclude my 2 named users from the statement. It should go somewhere here 

({Last Edited By|USERS|%})

 but not sure what this should be. Assume their names are Sonny and Cher, what should my statement look like?

 

thanks in advance.

MIchael.