Memo fields from Access is empty in SQL Server

Hi
I have a DTS packet where I import data from a Access database to SQL Server.
But when I try to import data in memo fields in Access to a nvarchar field in a table on SQL server, there is no data imported.
Anyone know how to solve this problem?Not positive on this one, but I would try to map a memo field in access to a TEXT field in SQL. It should be pretty straightforward to test.

Regards,

Hugh Scott

MDX to fetch last 300 non empty dates?

Can someone enlighten me on how to return the last 300 dates worth of data from an MDX query?

For instance, this returns me all the dates in 2007...

SELECT ([Measures].[Total]) ON 0,
NON EMPTY [Calendar].[Date].CHILDREN ON 1
FROM [DB]
WHERE ([Calendar].[Year].[2007])

But let's say I want to return the last 300 non-empty dates and their "Total" value? (The Calendar dimension has the standard Year, Month, Date hierarchy...)

Thanks in advance.

How about something like this:

SELECT ([Measures].[Total]) ON 0,

Tail(

Filter(

([Calendar].[Date].CHILDREN) As S

Not IsEmpty(S.Current))

,300)

ON 1

FROM [DB]

This should return the last 300 non empty dates.

Van Dieu

|||Thanks Van -- very helpful. For some reason the "Not IsEmpty(...)" didn't work, but no worries, this workaround did...

TAIL(FILTER([Calendar].[Date].CHILDREN, ABS([Measures].[P&L Total]) > 1), 300)

MDX syntax error only on VS 2005

Hi, look im having this error frecuently on Visual Studio 2005, while executing a MDX query...

SELECT NON EMPTY { [Measures].[Budget Lead Count] } ON COLUMNS
FROM [LTL_Budget_Forecast] CELL PROPERTIES VALUE

but when I execute the same query on SQL Management Studio 2005, everything goes fine... and the query is executed successfully

Do you know what could be the problem here?

thx...

Regards,

-EWhat is the exact error you are getting? And where are you executing the query from? Is it in reporting services? The reporting services provider does some extra work and I would guess that the "non empty" clause on the columns would update reporting services as it requires a fixed set of columns.|||Thanks Darren,
the exact error displays as follows:

SQL Execution Error

Executed SQL Statement: SELECT NON EMPTY { [Measures].[Budget Lead Count] } ON COLUMNS
FROM [LTL_Budget_Forecast] CELL PROPERTIES VALUE
Error Source: .Net SqlClient Data Provider
Error Message: Incorrect syntax near '{'

Im executing this query on MS Visual Studio 2005, and my project is a Report Server Project|||

> Error Source: .Net SqlClient Data Provider

There's your problem. You should be using either the Analysis Services Provider or the OLEDB provider with the MSOLAP 9.0 OLE DB driver. The Analysis Services Provider gives you a friendlier MDX designer experience, so I would probably try that one first.

If you have both SQL and MDX based reports you will need to set up a second data source. If the report project only needs to run MDX queries, the you will need to change the details of the current data source.

|||Thanks a lot Darren, I solved that problem setting up a second data source...

Regards,

-E

MDX Query to display no records for empty rows

Hi,

I am facing with the following problem.

I am using bar chart to display my report.

My MDX query is as follows:

SELECT
NON EMPTY { [Measures].[SUM_COUNT] } ON COLUMNS,
TopCount ( Filter ( {[DIM].[NAME].[NAME]}, [Measures].[SUM_COUNT] <> 0 ) , 10, [Measures].[SUM_COUNT] ) ON ROWS
FROM
[USAGE]

where <criteria>

I want to show the topmost 10 records. For some criteria I get the results in the chart.

But for some criteria or say for wrong criteria, there are no records. In such a case the X-axis contains all values for {[DIM].[NAME].[NAME] and value for the Y-axis is all 0. Its kind of blank report it will restrict to 10 records.

In such a scenario I want to show a message to the user saying "No records found", which I have set in the No Rows property of the chart.

If I remove the TopCount clause then I get the above message, which is obvious.

So how do I acheive the same message but at the same time limiting the records to 10?

How can I acheive this in my scenario? Can something be done at the query end?

any help is appreciated.

Thanks in advance!

I could solve my problem by the following query

SELECT
NON EMPTY { [Measures].[SUM_COUNT] } ON COLUMNS,
NON EMPTY TopCount ( Filter ( {[DIM].[NAME].[NAME]}, [Measures].[SUM_COUNT] <> 0 ) , 10, [Measures].[SUM_COUNT] ) ON ROWS
FROM
[USAGE]

|||I have a similur problem. What I need to do is display a message to the user "No Records Found" if there query returns no hits? I knwo a way to do this with SQL, but i'm not sure how I can do this with in the SQL reporting services.

Thanks,|||What I ended up doing was adding a footer to the report and putting the following logic in that footer: =IIF(IsNothing(Fields!yourFieldHere), "No Records Found", "").

Hope it helps.

MDX Query to display no records for empty rows

Hi,

I am facing with the following problem.

I am using bar chart to display my report.

My MDX query is as follows:

SELECT
NON EMPTY { [Measures].[SUM_COUNT] } ON COLUMNS,
TopCount ( Filter ( {[DIM].[NAME].[NAME]}, [Measures].[SUM_COUNT] <> 0 ) , 10, [Measures].[SUM_COUNT] ) ON ROWS
FROM
[USAGE]

where <criteria>

I want to show the topmost 10 records. For some criteria I get the results in the chart.

But for some criteria or say for wrong criteria, there are no records. In such a case the X-axis contains all values for {[DIM].[NAME].[NAME] and value for the Y-axis is all 0. Its kind of blank report it will restrict to 10 records.

In such a scenario I want to show a message to the user saying "No records found", which I have set in the No Rows property of the chart.

If I remove the TopCount clause then I get the above message, which is obvious.

So how do I acheive the same message but at the same time limiting the records to 10?

How can I acheive this in my scenario? Can something be done at the query end?

any help is appreciated.

Thanks in advance!

I could solve my problem by the following query

SELECT
NON EMPTY { [Measures].[SUM_COUNT] } ON COLUMNS,
NON EMPTY TopCount ( Filter ( {[DIM].[NAME].[NAME]}, [Measures].[SUM_COUNT] <> 0 ) , 10, [Measures].[SUM_COUNT] ) ON ROWS
FROM
[USAGE]

|||I have a similur problem. What I need to do is display a message to the user "No Records Found" if there query returns no hits? I knwo a way to do this with SQL, but i'm not sure how I can do this with in the SQL reporting services.

Thanks,|||What I ended up doing was adding a footer to the report and putting the following logic in that footer: =IIF(IsNothing(Fields!yourFieldHere), "No Records Found", "").

Hope it helps.

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 query

I'm building a report from SSAS 2005 with three parameters.

start date

end date

project stream

I would filter only the non empty project stream in the actual cost fact table included in the time interval specified with the first two paramenters.

SSRS automatically compose an MDX query that I try to modify to get what I want:

WITHMEMBER [Measures].[ParameterCaption] AS'[PROJECT].[STREAM].CURRENTMEMBER.MEMBER_CAPTION'MEMBER [Measures].[ParameterValue] AS'[PROJECT].[STREAM].CURRENTMEMBER.UNIQUENAME'MEMBER [Measures].[ParameterLevel] AS'[PROJECT].[STREAM].CURRENTMEMBER.LEVEL.ORDINAL'

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel], [Measures].[EUR] } ONCOLUMNS ,

NONEMPTY [PROJECT].[STREAM].ALLMEMBERSONROWS

FROM [PMO_MDB]

WHERE { STRTOMEMBER(@.FY_MONTH_START) : STRTOMEMBER(@.FY_MONTH_END)}

In this way I get all project streams and I'm not able to filter away the empty cell (using the [Measures].[EUR] defined in the actual cost fact table). In fact SSRS put some intrisict member properties on columns as calculated measure !

There is another way to extract the same info but filtering away the empty cell ?

Cosimo

Take a look at the EXISTING function. I believe you could use it on your row axis to limit Project.Stream members to those associated with a fact entry.

Bryan

|||

Thanks Brjan ... I tried but I get the same result also using EXISTING !

I think the problem is that SSRS put on columns as a calculated measure some properties.

There is another way to extract such properies ?

I try the dimension properties clause ... but nothing happen !

Cosimo

|||

Sorry. I meant the EXISTS function. (The names are so similar I always screw them up.) Here is an example of what I'm describing:

Code Snippet

select

[Measures].[Reseller Sales Amount] on 0,

EXISTS(

[Product].[SubCategory].Members,

{ STRTOMEMBER("[Date].[Date].[June 1, 2003]") : STRTOMEMBER("[Date].[Date].[June 30, 2004]")},

'Reseller Sales'

) on 1

from [Adventure Works]

WHERE { STRTOMEMBER("[Date].[Date].[June 1, 2003]") : STRTOMEMBER("[Date].[Date].[June 30, 2004]")}

For comparison:

Code Snippet

select

[Measures].[Reseller Sales Amount] on 0,

[Product].[SubCategory].Memberson 1

from [Adventure Works]

WHERE { STRTOMEMBER("[Date].[Date].[June 1, 2003]") : STRTOMEMBER("[Date].[Date].[June 30, 2004]")}

Sorry for the confusion,

Bryan