Analytics Report Chart with Dynamic Filter

Analytics:reportChart visualforce component is used to add Salesforce report chart to a Visualforce page. You can add filter to the chart data.

We have to pass report id in the reportId attribute and to filter the data, pass criteria in the filter attribute.

Before adding charts into Visulaforce page, make sure

1.   You have created required source reports in Salesforce org.
2.   Give required access to report folder.
3.   Important: Add chart to the Source Report.

If you forget adding chart into the report then it will show error message on the chart - You can't view the report chart because its report, report type, or chart has been deleted.

Steps to add Analytics chart into the VF page:

1.  Create report Opportunity Pipeline with chart added into it and save the report.
2.  Create a Visualforce page AnalyticsReportChart.
3.  Add <analytics:reportChart> component into the VF page.
4.  Put unique Report Id of the source report (get id from the Report URL) in the ReportId attribute in the <analytics:reportChart reportId=””> component.
5.  Or create a Custom Label to store the Report Id and use the $Label on page.

 6.  Save and run the page to see the Report chart on the page.

NOTE: To add report header (here Opportunity Pipeline), add the header text in the source Report chart.

The Filter Attribute

Filter a chart data by fields to show specific results. A report can have up to 20 field filters.

A filter has three attributes as below:

Column – Field API name

Operator – Condition API name (Example- Greater Than - greaterThan )

Value – The Filter Criteria. 

Example :-

NOTE: To get the API name of the Field and the Operator, make a describe request via the Analytics REST API or Analytics Apex Library.

To know more about the attributes, go through this link – analytics:reportChart

-------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------

Recently, in one of our project we got requirement where we had to pass dynamic filters such as selected Users and dynamic Date Range into the chart.

Passing dynamic value in filter is not a challenging work as we can pass the selected values for particular field in the value attribute of Filter (using {!varName}).

In Date Filter, the Value attribute only accepts yyyy-MM-dd format. If you will enter any other format of Date, it will show error message on the chart.

For Example :- If 08-10-2016 entered as a value in the filter for Created Date, it will display error as shown below:

Now the challenging part – When Date range (From Date & To Date) was selected and passed to the chart filter, it was showing wrong data on it and when chart was clicked to view the source report, it redirected to the report where the filter date was changed to some other date.

For Example :– Selected Date 2016-10-01 (1^st October) changed to 10/01/2016 (10^th January), it should be 01/10/2016 on the report.

To figure out the problem, we tried logged in as different users. And We found that, for some user it was working perfectly and also for some other user, it was showing wrong data.

Reason – Based on Locale Settings of each user, it changes the date format on the report filter (you can see the parameters passed on the report URL).

To resolve this issue, we explored all available forums, twitter, blogs but didn’t get any post related to this.

Solution we came up with :–

• Create a formula field Created_Date on Opportunity which return Number.
• Enter the following formula and save it.

• If Created date is 8/10/2016, it will return 20161008 (year+month+day) as its value.
• Replace the field from CREATED_DATE to Opportunity. Created_Date__c in the Column attribute of filter. 

• As the Created Date is in Number format, the Selected Date has to be passed as Number.
• So in the controller, the selected Date was formatted as Number (year+month+day).

Whenever the Date range was selected on the page to pass on to the report filter, it was getting formatted as number (year+month+day) and was checking with the custom Created_Date field which was also containing the CreatedDate in the Number format.
Finally, it started showing correct data on Chart irrespective of the Locale setting of the logged in user and its done.

I hope you will like this post. So, Shoot it. Save it. Share it.

Thanks!!

Part 2 – DML operation with CLIq

For other DML operations (Insert, Update, Upsert, Delete), you have to repeat the above steps. It means you will be having different folder for each operation.

Example – For Export we have created Export_Accounts directory structure likewise we have to repeat the same steps for other DML operation.

•  Select the operation and enter different process name.

• Enter the Object API name (Query in case of Export) and create the CLI files.

• It will create folder same as process name 

Instructions for Configuring DML

C:\dataloader\cliq_process\Import_Accounts\read 

• Replace this file with your csv. Do not change the file name. Just replace the existing .CSV file with your file.

• The first row should have the field names. Make sure to update the .sdl file. C:\dataloader\cliq_process\Import_Accounts\config\Import_Accounts.sdl.         

• Open processname/config/processname.sdl. Create a mapping with the CSV column header on the left and the Salesforce Field on the right.

Note: Insert will not require id mapping, update/delete requires id mapping. 

Now the configuration part is done. Just we need to schedule the bat file, which can be scheduled on Windows using Windows Scheduler.

Thanks!!

Data Loader CLIq (Command Line Interface quickstart)

Introduction:

Data Loader is tool provided by Salesforce for bulk import or export of data. Sometimes, we come across many scenarios where we want to automate the operations or we perform the same task repeatedly.

In this case, we can use Salesforce Data Loader Command Line Interface (CLI).

CLI runs Data Loader from the command line but it is challenging and difficult to set up. So there CLI quickstart (CLIq) comes into help. It will generate all required files for Command line data loader.

  Installation Steps:

•  Install Data Loader version 17.0 or later.
• To download Data Loader, login to Salesforce and go to

       Set Up -> Data Management -> Data Loader -> Download Data Loader

• Download the latest version of CLIq

          https://code.google.com/archive/p/dataloadercliq/downloads

• Unzip cliq.zip and copy & paste the ‘cliq’ folder into your Data Loader home directory

      Typically, this is: C:\Program Files\salesforce.com\Data Loader\cliq

 Configuration Steps:

Go to C:\Program Files\salesforce.com\Data Loader\cliq and open cliq.properties file for proxy settings.




Note: By default, cliq will set for Production. To use it for the sandbox, uncomment line 23 (remove # and save it).

Let’s Get Started – (Part 1 – Data Export)

To run CLIq, run cliq.bat on Windows (cliq.sh on UNIX).

1. Choose an Operation

2. Enter the process name (string). Example: Export_ Accounts

3. Click on Next and enter your org credentials (username, password + security token)

4. Enter Query (Export) or Entity (Object API Name).

5. Review results and create Data Loader CLI Files

Note: When trying to run process.bat for Salesforce Data Loader from the command line, I encountered an error, "The system cannot find the path specified".

Follow the below steps to resolve the error:

1) Create a folder dataloader (no spaces) in C:\ drive (C:\dataloader)

2) Copy dataloader.jar ,JAVA folder and cliq folder from C:\Program Files\salesforce.com\Data Loader to C:\dataloader

3) Run cliq.bat from cliq folder

A directory will be created with name same as Process name.

·         C:\dataloader\cliq_process\Export_Accounts

As we selected Export Operation, it will automatically create a Export_Accounts.csv file with all data in C:\dataloader\cliq_process\Export_Accounts\write.

Thanks!!