Community
Showing results for 
Search instead for 
Do you mean 
Reply

Problem passing field name as variable into custom dashboard

Copper Contributor
Posts: 20
Country: United States

Problem passing field name as variable into custom dashboard

Qsalesdata imports monthly sales totals for each linked contact from QuickBooks into 12 corresponding ACT! fields.  The fields are named STcur01 (Jan current year) through STcur12 (Dec current year).

 

It wrote a custom dashboard item to display the current months sales in a bar graph that is grouped by ID/STATUS.  It works great.  My problem is that I will need to edit the query in Act.Dashboard.DataChart.xml each month and enter the field name for the current month unless I can find a way to pass the field name into the query as a parameter.  For example, this month I had to manually change the field name from “STcur08” (for Aug) to “STcur09” (for Sep).

 

This is the entry in the dashboard xml file that works:

 

<value><string>

SELECT        SUM([STcur-08]), [ID/STATUS]

FROM             dbo.CONTACT

WHERE         [STcur-08] > 0

GROUP BY   [ID/STATUS]

                       ;9;FALSE

</string></value>

 

Below is my feeble attempt to pass the field name into the query as a parameter (variable).  I replaced the ‘WHERE’ clause above with the parameter filter thinking it would simplify.

 

Any ideas would be greatly appreciated… Note that the field name contains a “-“ so I’m assuming it would need quotes or brackets (or would it?)

 

<value><string>

DECLARE    @STcur AS STRING

SELECT        SUM(@STcur), [ID/STATUS]

FROM            dbo.CONTACT

WHERE         ( @STcur = {current month | STRING | STcur-08} )

GROUP BY   [ID/STATUS]

                        ;9;FALSE

</string></value>

Nickel Contributor
Posts: 175
Country: USA

Re: Problem passing field name as variable into custom dashboard

I'm not sure if you have a plug-in for this (I wouldn't think you'd need one for just a custom dashboard item), but my thought would be that when ACT! gets fired up, a plug-in could change the XML file for you. You could easily create your variable names based on the current date, and just check that created variable name against the existing query and if it doesn't match, change it. This could all be done in .NET (no ACT! SDK needed besides using it as the "delivery"), provided that there aren't any permission issues to the file. You could even just setup a task in Task Scheduler on the host machine to run an application on the first of each month to change the file, thus removing the need for any ACT! SDK programming.

Just my two cents.
Copper Contributor
Posts: 20
Country: United States

Re: Problem passing field name as variable into custom dashboard

Thanks knif!

 

I appreciate you taking the time to respond.  I thought about creating 12 copies of the xml file for dashboards and replacing the “.xml” extensions with numbers corresponding to months (eg “.01”, “.02”…) and then using a service to change the extension back to xml at 12am on the 1st day of every month.  Problem is when add dashboards in the future, I would have to update all 12 files.

I have since gotten a solution for the query from MSDN site; it’s posted below.

 

FYI, creating custom dashboards is a snap.  You just need to create a query for what you want to display and then add the query to Act.Dashboard.DataChart.xml in the Tools folder of the ACT program files.

 

There are also a couple add ins (available on this board) that when extracted and added to the Tools folder create additional dashboard options under the custom tab in design new dashboard.

Below are links for the add in and a great You Tube video by Vivek.  The add in below only works on 2012 SP1 and later.  See Pat Egen’s posts for previous versions.

 

http://www.youtube.com/watch?v=mYnxPsu5HG4&feature=channel&list=UL

 

http://community.act.com/sage/attachments/sage/ADN_Downloads/674/1/ACTDashboardControls2012SP1-v2.zi...

 

 

 

WITH FixedStructure AS
(
SELECT * from dbo.CONTACT 
UNPIVOT 
(
MonthValue FOR MonthName 
IN 
(
[STcur-01], [STcur-02], [STcur-03], [STcur-04], [STcur-05],[STcur-06], 
[STcur-07], [STcur-08], [STcur-09], [STcur-10], [STcur-11], [STcur-12]
)
) 
AS U
)
SELECT * FROM FixedStructure 
WHERE MonthName = 'STcur-' + convert(VARCHAR(2),CURRENT_TIMESTAMP, 101)