OWC 10 Chart question for Thao Moua

[Avi Perez]

Dear Mr. OWC Chart Guru

I was wondering if you have the quick solution to 2 messy problems:

1.
I have users who create multi-tiered MDX data sets, that bring back multiple
fields (at multiple levels) on both the row and column axis. When
auto-plotted in OWC, the chart tends to plot the upper levels only. Is there
a way to program the chart to select all levels or the lowest level - or
perhaps just simply control it? eg. query returns 5 columns in the cellset:
1997, Q1, Q2, Q3, Q4. Only 1997 gets plotted. The user *could* drill down -
but is there an auto way to set this?

2.
If the MDX brings back a simple table of data, and the user changes the
chart to pie (for example), only 1 series or 1 category of the underlying
table is shown (depending on the plot option). Again, the user *could*
manually set up a multi chart and then drag and drop the field. But, is
there a simple method of coding the movement of the multi series to the
multi chart field?

thanks in advance!!!

Avi

 

[Thao Moua [ms]]

1. Are you saying Chartspace is only plotting the 1997 fields and ignoring Q1, Q2, Q3, & Q4? If so then make sure you include the following code before you set the value data

ChartSpace1.plotallaggregates = chPlotAggregatesSerie
ChartSpace1.SetData chDimValues, chdatabound, array("1997","Q1", "Q2"

However, if you're looking for code to drill down the data, then tweak the following sample code

sFieldName = "Company
Set objPFS = ChartSpace1.InternalPivotTable.ActiveView.FieldSets(sFieldName
Set objPC = ChartSpace1.InternalPivotTable.Constant

'Setting the PivotField.IncludedMembers and PivotField.Excluded members to be include/exclude what we want/don't want. We will use the IncludedMembers property
Set objPF = objPFS.Fields(sFieldName
objPF.IncludedMembers = Array("msft","apple"

2. See the following cod

Chartspace1.HasMultipleCharts = tru
Chartspace1.setdata chDimCharts, chDataBound, "company

Hope this helps

Thao Mou
OWC Webchart Suppor

This posting is provided "AS IS" with no warranties, and
confers no rights. Use of included script samples are
subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

 

[Avi Perez]

Thanks Thao

Your answer really did help to clarify many things. And I now understand the whole internal pivot table concept for charts (which was hard to grasp in the beggining).

I have one follow up, which is related to Q1:

The include/exclude approach assumes that I have an enumerated list of items to keep and drop. But, I would like the programming to relfect the choices presented in the chart from the MDX automatically. An example MDX against the Foodmart 2000 Sales cube is as follows:

SELECT { [Time].&[1997].&[Q1], [Time].&[1997].&[Q2] } ON COLUMNS , { [Customers].[Country].&[USA], [Customers].[State Province].&[CA], [Customers].[State Province].&[OR] } ON ROWS FROM [Sales] WHERE ( [Measures].[Unit Sales] ).

When default plotted in the chart, only USA gets plotted. I have no trouble now coding to select the LOWEST level of say the row axis, and setting that in the setdata statement. As per below:

set vw = CSpace.InternalPivotTable.ActiveView
set m1 = vw.rowAxis.FieldSets

for each m2 in m1
set m3 = m2.fieldS
for each m4 in m3
if m4.isincluded then
set m5 = m4
end if
next

CSpace.setdata c.chdimCategories, c.chDataBound, m5
next

This code changes the default plot to CA and OR on the chart. But, I am having trouble setting it such that both levels (country and state province) on the row axis are plotted. Again, I don't have a literal list - only the output of the MDX - which the code is blind to.

Thanks again
Avi

 

[Thao Moua [ms]]

Plotting multiple fields from an OLAP can sometime be little tricky. Why don't you take this approach. Bind the PivotList conrol to the data and then bind Chartspace to the Pivotlist control. With this approach, Chartspace will always plot what's on the Pivotlist control

Pivotcontrol.ConnectionString = "Provider=MSOLAP.2;Persist Security Info=true;User ID=guest;Password="""";Data Source=zendata;Connect Timeout=60;Initial Catalog=FoodMart;Client Cache Size=25;Auto Synch Period=10000;

Pivotcontrol.commandtext="SELECT { [Time].&[1997].&[Q1], [Time].&[1997].&[Q2] } ON COLUMNS , { [Customers].[Country].&[USA], [Customers].[State Province].&[CA], [Customers].[State Province].&[OR] } ON ROWS FROM [Sales] WHERE ( [Measures].[Unit Sales] )

set webchart.datasource = Pivotcontro

Thao Mou
OWC Webchart Suppor

This posting is provided "AS IS" with no warranties, and
confers no rights. Use of included script samples are
subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm