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")
set chart=ActiveDocument.GetSheetObject("CH01")
ActiveDocument.Variables("chart_row_count").SetContent chart.GetNoOfRows, true
.
3 comments:
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.
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...")
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
Post a Comment