September 16th, 2009
Few months ago I wrote an article about exporting binary data like picture in Navision and now I want to bring up another article about having chart in Navision reports or in the form. Certainly I am not the first person to have this idea, in fact I can found others have done similar idea and create a component for our Navision. Despite of this, my horses still goes strong and runs like young stallion reaching to the finish line. Well tremendously I have some points to share to you where we could leverage our Navision to certain extend as follow:
- Able to show the chart in Navision form and report.
- You can print the chart in report as norm as typical Navision report.
- You can save the chart as Navision Picture (BMP) and store it within your Navision tables for sharing it with other peoples.
- If you wish, you could extract the chart picture into XML file and upload it into your web application.
The distractive points of this demonstration are:
- Any Navision clients who need to generate the chart need to register the .NET assembly.
- Need to consider with the chart resolution as the chart is BMP image format. The bytes of the chart need to consider if you need to keep them in the database.
I have attached in this article the .net codes project for your perusal and please don’t mind with the untidy codes and the nausea as the side effect whiles you studying it. If you are working as developer like me, you could use this code freely and show it off to your boss and hopefully you might get excellent appraisal at the end of years. Hope you can benefit from this article and let us start with substantial matters of how I doing it
The idea was slipped in my mind when I have posted the article about the .net COM to extract Navision picture into XML format file. With this possibility, I could then transfer any image into Navision world in Stream and store the binary into BLOB data type field. Since Navision report and form able to show picture in BMP format, thus I could have a .Net program to render the chart and format them as image and push back to Navision. Well that sound very straight forward isn’t it. Indeed, you could say the critical part is interoperability between Navision and .NET world, but this issue is not new if you had read my last article.I have to admitted that the challenge for creating .NET component is the designing the classes and specially the interface for COM client like Navision. Firstly I have to tackle the data type marshalling, secondly how you expect Navision C/AL create the automation and passing the data sufficiently to generate the chart itself and on the same time easy and flexible. Thus, I would like to elaborate the interface classes that I have in this project to ease your understanding the program and eventually you able to enhance it like a new toys.
Glimpse of the sample chart
I would like you to see some of the screen shoot of the chart report and chart in the form that I have here. Hope it is clear what you can have from this article and invite the ambience from beginning. Below screen is the chart in Navision forms.
Below report is the Standard Navision report of inventory Top 10 list. Trust me now you won’t like to print your Navision report like this just in black color.
The standard Salesman commission report would be that easy now to see who is making more money and who is not.
Designing the class interfaces are essential element in interoperability project. It is not only concerning the relationship and interaction within the classes in .Net project but also to think externally on how Navision C/AL will call up the component and accessing the component properties and compatibility. Thus my elaboration in this article would be more focus on the interface classes and how Navision C/AL using it and suggest you to study the rest of the implementation codes.In the .Net project, I have created two C# files to describe the interfaces class. In brief I can explain their functions as follow:
iTextStyling interface class
This interface textstyling class is to define following attribute font name, size, colour, and font style. I need to have this interface because there are some elements of the chart need to determine the text attributes. Namely Chart title could have its own size larger than the chart legend, while the axis text could have specific font family name.
- FontFamilyName property is to set the font name. You can simply refer the font name similar in the Microsoft word and use it.
- FontSize property is to set the size of the font. Again you can have a feel to see the size based on Microsoft word.
- StringFontStyle property is to set the font style. The valid string values are: Bold, Italic, Regular, Strikeout, Underline.
- RGBColor property is to set the colour in integer value. In Navision colour is calculated as value = Red + (Green*256) + (Blue*256*256).
iDataGraphSeries interface classThis interface class is mainly is to help COM application like Navision to submit the chart data and unite them in one entity of series. Thus one series should contain array of fact data that interrelated each other. For example like in the picture 1 above, the series 2008 is defined of element books, movie, game and toys and series 2009 should have the same number elements like series 2008. Not like other class interface which allow COM client instantiate the object, this class interface can be created from a method in iGraphGen interface class.
- SeriesName property is to give the name of the series.
- COMSeriesStyle property is to set the style of the Series. The style in here is referring to the iTextStyling class interface. With regard to .NET interoperability rule the complex object type in .Net need to use object type at interface and .Net will marshal this object as Variant Data type in COM application.
- SeriesColorRGB property is to set the colour of your series for the bar or line chart.
- AddDataSeries method is the method to add a new element of data into your series. You need supplying the caption, the data value and the Style of the caption. Only Pie chart would use the caption style for your legend and for your slice of pie colour.
- ResetData method is the method to clear all the data elements inside this series.
- TotalDataValue method is the method that returns total value of the data in this series.
iChartTitle interface classThis interface is simple interface class to specify your chart title. It is contain the string to contain your title and the title style object.
iGraphGen interface class
This is the main interface class that will consolidate the whole information and rendering the chart. Briefly the interfaces must make sure COM client easy to use and compatible with Navision C/AL.
- GraphHeight and GraphWidth properties specify the dimension of the graph area in pixel.
- BackgroundColorRGB and BorderColorRGB specify the colour of the chart canvas and the border
- CreateNewSeries method is the method to create a new iDataGraphSeries object. It needs series name, style and colour and there is internal parameter to push down from GraphGen class to this object and that is the reason this class cannot independently created just like that.
- UploadDataSeries method is to insert a new data series into the graph object.
- ResetDataSeries method is to clear all the data series in the graph object.
- PieChart, BarChart and LineChart is the method to render the chart respectively. The title is the only parameter input. The rendering is done at memory stream within GraphGen class container.
- GetIStreamBMPImage method is returning COM stream binary data of BMP format image. The image is the chart that has been rendered by previous chart method.
- A COMInitGraph method is the method for COM client to initiate this GraphGenerator object. COM component can only have default constructor with no parameters, therefore this method is mainly to address COM limitation.
Navision C/AL side
This section, I will run thru the C/AL code to use the .NET COM assembly to generate the chart at the form. This is code using simple static data to generate the chart. First I will show you the final form that we have to achieve:
Variable that we needIf you have registered the assembly correctly, you will able to find the automation with prefix name NavGraphGen like following screen.
Create SetTextStyle function
This function is to setup the TextStyle variable object, thus we can reduce repeated same code of lines.
Create GenerateColor function
This function is to generate random colour.
Instantiate the NavGraph Object and pass the parametersInside the push button to show the Graph I wrote the codes to generate the codes, firstly I need to supplying the size graph resolutions in pixel and call COMInitGraph method to initialize the parameters in the component.
Prepare the Chart Title
The Chart title is pretty straight forward
Prepare the Data series and upload it.
In here I have created the first data series and I call it 2008. Then I am pushing in the data element series by calling the AddDataSeries. At the end to enclose the data series, I have call UploadDataSeries to store it into NavGraph object. In the complete codes, I have created the second series and do similar steps.
Call chart method to render the chart
Once you have done upload all the data series, then you can call up one of any chart method to render the chart in memory. Then Call GetIStreamBMPImage to capture the chart image in BMP format and store them in temporary record blob field type. I am using Copystream function to transfer the binary stream to blob field.
Save the chart and Export the chart as BMP file
You should call modify to save the binary data into the temporary records and don’t forget the call Calcfields to ensure upon calling export, the blob binary is full loaded.
I do believe a thousand words may not enough to explain the details but by dissecting the codes directly might be better so I will leave the class implementation code to you to studying it. Nevertheless I would like to share with you of the knowledge that I learned from this program.
.Net colour and Navision colour
I ever mentioned earlier about the Navision colour which comprise of RGB values. However in .Net world this is not the normal way to specify the colour. I have define a class helper inside the project to help me do the transformation. You could see at below codes:
Within Navision C/AL , I define the colour randomly like below:
.Net chart resolution and Navision control resolution
While .net could be wide range of unit measurement when dealing with resolution, but the default unit is in pixel. While Navision unit measurement in determining the size is in 1/00 of millimetres. Thus to simplify the conversion into pixel = NAVresolution / 100 * 3.77. (The figure 3.77 is the fixed value that I get from internet.)Sample code in C/AL as follow:
GDI+ in .NET
This is the core library that intensively used to accomplish the objective to render a chart. Fortunately the way to use GDI API is not that complicated and with some exercises I could learn some skill enough to accomplish it. The rest of if more or less is my own logic flow to construct everything from the scratch.Chart Axis calculationBelow diagram is visualization area of the chart. The graph is divided into areas where each area needs to calculate dynamically. Currently the area placement is fixed and only the size could be different time to time. You might enhance in such the title or the legend could be in different location, but right now everything is in fixed place.
Text size Measurement
From the picture above, the size of each area are depends on one or many factors is text that we need to draw in there. In example, how much wide the legend needs are depends on length of the series name and the font that we use here certainly the font size. Thus we need information to gather the size required to draw a text. Here below the GDI API that provides such information.
Vertical axis scale calculation
In sort, I have been studying how Microsoft Excel draws the chart and what is the parameter to determine the scale of the chart. Until then I have come to conclusion that the scale is based on number 1, 2 & 5 subsequently depending on your max value the based number is simply multiple by 10. Another criterion is how many of scale that you want to display in your chart. Honestly displaying 10 time of vertical axis should be more than enough and that is how should be chart in Microsoft Excel works. Below codes is the logic for determine the vertical axis scale and number of vertical axis to display. Note: _maxBarMeter is set as 10.
Skewed/Vertical Label for horizontal Axis.
This is the most challenging effect that I have done in this project. Basically if the text need more space than it has, the text need to display in two options, skewed angle or vertical angle. The GDI+ function provide a way to achieve this effect but it is not straight forward. In my case I have to use few functions which involve rotation, translation and transformation. Especially if you do rotation it would affect drawing coordinate. You might find the text placing for skewed or vertical label is not easy and perhaps the current .Net project need a little bit adjustment.
I would be honest with you; I have just learnt the GDI+ functions and part of the codes just follow from the books or other contribution. The codes and the calculation might not be perfect to tackle any situation. Please freely to improve the codes and share with me if you willing to do so. I hope you can learn something from this article and can be useful for your project implementation.
Below is the .net project that I develop using Visual Studio 2008 Express edition in C# languange.
Last but not least, the Navision objects which are based on Nav 4.0 Sp3: