Memo field problem using Access97 with SQL2000 Backend

I have an application written in Access 97 that connects to a SQL2000
backend. One field is a description field that is a data type NTEXT in the
SQL database. In my access form, I can not enter more than 255 characters.

Before I converted the backend to SQL, the description field was a memo
field in Access.

What do I need to do to make it so I can enter more text into this field?NChar, NVarChar, and NText are double-byte unicode types. For
example, nchar(10) will hold 10 bytes of data but takes 20 bytes of
space. Convert the field's datatype to Text and you should be fine.

Look up "data types-SQL Server, described" in Books Online for more
info.

"Bob" <bobh@.wolv.tds.net> wrote in message news:<vta8f56bkredfd@.corp.supernews.com>...
> I have an application written in Access 97 that connects to a SQL2000
> backend. One field is a description field that is a data type NTEXT in the
> SQL database. In my access form, I can not enter more than 255 characters.
> Before I converted the backend to SQL, the description field was a memo
> field in Access.
> What do I need to do to make it so I can enter more text into this field?

Memo field in SQL Server 2005

I'm trying to create a site which allows me to add Memo type fields but when I insert or edit my record it will not take any of my text after an enter (vbNewLine).

In Access I used the field type "Memo" but I do not see that type in SQL Server 2005 just a nvarchar(max) which does not seam to work.

Thanks for the help!

Chad

Are you trying to enter the data directly or through a query in query analyzer. There is no Memo field in SQL 2005. varchar should be sufficient. Whether you need varchar(max) depends on how much data you want to put into it.|||

I tried directly in SQL Server and I also tried through my edit form in my application.

No work!

|||

If you are doing it from your application, and assuming you have some kind of textbox for users to enter data, you can replace linebreaks with vbcrlf when you isnert the data into the table.

txtBox1.text.replace(environment.newline,"<br>")

and do the reverse when you need to show the data back to the user.

|||How would you do that in ASP.net when I'm using a FormView to update and insert?|||Whether its update or insert, you have to pass the value the user entered to the proc/T-SQl. Instead of passing the value directly you can use the code I provided.|||

I'm sorry, I'm still not sure where you would add your code...I actually have used the replace alot in ASP but .net is a little diferent.

Do you add this in the code behind somewhere or in the aspx page?

again sorry for asking so much!

|||Yes it would go in your code behind. What I provided is .NET code. I assume for insert/update you prbly have the event code for the respective action.sql

memo data type Import error while importing data from Access file into SQl Server 2005

I have one column in SQL Server 2005 of data type VARCHAR(4000).

I have imported sql Server 2005 database data into one mdb file.After importing a data into the mdb file, above column

data type converted into the memo type in the Access database.

now when I am trying to import a data from this MS Access File(db1.mdb) into the another SQL Server 2005 database, got the error of Unicode Converting a memo data type conversion in Export/Import data wizard.

Could you please let me know what is the reason?

I know that memo data type does not supported into the SQl Server 2005.

I am with SQL Server 2005 Standard Edition with SP2.

Please help me to understans this issue correctly?

If the wizard error says Unicode then change your data type in SQL Server 2005 to Nvarchar(max) because memo can grow above 4000 which is the limit for Nvarchar data type without the max key word. Try the link below for details.

http://msdn2.microsoft.com/en-us/library/ms186939.aspx

|||

Thanks for the reply.

However why SQL Server 2000 is not throwing this error? MSSQL 2000 is also contains the nvarchar data type.

|||That could the related to 2005 data types definition is more strict than 2000.

Memo Data Type

can this datatype hold formatted? e.g. carriage returns?

There is no Memo data type... that is used in Access, you should look at the nvarchar(max) data type, it will do what you need.

|||nice one cheers for that.

Craigsql

Memo Data Type

I am transferring my access backend to sql 2005. access has a memo
data type. I tried using NVarChar(MAX), it will let me enter 254
characters. Then it gives me an error that the field is too small - it
won't even accept the 254. Is there another data type in SQL that I
can use that would be similar to the memo data type?
-doodleHi
"doodle" wrote:

> I am transferring my access backend to sql 2005. access has a memo
> data type. I tried using NVarChar(MAX), it will let me enter 254
> characters. Then it gives me an error that the field is too small - it
> won't even accept the 254. Is there another data type in SQL that I
> can use that would be similar to the memo data type?
> -doodle
>
nvarchar(max) can hold 2^31 -1 bytes see
http://msdn2.microsoft.com/en-gb/library/ms130896.aspx. It is most likely a
limit of the application that you are using that restricts imput/output to
254 bytes, for instance the tools/options menus in Query Analyser or SQL
Server Management Studio can be configure to set how many characters are
displayed.
John|||John,
I checked it in SQL, it is currently set to 2147483647 bytes. What do
I need to change it to?
-doodle|||"doodle" <ADraughn@.mazakcorp.com> wrote in message
news:1172510102.622413.4400@.k78g2000cwa.googlegroups.com...
> John,
> I checked it in SQL, it is currently set to 2147483647 bytes. What do
> I need to change it to?
> -doodle
>
I don't believe you can. If you need to store more than 2 gigs in a memo,
you may want to rethink your datastructure.
(perhaps a pointer to a file on a disk or something.)
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hi
"doodle" wrote:

> John,
> I checked it in SQL, it is currently set to 2147483647 bytes. What do
> I need to change it to?
> -doodle
>
You do not say how you are updating this value a statement such as
UPDATE MyTable
SET MyMemoColumn = 'A very long memo text that is much longer than this'
Will work fine.
To see what size this value is use the query
SELECT LEN(MyMemoColumn)
FROM MyTable
Query Analyser will not allow you to set a the number of output characters
to a value as large as 2147483647 neither will SSMS which has a maximum
display value of 8192. How did you obtain this figure?
John|||It's being updated by forms in access 97.
-doodle|||Hi
"doodle" wrote:

> It's being updated by forms in access 97.
> -doodle
>
Did you run the query from my last reply to see how big the data is?
John

Memo Data Type

I am transferring my access backend to sql 2005. access has a memo
data type. I tried using NVarChar(MAX), it will let me enter 254
characters. Then it gives me an error that the field is too small - it
won't even accept the 254. Is there another data type in SQL that I
can use that would be similar to the memo data type?
-doodleWhere are you trying to enter data, what is the exact error message
you are getting, and what application is generating the error?
-- Steve Kass
-- Drew University
-- http://www.stevekass.com
doodle wrote:

>I am transferring my access backend to sql 2005. access has a memo
>data type. I tried using NVarChar(MAX), it will let me enter 254
>characters. Then it gives me an error that the field is too small - it
>won't even accept the 254. Is there another data type in SQL that I
>can use that would be similar to the memo data type?
>-doodle
>
>

Memo Data Type

I am transferring my access backend to sql 2005. access has a memo
data type. I tried using NVarChar(MAX), it will let me enter 254
characters. Then it gives me an error that the field is too small - it
won't even accept the 254. Is there another data type in SQL that I
can use that would be similar to the memo data type?
-doodleWhere are you trying to enter data, what is the exact error message
you are getting, and what application is generating the error?
-- Steve Kass
-- Drew University
-- http://www.stevekass.com
doodle wrote:
>I am transferring my access backend to sql 2005. access has a memo
>data type. I tried using NVarChar(MAX), it will let me enter 254
>characters. Then it gives me an error that the field is too small - it
>won't even accept the 254. Is there another data type in SQL that I
>can use that would be similar to the memo data type?
>-doodle
>
>

Memo Data Type

I am transferring my access backend to sql 2005. access has a memo
data type. I tried using NVarChar(MAX), it will let me enter 254
characters. Then it gives me an error that the field is too small - it
won't even accept the 254. Is there another data type in SQL that I
can use that would be similar to the memo data type?
-doodleHi
"doodle" wrote:
> I am transferring my access backend to sql 2005. access has a memo
> data type. I tried using NVarChar(MAX), it will let me enter 254
> characters. Then it gives me an error that the field is too small - it
> won't even accept the 254. Is there another data type in SQL that I
> can use that would be similar to the memo data type?
> -doodle
>
nvarchar(max) can hold 2^31 -1 bytes see
http://msdn2.microsoft.com/en-gb/library/ms130896.aspx. It is most likely a
limit of the application that you are using that restricts imput/output to
254 bytes, for instance the tools/options menus in Query Analyser or SQL
Server Management Studio can be configure to set how many characters are
displayed.
John|||John,
I checked it in SQL, it is currently set to 2147483647 bytes. What do
I need to change it to?
-doodle|||"doodle" <ADraughn@.mazakcorp.com> wrote in message
news:1172510102.622413.4400@.k78g2000cwa.googlegroups.com...
> John,
> I checked it in SQL, it is currently set to 2147483647 bytes. What do
> I need to change it to?
> -doodle
>
I don't believe you can. If you need to store more than 2 gigs in a memo,
you may want to rethink your datastructure.
(perhaps a pointer to a file on a disk or something.)
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hi
"doodle" wrote:
> John,
> I checked it in SQL, it is currently set to 2147483647 bytes. What do
> I need to change it to?
> -doodle
>
You do not say how you are updating this value a statement such as
UPDATE MyTable
SET MyMemoColumn = 'A very long memo text that is much longer than this'
Will work fine.
To see what size this value is use the query
SELECT LEN(MyMemoColumn)
FROM MyTable
Query Analyser will not allow you to set a the number of output characters
to a value as large as 2147483647 neither will SSMS which has a maximum
display value of 8192. How did you obtain this figure?
John|||It's being updated by forms in access 97.
-doodle|||Hi
"doodle" wrote:
> It's being updated by forms in access 97.
> -doodle
>
Did you run the query from my last reply to see how big the data is?
John

Memo Data Type

Hi
"doodle" wrote:

> I am transferring my access backend to sql 2005. access has a memo
> data type. I tried using NVarChar(MAX), it will let me enter 254
> characters. Then it gives me an error that the field is too small - it
> won't even accept the 254. Is there another data type in SQL that I
> can use that would be similar to the memo data type?
> -doodle
>
nvarchar(max) can hold 2^31 -1 bytes see
http://msdn2.microsoft.com/en-gb/library/ms130896.aspx. It is most likely a
limit of the application that you are using that restricts imput/output to
254 bytes, for instance the tools/options menus in Query Analyser or SQL
Server Management Studio can be configure to set how many characters are
displayed.
John
"doodle" <ADraughn@.mazakcorp.com> wrote in message
news:1172510102.622413.4400@.k78g2000cwa.googlegrou ps.com...
> John,
> I checked it in SQL, it is currently set to 2147483647 bytes. What do
> I need to change it to?
> -doodle
>
I don't believe you can. If you need to store more than 2 gigs in a memo,
you may want to rethink your datastructure.
(perhaps a pointer to a file on a disk or something.)
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||Hi
"doodle" wrote:

> John,
> I checked it in SQL, it is currently set to 2147483647 bytes. What do
> I need to change it to?
> -doodle
>
You do not say how you are updating this value a statement such as
UPDATE MyTable
SET MyMemoColumn = 'A very long memo text that is much longer than this'
Will work fine.
To see what size this value is use the query
SELECT LEN(MyMemoColumn)
FROM MyTable
Query Analyser will not allow you to set a the number of output characters
to a value as large as 2147483647 neither will SSMS which has a maximum
display value of 8192. How did you obtain this figure?
John
|||Hi
"doodle" wrote:

> It's being updated by forms in access 97.
> -doodle
>
Did you run the query from my last reply to see how big the data is?
John
sql

Measure data type from UDM to ADOMD

Ok, so here is my dilemma.

I need to get the data type of a measure in ADOMD.NET.

I have a SSAS project which deploys a cube. The measure is defined in this project file as having a FormatString of "Currency" (this is in the "Basic" section of the measure properties in Visual Studio).

I have tried to use the format_string property by using "

Code Snippet

CELL PROPERTIES FORMAT_STRING

", but this still returns "#, #" as the format string.

How do I get the data type of the measure?

Thanks

hello,

MDSCHEMA_MEASURES schema rowset has DATA_TYPE column which contains measure's the data type.

In Adomd.net you can get to this information in 2 ways: either execute schema rowset request directly (with connection.GetSchemaDataSet method), or via metadata objects. Measure object has .Properties collection, and you can find the DATA_TYPE property in it. So Measure.Properties["DATA_TYPE"].Value should contain the data type of the measure. (i think you can compare the values with System.Data.OleDb.OleDbDataType enum values), for example i can test if measure's data type is returned as currentcy with something like this:

(UInt16)measure.Properties["DATA_TYPE"].Value == (UInt16)System.Data.OleDb.OleDbType.Currency

You should be able to get to Measure object either from .Measures colleciton exposed on cube, or by

cube.GetSchemaObject(SchemaObjectType.ObjectTypeMeasure, <unique name of the measure>) as Measure

hope this helps,

MDX Statement

Hi
I've four Dimensions
1. WAGroup (it's a Value type tree)
2. HDS (it's an organisation tree)
3. Dates
4. ValueLayer (a scenario selector)
I’ve to calculated Members
1. WAGroupVector based on the Dimension WAGroup-- Memberproperty
“WAGroupVector”
2. Vector based on the Dimension HDS-Memberproperty “Vector”
Now I’ve the follwing MDX-Query:
WITH MEMBER Measures.WAGroupVector AS
'[WAGroup].CURRENTMEMBER.PROPERTIES("WAGroupVector")'
MEMBER Measures.Vector AS '[HDS].CURRENTMEMBER.PROPERTIES("Vector")'
SELECT{Measures.WAGroupVector,Measures.Vector,Measures.[Value]} ON
COLUMNS ,
NonEmptyCrossJoin(
NonEmptyCrossJoin(
DESCENDANTS(HDS.[85000 (20050228)]),
Dates.Members
),
WAGroup.Members
) ON ROWS
FROM [CostOnMonthlyBasis]
WHERE (ValueLayer.IST)
Now to my question:
Is there a way to show within the result ONLY the Calculated Members and the
Measures –Value without all the other dynamic fields? I’m getting back s
o
many fields. But I’m only interested for the mentioned ones. And if I remo
ve
the Joins and use only the Dates Dimension (ON ROWS) - I’m only getting th
e
top level values…
Thanks a lot for any suggestions….
Regards,
DominicIf you're using Reporting Services (i.e. flattened rowset), then this
version may eliminate unwanted columns:
[vbcol=seagreen]
SELECT{Measures.[Value]} ON COLUMNS ,
NonEmptyCrossJoin(
NonEmptyCrossJoin(
DESCENDANTS(HDS.[85000
(20050228)]),
Dates.Members
),
WAGroup.Members
)
DIMENSION PROPERTIES
[WAGroup].[WAGroupVector],
[HDS].[Vector]
ON ROWS
FROM [CostOnMonthlyBasis]
WHERE (ValueLayer.IST)[vbcol=seagreen]
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

MDX Question

All,

I have a simple MDX query to return results based on user i/p in search screen. User can type in trade code and the results should give all the source of it. The dimension TRade contains couple of million members that are not unique. (Only source + trade is unique)

The below works when there is a single member corresponsing to input (xxxx). If there are multiple members, it returns nothing.

I can't use filter function here as the trade dimension is large and it takes time to filter. I can't search using member key as the key is made of Trade code + source where source is not avaiable during search,

any solution?

SELECT Measures.[Value] on 0,

NON EMPTY ([Source].[Source].Members) on 1

from MyCube

WHere [Trade].[Trade Code].[xxxx]

Cheers,

Arun

dear Arun,

Create a view with all distinct values that you pretend to aggredate. And in the datasourceview of AS, import this view and use it as a dimension.

regards!!

|||

Hi Pedro,

I dont really want to do that. The dimension design is right. I have another hierarchy in the Trade dimension with levels as Source & Trade Code. this is exactly what I need.

But my problem is filtering using the member name when it appears more than once in the dimension.

Cheers,

Arun

|||

Arun,

Do you have something like this:

Source Trade 1 T11 1 T12 2 T21 3 T31 3 T32 3 T33

correct?!

|||

Arun,

As PedroCGD has mentioned, make a Distinct Trade Value for Filter Criteria, and donot think about Single trade as you example

SELECT Measures.[Value] on 0,

NON EMPTY ([Source].[Source].Members) on 1

from MyCube

WHere [Trade].[Trade Code].[xxxx]

But think about Ranges.

Make a selection criteria for Start Range and End Range. I hope you will understand.

Bhudev

|||

Saw the rply only now.

I had manageed to solve the problem myself. Below is the query.

Source System dimension: (this dimension will have <20 members)

Key - name

1 - ss1

2 - ss2

3 - ss3

4 - ss4

trade dimension : (this can have millions of members) - uniquekey : SourceID + tradecode

SourceID TradeCode Key 1 TC11 Trade.TradeCode.&[1]&[TC11] 1 TC12 Trade.TradeCode.&[1]&[TC12] 2 TC11 Trade.TradeCode.&[2]&[TC11] 2 TC22 Trade.TradeCode.&[2]&[TC22] 3 TC31 Trade.TradeCode.&[3]&[TC31]

Code Snippet

SELECT [Measures].[Value] ON 0,

NON EMPTY (

STRTOSET(" {" +

GENERATE ( [Source System].[Source].[Source]. Members,

"[Trade].[Trade Code].&[" + CSTR([Source System].[Source].CurrentMember.Member_Key ) + "]&[xxxxxxx]" ,

"," )

+ "}")

)

ON 1

FROM [MyCube]

MDX Query

Hi,

I am having a query like this

SELECT NON EMPTY { [Measures].[Check Status],

[Measures].[Payment Amount], [Measures].[Check Type] }

ON COLUMNS, NON EMPTY

{ ([Activity].[Activity Identity].[Activity Identity].ALLMEMBERS *

[Activity].[Activity Description].[Activity Description].ALLMEMBERS

* [Faculty].[Faculty Name].[Faculty Name].ALLMEMBERS ) }

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM [cubActivity]

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,

FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Now i want to add a filter condition for this.

Where [Measures].[Payment Amount] > 4500 and [Measures].[Check Type] = 1

how to add and where to add this line the above MDX query

Can you please help me

Thanks

Dinesh

Something like the following might work

SELECT NON EMPTY { [Measures].[Check Status],

[Measures].[Payment Amount], [Measures].[Check Type] }

ON COLUMNS, NON EMPTY

FILTER({ ([Activity].[Activity Identity].[Activity Identity].ALLMEMBERS *

[Activity].[Activity Description].[Activity Description].ALLMEMBERS

* [Faculty].[Faculty Name].[Faculty Name].ALLMEMBERS ) }

, [Measures].[Payment Amount] > 4500 and [Measures].[Check Type] = 1)

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM [cubActivity]

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,

FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

MDX multiple where condition

Is there any way in MDX to give multiple where clause:
Example in SQL:
SELECT * FROM Price
WHERE Type in("Cost1", "Cost2", "Cost3")
I have total 10 Cost types. But I need this query to return only 3 listed
above. It is simple with SQL.
My question is How to do this in MDX?
I have tried using filter but I am not getting correct values. Is there any
way in WHERE clause? Is there any other solution?
My Development environment is:
Reporting Services using Visual Studio.Net
OLAP: MDX Queries (Base Database is MS SQL Server)
Thanks,
SamThis question would more likely be answered in the OLAP newsgroup.
microsoft.public.sqlserver.olap
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:7F350E56-F781-4DDA-8C92-0045F1B0FEB5@.microsoft.com...
> Is there any way in MDX to give multiple where clause:
> Example in SQL:
> SELECT * FROM Price
> WHERE Type in("Cost1", "Cost2", "Cost3")
> I have total 10 Cost types. But I need this query to return only 3 listed
> above. It is simple with SQL.
> My question is How to do this in MDX?
> I have tried using filter but I am not getting correct values. Is there
> any
> way in WHERE clause? Is there any other solution?
> My Development environment is:
> Reporting Services using Visual Studio.Net
> OLAP: MDX Queries (Base Database is MS SQL Server)
> Thanks,
> Sam|||Use named set.
with
set [Type Cost Set] as '{ [Type].[Cost1] : [Type].[Cost3] }'
select
{ [measures] } on columns,
{ [Type Cost Set] } on rows
from cube
"Jeff A. Stucker" wrote:
> This question would more likely be answered in the OLAP newsgroup.
> microsoft.public.sqlserver.olap
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Sam" <Sam@.discussions.microsoft.com> wrote in message
> news:7F350E56-F781-4DDA-8C92-0045F1B0FEB5@.microsoft.com...
> > Is there any way in MDX to give multiple where clause:
> >
> > Example in SQL:
> > SELECT * FROM Price
> > WHERE Type in("Cost1", "Cost2", "Cost3")
> >
> > I have total 10 Cost types. But I need this query to return only 3 listed
> > above. It is simple with SQL.
> > My question is How to do this in MDX?
> >
> > I have tried using filter but I am not getting correct values. Is there
> > any
> > way in WHERE clause? Is there any other solution?
> >
> > My Development environment is:
> > Reporting Services using Visual Studio.Net
> > OLAP: MDX Queries (Base Database is MS SQL Server)
> >
> > Thanks,
> > Sam
>
>|||Cost types are not sequential. It will not work with range. Is there anything
in MDX where condition:
Example: [Type].[Type1 or Type2 or Type3]
Sam
"mike" wrote:
> Use named set.
> with
> set [Type Cost Set] as '{ [Type].[Cost1] : [Type].[Cost3] }'
> select
> { [measures] } on columns,
> { [Type Cost Set] } on rows
> from cube
> "Jeff A. Stucker" wrote:
> > This question would more likely be answered in the OLAP newsgroup.
> > microsoft.public.sqlserver.olap
> >
> > --
> > Cheers,
> >
> > '(' Jeff A. Stucker
> > \
> >
> > Business Intelligence
> > www.criadvantage.com
> > ---
> > "Sam" <Sam@.discussions.microsoft.com> wrote in message
> > news:7F350E56-F781-4DDA-8C92-0045F1B0FEB5@.microsoft.com...
> > > Is there any way in MDX to give multiple where clause:
> > >
> > > Example in SQL:
> > > SELECT * FROM Price
> > > WHERE Type in("Cost1", "Cost2", "Cost3")
> > >
> > > I have total 10 Cost types. But I need this query to return only 3 listed
> > > above. It is simple with SQL.
> > > My question is How to do this in MDX?
> > >
> > > I have tried using filter but I am not getting correct values. Is there
> > > any
> > > way in WHERE clause? Is there any other solution?
> > >
> > > My Development environment is:
> > > Reporting Services using Visual Studio.Net
> > > OLAP: MDX Queries (Base Database is MS SQL Server)
> > >
> > > Thanks,
> > > Sam
> >
> >
> >|||with
set [Type Cost Set] as '{ [Type].[Cost1], [Type].[Cost2], [Type].[Cost3] }'
or if you want you could use the filter function, but know that using filter
the whole set is return then the results filtered so you get overhead. See
BOL.
"Sam" wrote:
> Cost types are not sequential. It will not work with range. Is there anything
> in MDX where condition:
> Example: [Type].[Type1 or Type2 or Type3]
> Sam
>
> "mike" wrote:
> > Use named set.
> >
> > with
> > set [Type Cost Set] as '{ [Type].[Cost1] : [Type].[Cost3] }'
> >
> > select
> > { [measures] } on columns,
> > { [Type Cost Set] } on rows
> > from cube
> >
> > "Jeff A. Stucker" wrote:
> >
> > > This question would more likely be answered in the OLAP newsgroup.
> > > microsoft.public.sqlserver.olap
> > >
> > > --
> > > Cheers,
> > >
> > > '(' Jeff A. Stucker
> > > \
> > >
> > > Business Intelligence
> > > www.criadvantage.com
> > > ---
> > > "Sam" <Sam@.discussions.microsoft.com> wrote in message
> > > news:7F350E56-F781-4DDA-8C92-0045F1B0FEB5@.microsoft.com...
> > > > Is there any way in MDX to give multiple where clause:
> > > >
> > > > Example in SQL:
> > > > SELECT * FROM Price
> > > > WHERE Type in("Cost1", "Cost2", "Cost3")
> > > >
> > > > I have total 10 Cost types. But I need this query to return only 3 listed
> > > > above. It is simple with SQL.
> > > > My question is How to do this in MDX?
> > > >
> > > > I have tried using filter but I am not getting correct values. Is there
> > > > any
> > > > way in WHERE clause? Is there any other solution?
> > > >
> > > > My Development environment is:
> > > > Reporting Services using Visual Studio.Net
> > > > OLAP: MDX Queries (Base Database is MS SQL Server)
> > > >
> > > > Thanks,
> > > > Sam
> > >
> > >
> > >|||I found a work around, instead getting row wise I am getting required data in
column axis.
Thanks for your help.
Sam
"mike" wrote:
> with
> set [Type Cost Set] as '{ [Type].[Cost1], [Type].[Cost2], [Type].[Cost3] }'
> or if you want you could use the filter function, but know that using filter
> the whole set is return then the results filtered so you get overhead. See
> BOL.
> "Sam" wrote:
> > Cost types are not sequential. It will not work with range. Is there anything
> > in MDX where condition:
> > Example: [Type].[Type1 or Type2 or Type3]
> > Sam
> >
> >
> > "mike" wrote:
> >
> > > Use named set.
> > >
> > > with
> > > set [Type Cost Set] as '{ [Type].[Cost1] : [Type].[Cost3] }'
> > >
> > > select
> > > { [measures] } on columns,
> > > { [Type Cost Set] } on rows
> > > from cube
> > >
> > > "Jeff A. Stucker" wrote:
> > >
> > > > This question would more likely be answered in the OLAP newsgroup.
> > > > microsoft.public.sqlserver.olap
> > > >
> > > > --
> > > > Cheers,
> > > >
> > > > '(' Jeff A. Stucker
> > > > \
> > > >
> > > > Business Intelligence
> > > > www.criadvantage.com
> > > > ---
> > > > "Sam" <Sam@.discussions.microsoft.com> wrote in message
> > > > news:7F350E56-F781-4DDA-8C92-0045F1B0FEB5@.microsoft.com...
> > > > > Is there any way in MDX to give multiple where clause:
> > > > >
> > > > > Example in SQL:
> > > > > SELECT * FROM Price
> > > > > WHERE Type in("Cost1", "Cost2", "Cost3")
> > > > >
> > > > > I have total 10 Cost types. But I need this query to return only 3 listed
> > > > > above. It is simple with SQL.
> > > > > My question is How to do this in MDX?
> > > > >
> > > > > I have tried using filter but I am not getting correct values. Is there
> > > > > any
> > > > > way in WHERE clause? Is there any other solution?
> > > > >
> > > > > My Development environment is:
> > > > > Reporting Services using Visual Studio.Net
> > > > > OLAP: MDX Queries (Base Database is MS SQL Server)
> > > > >
> > > > > Thanks,
> > > > > Sam
> > > >
> > > >
> > > >