exclude names that have numbers

Eg:
Create table test
(col1 char(20))
insert test values ('test')
insert test values ('test1')
insert test values ('te1st')
insert test values ('tes')
Basically I want the output to return those values that do not contain a
number in the entire value..
So the output from above should only include
test
tes
How can I do so ?
ThanksTry
select * from test
where col1 not like '%[0-9]%'
Ben Nevarez, MCDBA, OCP
Database Administrator
"Hassan" wrote:

> Eg:
> Create table test
> (col1 char(20))
> insert test values ('test')
> insert test values ('test1')
> insert test values ('te1st')
> insert test values ('tes')
> Basically I want the output to return those values that do not contain a
> number in the entire value..
> So the output from above should only include
> test
> tes
> How can I do so ?
> Thanks
>
>

Excel OLAP Report losing layout after migration

I have big numbers of OLAP Excel (Pivot tables) report and very complex.

Finally I am able to move my olap database from AS 2000 to AS 2005 after fixing some know issues some specific to my database.

Now the big challenge is all Excel 2002 reports should work un touched. I am using Excel Macro to update connection string in all Excel file but I can not afford to redesign all of them. Redesigning Excel report will almost kill my AS 2005 migration project.

After changing pivot table connection from 2000 database to 2005 database, when I refresh pivot table I am losing my reports layout. In my "Field List" all shared dimensions and measures has exact same names after refresh.

Looks it's ok for most of the dimensions but all shared dimensions had Hierarchy name like Company.Vendor , Company.Employee in AS 2000 has this issue in AS 2005.

After migration I do had to fix all Hierarchy shared dimensions because all came in AS 2005 as Company 1, Company 2.

Any idea what I should try to do fix them.

Thank you - Ashok

Macro to change Excel PivotTable connection from AS 2000 to AS 2005

With ActiveCell.PivotTable.PivotCache
Response = MsgBox("Current Connection: " & .Connection)
.Connection = Replace(.Connection, "MSOLAP.2", "MSOLAP.3")
.Connection = Replace(.Connection, "AS2000DB_Name", "AS2005DB_Name")
Response = MsgBox("New Connection: " & .Connection)
End With

Hello. Most respect to your situation but we have had this discusssion a few days ago here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1061202&SiteID=1

Kind regards

Thomas Ivarsson

|||

Thanks Thomas.

There is one way to fix this. It worked for me however still there are some open issues but may be it's in my case only.

Let's say we have Hierarchy shared dimensions in AS 2000

Company.Vendor

Company.Employee

Company.Location

Once we run the migration in AS 2005 we see dimensions like

Company (Hierarchy name Vendor)

Company 1 (Hierarchy name Employee)

Company 2 (Hierarchy name Location)

What I did before, I renamed them "Company Vendor", "Comapny Employee" and "Company Location". That created issues of losing layout in Excel Pivot table report.

This is what I tested and worked to fix Excel Pivot report losing layout issue.

- After migration create a dimension "Company" or user first one "Company" dimension, in that dimension create three Hierarchy "Vendor", "Employee" and "Location". If you use existing "Company" dimension then add two new "Employee" and "Location"

- Remove all Company dimensions from cubes

- Add new Company dimension which has more the one Hierarchy

- Process cube

- Open Old Excel Pivot table report which runs fine with AS 2000 cube

- Run macro to change connection to AS 2005

- Refresh report

You should see "Filed list" changed now "Company Vendor" DIM will be only "Vendor" but the big deal is you don't lose reports layout.

It worked for me I still have open issue that I have 5 cubes and one Cube with 5 Linked Cubes (like Virtual Cube in 2000). I don't know why I am not able to add my new "Company" dimension to my Virtual Cube.

|||

Hello Ashok. The cube migration wizard(AS2000 to SSAS2005) will only copy the structures in AS2000 and do its best to make it fit in SSAS2005. You are already aware of this as I can see.

Another problem that you might see is that some MDX will no longer work as expected.

If you have several customer dimensions in AS2000 you will still have several customer dimensions in SSAS2005. But one design fundamental is that you can have one customer dimension in SSAS2005 with several user hierarchies in that dimension.

Attribute relations is another fundamental thing in SSAS2005 that I do not think that the migration wizard will get right.

In SSAS2005 you no longer have virtual cubes but a logical cube with one or several measure groups(fact tables)

I have done some migration projects and I have always recommended my customers to avoid the migation wizard and redesign the AS2000 cubes according to what is best in SSAS2005. I think that this have been the best approach.

This is the best chance you will have to improve you AS2000 cubes.

Regards

Thomas Ivarsson

Excel Numbers format

Hi all i'm having an issues when i trying to do a report:

i make a report and there is a field which contains data that can be numeric and numeric with letter: "77756" or "345WS" when i export my report to excel i and i open it i get like a make in the upper left coner of the cells that have only numbers asking me to convert this to numeric format, but this think is that i need this values to be string so i can apply filtering. is there a way that i can format my cell to string so the excel take it like that and allowme to do the autoifilter.

I would really appriciate your Help

Thanks

The value in the cell is inserted as a string, so filtering should work. The indicator is just Excel warning you that this string could be changed into a number. You can turn off this warning in the options settings of Excel.

Ian|||

Thanks Ian but the autofilter doesn't work if i do a greater than or less than filter.

But i have another field that only contains names and it does the filter by this criterio, i have try to put in the RDL CSTR() function but doesn't work. something similar happened to me with date fiels and i just addded a Cdate to the RDl field and added the mm/dd/yyyy format for that field and i got the autofilter.

I don't know what else i could try

Thanks

|||

You know how in excel you can prefix a value with a single quote, to force it to accept the input as text. You could try that in your report. The downside is that you'd see this in the web view but should not be visible in the export.

I played around in excel and if I prefix my values with a single quote then the excel filter only works if I also include the single quote in my filter expression.

|||
If you can provide an example of what you a trying to accomplish in the generated spreadsheet, I may be able to point you in the right direction. Using just Excel, can you give me an example of how to add a less than or greater than filter for text?

Ian|||sure i can give you an excel sample, but how can i attache the excel here ? or what address can i send it|||In a excel you can auto filter by greater than and less than by using the custom filtering, that appears when you click the selected column with the autofilter on and there it show custom, then it opens a dialog box where you can especify if you want the condition , Equal, greater than or less than...|||Thanks Adam for t he tip, i added the single quote to the expresion in the report and i created a new report and exported to excel, absouly it works, it stops givng me the error for the text to number conversion but i can see the single quote when i open the Excel even if i print it, is there a way that that single quote can be invisible?... jejjeje or am i asking too much?|||

I have this exact same issue. I have an Access Database that exports a tool report to Excel. The report exports properly and has worked for a long time. I recently updated it to Access 2003 and now it has problem. The tools part number is in the form 99-99-9999 but when it tries to export to Excel it turns it into a number and I get a single value such as 359. So I already figured I could just insert the single quote in front of the values and that works but I can see it in Excel. If you enter a number such as this in Excel directly with the single quote it does not have the problem but exporting from Access to Excel it does.

Are there any solutions to that?

|||

this issues for me began with sorting dates, and i have the same problem that you have wit hthis i couldn't get the date right to be filter, but in my RDL i found the solution to that on, in the expresion field that you are getting the date value you have to add the Cdate function and in the format of that textbox in the RDl you have to specify the format yo want it to be: mm/dd/yyyy so that you will avoid to add the single quote to the date fields, but i still have found a solution for my filtering issues when there are numbers and numbers with characters in the same column :-(

Anyone?

|||
You may want to try using the CStr() function, or ToString() on the value of the field, to cast the value to a string. The latter approach, however, may cause an exception if the value of the field is Nothing.

Ian|||

Thank you for the advice, I tried it but I think the problem is I am using an Access report to export to Excel using DoCmd.OutputTo. So between the report and Excel I can't change the formatting apparently. Maybe I need to look into a way to export directly from my Access Query to Excel. That may improve things as I would be taking out a step. If you know which command that would be please let me know but I will probably begin a search for it.