Jump to content
Software FX Community
Sign in to follow this  
User (Legacy)

Issues with OLAP datasource

Recommended Posts

I'm trying to create a simple stacked column graph using a dataset from 

Analysis Services 2000 however when I try and apply the data to the chart,

ChartFX is picking up the measures from the cube as type System.Object and

not allowing me to continue (because it claims I have no numeric field). I

have no problems using the standard Reporting Services chart. How can I

convince it that the measures really are numeric (the underlying datatype is

an integer). The dimensions are shown as System.String which is fine but not

much use without the measures :-)

--

Cheers,

Jasper Smith

Share this post


Link to post
Share on other sites

I'm trying to create a simple stacked column graph using a dataset from 

Analysis Services 2000 however when I try and apply the data to the chart,

ChartFX is picking up the measures from the cube as type System.Object and

not allowing me to continue (because it claims I have no numeric field). I

have no problems using the standard Reporting Services chart. How can I

convince it that the measures really are numeric (the underlying datatype is

an integer). The dimensions are shown as System.String which is fine but not

much use without the measures :-)

--

Cheers,

Jasper Smith

Share this post


Link to post
Share on other sites

We are unable to reproduce this problem.

I tried with one of the OLAP sample Databases (Foodmart2000, Adventure

Works) and the type returned is Integer.

Here is one of the queries I tried:

SELECT NON EMPTY { [Measures].[Amount] } ON COLUMNS, NON EMPTY {

([Time].[Month].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION,

MEMBER_UNIQUE_NAME ON ROWS FROM [budget] CELL PROPERTIES VALUE

Against Foodmart2000. I get amount as an integer (as it should be).

I also tried the following against Adventure Works:

SELECT NON EMPTY { [Measures].[Order Count] } ON COLUMNS, NON EMPTY {

([Date].[Day of Month].[Day of Month].ALLMEMBERS ) } DIMENSION PROPERTIES

MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL

PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING,

FONT_NAME, FONT_SIZE, FONT_FLAGS

Here also I get the right type.

Can you provide a sample query for either of these sample DB's that

reproduces the problem ?

--

Francisco Padron

www.chartfx.com

Share this post


Link to post
Share on other sites

We are unable to reproduce this problem.

I tried with one of the OLAP sample Databases (Foodmart2000, Adventure

Works) and the type returned is Integer.

Here is one of the queries I tried:

SELECT NON EMPTY { [Measures].[Amount] } ON COLUMNS, NON EMPTY {

([Time].[Month].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION,

MEMBER_UNIQUE_NAME ON ROWS FROM [budget] CELL PROPERTIES VALUE

Against Foodmart2000. I get amount as an integer (as it should be).

I also tried the following against Adventure Works:

SELECT NON EMPTY { [Measures].[Order Count] } ON COLUMNS, NON EMPTY {

([Date].[Day of Month].[Day of Month].ALLMEMBERS ) } DIMENSION PROPERTIES

MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL

PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING,

FONT_NAME, FONT_SIZE, FONT_FLAGS

Here also I get the right type.

Can you provide a sample query for either of these sample DB's that

reproduces the problem ?

--

Francisco Padron

www.chartfx.com

Share this post


Link to post
Share on other sites

I think I have an idea of what the problem is. If I use the Analysis 

Services type connection (Microsoft SQL Server Analysis Services

(AdomdClient)) for the datasource against an AS2005 cube then there are no

problems.

However if I use an OLEDB connection (specifying the Microsoft OLE DB

Provider for OLAP Services 8.0 driver) against an AS2000 cube then measures

are detected as System.Object. This works fine using the standard built in

Reporting Services chart.

So I guess the question is does the chart component support AS2000 using the

normal AS2000 driver? I'm planning on migrating to AS2005 anyway but I was

hoping to use the charts against AS2000 initially.

--

Cheers,

Jasper Smith

"SoftwareFX Support" <noreply@softwarefx.com> wrote in message

news:ihVxqbHbGHA.1536@webserver3.softwarefx.com...

> We are unable to reproduce this problem.

>

> I tried with one of the OLAP sample Databases (Foodmart2000, Adventure

> Works) and the type returned is Integer.

>

> Here is one of the queries I tried:

>

> SELECT NON EMPTY { [Measures].[Amount] } ON COLUMNS, NON EMPTY {

> ([Time].[Month].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION,

> MEMBER_UNIQUE_NAME ON ROWS FROM [budget] CELL PROPERTIES VALUE

> Against Foodmart2000. I get amount as an integer (as it should be).

>

>

>

> I also tried the following against Adventure Works:

>

> SELECT NON EMPTY { [Measures].[Order Count] } ON COLUMNS, NON EMPTY {

> ([Date].[Day of Month].[Day of Month].ALLMEMBERS ) } DIMENSION PROPERTIES

> MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL

> PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING,

> FONT_NAME, FONT_SIZE, FONT_FLAGS

>

> Here also I get the right type.

>

> Can you provide a sample query for either of these sample DB's that

> reproduces the problem ?

>

> --

> Francisco Padron

> www.chartfx.com

>

Share this post


Link to post
Share on other sites

I think I have an idea of what the problem is. If I use the Analysis 

Services type connection (Microsoft SQL Server Analysis Services

(AdomdClient)) for the datasource against an AS2005 cube then there are no

problems.

However if I use an OLEDB connection (specifying the Microsoft OLE DB

Provider for OLAP Services 8.0 driver) against an AS2000 cube then measures

are detected as System.Object. This works fine using the standard built in

Reporting Services chart.

So I guess the question is does the chart component support AS2000 using the

normal AS2000 driver? I'm planning on migrating to AS2005 anyway but I was

hoping to use the charts against AS2000 initially.

--

Cheers,

Jasper Smith

"SoftwareFX Support" <noreply@softwarefx.com> wrote in message

news:ihVxqbHbGHA.1536@webserver3.softwarefx.com...

> We are unable to reproduce this problem.

>

> I tried with one of the OLAP sample Databases (Foodmart2000, Adventure

> Works) and the type returned is Integer.

>

> Here is one of the queries I tried:

>

> SELECT NON EMPTY { [Measures].[Amount] } ON COLUMNS, NON EMPTY {

> ([Time].[Month].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION,

> MEMBER_UNIQUE_NAME ON ROWS FROM [budget] CELL PROPERTIES VALUE

> Against Foodmart2000. I get amount as an integer (as it should be).

>

>

>

> I also tried the following against Adventure Works:

>

> SELECT NON EMPTY { [Measures].[Order Count] } ON COLUMNS, NON EMPTY {

> ([Date].[Day of Month].[Day of Month].ALLMEMBERS ) } DIMENSION PROPERTIES

> MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL

> PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING,

> FONT_NAME, FONT_SIZE, FONT_FLAGS

>

> Here also I get the right type.

>

> Can you provide a sample query for either of these sample DB's that

> reproduces the problem ?

>

> --

> Francisco Padron

> www.chartfx.com

>

Share this post


Link to post
Share on other sites

My Foodmart2000 DB is in SQL 2000. However I connected to it using the 

"Microsoft SQL Server Analysis Services" driver, not OLEDB (see attached

screenshot)

--

Francisco Padron

www.chartfx.com

Share this post


Link to post
Share on other sites

My Foodmart2000 DB is in SQL 2000. However I connected to it using the 

"Microsoft SQL Server Analysis Services" driver, not OLEDB (see attached

screenshot)

--

Francisco Padron

www.chartfx.com

Share this post


Link to post
Share on other sites

And that works for setting up the graph, but if I try and preview it or 

deploy it to a report server and then view it I get an error (and I've tried

on multiple machines). I'm pretty sure you can't use the Microsoft SQL

Server Analysis Services connection to connect to a SQL2000 Analysis

Services server in a deployed report (I'm not really sure why it works in

the report designer). The error I get when previewing the report in the

report designer is the same error I get if I try and connect to an Analysis

Services 2000 server using SQL Server Management Studio.

--

Cheers,

Jasper Smith

"SoftwareFX Support" <noreply@softwarefx.com> wrote in message

news:U4gIbpSbGHA.1540@webserver3.softwarefx.com...

> My Foodmart2000 DB is in SQL 2000. However I connected to it using the

> "Microsoft SQL Server Analysis Services" driver, not OLEDB (see attached

> screenshot)

>

>

> --

> Francisco Padron

> www.chartfx.com

>

>

post-2106-13922382415209_thumb.png

post-2106-13922386110335_thumb.png

Share this post


Link to post
Share on other sites

And that works for setting up the graph, but if I try and preview it or 

deploy it to a report server and then view it I get an error (and I've tried

on multiple machines). I'm pretty sure you can't use the Microsoft SQL

Server Analysis Services connection to connect to a SQL2000 Analysis

Services server in a deployed report (I'm not really sure why it works in

the report designer). The error I get when previewing the report in the

report designer is the same error I get if I try and connect to an Analysis

Services 2000 server using SQL Server Management Studio.

--

Cheers,

Jasper Smith

"SoftwareFX Support" <noreply@softwarefx.com> wrote in message

news:U4gIbpSbGHA.1540@webserver3.softwarefx.com...

> My Foodmart2000 DB is in SQL 2000. However I connected to it using the

> "Microsoft SQL Server Analysis Services" driver, not OLEDB (see attached

> screenshot)

>

>

> --

> Francisco Padron

> www.chartfx.com

>

>

Share this post


Link to post
Share on other sites

For me it works in both Preview and when I hit Run (Start Debugging).

I did find a problem in Chart FX RS hen connecting using this driver but I

have fixed it. Are you getting the error in the chart object or do you get

the error even when you don't have a char in your report.

--

Francisco Padron

www.chartfx.com

Share this post


Link to post
Share on other sites

For me it works in both Preview and when I hit Run (Start Debugging).

I did find a problem in Chart FX RS hen connecting using this driver but I

have fixed it. Are you getting the error in the chart object or do you get

the error even when you don't have a char in your report.

--

Francisco Padron

www.chartfx.com

Share this post


Link to post
Share on other sites

I get an error even without the chart i.e. just using a table in the report. 

Having said that, every server I have tested on actually has both AS2000 +

AS2005 (as a named instance) on it which may be complicating matters (SP4

for AS2000 and SP1 for AS2005). In the report preview I get an error stating

"Cannot create a connection to datasource 'AS2000'. A connection cannot be

made. No connection could be made because the target server actively refused

it." (althought the data and layout panes function normally)

On the report server itself, I tend to get an error like "An error has

occurred during processing of the CustomReportItem 'chart1' with the 'Chart'

extension. Specified cast is not valid." (see below for detail) however I

have managed to get it working ok when not using a custom chart

aspnet_wp!webserver!9!05/02/2006-22:47:14:: e ERROR: Reporting Services

error Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An

error has occurred during processing of the CustomReportItem 'chart1' with

the 'Chart' extension. --->

Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: An

error has occurred during processing of the CustomReportItem 'chart1' with

the 'Chart' extension. ---> System.InvalidCastException: Specified cast is

not valid.

at

ChartFX.ReportingServices.DataProviders.CrosstabDataProvider.a(TypeCode A_0,

Object A_1)

at ChartFX.ReportingServices.DataProviders.CrosstabDataProvider.p()

at ChartFX.ReportingServices.DataSourceSettings.a()

at ChartFX.ReportingServices.DataSourceSettings.e()

at ChartFX.ReportingServices.DataSourceSettings.set_DataSource(Object

value)

at ChartFX.ReportingServices.Chart.b()

at ChartFX.ReportingServices.Chart.c()

at

Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeRICollection.CreateInstance(ReportItem

reportItem, Boolean setupEnvironment, Int32 index)

--

Cheers,

Jasper Smith

"SoftwareFX Support" <noreply@softwarefx.com> wrote in message

news:2wh4%23ThbGHA.1540@webserver3.softwarefx.com...

> For me it works in both Preview and when I hit Run (Start Debugging).

>

> I did find a problem in Chart FX RS hen connecting using this driver but I

> have fixed it. Are you getting the error in the chart object or do you get

> the error even when you don't have a char in your report.

>

> --

> Francisco Padron

> www.chartfx.com

>

Share this post


Link to post
Share on other sites

I get an error even without the chart i.e. just using a table in the report. 

Having said that, every server I have tested on actually has both AS2000 +

AS2005 (as a named instance) on it which may be complicating matters (SP4

for AS2000 and SP1 for AS2005). In the report preview I get an error stating

"Cannot create a connection to datasource 'AS2000'. A connection cannot be

made. No connection could be made because the target server actively refused

it." (althought the data and layout panes function normally)

On the report server itself, I tend to get an error like "An error has

occurred during processing of the CustomReportItem 'chart1' with the 'Chart'

extension. Specified cast is not valid." (see below for detail) however I

have managed to get it working ok when not using a custom chart

aspnet_wp!webserver!9!05/02/2006-22:47:14:: e ERROR: Reporting Services

error Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An

error has occurred during processing of the CustomReportItem 'chart1' with

the 'Chart' extension. --->

Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: An

error has occurred during processing of the CustomReportItem 'chart1' with

the 'Chart' extension. ---> System.InvalidCastException: Specified cast is

not valid.

at

ChartFX.ReportingServices.DataProviders.CrosstabDataProvider.a(TypeCode A_0,

Object A_1)

at ChartFX.ReportingServices.DataProviders.CrosstabDataProvider.p()

at ChartFX.ReportingServices.DataSourceSettings.a()

at ChartFX.ReportingServices.DataSourceSettings.e()

at ChartFX.ReportingServices.DataSourceSettings.set_DataSource(Object

value)

at ChartFX.ReportingServices.Chart.b()

at ChartFX.ReportingServices.Chart.c()

at

Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeRICollection.CreateInstance(ReportItem

reportItem, Boolean setupEnvironment, Int32 index)

--

Cheers,

Jasper Smith

"SoftwareFX Support" <noreply@softwarefx.com> wrote in message

news:2wh4%23ThbGHA.1540@webserver3.softwarefx.com...

> For me it works in both Preview and when I hit Run (Start Debugging).

>

> I did find a problem in Chart FX RS hen connecting using this driver but I

> have fixed it. Are you getting the error in the chart object or do you get

> the error even when you don't have a char in your report.

>

> --

> Francisco Padron

> www.chartfx.com

>

Share this post


Link to post
Share on other sites

SQL2005 BOL (April 2006) does actually state that you should use OLEDB to 

connect to AS2000 "To retrieve SQL Server 2000 Analysis Services data, use

the OLE DB Provider for OLAP Services 8.0."

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/9d11d055-a3be-45aa-99a7-46447a94ed42.htm

--

Cheers,

Jasper Smith

"SoftwareFX Support" <noreply@softwarefx.com> wrote in message

news:2wh4%23ThbGHA.1540@webserver3.softwarefx.com...

> For me it works in both Preview and when I hit Run (Start Debugging).

>

> I did find a problem in Chart FX RS hen connecting using this driver but I

> have fixed it. Are you getting the error in the chart object or do you get

> the error even when you don't have a char in your report.

>

> --

> Francisco Padron

> www.chartfx.com

>

Share this post


Link to post
Share on other sites

SQL2005 BOL (April 2006) does actually state that you should use OLEDB to 

connect to AS2000 "To retrieve SQL Server 2000 Analysis Services data, use

the OLE DB Provider for OLAP Services 8.0."

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/9d11d055-a3be-45aa-99a7-46447a94ed42.htm

--

Cheers,

Jasper Smith

"SoftwareFX Support" <noreply@softwarefx.com> wrote in message

news:2wh4%23ThbGHA.1540@webserver3.softwarefx.com...

> For me it works in both Preview and when I hit Run (Start Debugging).

>

> I did find a problem in Chart FX RS hen connecting using this driver but I

> have fixed it. Are you getting the error in the chart object or do you get

> the error even when you don't have a char in your report.

>

> --

> Francisco Padron

> www.chartfx.com

>

Share this post


Link to post
Share on other sites

The exception:

System.InvalidCastException: Specified cast is not valid.

Is the error I found that I was talking about. Please contact Software FX

Support for a Hotfix that fixes this problem.

--

Francisco Padron

www.chartfx.com

Share this post


Link to post
Share on other sites

The exception:

System.InvalidCastException: Specified cast is not valid.

Is the error I found that I was talking about. Please contact Software FX

Support for a Hotfix that fixes this problem.

--

Francisco Padron

www.chartfx.com

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...