Problem of "Export to Excel" for "Chart" within "Matrix"

Hi
Please help to fix the following problem.
I design a report using a matrix to show the Sales data. Within the matrix,
I want to use a Bar Chart to show some the data.
Preview and Export to pdf format is ok and does not have any problem.
However, I get the error message "Specified Cast is not Valid" when I try to
export to Excel. How can I fix it, please help!
Thank You
TonyI have The same problem but with a table on report.
Anybody knows what is causing it?
"Tony" wrote:
> Hi
> Please help to fix the following problem.
> I design a report using a matrix to show the Sales data. Within the matrix,
> I want to use a Bar Chart to show some the data.
> Preview and Export to pdf format is ok and does not have any problem.
> However, I get the error message "Specified Cast is not Valid" when I try to
> export to Excel. How can I fix it, please help!
> Thank You
> Tony

Problem migrating NonEmptyCrossJoin query to AS2005

Hi,

In AS2000 we were using following query as a basis for product report:

select {[Measures].[Stock], [Measures].[Sales Qty YTD], [Measures].[Sales Qty LW]} on columns,
NonEmptyCrossJoin([Hierarchy].[Sub Category].Members, [SKU].[SKU Name].Members, [Supplier].[Short Name].Members) on rows
from eSalesActStock
where [DOp Calendar].[OpYear].[2006].[08.February].[Week 32]

It was used to show all products either on stock, or sold this year or both.
It was getting approx 9k rows as result
The cube used was a virtual one, merging stock and sales cubes.
The where clause is only to put calculated members from sales cube in context.
Dimensions are crossjoined only because we need them for grouping and on report layout. Every SKU has a supplier and is attached to assortment hierarchy.

In AS2005 we tried to recreate the dataset, and this is what we ended up with:

SELECT NON EMPTY { [Measures].[Sales Qty YTD], [Measures].[Stock],
[Measures].[Sales Qty LW]} ON COLUMNS,
NonEmpty ([DHierarchy].[DHierarchy].[Division].ALLMEMBERS * [DSKU].[DSKU Name].[DSKU Name].ALLMEMBERS *
[DSupplier].[DSupplier].[Supplier].ALLMEMBERS ) ON ROWS
FROM [SalesStock]
WHERE ( [DCalendar].[DOp Calendar].[OpYear].&[2006].&[08.February].&[Week 32] )

Unfortunately this query only results in 1.1k rows result, with the same data and parameters.
We have played a lot around with it, even trying to use nonemptycrossjoin but the results remained the same.
We did find out, that after removing where clause, the query retrieves 9k rows, which seems to be correct amount, but the data are useless, as calculated members do not have date context.

The only thing that differs from the AS2000 is that in AS2000 we had stock figures artificially tied to a date, to see the stock figures if you chose any date dimension filters. In AS2005 this is no longer necessary, so we just left date dimension and stock measure group unrelated. Can this be the cause of problem ? It seems unlikely, as crossjoin is made on different dimensions...

Are we missing something ? It used to work as the result of crossjoin was not related to where clause on non crossjoined dimension, now it seems that date dimension actually influences the results of the cross join...

Maybe there is a way to solve or walk around this problem ?

Any feedback appreciated :)

Thanks,

Marcin

Hi Marcin,

The role of the date wrt. stock data isn't clear to me; but since the NonEmpty() returns the right results without the where clause, try adding the default date as the filter:

>>

SELECT NON EMPTY { [Measures].[Sales Qty YTD], [Measures].[Stock],
[Measures].[Sales Qty LW]} ON COLUMNS,
NonEmpty ([DHierarchy].[DHierarchy].[Division].ALLMEMBERS * [DSKU].[DSKU Name].[DSKU Name].ALLMEMBERS *
[DSupplier].[DSupplier].[Supplier].ALLMEMBERS,

{[DCalendar].[DOp Calendar].DefaultMember} ) ON ROWS
FROM [SalesStock]
WHERE ( [DCalendar].[DOp Calendar].[OpYear].&[2006].&[08.February].&[Week 32] )
>>

|||Thanks a lot for your answer, it did solve the problem :)

Problem is forming Query

Hello,

I have a table
say SalesLeads. This table is accessed through various front -ends and
it stores data for Sales leads of various products.

This table has a reference to another table(SalesLeadRefTable)

Sample Data in SalesLeads

SalesLeadID Comments RefTableID
-----------------
1 Sample 1

Sample Data in SalesLeadRefTable

ReferenceID TableName TableFieldName
-----------------
1 ProductTable ProductID

I need to form a query which will refer to the refdtableID and form a
dynamic query like

Select Comments , TableName.TableFieldName from SalesLeads,TableName

Please let me know if this can be doen in 1 queryYou could implement a JOIN to one of many tables like this:

SELECT A.keycol, COALESCE(B.col1, C.col1, D.col1) AS col1
FROM Something AS A
LEFT JOIN Table1 AS B
ON A.keycol = B.keycol AND A.entity = 1
LEFT JOIN Table2 AS C
ON A.keycol = C.keycol AND A.entity = 2
LEFT JOIN Table3 AS D
ON A.keycol = D.keycol AND A.entity = 3

Otherwise you would have to use Dynamic SQL inside an EXEC statement.

--
David Portas
SQL Server MVP
--