OWC and MDX

[Julien]

Hi,
I use OWC.10 to create pivot tables and charts from a recordset and display
the result in an ASP page.
It works quite well, but I have a problem with MDX syntaxe.

I want to add a calculated field that diplays percentages. I guess MDX is
the solution but I can not figure how it works.

In my pivot I has that strucure:
Item | Value 1 | Value 2 | Value 3 | Total |
1 | 1 | 1 | 1 | 3 |
2 | 1 | 1 | 0 | 2 |
3 | 1 | 0 | 0 | 1 |
Total | 3 | 2 | 1 | 6 |

I would like to have the percentage as shown:
Item | Value 1 | Value 2 | Value 3 | Total | Percentage of value 1
1 | 1 | 1 | 1 | 3 | 33
2 | 1 | 1 | 0 | 2 | 50
3 | 1 | 0 | 0 | 1 | 100
Total | 3 | 2 | 1 | 6 | 50

Do you have any suggestions?

Thanks a lot in advance.
Kind regards.
Julien

 

[sefe dery]

Hi Julien,
ist it possible that you post your code?

I am in the beginning of my project and i want to connect a olap
datasource to owc with asp.net too.
best regards
Sefer

 

[Julien]

RS is a recordset that returns only IDs.
RVAR, CVAR, CIVAR, DEDUCTION are only 1 or 0 values indicating that records
meets or not specific conditions => It makes a count.
I would like to have a % of those that meet the condition.

Thanks for your help

'''''''''''''''''''''''
Sub pivotcountvar(rs)
Dim oPivot
Dim oView

Set oPivot = Server.CreateObject("OWC10.PivotTable")
Set oView = oPivot.ActiveView
oPivot.ConnectionString = strProvider
oPivot.CommandText = "Select Clientgroup_Desc, Team_Desc, RVAR, CVAR,
CIVAR, DEDUCTION FROM Qry_EAC_CountVariance WHERE EAC_ID IN (" & splitids(rs)
& ")"

Dim oTeam, oCG, oRVAR, oCVAR, oCIVAR, oDEDUCTION, oRVAR_Total,
oCVAR_Total, oCIVAR_Total, oDEDUCTION_Total
Dim oNET, oNET_Total, oTotal, oTotal_Total, oCount, oCount_Total,
oPercent, oPercent_Total

Set oTeam = oView.FieldSets("Team_Desc")
oTeam.Fields(0).Caption = "Team_Desc"
Set oCG = oView.FieldSets("Clientgroup_Desc")
oCG.Fields(0).Caption = "Clientgroup_Desc"
Set oRVAR = oView.FieldSets("RVAR")
oRVAR.Fields(0).Caption = "RVAR"
Set oCVAR = oView.FieldSets("CVAR")
oCVAR.Fields(0).Caption = "CVAR"
Set oCIVAR = oView.FieldSets("CIVAR")
oCIVAR.Fields(0).Caption = "CIVAR"
Set oDEDUCTION = oView.FieldSets("DEDUCTION")
oDEDUCTION.Fields(0).Caption = "DEDUCTION"
Set oTotal = oView.AddFieldSet("Total")
oTotal.AddCalculatedField "Total", "Total", "Total", "RVAR + CVAR + CIVAR"
Set oNET = oView.AddFieldSet("NET")
oNET.AddCalculatedField "NET", "Net Count", "NET", "RVAR + CVAR + CIVAR -
DEDUCTION"


Set oRVAR_Total = oView.AddTotal("NR over 100K USD", oRVAR.Fields(0),
oPivot.Constants.plFunctionSum)
'Set oPercent_Total = oView.AddTotal("Percent", oPercent.Fields(0),
oPivot.Constants.plFunctionSum)
Set oCVAR_Total = oView.AddTotal("C over 50K USD", oCVAR.Fields(0),
oPivot.Constants.plFunctionSum)
Set oCIVAR_Total = oView.AddTotal("CI over 50K USD", oCIVAR.Fields(0),
oPivot.Constants.plFunctionSum)
Set oDEDUCTION_Total = oView.AddTotal("Deduction", oDEDUCTION.Fields(0),
oPivot.Constants.plFunctionSum)
Set oTotal_Total = oView.AddTotal("Total", oTotal.Fields(0),
oPivot.Constants.plFunctionSum)
Set oNET_Total = oView.AddTotal("Net Count", oNET.Fields(0),
oPivot.Constants.plFunctionSum)
Set oCount_Total = oView.AddTotal("Count of all projects", oNET.Fields(0),
oPivot.Constants.plFunctionCount)

Set oPercent = oView.AddFieldSet("oPercent")
oPercent.AddCalculatedField "Percent", "Percent", "Percent", "RVAR /
(RVAR + CVAR + CIVAR)"

oPivot.ActiveData.HideDetails
oPivot.BackColor = "Brown"

oView.TitleBar.Caption = "Variance summary. Count of variance."
oView.TitleBar.BackColor = "Brown"
oView.FieldLabelBackColor = "Brown"
oView.HeaderBackColor = "gold"
oView.TotalBackColor = "white"

'oView.RowAxis.InsertFieldSet oCG
oView.RowAxis.InsertFieldSet oTeam
oView.DataAxis.InsertFieldSet oRVAR
oView.DataAxis.InsertFieldSet oPercent
oView.DataAxis.InsertFieldSet oCVAR
oView.DataAxis.InsertFieldSet oCIVAR
oView.DataAxis.InsertFieldSet oTotal
oView.DataAxis.InsertFieldSet oNET
'oView.DataAxis.InsertFieldSet oCount

oView.DataAxis.InsertTotal oRVAR_Total
'oView.DataAxis.InsertTotal oPercent_Total
oView.DataAxis.InsertTotal oCVAR_Total
oView.DataAxis.InsertTotal oCIVAR_Total
oView.DataAxis.InsertTotal oTotal_Total
oView.DataAxis.InsertTotal oNET_Total
oView.DataAxis.InsertTotal oCount_Total

'oPivot.ActiveView.AutoLayout

Response.Write ("<blockquote>" & oPivot.HTMLData & "</blockquote>")
response.Flush()
session("pagehtml") = session("pagehtml") & "<blockquote>" &
oPivot.HTMLData & "</blockquote>"

Set oPercent = Nothing
Set oCount_Total = Nothing
Set oCount = Nothing
Set oTotal_Total = Nothing
Set oTotal = Nothing
Set oNET_Total = Nothing
Set oNET = Nothing
Set oDEDUCTION_Total = Nothing
Set oCIVAR_Total = Nothing
Set oCVAR_Total = Nothing
Set oRVAR_Total = Nothing
Set oDEDUCTION = Nothing
Set oCIVAR = Nothing
Set oCVAR = Nothing
Set oRVAR = Nothing
Set oCG = Nothing
Set oTeam = Nothing

Set oView = Nothing
Set oPivot = Nothing

End sub

 

[olap]

Hi,

Just visit www.gmsbv.nl / www.reportportal.com and see how you can have
both MDX, HTML, OWC live in ASP.NET.

You can order / download even a trial

Regards, Marco

Julien schreef: