Sexy Chart Maker!

[red1]

Would you like your Sales YTD Chart looking like the first one? And then with a simple input and click becomes the next one? Dream no more. It is happening with the Sexy Chart Maker!

StackedBarDifferent.png (42.49 KiB) Viewed 13215 times

The first was created by login as SystemAdmin and at the Chart Maker Process:

ChartMakeByProduct.png (37.6 KiB) Viewed 13215 times

After click OK, relogin as your Client and call up your Sales Rep Dashboard and look at the Sales YTD Chart inside. Your sales data over the months are broken down according to Product. To switch to a break-down by Customers, go back to the SystemAdmin > Chart Maker:

ChartMakeByBP.png (35.35 KiB) Viewed 13215 times

If you call up the Chart window and look for Sales YTD you will see it been rewritten as:

GeneratedChartDS.png (121.24 KiB) Viewed 13215 times

If you specify a new ChartName, it will generate for you a new one (not tested yet so if anyone hits any bugs do tell me and it should be easily fixed).

In theory this Chart Maker can generate from any table source, to any field amount, to time based (or even different category but its not tested) of different time type (Day, Week, Month, Year), and any breakdown column. Just specify the in between tables for it to join and that final column breaker as shown in the two examples above. It can theoretically go to any length of joins. Thus you can have LineNetAmt>C_Order.C_BPartner_Location.C_Location.C_Region_ID (this is untested imaginary example to give you the idea behind my creation so check if your tables joining is logical with the breaker column as existing and it must work.

The chart datasource generating also will check the table data set (C_OrderLine) as having such break data before it creates that datasource line. Thus if you have fresh new data for the break, you should just give this ChartMaker a reprocess again. Just remember to do a Chart Maker process entirely in the SystemAdmin or else it will not delete its old datasource records.

This Chart model is a graphical statistical presentation layer and has no impact to actual live data. By the way this ChartMaker process is also created using the Aladdin Magic Plugin to speed up my development of this.

The Chart Maker source is committed to http://bitbucket.org/red1/org.red1.chartmaker. Binary plugin is at http://sourceforge.net/projects/red1/files/ChartMaker/. You do not need the 2Pack. It is already included in the plugin. Just install using Felix Console and login SystemAdmin you will see the Chart Maker process.

[red1]

I found a trick where you can do Chart Maker and Dashboard view side by side without switching to SystemAdmin and back! First in SystemAdmin delete the Sales YTD's DataSource tab details as you be generating them entirely from your ClientAdmin.
You can also make the DataAccessLevel of Chart and ChartDataSource tables to 'ALL'.

TableAccessLevel.png (43.83 KiB) Viewed 13112 times

Then you can operate freely in your GardenWorld or ClientAdmin calling up the Chart Maker and Sales Rep Dashboard side by side.

SideBySide.png (45.42 KiB) Viewed 13112 times

After clicking OK, you can see the Dashboard, then go change some break column and refresh the Dashboard magically.

[red1]

You noticed the last screen shot uses the WhereClause space but this needs the Join Tables description in the ValueBreak space to contain the Table that is referred to. It happens to have >C_Order to get its DocStatus. But if you are using >M_Product it was not sophisticated enough to include the C_Order. So now i have made it possible to simply set that extra Table in the joining as shown below.

WhereClauseControl.png (45.3 KiB) Viewed 13103 times

Note how the DocStatus that belongs to C_Order table is referred as 'b' alias. Just remember that simple sequence, every table follows subsequent alphabet after the main Table. So in this case its C_OrderLine, C_Order, M_Product as been a,b,c for you to refer in your Sql WHERE clause. You can theoretically more tables before the last break column ID such as C_Orderline,C_Order,C_BPartner_Location,C_Location,C_Country,M_Product in order that your Product breaking is filtered to certain country or region only.

Thus, i have made it possible for you to define any sophisticated WhereClause without wasting time constructing the INNER JOINs and finding the right Table alias to JOIN. My latest short snip below does the heavy plumbing to seek out the correct alias:

Code: Select all

   private char getCorrectAlias(String[] tablesjoin) {
      char a = (char)'a';
      for (int i=0;i<tablesjoin.length;i++){
         MTable table = new Query(Env.getCtx(),MTable.Table_Name,MTable.COLUMNNAME_TableName+"=?",get_TrxName())
         .setParameters(tablesjoin[i]).first();
         if (table!=null)
            return (char)(a+i);
      }
      return 0;
   }

This is called by:

Code: Select all

                  char prealias = getCorrectAlias(tablesjoin);
                  alias = (char)('b'+i);
                  
                  String tablename = tablejoin.getTableName();
                  String tableID = tablename+"_ID";
                  FromClause.append(" INNER JOIN "+tablename+" "+alias
                        +" ON ("+alias+"."+tableID+"="+(prealias)+"."+tableID+")");

[red1]

Made a Youtube movie to show how to setup from scratch and make it work on same Client page.

[red1]

In the movie, when i clicked on the bar, it does not open up with selected data because i have set the base table as C_OrderLine and its window is Sales Order which is based on C_Order table. So in order for it to work, it is very easily solved. Just create a new Window 'Order Line' with a tab for C_OrderLine. Then in the Table and Column for C_OrderLine, set its window to point to this new one.

OrderLineWindowSet.png (57 KiB) Viewed 12979 times

Login back to Client, it works as below:

MouseoverBarInfo.png (21.69 KiB) Viewed 12979 times

Mouse over any bar will give info about what is represented. In this case its a product total value of 40.5. Then clicking on it...

OrderLineWindow.png (48.16 KiB) Viewed 12979 times

The new Order Line window is open and perfectly giving only the associated records, two Order records with their LineNetAmt column totaling 40.5. You can even right click on the parent order and zoom into the Sales Order window. Now this is truly powerful and fully integrated. Not only that, remember also that this plugin brings no impact to the core iDempiere. No core code nor the ChartDataSource model needs to change.

[red1]

(ChartMaker tutorial is included in SalesCRMTutorial.pdf pages 21-25). From a request posted by Norbert Bede here - http://groups.google.com/forum/#!topic/ ... OrZ7ZWKUSE about a Pipeline Chart for Sales Funnel. I first created numerous Leads on the fly, convert them to Opportunities with different ExpectedCloseDates and SalesStages. Then I set the Chart Maker with :

OpportunityChartMaker.png (41.82 KiB) Viewed 12860 times

Note how minimal the input required to generate the sophisticated ChartDatasource model. I copy here for easy pasting to test:

Code: Select all

My Sales Pipeline
ExpectedCloseDate
OpportunityAmt>C_SalesStage.C_SalesStage_ID

The result works like instant magic:

Opportunity3DStacked.png (25.21 KiB) Viewed 12860 times

This gives a better time series Sales Funnel as the vertical columns are ExpectedCloseDates according to months. Mouse-over any colour bar will highlight its value and stage. Clicking on it will open up the linked record(s). The OpportunityAmt can be Weighted Amount with a formula * Probability. I have not tried but in theory it should work with

Code: Select all

OpportunityAmt*a.Probability/100>C_SalesStage.C_SalesStage_ID

Remember that the settings above are stored in the SavedParameters log. You can assign a label to each setting and recall them at will in just 2 clicks. No further remembering of formulas once done.

[red1]

It seems the whole bloody thing does work! Here is the first sample (with new Opportunity data)

TotalOpportunity.png (27.47 KiB) Viewed 12818 times

And after applying the weighted formula:

WeightedOpportunity.png (27.53 KiB) Viewed 12818 times

You can click on any bar to zoom into the record and see the Weighted values as exact. And all such formulas are saved in the PInstance:

SavedParameters.png (62.45 KiB) Viewed 12818 times

[red1]

Fixed some small bugs when testing as 2 dimensional Category chart, for Opportunities according to Campaign with breakdown by Sales Stages:

CampaignAndStage.png (24.38 KiB) Viewed 12811 times

C'est parfait! The secret is due to putting a correct table field in the Chart Maker:

CampaignStageSetting.png (64.53 KiB) Viewed 12811 times

Note the extra table C_Campaign and the b.Name which refers to that and so every Campaign ID is filtered by its JOIN name field. The Time field is kept blank. This proves the use of non time series as a CategoryColumn. Now you can play with any type of dimension. Source and plugin links updated respectively.
With the power of the Saved Parameters, you can set all these charts one time and choose them at will. Here is the Sales Rep Dashboard for the two charts side by side to see its appeal.

BothCharts.png (89.46 KiB) Viewed 12811 times

[red1]

Made a movie to better demonstrate the latest above.