Focal Point
[OPEN case]get rid of trailing blank with Microsoft SQLServer analysis server in OLAP

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/505100615

August 25, 2010, 11:21 PM
Landerman
[OPEN case]get rid of trailing blank with Microsoft SQLServer analysis server in OLAP
Hi, everybody
I use WF 7.7.01 and I try to generate a OLAP with data source MSAS.
but when i run the fex and the dropdown selection of HIERARCHY have no data.
and I use lenv function to view the source data ,I found the data has trailing blank.
so any body can get rid of the trailing blank in wf whole Environment?
thanks a lot

This message has been edited. Last edited by: Landerman,


Landerman

WebFOCUS 7.7.01
Windows 2003 server
all output
August 25, 2010, 11:31 PM
Waz
I am assuming this is related to varchar fields, it may not though.

Have you tried the SQL server adapter options ?

ENGINE SQLMSS SET VARCHAR OFF


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

August 26, 2010, 12:11 AM
Landerman
Hi, waz
Thanks for your reply
I test your suggistion
and it does not work.

otherway ,my datasource is MSAS not SQLMSS
thank you very much.


Landerman

WebFOCUS 7.7.01
Windows 2003 server
all output
August 26, 2010, 12:19 AM
Waz
Whats the Master file look like ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

August 26, 2010, 01:08 AM
Landerman
Here is the some mas
thanks Waz

FILENAME=Analysis_Services_Tutorial, SUFFIX=SSAS , $
SEGMENT=ANALYSIS_SERVICES_TUTORIAL, SEGTYPE=S0, $
$ MEASURES FOR CUBE Analysis Services Tutorial
FIELDNAME=ORDER_QUANTITY, ALIAS='Order Quantity', USAGE=I11, ACTUAL=I4,
MISSING=ON,
TITLE='Order Quantity',
PROPERTY=MEASURE, $
FIELDNAME=UNIT_PRICE, ALIAS='Unit Price', USAGE=D18.2, ACTUAL=D8,
MISSING=ON,
TITLE='Unit Price',
PROPERTY=MEASURE, $
FIELDNAME=EXTENDED_AMOUNT, ALIAS='Extended Amount', USAGE=D18.2, ACTUAL=D8,
MISSING=ON,
TITLE='Extended Amount',
PROPERTY=MEASURE, $
FIELDNAME=UNIT_PRICE_DISCOUNT_PERCENTAGE, ALIAS='Unit Price Discount Percentage', USAGE=D18.2, ACTUAL=D8,
MISSING=ON,
TITLE='Unit Price Discount Percentage',
PROPERTY=MEASURE, $
FIELDNAME=DISCOUNT_AMOUNT, ALIAS='Discount Amount', USAGE=D18.2, ACTUAL=D8,
MISSING=ON,
TITLE='Discount Amount',
PROPERTY=MEASURE, $
FIELDNAME=PRODUCT_STANDARD_COST, ALIAS='Product Standard Cost', USAGE=D18.2, ACTUAL=D8,
MISSING=ON,
TITLE='Product Standard Cost',
PROPERTY=MEASURE, $
FIELDNAME=TOTAL_PRODUCT_COST, ALIAS='Total Product Cost', USAGE=D18.2, ACTUAL=D8,
MISSING=ON,
TITLE='Total Product Cost',
PROPERTY=MEASURE, $
FIELDNAME=SALES_AMOUNT, ALIAS='Sales Amount', USAGE=D18.2, ACTUAL=D8,
MISSING=ON,
TITLE='Sales Amount',
PROPERTY=MEASURE, $
FIELDNAME=TAX_AMOUNT, ALIAS='Tax Amount', USAGE=D18.2, ACTUAL=D8,
MISSING=ON,


Landerman

WebFOCUS 7.7.01
Windows 2003 server
all output
August 26, 2010, 01:23 AM
Waz
I am confused, which field is giving you the blank char ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

August 27, 2010, 12:00 AM
Landerman
HI,Waz
sorry for the confuse.
I only put a little metadata in the reply.
the whole mas file is
FILENAME=Analysis_Services_Tutorial, SUFFIX=SSAS , $
SEGMENT=ANALYSIS_SERVICES_TUTORIAL, SEGTYPE=S0, $
$ MEASURES FOR CUBE Analysis Services Tutorial
FIELDNAME=Order_Quantity, ALIAS='Order Quantity', USAGE=I11,, ACTUAL=I4,
MISSING=ON,
TITLE='Order Quantity',
PROPERTY=MEASURE, $
FIELDNAME=Unit_Price, ALIAS='Unit Price', USAGE=D18.2, ACTUAL=D8,
MISSING=ON,
TITLE='Unit Price',
PROPERTY=MEASURE, $
FIELDNAME=Extended_Amount, ALIAS='Extended Amount', USAGE=D18.2, ACTUAL=D8,
MISSING=ON,
TITLE='Extended Amount',
PROPERTY=MEASURE, $
FIELDNAME=Unit_Price_Discount_Percentage, ALIAS='Unit Price Discount Percentage', USAGE=D18.2, ACTUAL=D8,
MISSING=ON,
TITLE='Unit Price Discount Percentage',
PROPERTY=MEASURE, $
FIELDNAME=Discount_Amount, ALIAS='Discount Amount', USAGE=D18.2, ACTUAL=D8,
MISSING=ON,
TITLE='Discount Amount',
PROPERTY=MEASURE, $
FIELDNAME=Product_Standard_Cost, ALIAS='Product Standard Cost', USAGE=D18.2, ACTUAL=D8,
MISSING=ON,
TITLE='Product Standard Cost',
PROPERTY=MEASURE, $
FIELDNAME=Total_Product_Cost, ALIAS='Total Product Cost', USAGE=D18.2, ACTUAL=D8,
MISSING=ON,
TITLE='Total Product Cost',
PROPERTY=MEASURE, $
FIELDNAME=Sales_Amount, ALIAS='Sales Amount', USAGE=D18.2, ACTUAL=D8,
MISSING=ON,
TITLE='Sales Amount',
PROPERTY=MEASURE, $
FIELDNAME=Tax_Amount, ALIAS='Tax Amount', USAGE=D18.2, ACTUAL=D8,
MISSING=ON,
TITLE='Tax Amount',
PROPERTY=MEASURE, $
FIELDNAME=Freight, ALIAS=Freight, USAGE=D18.2, ACTUAL=D8,
MISSING=ON,
TITLE='Freight',
PROPERTY=MEASURE, $
FIELDNAME=Internet_Sales_Count, ALIAS='Internet Sales Count', USAGE=I11,, ACTUAL=I4,
MISSING=ON,
TITLE='Internet Sales Count',
PROPERTY=MEASURE, $
DIMENSION=[Customer], CAPTION='Customer', $
HIERARCHY=[Customer].[Customer Geography], CAPTION='Customer Geography', HRY_DIMENSION=[Customer], HRY_STRUCTURE=STANDARD, $
FIELDNAME=Country_Region, ALIAS=Country-Region, USAGE=A14, ACTUAL=A14,
TITLE='Country-Region',
WITHIN='*[Customer].[Customer Geography]',
PROPERTY=CAPTION, $
FIELDNAME=State_Province, ALIAS=State-Province, USAGE=A19, ACTUAL=A19,
TITLE='State-Province',
WITHIN=Country_Region,
PROPERTY=CAPTION, $
FIELDNAME=City, ALIAS=City, USAGE=A21, ACTUAL=A21,
TITLE='City',
WITHIN=State_Province,
PROPERTY=CAPTION, $
FIELDNAME=Full_Name, ALIAS='Full Name', USAGE=A31, ACTUAL=A31,
TITLE='Full Name',
WITHIN=City,
PROPERTY=CAPTION, $
$ LEVEL [Customer].[Customer Geography].[Full Name] PROPERTIES
FIELDNAME=Birth_Date, ALIAS='Birth Date', USAGE=A10, ACTUAL=A10,
TITLE='Birth Date',
REFERENCE=Full_Name, PROPERTY=ATTRIBUTE, $
FIELDNAME=Marital_Status, ALIAS='Marital Status', USAGE=A7, ACTUAL=A7,
TITLE='Marital Status',
REFERENCE=Full_Name, PROPERTY=ATTRIBUTE, $
FIELDNAME=Gender, ALIAS=Gender, USAGE=A7, ACTUAL=A7,
TITLE='Gender',
REFERENCE=Full_Name, PROPERTY=ATTRIBUTE, $
FIELDNAME=Yearly_Income, ALIAS='Yearly Income', USAGE=D7, ACTUAL=D8,
TITLE='Yearly Income',
REFERENCE=Full_Name, PROPERTY=ATTRIBUTE, $
FIELDNAME=Total_Children, ALIAS='Total Children', USAGE=I1, ACTUAL=I4,
TITLE='Total Children',
REFERENCE=Full_Name, PROPERTY=ATTRIBUTE, $
FIELDNAME=Number_Children_At_Home, ALIAS='Number Children At Home', USAGE=I1, ACTUAL=I4,
TITLE='Number Children At Home',
REFERENCE=Full_Name, PROPERTY=ATTRIBUTE, $
FIELDNAME=Education, ALIAS=Education, USAGE=A19, ACTUAL=A19,
TITLE='Education',
REFERENCE=Full_Name, PROPERTY=ATTRIBUTE, $
FIELDNAME=Occupation, ALIAS=Occupation, USAGE=A14, ACTUAL=A14,
TITLE='Occupation',
REFERENCE=Full_Name, PROPERTY=ATTRIBUTE, $
FIELDNAME=House_Owner_Flag, ALIAS='House Owner Flag', USAGE=A7, ACTUAL=A7,
TITLE='House Owner Flag',
REFERENCE=Full_Name, PROPERTY=ATTRIBUTE, $
FIELDNAME=Number_Cars_Owned, ALIAS='Number Cars Owned', USAGE=I1, ACTUAL=I4,
TITLE='Number Cars Owned',
REFERENCE=Full_Name, PROPERTY=ATTRIBUTE, $
FIELDNAME=Phone, ALIAS=Phone, USAGE=A19, ACTUAL=A19,
TITLE='Phone',
REFERENCE=Full_Name, PROPERTY=ATTRIBUTE, $
FIELDNAME=Date_First_Purchase, ALIAS='Date First Purchase', USAGE=A10, ACTUAL=A10,
TITLE='Date First Purchase',
REFERENCE=Full_Name, PROPERTY=ATTRIBUTE, $
FIELDNAME=Commute_Distance, ALIAS='Commute Distance', USAGE=A10, ACTUAL=A10,
TITLE='Commute Distance',
REFERENCE=Full_Name, PROPERTY=ATTRIBUTE, $
FIELDNAME=Geography, ALIAS=Geography, USAGE=I3, ACTUAL=I4,
TITLE='Geography',
REFERENCE=Full_Name, PROPERTY=ATTRIBUTE, $
FIELDNAME=Email_Address, ALIAS='Email Address', USAGE=A33, ACTUAL=A33,
TITLE='Email Address',
REFERENCE=Full_Name, PROPERTY=ATTRIBUTE, $
DIMENSION=[Due Date], CAPTION='Due Date', $
HIERARCHY=[Due Date].[Calendar Time], CAPTION='Due Date.Calendar Time', HRY_DIMENSION=[Due Date], HRY_STRUCTURE=STANDARD, $
FIELDNAME=Calendar_Year, ALIAS='Calendar Year', USAGE=I4, ACTUAL=I4,
TITLE='Calendar Year',
WITHIN='*[Due Date].[Calendar Time]',
PROPERTY=CAPTION, $
FIELDNAME=Calendar_Semester, ALIAS='Calendar Semester', USAGE=A10, ACTUAL=A10,
TITLE='Calendar Semester',
WITHIN=Calendar_Year,
PROPERTY=CAPTION, $
FIELDNAME=Calendar_Quarter, ALIAS='Calendar Quarter', USAGE=A10, ACTUAL=A10,
TITLE='Calendar Quarter',
WITHIN=Calendar_Semester,
PROPERTY=CAPTION, $
FIELDNAME=Calendar_Month, ALIAS='Calendar Month', USAGE=A14, ACTUAL=A14,
TITLE='Calendar Month',
WITHIN=Calendar_Quarter,
PROPERTY=CAPTION, $
FIELDNAME=Date, ALIAS=Date, USAGE=A11, ACTUAL=A11,
TITLE='Date',
WITHIN=Calendar_Month,
PROPERTY=CAPTION, $
$ LEVEL [Due Date].[Calendar Time].[Date] PROPERTIES
FIELDNAME=Calendar_Year1, ALIAS='Calendar Year', USAGE=A4, ACTUAL=A4,
TITLE='Calendar Year',
REFERENCE=ANALYSIS_SERVICES_TUTORIAL.Date, PROPERTY=ATTRIBUTE, $
FIELDNAME=Calendar_Semester1, ALIAS='Calendar Semester', USAGE=A10, ACTUAL=A10,
TITLE='Calendar Semester',
REFERENCE=ANALYSIS_SERVICES_TUTORIAL.Date, PROPERTY=ATTRIBUTE, $
FIELDNAME=Calendar_Quarter1, ALIAS='Calendar Quarter', USAGE=A10, ACTUAL=A10,
TITLE='Calendar Quarter',
REFERENCE=ANALYSIS_SERVICES_TUTORIAL.Date, PROPERTY=ATTRIBUTE, $
FIELDNAME=English_Month_Name, ALIAS='English Month Name', USAGE=A14, ACTUAL=A14,
TITLE='English Month Name',
REFERENCE=ANALYSIS_SERVICES_TUTORIAL.Date, PROPERTY=ATTRIBUTE, $
DIMENSION=[Order Date], CAPTION='Order Date', $
HIERARCHY=[Order Date].[Calendar Time], CAPTION='Order Date.Calendar Time', HRY_DIMENSION=[Order Date], HRY_STRUCTURE=STANDARD, $
FIELDNAME=Calendar_Year2, ALIAS='Calendar Year', USAGE=I4, ACTUAL=I4,
TITLE='Calendar Year',
WITHIN='*[Order Date].[Calendar Time]',
PROPERTY=CAPTION, $
FIELDNAME=Calendar_Semester2, ALIAS='Calendar Semester', USAGE=A10, ACTUAL=A10,
TITLE='Calendar Semester',
WITHIN=Calendar_Year2,
PROPERTY=CAPTION, $
FIELDNAME=Calendar_Quarter2, ALIAS='Calendar Quarter', USAGE=A10, ACTUAL=A10,
TITLE='Calendar Quarter',
WITHIN=Calendar_Semester2,
PROPERTY=CAPTION, $
FIELDNAME=Calendar_Month1, ALIAS='Calendar Month', USAGE=A14, ACTUAL=A14,
TITLE='Calendar Month',
WITHIN=Calendar_Quarter2,
PROPERTY=CAPTION, $
FIELDNAME=Date1, ALIAS=Date, USAGE=A11, ACTUAL=A11,
TITLE='Date',
WITHIN=Calendar_Month1,
PROPERTY=CAPTION, $
$ LEVEL [Order Date].[Calendar Time].[Date] PROPERTIES
FIELDNAME=Calendar_Year3, ALIAS='Calendar Year', USAGE=A4, ACTUAL=A4,
TITLE='Calendar Year',
REFERENCE=Date1, PROPERTY=ATTRIBUTE, $
FIELDNAME=Calendar_Semester3, ALIAS='Calendar Semester', USAGE=A10, ACTUAL=A10,
TITLE='Calendar Semester',
REFERENCE=Date1, PROPERTY=ATTRIBUTE, $
FIELDNAME=Calendar_Quarter3, ALIAS='Calendar Quarter', USAGE=A10, ACTUAL=A10,
TITLE='Calendar Quarter',
REFERENCE=Date1, PROPERTY=ATTRIBUTE, $
FIELDNAME=English_Month_Name1, ALIAS='English Month Name', USAGE=A14, ACTUAL=A14,
TITLE='English Month Name',
REFERENCE=Date1, PROPERTY=ATTRIBUTE, $
DIMENSION=[Product], CAPTION='Product', $
HIERARCHY=[Product].[Poduct Model Lines], CAPTION='Poduct Model Lines', HRY_DIMENSION=[Product], HRY_STRUCTURE=STANDARD, $
FIELDNAME=Product_Line, ALIAS='Product Line', USAGE=A10, ACTUAL=A10,
TITLE='Product Line',
WITHIN='*[Product].[Poduct Model Lines]',
PROPERTY=CAPTION, $
FIELDNAME=Model_Name, ALIAS='Model Name', USAGE=A27, ACTUAL=A27,
TITLE='Model Name',
WITHIN=Product_Line,
PROPERTY=CAPTION, $
FIELDNAME=Product_Name, ALIAS='Product Name', USAGE=A32, ACTUAL=A32,
TITLE='Product Name',
WITHIN=Model_Name,
PROPERTY=CAPTION, $
$ LEVEL [Product].[Poduct Model Lines].[Product Name] PROPERTIES
FIELDNAME=Standard_Cost, ALIAS='Standard Cost', USAGE=D10.4, ACTUAL=D8,
TITLE='Standard Cost',
REFERENCE=Product_Name, PROPERTY=ATTRIBUTE, $
FIELDNAME=Color, ALIAS=Color, USAGE=A12, ACTUAL=A12,
TITLE='Color',
REFERENCE=Product_Name, PROPERTY=ATTRIBUTE, $
FIELDNAME=Safety_Stock_Level, ALIAS='Safety Stock Level', USAGE=I4, ACTUAL=I4,
TITLE='Safety Stock Level',
REFERENCE=Product_Name, PROPERTY=ATTRIBUTE, $
FIELDNAME=Reorder_Point, ALIAS='Reorder Point', USAGE=I3, ACTUAL=I4,
TITLE='Reorder Point',
REFERENCE=Product_Name, PROPERTY=ATTRIBUTE, $
FIELDNAME=List_Price, ALIAS='List Price', USAGE=D10.4, ACTUAL=D8,
TITLE='List Price',
REFERENCE=Product_Name, PROPERTY=ATTRIBUTE, $
FIELDNAME=Size, ALIAS=Size, USAGE=A2, ACTUAL=A2,
TITLE='Size',
REFERENCE=Product_Name, PROPERTY=ATTRIBUTE, $
FIELDNAME=Size_Range, ALIAS='Size Range', USAGE=A8, ACTUAL=A8,
TITLE='Size Range',
REFERENCE=Product_Name, PROPERTY=ATTRIBUTE, $
FIELDNAME=Weight, ALIAS=Weight, USAGE=D8.2, ACTUAL=D8,
TITLE='Weight',
REFERENCE=Product_Name, PROPERTY=ATTRIBUTE, $
FIELDNAME=Days_To_Manufacture, ALIAS='Days To Manufacture', USAGE=I1, ACTUAL=I4,
TITLE='Days To Manufacture',
REFERENCE=Product_Name, PROPERTY=ATTRIBUTE, $
FIELDNAME=Product_Line1, ALIAS='Product Line', USAGE=A10, ACTUAL=A10,
TITLE='Product Line',
REFERENCE=Product_Name, PROPERTY=ATTRIBUTE, $
FIELDNAME=Dealer_Price, ALIAS='Dealer Price', USAGE=D10.4, ACTUAL=D8,
TITLE='Dealer Price',
REFERENCE=Product_Name, PROPERTY=ATTRIBUTE, $
FIELDNAME=Class, ALIAS=Class, USAGE=A1, ACTUAL=A1,
TITLE='Class',
REFERENCE=Product_Name, PROPERTY=ATTRIBUTE, $
FIELDNAME=Style, ALIAS=Style, USAGE=A1, ACTUAL=A1,
TITLE='Style',
REFERENCE=Product_Name, PROPERTY=ATTRIBUTE, $
FIELDNAME=Model_Name1, ALIAS='Model Name', USAGE=A27, ACTUAL=A27,
TITLE='Model Name',
REFERENCE=Product_Name, PROPERTY=ATTRIBUTE, $
FIELDNAME=Start_Date, ALIAS='Start Date', USAGE=A8, ACTUAL=A8,
TITLE='Start Date',
REFERENCE=Product_Name, PROPERTY=ATTRIBUTE, $
FIELDNAME=End_Date, ALIAS='End Date', USAGE=A9, ACTUAL=A9,
TITLE='End Date',
REFERENCE=Product_Name, PROPERTY=ATTRIBUTE, $
FIELDNAME=Status, ALIAS=Status, USAGE=A7, ACTUAL=A7,
TITLE='Status',
REFERENCE=Product_Name, PROPERTY=ATTRIBUTE, $
DIMENSION=[Ship Date], CAPTION='Ship Date', $
HIERARCHY=[Ship Date].[Calendar Time], CAPTION='Ship Date.Calendar Time', HRY_DIMENSION=[Ship Date], HRY_STRUCTURE=STANDARD, $
FIELDNAME=Calendar_Year4, ALIAS='Calendar Year', USAGE=I4, ACTUAL=I4,
TITLE='Calendar Year',
WITHIN='*[Ship Date].[Calendar Time]',
PROPERTY=CAPTION, $
FIELDNAME=Calendar_Semester4, ALIAS='Calendar Semester', USAGE=A10, ACTUAL=A10,
TITLE='Calendar Semester',
WITHIN=Calendar_Year4,
PROPERTY=CAPTION, $
FIELDNAME=Calendar_Quarter4, ALIAS='Calendar Quarter', USAGE=A10, ACTUAL=A10,
TITLE='Calendar Quarter',
WITHIN=Calendar_Semester4,
PROPERTY=CAPTION, $
FIELDNAME=Calendar_Month2, ALIAS='Calendar Month', USAGE=A14, ACTUAL=A14,
TITLE='Calendar Month',
WITHIN=Calendar_Quarter4,
PROPERTY=CAPTION, $
FIELDNAME=Date2, ALIAS=Date, USAGE=A11, ACTUAL=A11,
TITLE='Date',
WITHIN=Calendar_Month2,
PROPERTY=CAPTION, $
$ LEVEL [Ship Date].[Calendar Time].[Date] PROPERTIES
FIELDNAME=Calendar_Year5, ALIAS='Calendar Year', USAGE=A4, ACTUAL=A4,
TITLE='Calendar Year',
REFERENCE=Date2, PROPERTY=ATTRIBUTE, $
FIELDNAME=Calendar_Semester5, ALIAS='Calendar Semester', USAGE=A10, ACTUAL=A10,
TITLE='Calendar Semester',
REFERENCE=Date2, PROPERTY=ATTRIBUTE, $
FIELDNAME=Calendar_Quarter5, ALIAS='Calendar Quarter', USAGE=A10, ACTUAL=A10,
TITLE='Calendar Quarter',
REFERENCE=Date2, PROPERTY=ATTRIBUTE, $
FIELDNAME=English_Month_Name2, ALIAS='English Month Name', USAGE=A14, ACTUAL=A14,
TITLE='English Month Name',
REFERENCE=Date2, PROPERTY=ATTRIBUTE, $


when I read the dimension from the mas file
I get the length of each value +1
example:
I use the fex to read the Country_Region length of value

TABLE FILE ANALYSIS_SERVICES_TUTORIAL
PRINT Country_Region AND
COMPUTE NAME_LEN/I3 = ARGLEN(15, Country_Region, NAME_LEN);
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
END


I get

Country-Region NAME_LEN
Australia 10
Canada 7
France 7
Germany 8
United Kingdom 14
United States 14
Unknown 8



so it is not right

thank you


Landerman

WebFOCUS 7.7.01
Windows 2003 server
all output
August 27, 2010, 12:33 AM
Waz
Interesting, can you try out the code below.

TABLE FILE ANALYSIS_SERVICES_TUTORIAL
PRINT Country_Region AND
COMPUTE NAME_LEN/I3 = ARGLEN(15, Country_Region, NAME_LEN);
COMPUTE NAME_HEX/A28 = UFMT(Country_Region, 14, NAME_HEX) ;
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
END



It will show the hex value of the field.

My guess is that the space is at the begining of the field. If not then it will show you the hex code of the "non printable" character.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

August 27, 2010, 02:26 AM
Landerman
Hi,Waz
I tried your code
the result is




Country-Region NAME_LEN NAME_HEX
Australia 10 4175737472616C69610020202020
Canada 7 43616E6164610020202020202020
France 7 4672616E63650020202020202020
Germany 8 4765726D616E7900202020202020
United Kingdom 14 556E69746564204B696E67646F6D
United States 14 556E697465642053746174657300
Unknown 8 556E6B6E6F776E00202020202020


thanks


Landerman

WebFOCUS 7.7.01
Windows 2003 server
all output
August 27, 2010, 02:38 AM
Waz
The extra character is not a space but a null character, Hex 00


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

August 27, 2010, 02:43 AM
Landerman
and Waz
how can I get rid of the extra character in the whole wf environment?

thanks


Landerman

WebFOCUS 7.7.01
Windows 2003 server
all output
August 27, 2010, 09:31 PM
Landerman
hi,
dose anyone have ideas?


Landerman

WebFOCUS 7.7.01
Windows 2003 server
all output
August 28, 2010, 12:32 AM
Dan Satchell
I suspect the extra space/null is caused by your COMPUTE. Column Country_Region is 14 long, not 15. Change your COMPUTE to this:

COMPUTE NAME_LEN/I3 = ARGLEN(14, Country_Region, NAME_LEN);

and see if the extra space/null is eliminated.


WebFOCUS 7.7.05
August 28, 2010, 12:43 AM
Landerman
hi, Dan
I do not think so.
I try the fex
-* File 3.fex
TABLE FILE ANALYSIS_SERVICES_TUTORIAL
PRINT Country_Region AND
COMPUTE NAME_LEN/I3 = ARGLEN(14, Country_Region, NAME_LEN);
COMPUTE NAME_HEX/A28 = UFMT(Country_Region, 14, NAME_HEX) ;
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
END


and get the result


Country-Region NAME_LEN NAME_HEX
Australia 10 4175737472616C69610020202020
Canada 7 43616E6164610020202020202020
France 7 4672616E63650020202020202020
Germany 8 4765726D616E7900202020202020
United Kingdom 14 556E69746564204B696E67646F6D
United States 14 556E697465642053746174657300
Unknown 8 556E6B6E6F776E00202020202020


Landerman

WebFOCUS 7.7.01
Windows 2003 server
all output
August 28, 2010, 02:18 AM
Dan Satchell
I would open a case with IBI Tech Support.


WebFOCUS 7.7.05
August 28, 2010, 08:57 AM
Landerman
o, Dan
thank you very much
and I think some other information is very usefull.
WF version: 7.7.01
Datasource: microsoft sqlserver analysis service 2005 with sp3
OS: windows 2003 64 edition and window7 64 edition.
language:S-chinese
codepage:946


Landerman

WebFOCUS 7.7.01
Windows 2003 server
all output
August 28, 2010, 07:13 PM
Dan Satchell
Just to be clear, I am suggesting that you open a case with IBI - not me.


WebFOCUS 7.7.05
August 28, 2010, 07:42 PM
Landerman
OK thanks


Landerman

WebFOCUS 7.7.01
Windows 2003 server
all output
August 30, 2010, 08:03 AM
Landerman
does anybody have other suggistion?


Landerman

WebFOCUS 7.7.01
Windows 2003 server
all output
August 30, 2010, 05:42 PM
Waz
If there are Hex 00 in your data, the question is, where did they come from.

What is the version of the SSAS ?

Are the Hex 00 part of the data ?

Is it being added after retrieval ?

As this is on WebFOCUS 7.7.0.1, I would also stongly suggest, as Dan does, to put in a case with IBI.
It may be a Bug.

My gut feeling is that it is in the data, perhaps from the original load, as a Hex 00 would normally be some sort of field terminator.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

August 31, 2010, 03:41 AM
Alan B
I would also look closely at the data, incoming and within MSAS.

The ARGLEN is working as expected, removing trailing blanks, but not removing the NULL, it is not designed to do that.

If the data is coming in to MSAS from SSIS, it is possible that the source has a NULL end and this is being taken through SSIS as part of the data. It is possible to remove this within SSIS with a regular expression "\x00"; though SSIS should clean NULLs out, it doesn't apparently. It would not be considered normal for data to have a NULL within a field in MSAS.


Alan.
WF 7.705/8.007