Few "Salesforce Reports" tips and tricks

Again, it's been long time since my last post. But, better late than never. Isn't it? :)

Lets discuss about some tips and tricks for salesforce reports.

1. First of all, if we want to access a list of all the  reports programatically, you can get it in xml format by opening following URL:
"https://instance.salesforce.com/servlet/servlet.ReportList". Needless to say, you have to be logged into your salesforce instance.

2. Next, let us say we want to show a particular report in a visualforce page on a link/button click. Simply include an iFrame in the page with src="https://instance.salesforce.com/00Ox000000xxxxx".
This was simple one. Right? :)

3. But, the above page will have one problem, it will show the headers in the iFrame which looks kind of odd to have two headers (list of tabs visible).
So, how to remove this. Simple, just modify the URL by putting "?isdtp=mn" in the end. Which makes the URL look like this:
"https://instance.salesforce.com/00Ox000000xxxxx?isdtp=mn". And whooshh. The header is gone.

4. Now, let us assume you need to give a direct download link to a report in csv/xls format. The url for the link would be:
"https://instance.salesforce.com/00Ox0000000xxxx?export=1&enc=UTF-8&xf=csv" or "https://instance.salesforce.com/00Ox0000000xxxx?export=1&enc=UTF-8&xf=xls".
This is done by setting the parameters export, enc (encoding) and xf (the format). One thing is quite interesting here, the "xls" format report is not "a native excel file".
They just format it in this way and put an ".xls" extension, so that by default it opens with MS Excel. However, if you try to open this file in some mobile application which supports excel files, it wont open properly as it is not actually an excel file.

5. Finally, this one will look an old one, but still useful.
Suppose we want to give some filter criteria for a report at run time (e.g. there is report link visible to all but the filter criteria for country depends on user's country).
For this, create a report; create the filter but don't but any values in the filter. Keep them empty.Now, build a URL in the following format:
"https://instance.salesforce.com/00Ox0000000xxxx?pv0=India&pv1=Active" (assuming filter 1 is for country and 2 is for some status field).
The values "India" and "Active" can be dynamically generated in a controller class.
If you have more filters, you can continue to pv2, pv3 and so on.

There are some more parameters that you pass in the URL to do a thing or two. For example to delete a report the URL would be something like this:
"https://instance.salesforce.com/00Ox0000000xxxx?delrep". Want to explore more thing? Open a report, view HTML source and find out the actions of the buttons/links on the report page.


Don't forget to put Id of your report instead of the ones given in the examples :).


Please note that the salesforce may change any of the above parameters without notice (may be not so soon though) bacause they don't provide standard API access to these thing and not even standard documentation.


These tricks are for in situations when you are asked "just do it" and you have no standard documented way of doing it.

There are very little documentation for point 5. You can find one of them here.

You can find some more references from "Salesforce Heretic blog (http://sfdc-heretic.warped-minds.com/2006/04/10/progmatic-access-to-salesforcecom-reports/)" and developer force discussion boards.

Let me know if any of the above tricks don't work for you.



Cheers. Happy Clouding :)

Clock in Visualforce page

It has been a long time since my dormant mode started, finally I am back.

Well, this may not be a very peculiar requirement that you get from your client but you may find it interesting to show a countdown timer or a stop watch or even a clock on a visualforce page.

I will discuss, how to show a ticking clock in a visualforce page. The countdown timer or stop watch can be derived from this.

I have taken reference from W3Schools for the javascript. Infact, I was searching for some javascript function which does something on some specified time interval and I found two very useful javascript functions, "setInterval" and "clearInterval".

The visualforce page tag is just a container for the html and javascript.

<apex:page id="thePage" showHeader="false">

<html>
<body>
<center>
<div id="clock"></div>
</center>

<br/>
<div align="right">
<button onclick="int=window.clearInterval(int);" id="stop">Stop Clock</button>
</div>
</body>

<script type="text/javascript">
var int = self.setInterval("clock()",1000);
function clock()
{
    var d=new Date();
    var t=d.toLocaleTimeString();
    document.getElementById("clock").innerHTML = "<B>" + t + "</B>";
}
</script>

</html>

</apex:page>


You can play around with "setInterval" and "clearInterval" functions to create a stop watch or countdown timer code.

Hope this was interesting and useful.

Happy Clouding.. :)

Horizontal Bar graph in visualforce page

It came to me multiple times to create horizontal bar graph kind of representation of some status based on some field values; both on standard layout and on a visualforce page.

Let us take an example, you want to show a bar graph to user with red and green color with varying width of red and green color depending upon a percent or number type
field (say Percent_Complete__c). If the value of the field is 40,you want show a bar with 40% green and remaining 60% as red and so on.

You could create a formula field of type text then using an IMAGE function. The formula could be -

(IMAGE("/img/samples/color_green.gif", "green", 15, Percent_Complete__c*2) & IMAGE("/img/samples/color_red.gif", "red", 15, 200 - Percent_Complete__c*2))

But here is a catch in using a formula field, waht if the caculation is based upon data from multiple objects and fields. This could be very painful or even sometime not possible
to do this using formula field.

So what options do we have left, VISUALFORCE page.

Again, to do this in visualforce, there many be many ways. Some people may use some javascript library like jQuery or some other library. But there is an easier way of doing this.
This solution WE (I along with a colleague of mine, Chetan - AS) came across while working on a similar requirement. We used a simple html table with one row amd two table data
(columns) with background color of green and red. The width of the columns would be varying based upon your requirements.

Here is a simple code snippet assuming a single field would be basis to calculate the width of red and green colored columns.


HorizontalBar.page

<apex:page controller="BarController">
  <table>
    <tr>
      <td style="background-color:red" width="{!redPart}">
      </td>
   
      <td style="background-color:green" width="{!greenPart}">
      </td>
    </tr>
  </table>
</apex:page>

BarController.cls
public class BarController(){
    public Integer redPart{get;set;}
    public Integer greenPart{get;set;}

    public BarController(){
        Object__c obj = [SELECT Percent_Complete__c FROM Object__c WHERE XYZ = 'xyz'][0];
        greenPart = obj.Percent_Complete__c;
        redPart = 100 - greenPart;
    }
}

 AND that is it. This page can even be used in inline VF page in standard page layout.

Hope this helps.

Happy Clouding... :)

Using IMAGE function in formula fields

Things that are "visible" are more easy to understand than things that are meant to "read".

In salesforce standard pages, IMAGE function is the answer to above line. You can show images to the user instead of just having TEXT. And your images can be very dynamic, the way you want them to be. This is the very core objective of salesforce IMAGE formula.

Let us take an example of a very simple IMAGE field. Suppose we want to create an IMAGE field which shows a RED flag when your Lead score is less than 2, yellow when score is between 2 and 4 and green when Lead score is 5.

Create a formula field by going through Setup --> Customize -->Lead --> Fields --> Create  New Custom field and the return data type as TEXT.
Select the field type as formula field. in the formula editor type in the following:

IMAGE(IF(Lead_Score__c <= 2, "/img/samples/flag_red.gif", IF(AND(Lead_Score__c > 2,Lead_Score__c <= 4), "/img/samples/flag_yellow.gif",IF(Lead_Score__c = 5, "/img/samples/flag_green.gif"))))

Now you can put this field in your page layout and try changing your Lead Score field (I am assuming you have Lead Score field on your Lead object, else create one) and see the flag changing. The images that you see here are standard icons provided by salesforce. You can get more icons at mgsmith's blog post here.

One more formula field that I used in one of my project implementation, is below. It just illustrates how you can "concatenate" your image formula field the same way you do it with TEXT.

IF(OR(NumberofLocations__c=1,NumberofLocations__c=2,NumberofLocations__c=3,NumberofLocations__c=4),IMAGE("/img/samples/color_green.gif", "green", 15, 49),IMAGE("/img/samples/color_red.gif", "red", 15, 49))
& IMAGE("/s.gif", "green", 15, 1)
& IF(OR(NumberofLocations__c=2,NumberofLocations__c=3,NumberofLocations__c=4),IMAGE("/img/samples/color_green.gif", "green", 15, 49),IMAGE("/img/samples/color_red.gif", "red", 15, 49))
& IMAGE("/s.gif", "green", 15, 1)
& IF(OR(NumberofLocations__c=3,NumberofLocations__c=4),IMAGE("/img/samples/color_green.gif", "green", 15, 49),IMAGE("/img/samples/color_red.gif", "red", 15, 49))
& IMAGE("/s.gif", "green", 15, 1)
& IF(OR(NumberofLocations__c=4),IMAGE("/img/samples/color_green.gif", "green", 15, 49),IMAGE("/img/samples/color_red.gif", "red", 15, 49)).

Here I have taken refrence of a field NumberofLocations__c on say Account object. It displays a series of images concatenated. So this is kind of Progress Bar that you can show to your user.

Did you note the '&', the numbers 15 and 49, and /s.gif location?
 The '&'  is used to concatenate two strings or texts. In salesforce image fields are treated as string so you can 'add' them.
The number 15 is the height of your image and 49 is the width or span.
One very good image provided by salesforce is "blank" image i.e. '/s.gif'. So when you do not want to show any image in your condition, simply put the url of image as '/s.gif'. It shows a blank. In case you do not want to show an image and leave the url part as blank, it shows a red cross instead of blank. Which definetely doesn't look good.

You can find more about image formulas at salesforce documentation here.

Hope this was useful.

Happy Clouding :)

Creating Visualforce page code against your standard page layout using AJAX toolkit

I was wondering if we somehow, could automate this and I came across a post by Jeff Douglas. This was simply amazing. I got the base. he had done it using web service API in .NET and this was somewhat disappointing because I never worked on it. I decided to run it, got environment setup using some tutorials on .NET and the result.. wow..

My next step was to simplify it without using the WSDL and all that stuff. But this is not possible in Apex (please correct me, if I am wrong), so I had to look for an alternate way. My search ended up with AJAX Toolkit. And I learned AJAX for the first time then only. (Credit goes to Jeff, he made me learn two new technologies). I must tell you this is simply powerfullll. I used it in a visualforce page along with a small controller class (I had to use the class, its a different story). Later on I found that it is possible even without using the class. Here is how it looks like.

Input Screen

And here is the generated visualforce code

The logic is almost the same. You can even see variable names not very different. Below is the code for all this. (Please excuse me, code with no comments :)).

Page Code

<apex:page controller="OutputController">
<apex:form >

<script type="text/javascript">
var __sfdcSessionId = '{!GETSESSIONID()}';
</script>

<script src="../../soap/ajax/19.0/connection.js" type="text/javascript"></script>

<script type="text/javascript">
//var output='';
function setupPage() {
    var obj = document.getElementById("obj");
    var opt = document.getElementById("opt");
    var rct = document.getElementById("rct");
    var result;
    try{
        if(rct.value != '' && rct.value != null){
            result = sforce.connection.describeLayout(obj.value, new Array(rct.value));        
        }else {
            result = sforce.connection.describeLayout(obj.value);
            alert(result);
            alert('please note that you have not provided record type id. if the object has more than one record type and different page layout assignments, you will get the code with all the page layouts');
        }
        //call methods edit or details
        if(opt.value == 'Edit')editLayoutResults(result, obj);
        else detailLayoutResults(result, obj);
    }catch(error){       
        var er = new String(error);
        if(er.indexOf('INVALID_TYPE') != -1)alert('please check object api name');
        document.getElementById("{!$Component.hide}").value = '';
    }
}


//edit*****************************************layout
function editLayoutResults(result, obj) {
    output = '';
    var layouts = result.getArray("layouts");

    output += '<' + 'apex' + ':' + 'page standardController=' + '"' + obj.value + '"' + '>';
    output += '\n';
    output += '<' + 'apex' + ':' + 'sectionHeader' + ' title=' + '"' + obj.value + ' Edit' + '"' + ' subtitle=' + '"' + '{' + '!' + obj.value + '.name}' + '"' + '/>';
    output += '\n';
    output += '<' + 'apex' + ':' + 'form' + '>';
    output += '\n';
    output += '<' + 'apex' + ':' + 'pageBlock title=' + '"' + obj.value + ' Edit' + '"' + ' mode=' + '"edit">';
    output += '\n';
    output += '\n';
    output += '<' + 'apex' + ':' + 'pageBlockButtons location=' + '"top">';
    output += '\n';
    output += '<' + 'apex:commandButton value=' + '"' + 'Save' + '" ' + 'action=' + '"' + '{' + '!' + 'save' + '}"' + '/>';
    output += '\n';
    output += '<' + 'apex:commandButton value=' + '"' + 'Save & New' + '"' + ' action=' + '"' + '{' + '!save}" />';
    output += '\n';
    output += '<' + 'apex:commandButton value="Cancel" action=' + '"' + '{' + '!cancel}' + '"/>';
    output += '\n';
    output += '<' + '/apex:pageBlockButtons>';
    output += '\n';
    output += '\n';
    output += '<' + 'apex' + ':' + 'pageBlockButtons location=' + '"bottom">';
    output += '\n';
    output += '<' + 'apex:commandButton value=' + '"' + 'Save' + '" ' + 'action=' + '"' + '{' + '!' + 'save' + '}"' + '/>';
    output += '\n';
    output += '<' + 'apex:commandButton value=' + '"' + 'Save & New' + '"' + ' action=' + '"' + '{' + '!save}" />';
    output += '\n';
    output += '<' + 'apex:commandButton value="Cancel" action=' + '"' + '{' + '!cancel}' + '"/>';
    output += '\n';
    output += '<' + '/apex:pageBlockButtons>';

   
    //adding fields and sections
    var allTheLayouts = result.getArray("layouts");
    for (var i = 0; i < allTheLayouts.length; i++){
        var layout = allTheLayouts[i];
        if (layout.editLayoutSections != null){
            var elSections = layout.getArray("editLayoutSections");
            for (var j = 0; j < elSections.length; j++){
                var els = elSections[j];
                
                output += '\n';
                output += '\n';
                output += '<' + 'apex:pageBlockSection title=' + '"' + els.heading + '" ' +  'columns=' + '"' + els.columns + '"' + '>';
                output += '\n';

                var allTheLayoutRows = els.getArray("layoutRows");
                for (var k = 0; k < allTheLayoutRows.length; k++){
                    var lr = allTheLayoutRows[k];
                    var lis = lr.getArray("layoutItems");
                    for (var h = 0; h < lis.length; h++){
                        var li = lis[h];
                        //only in case of Lead and Contact First Name, which includes Salutation also
                        if (li.layoutComponents != null && li.layoutComponents.length == 2){                           
                            output += '<' + 'apex:inputField value=' + '"' + '{' + '!' + obj.value + '.' + li.layoutComponents[1].value + '}' + '" ' + 'required=' + '"' + li.required.toString() + '"' + '/>';
                            output += '\n';
                        }
                        //for all other fields
                        else if (li.layoutComponents != null){
                            output += '<' + 'apex:inputField value=' + '"' + '{' + '!' + obj.value + '.' + li.layoutComponents.value + '}' + '" ' + 'required=' + '"' + li.required.toString() + '"' + '/>';
                            output += '\n';
                        }
                    }
                }
                output += '<' + '/apex:pageBlockSection>';
                output += '\n';
            }
        }
    }
    output += '\n';
    output += '<' + '/apex:pageBlock>';
    output += '\n';
    output += '<' + '/apex:form>';
    output += '\n';
    output += '<' + '/apex:page>';
 
    document.getElementById("{!$Component.hide}").value = output; 
}


//details**********************************layout
function detailLayoutResults(result, obj) {
    var layouts = result.getArray("layouts");
    var output = '';

    output += '<' + 'apex' + ':' + 'page standardController=' + '"' + obj.value + '"' + '>';
    output += '\n';
    output += '<' + 'apex' + ':' + 'sectionHeader' + ' title=' + '"' + obj.value + '"' + ' subtitle=' + '"' + '{' + '!' + obj.value + '.name}' + '"' + '/>';
    output += '\n';
    output += '<' + 'apex' + ':' + 'pageBlock title=' + '"' + obj.value + '"' + '>';
    output += '\n';
   
    //adding fields and sections
    var allTheLayouts = result.getArray("layouts");
    for (var i = 0; i < allTheLayouts.length; i++){
        var layout = allTheLayouts[i];
        if (layout.editLayoutSections != null){
            var elSections = layout.getArray("editLayoutSections");
            for (var j = 0; j < elSections.length; j++){
                var els = elSections[j];
                
                output += '\n';
                output += '<' + 'apex:pageBlockSection title=' + '"' + els.heading + '" ' +  'columns=' + '"' + els.columns + '"' + '>';
                output += '\n';

                var allTheLayoutRows = els.getArray("layoutRows");
                for (var k = 0; k < allTheLayoutRows.length; k++){
                    var lr = allTheLayoutRows[k];
                    var lis = lr.getArray("layoutItems");
                    for (var h = 0; h < lis.length; h++){
                        var li = lis[h];
                        //only in case of Lead and Contact First Name, which includes Salutation also
                        if (li.layoutComponents != null && li.layoutComponents.length == 2){                           
                            output += '<' + 'apex:outputField title=' + '"' + li.label + '" value="' + '{' + '!' + obj.value + '.' + li.layoutComponents[1].value + '}' + '"' + '/>';
                            output += '\n';
                        }
                        //for all other fields
                        else if (li.layoutComponents != null){
                            output += '<' + 'apex:outputField title=' + '"' + li.label + '" value="' + '{' + '!' + obj.value + '.' + li.layoutComponents.value + '}' + '"' + '/>';
                            output += '\n';
                        }
                    }
                }
                output += '<' + '/apex:pageBlockSection>';
                output += '\n';
            }
        }
    }
    output += '\n';
    output += '<' + '/apex:pageBlock>';   
    output += '\n';
    output += '<' + '/apex:page>';

    document.getElementById("{!$Component.hide}").value = output;
}
</script>

<p><b>Enter the object API Name and record type Id associated to create visualforce code of the page layout for the selected page type.</b></p>

<table>
<tr >
<td><b>Object API Name</b></td>
<td><input type="text" id="obj"/></td>
</tr>

<tr >
<td><b>Record Type Id</b></td>
<td><input type="text" id="rct"/></td>
</tr>

<tr >
<td><b>Page Type</b></td>
<td><select id="opt">
  <option value="Edit">Edit</option>
  <option value="Detail">Detail</option>
</select></td>
</tr>
</table>

<center><apex:commandButton id="but" value="Create Page Code" onclick="setupPage()" action="{!create}" rerender="pan" status="pageStatus"/></center>
<apex:inputHidden id="hide" value="{!hide}"/>

<br><br></br></br>
<apex:actionStatus id="pageStatus" startText="Getting page code..." stopText="Page Code"/>
<table >
<tr>
<td>
<apex:outputPanel id="pan">
{!output}
</apex:outputPanel>
</td>
</tr>
</table>

</apex:form>
</apex:page>

Controller Class

public class OutputController {       
    //variables and getter/setter
    public String output {get;set;}
    public String hide {get;set;}   
   
    //action method of the button
    public PageReference create() {
        output = hide;
        return null;
    }        
   
    //test method
    public static testMethod void testPage(){
        Test.setCurrentPage(Page.VisualforceCodeCreator);
        Test.startTest();
        OutputController oc = new OutputController();
        oc.create();
        Test.stopTest();
    }
}

An This Is It.

The controller, I had to use for a reason. We cannot just print some text with HTML encoding in it in a visualforce page. The option I came across later on is to use HTMLENCODE() function in the page and you'll get rid of the controller.


Happy Clouding.
And its not my dialog but I love it...Human Knowledge belongs to the world.

Migrating translations for fields and other components using Force.com IDE

Well, this is quite risky job if your machine does not support a language to be edited in its proper format and re formats the content into some garbage characters. This might be the case with chinese, japanese and for all the languages that do not use 'script' similar to english.

The steps are quite easy with just one DON'T..what? wait for a minute, you'll get to know soon..
I am assuming that you know how to use Force.com IDE. Now

• Create a Force.com package using your credentials for your source environment/sandbox
• While selecting metadata components, select second option (i.e. Choose manually)
• Expand "objects" and select the object(s) whose translation you want to migrate (including field translations). Example: Account, MyObject__c
  
• Expand "translations" and select the languages for which the translation is done. Example: ja, zh_CN
  
• Next expand "objectTranslation" and select the translation for the objects in step3 and select the labguage for those objects in step 4.

Now when you will click Next and Finish, three folders should be there in your project's "src" folder, viz. object, tranaslation and objectTranslation. And here is that DON'T. DO NOT open any objectTranslation file on your machine. just keep them as they are. Although you can open a fine that supports your "keyboard". for example if you are using an english keybord, you can open French or German file and so on.

• To deploy this to your target environment/sandbox, right click on project --> Force.com -->Deploy to server
• Enter the credentials
• On the next page, "Deselect All" the components, then manually select only the objectTranslation file (assuming you have already migrated objects and have set up the language otherwise select all the components)
• Validate your deployment before clicking Next
• Click Next when you get a Green signal
• And yayy..you are done..

See how simple. For further reference go to
http://wiki.developerforce.com/index.php/Documentation#Usage_and_Implementation_Guides

and the book is Localizing with the Force.com IDE.

Here is the direct link

Hope this was useful.

Happy clouding..

Business Hours SLA in Salesforce workflow

Someone once said, there are two ways to do a job. One is doing smartly and the other is doing hardly.

When it came to implement a requirement, I chose the second way..alas..

The scenario was to calculate End Time of a Start Time not in 24 hours format. I had to exclude weekend, holidays and Non-Business Hours (only Business Hours, say 9am to 6pm taken into consideration).

I tried a hell lot of things, and ended up doing everything my way. JPSeabury did this very thing the salesforce way, the easiest way. You can find it here in his blogpost.

I tried everything in the same order he did. Then I came up with "my" way.
1. Created an object to store Holidays
2. Created another object to store user locales (Time Zones)
3. Created a lot of apex (about 400 lines) to calculate the time elapsed between two times.

Morale: Don't reinvent the wheel


PS: If you have faced a problem like this, please refer to JP's blog.


Happy Clouding...