Naming Custom Group

[jvn]

Hi,
I am using OWC11, ASP.NET and SQL Server Cubes.

In the Pivot table component, I was able to create custom groups, name
them and save the Report.

When I open the saved report the custom grouping stays intact but the
Custom Group Name changes to the Item Unique Name which is a system generated
Id. Is there a way to save the Custom Group Name?.

Thanks

 

[Wei-Dong XU [MSFT]]

Hi,

Could you be so kind to post your code? I will research this issue for you.
Thanks in advance!

Expect to your reply!

Best Regards,
Wei-Dong XU (WD)
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[jvn]

Hi,
Thanks for replying. I am doing this in the Pivot component manually and
not through code. Unfortunately I am not able to paste the screen snap shot
or attach it to this message.

Here are the steps I am doing:
1) Pick two fields and drag them to the ROW FIELDS AREA
2) Double-click 1-2 measures
3) Select couple of rows from second column and Right click then select
GROUP ITEMS.
4) You can now see the new group appears with the Name GROUP1
5) Select GROUP1 and now Right Click and Select COMMAND AND OPTIONS
6) Choose Captions and rename GROUP1 to TESTGRP.
7) Save the Report and Open it again.
Now you will see a system Generated Name instead of TESTGRP which is
the same as ITEM NAME (You can see the ITEM Name in the Command and Options
-> Captions Menu)

My Question is how do I change the ITEM NAME or make the name TESTGRP
permanent.

Thanks

 

[Wei-Dong XU [MSFT]]

Hi,

Thanks for your detailed information!

From my experience, I'd suggest you can use Frontpage for this issue. List
the steps for you:
1. open your page at frontpage.
2. edit the OWC PivotTable to host the data. At the design time, Frontpage
will obtain the data from the data source and you will see the data being
filled into the PivotTable. Create your grouping and save the file
3. then from the code view, you will find the XMLData property under the
object tag of PivotTable as below:
<object classid="clsid:0002E55A-0000-0000-C000-000000000046"
id="Pivottable1">
<param name="XMLData" value="&lt;xml
xmlns:x=&quot;urn:schemas-microsoft-comffice:excel&quot;&gt;
&lt;xivotTable&gt; ...
&lt;/xivotView&gt;
&lt;/xivotTable&gt;
&lt;/xml&gt;">

4. the param tag contains the property XMLData which stores your
configuration information on this PivotTable control. When IE renders this
page, it will set the value at this tag to the XMLData property of
PivotTable and the control will render the data then

This way, there is no need for you to write one code for setting the
grouping name. It will also be persisted in the xml string of param tag.

If your page can't be editted by Frontpage directly, you can set up one
blank page and place the web component from "Insert->Web
Component->SpreadSheets and Charts->Office PivotTable". Then edit the data
and copy the full param tag with the content to your page.

Please feel free to let me know if you have any question.

Best Regards,
Wei-Dong XU (WD)
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[jvn]

Hi,
Thanks for you reply. Since I am using the Visual Studio .NET 1.1, I
already have the object Id information as specified by you and it has XMLDATA
in the tag.

When user saves the report I store the XML Metadata on the server and the
following tag is stored for Custom Grouping:

<xivotField>
<x:Name>Dr Specialty1</x:Name>
<x:SourceName>[Dr Specialty].[Dr Specialty1]</x:SourceName>
<x:FilterCaption>Dr Specialty</x:FilterCaption>
<x:Orientation>Row</x:Orientation>
<xosition>2</xosition>
<x:CompareOrderedMembersBy>UniqueName</x:CompareOrderedMembersBy>
<x:AutoSortOrder>Custom</x:AutoSortOrder>
<x:OrderedMember>
<x:Name><7667EA04-03F7-41C1-B771-F87F0072193B></x:Name>
<x:UniqueName>[<7667EA04-03F7-41C1-B771-F87F0072193B>]</x:UniqueName>
<x:Caption>TestGrp</x:Caption>
</x:OrderedMember>
<x:OrderedMember>
<x:Name><4791D606-38E2-455C-9080-1BA2B55964B4></x:Name>
<x:UniqueName>[<4791D606-38E2-455C-9080-1BA2B55964B4>]</x:UniqueName>
<x:Caption>Other</x:Caption>
</x:OrderedMember>

......................

I would like to change the value for the XML tag <x:OrderedMember>
<x:Name>.

My Question:
1) Is there a way I can change some settings in the Pivot Component which
would save the custom Name
or
2) How do I change this programatically. I extract the XML Metdata from
the Pivot table by

frm1.PivotTable1.XMLData

The above property gives me the system generated Id for the Custom Group
Name and not the User Changed value. How do you think I can achieve this?

If I am not clear let me know how.

Thanks and waiting to hear options from you


I am OK to write code to change it. But how do I do it. I call the web
service
with the parameters
service.svcOLAP.callService(onSaveReportResult, 'SaveReport', strEmailId,
strReportXMLData, strReportname, strReportcmt);

 

[Wei-Dong XU [MSFT]]

Hi,

This is really very odd! During my test, I used the SQL sample database
Northwind as the data source. It works very smoothly. Could you repro this
issue with NorthWind? If not, could you be so kind to let me know your data
source. OLAP or pure SQL or any others?

One repro scenario will greatly appreciated! Expect to your reply!

Best Regards,
Wei-Dong XU (WD)
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[jvn]

Hi,
I am using Office 2003, SQL OLAP, SQL Server 2000, ASP.NET. In order for
you to reproduce What I am experiencing I used FoodMart 2000 and Cube - Sales.

Here are my steps which I did:

1) After my connection with Sales Cube. I used the following fields Product
Family, Promotion Name, Profit and Store Sales.

2) Changed the Promotion Labels of Bag Stuffers to Bag Stuffers-1 and Best
Savings to Best Savings-1

3) Created 2 Groups One for the Promotion Name which starts with B and the
Second which starts with CD and F. One I am done the Group Names will be
Group1, Group2 and Other.

4) Changed the Group Names Group1 to GroupB and Group2 to GroupCDF and left
Other as is.

5) Saved the Report.

6) When I Retrieved the saved report the Group Captions changed to system
Generated but the Promotion Captions which I changed stayed in Place.

The Group Caption is saved in the XML File but when it is displaying it
displays the <X:Name> tag value rather than <X:CAPTION> Value. The XML File
with the attribute which was saved is

<x:OrderedMember>
<x:Name><532FD60D-9CBB-4701-B8B6-F33A0F1C1D8A></x:Name>
<x:UniqueName>[<532FD60D-9CBB-4701-B8B6-F33A0F1C1D8A>]</x:UniqueName>
<x:Caption>GroupB</x:Caption>
</x:OrderedMember>
<x:OrderedMember>
<x:Name><042638EC-0877-45EB-944D-25AB9A24E499></x:Name>
<x:UniqueName>[<042638EC-0877-45EB-944D-25AB9A24E499>]</x:UniqueName>
<x:Caption>GroupCDF</x:Caption>
</x:OrderedMember>
<x:OrderedMember>
<x:Name><2F13AA44-8B59-4C07-B9A5-1D8272CACFBE></x:Name>
<x:UniqueName>[<2F13AA44-8B59-4C07-B9A5-1D8272CACFBE>]</x:UniqueName>
<x:Caption>Other</x:Caption>
</x:OrderedMember>

I hope this helps you to reproduce what I have done. Looking forward for
a solution from you.

Thanks

 

[jvn]

Hi,
I found out that custom Group Name (System Generated ID) is different
each time when we load the saved report. I initially thought the pivot table
is naming the Group Name (Caption) with <X:Name> or <X:UNIQUENAME> apparently
it is not the same.

Thanks

 

[jvn]

Hi Wei-Dong,
I have not received your screensnap shot will you be able to send it
again. I don't have Outlook Express installed on my machine.

I am accessing the SQL OLAP cubes based on a Microsoft article
http://msdn.microsoft.com/msdnmag/issues/03/10/olap/ . The article refers you
to use a web service to retrieve and save XMLdata from the Pivot table
component.

In the web service I am just assigning the XML to the XMLDATA property of
the pivot table component. I am sending the Javascript code and Web Service
Method for your review. I am following the same way as described in the above
Microsoft article. Please let me know If I need to change or add anything in
the code. If possible I would appreciate if you could send me the sample code
which was working for you.

Thanks

JAVASCRIPT

function LoadSavedReport() {
// Purpose: Call Web Service method load the saved report

var iCallID = service.svcOLAP.callService(onLoadSavedReportResult,
'LoadSavedReport', 'OLAPReport1.xml');
}

function onLoadSavedReportResult(result) {
// Purpose: This function handles the wsOLAP.onLoadSavedReportResult() web
service result
var text = result.value; // result string
// Evaluate return result
if (!result.error) {
if (text.indexOf("<err>") > -1 ) {
alert(text);
}
// Assign the XML to the PivotList XMLData value
frm1.PivotTable1.XMLData = text;
}
else {
alert("Unhandled error - " + result.errorDetail.code + " " +
result.errorDetail.string);
}
}

WEB SERVICE METHOD

<WebMethod()> Public Function LoadSavedReport(ByVal strFileName As
String) As String

Dim m_xml As String
Dim strFilePath As String
Dim objPT As PivotTableClass = New PivotTableClass

Try

strFilePath = "C:\\Inetpub\\wwwroot\\OLAPReport\\" & strFileName
m_xml = ReadStringFromFile(strFilePath)
objPT.XMLData = m_xml
m_xml = objPT.XMLData
Catch err As Exception
m_xml = "<err>" & err.Source & " - " & err.Message & "</err>"
Finally

End Try

Return (m_xml)
End Function

 

[Wei-Dong XU [MSFT]]

Hi,

Thank you for letting me the article link!

Build an OLAP Reporting App in ASP.NET Using SQL Server 2000 Analysis
Services and Office XP
http://msdn.microsoft.com/msdnmag/issues/03/10/olap/

From my reading on the code, I found the two lines in the SaveReport method
of WebService.asmx.
strReportXMLData = strReportXMLData.Replace("{", "<")
strReportXMLData = strReportXMLData.Replace("}", ">")

These codes will replace the "{"&"}" to "<"&">". From the value of XMLData
in PivotTable, the :"{"&"}" is used to embrace the GUID of custom grouping
name. You can figure out from my screen shot picture. At the same time,
there is one corresponding method from the ReadStringFromFile method;
because if we perform such replacing, the xml file format will crash; all
">" & "<" will be replaced by "{" and "}".

So we can troubleshoot this issue in two ways.
1: comment the two lines to see whether this code works as expected
2: change the < > to other literal for example
' you can feel free to decide which new character or string used to replace
the { and }
strReportXMLData = strReportXMLData.Replace("{", "~^")

Then from the ReadStringFromFile method, we can perform one contrary
operation.
strReportXMLData = strReportXMLData.Replace("~^", "{")

Please feel free to let me know if you have any question.

Best Regards,
Wei-Dong XU (WD)
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[jvn]

Hi,
Thanks a lot I got the Grouping to work. I did the following
modifications to the code:
Javascript:SaveReport
Used to replace < with ~^ and < ^~

Service.ASMX
Before saving the file did the replace ~^ with < and > with ^~

I did not have to change any while doing the Load Report.

Once again thanks a lot.

 

[Wei-Dong XU [MSFT]]

You are very welcome! :0)

Best Regards,
Wei-Dong XU (WD)
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.