Histogram Interval

May 24, 2012 at 7:00 PM

Hello Tony,

Given this method:

protected void

    AddHistogramImageToPictureBox

    (

        String sWorksheetName,

        DynamicFilterParameters oDynamicFilterParameters,

        PictureBoxPlus oPictureBox

    )

    {

        Debug.Assert( !String.IsNullOrEmpty(sWorksheetName) );

        Debug.Assert(oDynamicFilterParameters != null);

        Debug.Assert(oPictureBox != null);

        AssertValid();

 

        // This method uses the following technique to get Excel to generate

        // the histogram image.

        //

        // There is a hidden chart on the Miscellaneous worksheet that is

        // used for the histogram.  It gets its data from two columns in a

        // hidden table on the Overall Metrics worksheet that use Excel

        // formulas to calculate the frequency distribution of the values in an

        // Excel column, called the "source column."  The formulas use Excel's

        // INDIRECT() function to get the address of the source column from a

        // cell named NamedRange.DynamicFilterSourceColumnRange.

        //

        // In a new workbook, the frequency distribution columns initially

        // contain all #REF! errors, because the named range is empty.  This

        // method sets the named range to something like

        // "Vertices[ColumnName]", then forces Excel to recalculate the

        // frequency distribution columns.  This causes a histogram to appear

        // in the chart.  An image of the chart is then copied to the clipboard

        // and pasted into the PictureBox.

 

        Microsoft.Office.Interop.Excel.Worksheet oOverallMetricsWorksheet,

            oMiscellaneousWorksheet;

 

        Microsoft.Office.Interop.Excel.Range oDynamicFilterSourceColumnRange,

            oDynamicFilterForceCalculationRange;

 

        Microsoft.Office.Interop.Excel.Chart oDynamicFilterHistogram;

 

        if (

            ExcelUtil.TryGetWorksheet(m_oWorkbook,

                WorksheetNames.OverallMetrics, out oOverallMetricsWorksheet)

            &&

            ExcelUtil.TryGetRange(oOverallMetricsWorksheet,

                NamedRangeNames.DynamicFilterSourceColumnRange,

                out oDynamicFilterSourceColumnRange)

            &&

            ExcelUtil.TryGetRange(oOverallMetricsWorksheet,

                NamedRangeNames.DynamicFilterForceCalculationRange,

                out oDynamicFilterForceCalculationRange)

            &&

            ExcelUtil.TryGetWorksheet(m_oWorkbook,

                WorksheetNames.Miscellaneous, out oMiscellaneousWorksheet)

            &&

            ExcelUtil.TryGetChart(oMiscellaneousWorksheet,

                ChartNames.DynamicFilterHistogram, out oDynamicFilterHistogram)

            )

        {

 

            // Set the named range to the address of the source column.

            // Sample: "Vertices[Degree]".

            oDynamicFilterSourceColumnRange.set_Value(Missing.Value,

                String.Format(

                    "{0}[{1}]",

                    sWorksheetName,

                    oDynamicFilterParameters.ColumnName

                    )

                );

 

            // Excel's automatic calculation may be turned off, either by the

            // user or by code elsewhere in this dialog.  Make sure the

            // frequency distribution columns get calculated.

 

            oDynamicFilterForceCalculationRange.Calculate();

 

            // Make sure the chart is drawn immediately.

 

            oDynamicFilterHistogram.Refresh();

 

            // Tell Excel to copy the chart image to the clipboard.  (Although

            // the second argument to CopyPicture is xlBitmap, no bitmap gets

            // copied.  Instead, Excel uses an enhanced metafile.)

 

            oDynamicFilterHistogram.CopyPicture(

                Microsoft.Office.Interop.Excel.XlPictureAppearance.xlScreen,

                Microsoft.Office.Interop.Excel.XlCopyPictureFormat.xlBitmap,

                Microsoft.Office.Interop.Excel.XlPictureAppearance.xlScreen);

 

            oPictureBox.TryPasteEnhancedMetafile();

        }

    }

I was wondering if it's possible change the maximum and the minimum value of the interval where the frequency distribution is defined on. So that the chart image will come out different.

In other words we always take all the column values to calculate the frequency distribution but we just change the extreme values (Max and Min) of the chart image.

Any help is appreciated

Kind regards,

--Jacopo 

May 27, 2012 at 5:48 PM

Jacopo:

You can change the minimum and maximum values of the x-axis of the histogram by doing this:

1. Unhide the Misc worksheet.

2. Find the chart called DynamicFilterHistogram on the Misc worksheet.  In the current version of NodeXL, it's in cell Q2.

3. Right-click the DynamicFilterHistogram chart and select Select Data.

4. In the Select Data Source dialog box, click the Edit button for the "Legend Entries (Series)".

5. In the Edit Series dialog box, change the "Series values" from "='Overall Metrics'!$U$2:$U$45" to "='Overall Metrics'!$U$3:$U$44", for example.  Click OK to close the Edit Series dialog box.

6. In the Select Data Source dialog box, click the Edit button for the "Horizontal (Category) Axis Labels". 

7. In the Axis Labels dialog box, change the "Axis label range" from "='Overall Metrics'!$T$2:$T$45" to "='Overall Metrics'!$T$3:$T$44", for example.  Click OK to close the Axis Labels dialog box.

8. Click OK to close the Select Data Source dialog box.

What you've done here is to change the source data for the histogram from the entire data range to the entire data range minus one "bin" on each end.  (There are 43 bins.)  You can chop off additional bins by narrowing the data range further.

For more information on how NodeXL uses the Excel chart to generate a histogram, read the following:

A. The comments in the source code you posted.

B. The explanatory text a few cells down from the DynamicFilterHistogram chart on the Misc worksheet.

-- Tony

May 31, 2012 at 6:32 PM

Thank you so much Tony. That wasn't really what I needed but all those infos helped me a lot.

I have an other question, which is not really related with the previous one:

Could you please advice me in which way could I change the "DynamicFilterRangeTrackBar", in order to let the user to choose a smaller value than "AvailableMinimum" and a bigger value than "AvailableMaximum", by changing the value from the two differents "NumericUpDown" Control?

Thank you in advance for any tips.

 

Kind Regards,

--Jacopo

May 31, 2012 at 7:36 PM

That makes no sense to me.  Those controls are designed to let the user select a range of numbers (or DateTimes) from a range of available numbers.  What is the meaning of selecting a range that isn't available?

But if it makes sense to you, the source code for the controls is available on CodePlex and you can modify the controls to suit your needs.  You can find the source code on the Downloads tab at http://nodexl.codeplex.com/releases.  Look for the latest "WinFormsControls Class Library" under "Other Downloads."

-- Tony

May 31, 2012 at 8:46 PM
Edited May 31, 2012 at 8:47 PM

Do you think is that not feasible?

The column connected to the "DynamicFilterRangeTrackBar" considered have values changing all the time, and the range available is difficult to predict a priori. 

So my plan would be to set a values range  at the beninning on the TrackBar and give the possibility to the user to go outside the pre-determined range by the two NumericUpDown.

Thank you.

--Jacopo

May 31, 2012 at 9:11 PM
Edited May 31, 2012 at 9:20 PM

As a user, I would find that confusing.  The control displays a range of available values, and the numbers indicate the actual range I've selected.  The sliders and the range bar (the colored bar between the sliders) are a graphical indication of the same selected range.  Among other usability problems, if I can enter a number that isn't in the available range, what should the sliders and range bar do?  They can no longer represent the selected range, because the selected range extends beyond the available range.  Thus, sometimes the sliders and range bar would indicate the selected range, and sometimes they wouldn't.

In Dynamic Filters in the ExcelTemplate, the column data is assumed to be static while the Dynamic Filters dialog box is open.  If you edit a column while the dialog box is open, you can update the available ranges within the dialog by clicking Refresh Filters.

Your scheme is feasible, if you want to modify the source code.  But I think it would be a bad design.

-- Tony

Jun 6, 2012 at 8:42 AM

Thank you Tony. I wasnt neither confident to choose that approach. In fact I managed in a different way: I found a lower bound and an upper bound of my dynamic "weighted network" and now are those values the bounds of my global dynamic filter.

 

One more thing: I would like to know if you can advice me a way to create a package in order to install in an other windows system my NodeXL version. I have also in my solution a BerkeleyDB project since I implemented a bdb database to store the dynamic network's data.

Thank you in advance.

Kind Regards.

--Jacopo

Jun 7, 2012 at 4:08 PM
Edited Jun 8, 2012 at 4:32 PM

The Excel Template setup program is complex, temperamental, brittle, and is going to be thrown out and started from scratch when we get the time.  Because of that, I can offer only limited support for developers who want to use or modify it for their own applications.

First, to build the setup program, do this:

    1. If you have modified the file NodeXL\ExcelTemplate\NodeXLGraph.xltx, you must copy the modified file to NodeXL\ExcelTemplateSetup\TemplateModifiedForClickOnce\NodeXLGraph.xltx.

    2. In Visual Studio, go to Build, Clean Solution.

    3. Go to Build, Rebuild Solution.

    4. In Solution Explorer, select the ExcelTemplateSetup project.

    5. Go to Build, Build ExcelTemplateSetup.  (Do NOT use Build, Rebuild ExcelTemplateSetup.  Remember when I mentioned "temperamental"?)

You will find your Setup.exe program in NodeXL\ExcelTemplateSetup\Debug.  To run it on another computer, you need to copy the entire contents of the Debug folder, including its subfolders, to the other computer, then run Setup.exe there.

If you have created some sort of database, you'll need to add it to the ExcelTemplateSetup project so it will become part of the setup:

    6. See "How to: Add Items to a Deployment Project" at http://msdn.microsoft.com/en-us/library/z11b431t.aspx.

-- Tony

Jun 11, 2012 at 9:41 PM

Hi Tony,

Thank you for your reply.

Sorry if I asked too much to you. But you are very clear and useful all the time with your tips that it's hard to manage without.

If you have time, I have a question:

How can I approach the problem to add the feature "edges' groups" similar to "vertices' groups" which already exists?

Do I need to know something in particular to add this functionality, do you have particular tips?

I really need it, since I'm visualizing different congestions in road networks.

Thank you in advance.

Kind Regards.

--Jacopo

Jun 12, 2012 at 4:07 AM

Jacopo:

That's a big question.  Before we talk about implementation, you need to be clear about what your goals are.  Specifically, you should ask yourself two important design questions:

1. What are edge groups, and how are they created?

(When we designed vertex groups, the answer was that a vertex group is a set of vertices that are related in some way, and that they can be created using one of three techniques.  First, NodeXL can look at how all the graph's vertices are connected to one another and group the vertices by the clusters or connected components it finds.  Second, NodeXL can group the vertices according to the values it finds in a column on the Vertices worksheet.  And third, the user can manually select a set of vertices and tell NodeXL to put them in a group.)

2. What can you do with edge groups?

(For vertex groups, you can select a group, which selects its vertices and edges; assign the same shape and color to all the vertices in a group; lay out the groups in boxes; collapse a group; hide a group; and calculate metrics for a group.)

Because you can do so many things with vertex groups, their implementation was quite complicated.  We probably spent several man-months of programming time getting it all to work.  I'm sure you don't want to spend that much time on edge groups, so if you can specify exactly what you need them for, perhaps we can talk about a simpler implementation that won't take up your entire summer.

-- Tony

Jun 16, 2012 at 2:18 AM
Edited Jun 16, 2012 at 2:21 AM

Tony,

thank you so much for your help. A simple implementation is a good idea for me.

I'm going to explain to you what I'm working on.

I have been coding in order to visualize a time evolving network, which can have edges' weight or nodes' weight evolving over time. The weight is an attribute of the Edges' Worksheet or the Nodes' Worksheet according to the kind of network imported, which can be edges or nodes evolving over time. So all the informations about the network are stored in a Berkeley Database. Each time the user, by adjusting a slider, changes the time interval to visualize: the Berkeley DB is queried, the worksheet is updated with the query's results and the graph is refreshed.

I add also an other functionality to discover all Significant Anomalous Regions in the network (a region is a set of connected edges or nodes, according to the kind of network imported). An othere Berkeley Database contains the informations about the regions. According to the time interval selected an other column (besides the weight column), called "Region" is updated. The"Region" column contains the region's number of the edge or node associated. So we can have three cases:

Given a time interval selected:

1)  if the edge or node belong to a region, then the "Region" column's cell associated with that edge or node is updated with the region's number.

2)  if the edge or node doesn't belong to any region, then the "Region" column's cell associated with that edge or node is updated with an empty cell. 

3)  if the edge or node belong to more than one region (we call this case "overlap"), then the "Region" column's cell associated with that edge or node is updated with 0.

Moreover each region's number has a univocal color associated in order to show a given region always with the same color.

So when the "Region" column is updated also the Color column is updated.

I would like to have a functionality for edges called edges groups and similarly with nodes, vertices groups, but maybe I'm already able to do that with vertices if programmatically put a set of vertices in a group. Do you think is a good idea?

This new feature will create a new Worksheet (Edges Groups or Vertices Groups) with three columns: Region ID, Color and Score. Each time I select a group, all the edges or nodes belonging to that group are highlighted in the Graph.

I hope I have been clear. Could you give me some advice?

Thank you in advance.

Kind Regards.

--Jacopo

Jun 18, 2012 at 7:38 PM

Jacopo:

First, NodeXL already has vertex groups, so you don't need to implement those.  One way you can create vertex groups is with NodeXL, Analysis, Groups, Group by Vertex Attribute, where you specify a column on the Vertices worksheet whose values you want to use to create the groups.  In your case, you might tell it to group by the values in the Region column.  All the vertices in "Region 1" will then go into one group, all the vertices in "Region 2" will go in another, and so on.  NodeXL stores the group information in two worksheets--Groups and Group Vertices--although most users will look only at the Groups worksheet.

When your graph has groups, you can select a group on the Groups worksheet.  That will select all the vertices in the group, and all the edges incident to those vertices.  Also, you can control the color and shape of the vertices in each group.

So does that satisfy your requirements?  If not, what specifically is missing?  Do you need to be able to select an edge group (a concept that NodeXL doesn't implement) and have only the edges in that group be selected, without any vertices being selected?

-- Tony

Jun 18, 2012 at 9:06 PM

Thank you very much Tony.

I just need a funcionality identical to "Group by Vertex Group"  for edges where in:

-"Group the graph's vertices using the values in this column on the Vertices worksheet", I insert Region

-and in "The column's values are", I select "Numbers".

 

And Yes I need to be able to select an edge group and have only the edges in that group to be selected. It doesnt matter if the vertices, belonging to the edge group selected, are selected or not.

Do you think will it be hard to implement?

Thank you again for your tips.

ciao,

--Jacopo

Jun 18, 2012 at 9:29 PM
Edited Jun 18, 2012 at 9:30 PM

One last thing:

How could I find out the argb code((x,y,z)) of the edges default grey colour?

I need to avoid to include that colour among the regions' colours.

Thank you

--Jacopo

Jun 18, 2012 at 11:51 PM

On the default edge color:

You'll find it in the GeneralUserSettings.cs file.  It's the DefaultSettingValueAttribute for the EdgeColor property:

    DefaultSettingValueAttribute("Gray")

"Gray" has an RGB value of 128, 128, 128.

-- Tony

Jun 19, 2012 at 12:45 AM
Edited Jun 19, 2012 at 12:46 AM

Jacopo:

I encourage you to find a simple way to implement the selection behavior you need without having to create the infrastructure for a new, full-blown concept of "edge groups."  As I mentioned, vertex groups are quite complicated in NodeXL, because there are so many ways for the user to create them and use them.  Edge groups might be somewhat simpler, because there would be fewer required behaviors, but they still wouldn't be trivial to implement.

If your goal is to let the user select a group of edges, would the following simple idea suffice?

    1. You add a column called Group to the Edges worksheet.  Note that there is no new worksheet added here; you're just adding a column to an existing worksheet.

    2. You populate the column with group names in a manner appropriate to your application.  Rows 2 and 4 might have a "Group" value of "Group A," and rows 3 and 5 might have a value of "Group B," for example.

    3. You add a "Select Edge Group" button to the Ribbon that pops up a Select Edge Group dialog box.

    4. In the Select Edge Group dialog box, you programmatically read the Group column on the Edges worksheet and populate a ListBox with the unique names of the group names you find there.  The user will see a list containing "Group A" and "Group B," for example.

    5. The user selects "Group A" in the ListBox.  In response, you programmatically select all the rows in the Edges worksheet that have a Group value of "Group A," which are rows 2 and 4.  If he then selects "Group B," you clear the worksheet's selection and then select all the rows that have a Group value of "Group B," which are rows 3 and 5.

All that's involved here is a new dialog box that talks to the Edges worksheet.  It doesn't have to interact with any other part of the program, such as the TaskPane or the NodeXLControl.  That's because when you select a row in the Edges worksheet, NodeXL already takes care of selecting the corresponding edge in the NodeXLControl.

If this idea works for edges, you might also do the same thing for vertices (a Select Vertex Group dialog box that talks to the Vertices worksheet) and not use NodeXL's vertex groups at all.  That would make for a consistent user experience.  Or you might combine both functions into one dialog box.

Note that selecting an edge in the Edges worksheet automatically selects the edge's adjacent vertices by default, and selecting a vertex in the Vertices worksheet does the same for the vertex's incident edges.  You can, however, change this by going to the Vertices tab of the Graph Options dialog box and unchecking "when a vertex is clicked, select its incident edges."

-- Tony

Jun 22, 2012 at 1:06 AM

Thank you very much Tony. It's the best idea. I will tell you more what I think about that later on. Thanks.

I have a short question: Where should I look, in the code, if I want to know how works when I press a group's cell and automatically it will select the mathing vertices?

Thanks.

Best Regards.

--Jacopo

Jun 22, 2012 at 6:42 PM
Edited Jun 22, 2012 at 6:42 PM

Jacopo:

That task is managed by a class called SelectionCoordinator, which you will find in NodeXL\ExcelTemplate\Util\SelectionCoordinator.cs.

-- Tony