+ Start a Discussion
chrismclarenchrismclaren 

Scontrol Printable view

Is there any way to integrate an scontrol to run a report and click the printable view button, without the user seeing the report being run.  I want to have a custom link that users click that runs a report.  I don't want users to be able to see the report as this will encourage them to alter the report (which I don't want).
 
Basically an scontrol that runs a report (the user doesn't see this report) and then runs the printable view button to display the results in excel (the user would basically click the custom link, from the home page, then the excel file would be displayed).
 
The information I want to dipaly is a customopportunity field and  a few standard opportunity fields.
 
Is this possible?  How do I go about this?  Has anyone got any sample code?
 
 
darozdaroz
This could be a good start for you here:

http://sfdc-heretic.warped-minds.com/2006/04/10/progmatic-access-to-salesforcecom-reports/


Edit: Made link clickable

Message Edited by daroz on 04-13-2006 02:13 PM

chrismclarenchrismclaren
I think I might have a better way to do this but not sure of how to code it. This is achievable using an scontrol that will show a range of opportunities and the sum of the amount of these opportunities.
 
Would anyone be able to help with the code for this?
chrismclarenchrismclaren
I have managed to (sort of) do this with the follwoing code.
 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head> 
    <title>sort</title>
    <script src="https://www.salesforce.com/services/lib/ajax/beta3.3/sforceclient.js?browser=true" type="text/javascript"></script>
    <script type="text/javascript">
    function load(){
        sforceClient.registerInitCallback(main);
        sforceClient.init("{!API_Session_ID}", "{!API_Partner_Server_URL_70}", true);
    }
   
    function main(){
        var orden = new Array();
        var cd = new Array();
        cd.push("CloseDate");
        cd.push("ASC");
       
        var n = new Array();
        n.push("Name");
        n.push("DESC");
       
        orden.push(cd);
        orden.push(n);
       
        sforceClient.QueryAllSorted("select Id,My_Commision__c, Name, CloseDate, Amount from Opportunity where OwnerId = '{!User_ID}'", orden);
    }
   
    function displayResults(opps) {
        var out ="";
        out += "<table border='1'><tbody>";
        for (var i=0; i<opps.length; i++){
            out += "<TR><TD>" + opps[i].get("Name") + "</TD><TD>" +opps[i].get("CloseDate")+ "</td><TD>" +opps[i].get("Amount") + "</TD><TD>" +opps[i].get("My_Commision__c")+ "</TD></TR>";
        }
        out += "</tbody></table>";     
        document.getElementById("maindiv").innerHTML = out;
    }
    function sortSObject(a, b){
        for (var i=0; i<sforceClient.sortArray.length; i++){
            var va = a.get(sforceClient.sortArray[i][0]);
            var vb = b.get(sforceClient.sortArray[i][0]);
           
            var s = 1;
           
            if (sforceClient.sortArray[i][1] == "DESC"){
                s = -1;
            }
            if (va > vb){
                return 1 * s;
            } else {
                if (va < vb){
                    return -1 *s;
                }
            }
        }
       
        return 0;
    }
   
    sforceClient.QueryAllSorted = function(soql, sort_orders) {
        sforceClient.sortArray = sort_orders;
       
        sforceClient.sobjects = new Array();
        this.Query(soql, queryCallback);
    }
   
    sforceClient.sortArray = new Array();
   
    queryCallback = function(ret) {
        if (ret.size > 0){
            sforceClient.sobjects = sforceClient.sobjects.concat(ret.records);
            if (ret.done == false){
                    sforceClient.queryMore(ret.queryLocator, queryCallback)
            } else {
                displayResults(sforceClient.sobjects.sort(sortSObject));
            }
        }
    }
   
   
    </script>
</head>
<body onLoad="load();">
    <div id="maindiv"></div>
</body>
</html>
 
 
 
I would like to be able to sum the query of the Amount value but this does not seem possible.  Does anyone know how I can do this with soql?  How would I add all the values stored in the array of Amount.
SteveBowerSteveBower
Unless I'm missing something, couldn't you just modify your displayResults to:

Code:
function displayResults(opps) {

        var sum_amount = 0.0;                                  // added
var out =""; out += "<table border='1'><tbody>"; for (var i=0; i<opps.length; i++){ out += "<TR><TD>" + opps[i].get("Name") + "</TD><TD>" +opps[i].get("CloseDate")+ "</td><TD>" +opps[i].get("Amount") + "</TD><TD>" +opps[i].get("My_Commision__c")+ "</TD></TR>";
sum_amount += opps[i].get("Amount"); // added
}
out += "</tbody></table>";
out += "<br>Total Amount: $" + sum_amount + "<BR>" // added
document.getElementById("maindiv").innerHTML = out; }

 

chrismclarenchrismclaren
Ok.  That's working.  Thankyou, fantastic.  Have a new problem.  I need the SOQL to search for opportunities that have closed this month, last month and this year.  I am having problems with the AND operator and the date.  Can anyone help???
Ron HessRon Hess
can you post the SOQL you tried?
vandervander

your date is most likely the problem.  it needs to be of the following format:

Use

Format Syntax

Example

Date only

YYYY-MM-DD

1999-01-01

Date, time, and time zone offset

•YYYY-MM-DDThh:mm:ss+hh:mm

•YYYY-MM-DDThh:mm:ss-hh:mm

•YYYY-MM-DDThh:mm:ssZ

•1999-01-01T23:01:01+01:00

•1999-01-01T23:01:01-08:00

•1999-01-01T23:01:01Z

chrismclarenchrismclaren

sforceClient.QueryAllSorted("select Id,My_Commision__c, Name, CloseDate, Amount from Opportunity WHERE CloseDate =

'Tue Jan 10 00:00:00 UTC 2006' Region__c = 'Europe' AND Probability > 0 AND Probability < 100 ", orden);

 

Even this doesn't work.  Although what I want is instead of 'Tue Jan 10 00:00:00 UTC 2006'  to have the current date stamp. 

 

 'Tue Jan 10 00:00:00 UTC 2006'  is just pasted from a known result with that as the close date.

Message Edited by chrismclaren on 04-20-2006 03:34 AM

DevAngelDevAngel
For a date query you need to use and ISO 8601 formatted date.  This is YYYY-MM-DDTHH:MM:SS.mmmZ where z is either literally a Z or an offset such as -08:00 or +02:00.

Using the AJAX toolkit, you can convert from a Date object to the ISO format using Sforce.Util.ToIsoDateTime(new Date()).  This would return the current date and time as an iso string.

"Select Id From Contact Where CreatedDate > " + Sforce.Util.ToIsoDateTime(new Date())
chrismclarenchrismclaren
Tried this (and many other variations)
 
select Id,My_Commision__c, Name, CloseDate, Amount from Opportunity WHERE Region__c = 'Europe' AND IsWon = TRUE AND CloseDate > ' + Sforce.Util.ToIsoDateTime(new Date())' "
 
Does not work.  Also I am wanting it for the last 30 days.  Is it possible to just subtract 30 from this varaible?
DevAngelDevAngel
Why is the date string in quotes?  Please see my previous example.
chrismclarenchrismclaren
It now works.  Thank you very much.  That is a great help.  How do I make it so that it searches for 30 days prior to today?
Ron HessRon Hess
something like this should work ( not tested)

code:

var today = new Date();
var isoDate30daysago = Sforce.Util.ToIsoDateTime(today.setDate(-30))
darozdaroz
Ron, I think that code is invalid. In Java you could new Date().addDays(-30) but that's not available in JS... Probibly the easiest thing to do is...


var yourDate = new Date();
yourDate.setTime( yourDate.getTime() - ( 30 /*days*/ * 24 /*hours/day*/ * 60 * 60 /*Mins+Sec*/ * 1000 /*Milliseconds*/) );


Essentially you get the current date/time, convert it to time in milliseconds using getTime(), subtract 30 days in milliseconds (the 30*24*60*60*1000 part) and set it back using setTime().

PITA, yes... but it works. ;)
Ron HessRon Hess
here is some code which does test and backs up 30 days, the output is

Sun Apr 23 2006 18:38:15 GMT-0700 (Pacific Daylight Time)
Fri Mar 24 2006 18:38:15 GMT-0800 (Pacific Standard Time)

basicaly, setDate(1) moved to the first date of the current month
setDate(0) moves to the last date fo last month, and setDate(23-30) moves from april 23 to march 24, pretty cool, but does not appear to be well documented...

Code:
var d = new Date()
document.write(d+"<br>")
d.setDate(d.getDate()-30)
document.write(d)

 

handy tryit editor
http://www.w3schools.com/jsref/tryit.asp?filename=tryjsref_setdate

darozdaroz
I stand corrected. :D

And to think an undocumented use I hadn't exploited. I must be loosing my touch. ;)