Friday, December 16, 2011

Count of Rows in a Chart






Once in a while someone asks me if there's a way to count the rows in a chart and put the number of rows in the chart title. I usually respond by asking, "why would anyone want to do that?" Usually, an expression that counts the distinct values of the fields used as the chart dimension is close enough. If you want a method that actually counts the rows in a straight table chart, here's something I have used that counts the rows and puts the count into a variable named chart_row_count. It will need to be modified for your environment and tested. (***make sure you also see the suggestion near the end)


Create a macro in the module code that looks like this:


Sub Count_Rows

'-------------

'/* Put your chart object id in the next line */

Set CHRT=ActiveDocument.GetSheetObject("CH70")

On error resume next

'/* in the next line, put an upper limit on the */

'/* loop bigger than your expected chart row count */

For i=1 to 500

ccontent=CHRT.GetCell(i,0).text

If Err.Number <> 0 then

Err.Clear

On error goto 0

ActiveDocument.Variables("chart_row_count").SetContent i-1, True

Exit For

End If

Next

On error goto 0

Set CHRT=nothing

End sub


The count in the variable includes the total line if there is one. You can use the variable value in a text expression in your chart title. One thing you must do that will vary from document to document depending on your requirements-- you must figure out how to trigger the macro when the number of rows in the chart might have changed. Triggering when a selection changes is easy to do. Triggering when a cycle group is changed will be harder. For testing, use a button to trigger the macro.


= = = = = = = = = = = = = = = = = = = = = = = = = = = = =


*** nrbhattad suggests this much simpler macro for finding row count:
set chart=ActiveDocument.GetSheetObject("CH01")

ActiveDocument.Variables("chart_row_count").SetContent chart.GetNoOfRows, true



.

3 comments:

John W. said...

I want to set the variable intRows to table.GetRowCount. Why do I need True or False after the first argument and what does that mean?

Have looked for an explanation elsewhere and cannot find.

-TB said...

Hi jjwild,
You are talking about the setcontent method used to update a document variable. The API Guide (available for download from QlikCommunity) says the second parameter is the UpdateMRU parameter and I have always set it to true because every time I've seen an example of that method it was set to true. You should try leaving the second parameter off and see if it works (I often give the advice: "try it - what's the worst that can happen...")

brooks said...

Thanks for the post. I'm using it because my straight table is set to a %. I'm getting the count from that and using it in the aggr for a drill in pivot. Note: to count out the 'Others' I'm testing for the word 'Others' and removing a row (actually 3 to account for headers and sumbtotal row) off the total row count...works great! Thanks, Brooks