Reporting Services and Analysis Services - help please - desperate!

Hi folks,
Please please can someone help, this problem has just been going on for too
long.We need to run reports off RS using AS as the data source.
Our setup is as follows
RS - on seperate web server (srvWeb)
RS DB - on seperate SQL server (srvDB)
No problem - everything has worked fine connecting directly to the databases
to extract data for the reports.
However, when I connect to Analysis services on the RS DB (srvDB) server I
get the following error
a.. An error has occurred during report processing. (rsProcessingAborted)
Get Online Help
a.. Cannot create a connection to data source 'AS_Admin'.
(rsErrorOpeningConnection) Get Online Help
a.. Database 'BI_Admin' does not exist.
If I connect using localhost as the datasource name - it works 100% on my
machine but the minute I try to connect to a server (srvDB) that is not on
my machine I get the error.
We have installed Analysis Services on the web server (srvWeb) as well
because it didn't have the correct drivers but this has not helped at all -
I also registered the servers on AS (srvWeb) for what it's worth.
Does anyone please please know what I should do to get this working.
Thanks
KIf you're running RS on a Windows 2000 Server, and trying to use Stored
Windows Credentials to access a remote AS data source, this might help:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rstshoot/htm/trs_tshootdev_v1_3dcz.asp
Troubleshooting Server and Database Problems
...
You can get an rsConnectionError error that shows a failed login for ASP.NET
when you configure a data source to use prompted or stored Windows
credentials, and the report server hosting the report runs under a domain
user account on a Windows 2000 server. When installing Reporting Services on
a Windows 2000 server, Microsoft recommends that you use the built-in account
(NT AUTHORITY/SYSTEM). If you use a domain user account (even one that has
local administrator privileges), it will limit your choices on how to
configure a data source connection for a report. Specifically, you cannot use
prompted or stored Windows credentials to connect to external data sources.
...
"Kathy" wrote:
> Hi folks,
> Please please can someone help, this problem has just been going on for too
> long.We need to run reports off RS using AS as the data source.
> Our setup is as follows
> RS - on seperate web server (srvWeb)
> RS DB - on seperate SQL server (srvDB)
> No problem - everything has worked fine connecting directly to the databases
> to extract data for the reports.
> However, when I connect to Analysis services on the RS DB (srvDB) server I
> get the following error
> a.. An error has occurred during report processing. (rsProcessingAborted)
> Get Online Help
> a.. Cannot create a connection to data source 'AS_Admin'.
> (rsErrorOpeningConnection) Get Online Help
> a.. Database 'BI_Admin' does not exist.
> If I connect using localhost as the datasource name - it works 100% on my
> machine but the minute I try to connect to a server (srvDB) that is not on
> my machine I get the error.
> We have installed Analysis Services on the web server (srvWeb) as well
> because it didn't have the correct drivers but this has not helped at all -
> I also registered the servers on AS (srvWeb) for what it's worth.
> Does anyone please please know what I should do to get this working.
> Thanks
> K
>
>
>

Reporting Services and Analysis Services

I have Reporting Services on one server and SQL and Analysis Services on another Server. I can develop a report using Analysis Services as the source from my local machine and it works fine. When I deploy it to the Reporting Services Server, it gives the following error:

An error has occurred during report processing.
Cannot create a connection to data source 'DataSource1'.
A connection cannot be made. Ensure that the server is running.
No connection could be made because the target machine actively refused it

Thank you in advnace for any help.

James

Also, I am running Reporting Services 2005. The Reporting Services databases are on a SQL2005 box and the Analysis Services is 2000 and it is on a seperate box.|||

Check the credentials that are specified. When you are working with a report in Report Designer you have specified in your project what credentials to use. When you publish, those may or may not be the same credentials that are used on the server. Go to Report Manager, navigate to the data source and see if what is specified is what you expect.

|||

I have used 'Credentials stored securely in the report server' and verified my credentials. I use the same credentials for the AS2005 server, which I can report against, also.

Has anyone had luck with this configuration of using RS2005 on its own box and reporting off of AS2000?

|||

If you haven't done so, you need to check also the Use as Windows credentials when connecting to the data source checkbox. You user name has to be in the format domain\user. If this doesn't work, Active Directory may be messing things up. One thing you can check is to create a local Windows account on the AS2000 box and use its credentials to rule out AD.

Reporting Services and Analysis Services

I have Reporting Services on one server and SQL and Analysis Services on another Server. I can develop a report using Analysis Services as the source from my local machine and it works fine. When I deploy it to the Reporting Services Server, it gives the following error:

An error has occurred during report processing.
Cannot create a connection to data source 'DataSource1'.
A connection cannot be made. Ensure that the server is running.
No connection could be made because the target machine actively refused it

Thank you in advnace for any help.

James

Also, I am running Reporting Services 2005. The Reporting Services databases are on a SQL2005 box and the Analysis Services is 2000 and it is on a seperate box.|||

Check the credentials that are specified. When you are working with a report in Report Designer you have specified in your project what credentials to use. When you publish, those may or may not be the same credentials that are used on the server. Go to Report Manager, navigate to the data source and see if what is specified is what you expect.

|||

I have used 'Credentials stored securely in the report server' and verified my credentials. I use the same credentials for the AS2005 server, which I can report against, also.

Has anyone had luck with this configuration of using RS2005 on its own box and reporting off of AS2000?

|||

If you haven't done so, you need to check also the Use as Windows credentials when connecting to the data source checkbox. You user name has to be in the format domain\user. If this doesn't work, Active Directory may be messing things up. One thing you can check is to create a local Windows account on the AS2000 box and use its credentials to rule out AD.

sql

Reporting Services 2005 using Analysis Services 2000 as a datasource

Has anyone tried this and if so what degree of success have you had.
I've tried with both April and June CTP and cannot get anything of any
use to return. This begs the question, is the only way to get data from
SSAS 2000 into reporting services is to use a flattened dataset through
OLEDB.
In fact any information regarding registering, querying or browsing
2000 servers with the 2005 tools would be useful.
Thanks
SteveI've answered my own question, the answer is a big fat dissapointing
"no".

Reporting Services 2005 and Analysis Services 2000

Hello,

i have a small problem with the data sources in the reporting services, maybe you can clarify the situation... I have a MS CRM 3 solution with the reporting services 2005 installed. From an other vendor we have a arcplan solution on the 2000 analysis services. I would like to get access from the 2005 reporting services to the 2000 cube. Is this possible? Ive read that the SSMS cant connect to the 2000 cubes. Can i connect from the reporting services?

thank you

regards

Andreas

Sure you can. However, you have to use the Microsoft OLE DB Provider for Analysis Services when you set up your data source in SSRS. You may find this whitepaper useful although it's been written for SSRS 2000.

|||

thank you for the quick answer. i thought of a compatibility issue because i can choose the server and the database in the driver. but when i try to send a mdx the driver always tells me that the connection is lost. Ill try it on a second server, maybe its a computer issue...

thank you

andreas

Reporting Services + Analysis Services KPIs

Hallo
Where can I find documentation about how to use Analysis services KPIs in a
Reporting Sevices report'
ThanksOnline help is sufficient, you still need more go download "Project Real" Its
a good sample. some of the samples of SSAS is also provided in the help and
micsoroft web site. its good.
Amarnath
"Ale Dori" wrote:
> Hallo
> Where can I find documentation about how to use Analysis services KPIs in a
> Reporting Sevices report'
> Thanks

Reporting Services + Analysis services Data Source

Hi all,

Till now all the reports for our application was created in Reporting Services 2005 with SQL Server Database(OLTP) stored procedures.

Most of the reports take 10 to 15 report Parameters and all are multi valued.

Now because of the slow response time,we have decided to use OLAP cubes data source. We have created the OLAP cube in Microsoft Analysis Services.

Can someone tell me how to use the report parameters to query the Cube.

Any links,books for reference would be of great help..

Thanks,

Looking forward to your replies

Hi there,

Try searching http://blogs.msdn.com.

Here is a link on how to use parameters.

http://www.databasejournal.com/features/mssql/article.php/10894_3504651_11

and here

http://blogs.msdn.com/bwelcker/archive/2005/04/24/411588.aspx

cheers,

Andrew

|||Thank you very much for replying.. i am looking at these sites.

Reporting Services - Analysis Services - Displaying Dimension Members as Columns

I think I've seen a similar post on a blog or on the forums - but it seems like this should be possible -

I have an MDX query - that works fine in SQL Enterprise Manager, and has my dimension members on columns, and my measures on the rows. When I try the same query in Reporting Services, I get the error:

"The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension..
Parameter name: mdx (MDXQueryGenerator)"

Although it works when you pivot the view, I really need my data presented with the members on the columns and the measures on the rows. Another forum post mentioned using the SQL 9.0 driver, but I can't see this listed anywhere (the only one I see is the .NET framework Data Provider for Microsoft Analysis Services).

Here's what my query looks like -

SELECT
{ [Time].[Month].&[2006-09-01T00:00:00] ,
[Time].[Month].&[2006-10-01T00:00:00],
[Time].[Month].&[2006-11-01T00:00:00],
[Time].[Month].&[2006-12-01T00:00:00]
} on COLUMNS,
{
[Measures].[Unique Users],
[Measures].[UU Pct 1],
[Measures].[UU Pct 2],
} ON ROWS
FROM [Cube]

Any ideas?

Thanks,
Arjun

As the error message says, the SSRS Analysis Services provider expects dimension on rows. If you want to use this query, you need to bypass the SSRS Analysis Services provider and use directly the OLE DB Provider for Analysis Services 9.0. The limitation of this approach is that the OLE DB driver doesn't support parameters. If you need to parameterize your report, you need to use an expression-based query that concatenates the parameter values, a-la RS 2000.

|||Thanks Teo, that makes sense. I found the OLE DB driver and I'll use this method. Really strange limitation in SSRS, hope they get rid of it in the next release.|||

you need to bypass the SSRS Analysis Services provider and use directly the OLE DB Provider for Analysis Services 9.0

How do you do this, Please?

|||When setting up your data source, choose the Microsoft OLE DB Provider for Analysis Services 9.0.|||thanks, but that choice does not show up when creating a datasource in reporting services 2005. OLE DB provider for analysis service 9.0 is definitely installed on that machine. I do see that choice from Excel when creating a pivot table based on a cube. Thanks for your help.|||Chose OLE DB as Type, then click on the Edit button.|||Thanks, it works .

Reporting Services - Analysis Services - Displaying Dimension Members as Columns

I think I've seen a similar post on a blog or on the forums - but it seems like this should be possible -

I have an MDX query - that works fine in SQL Enterprise Manager, and has my dimension members on columns, and my measures on the rows. When I try the same query in Reporting Services, I get the error:

"The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension..
Parameter name: mdx (MDXQueryGenerator)"

Although it works when you pivot the view, I really need my data presented with the members on the columns and the measures on the rows. Another forum post mentioned using the SQL 9.0 driver, but I can't see this listed anywhere (the only one I see is the .NET framework Data Provider for Microsoft Analysis Services).

Here's what my query looks like -

SELECT
{ [Time].[Month].&[2006-09-01T00:00:00] ,
[Time].[Month].&[2006-10-01T00:00:00],
[Time].[Month].&[2006-11-01T00:00:00],
[Time].[Month].&[2006-12-01T00:00:00]
} on COLUMNS,
{
[Measures].[Unique Users],
[Measures].[UU Pct 1],
[Measures].[UU Pct 2],
} ON ROWS
FROM [Cube]

Any ideas?

Thanks,
Arjun

As the error message says, the SSRS Analysis Services provider expects dimension on rows. If you want to use this query, you need to bypass the SSRS Analysis Services provider and use directly the OLE DB Provider for Analysis Services 9.0. The limitation of this approach is that the OLE DB driver doesn't support parameters. If you need to parameterize your report, you need to use an expression-based query that concatenates the parameter values, a-la RS 2000.

|||Thanks Teo, that makes sense. I found the OLE DB driver and I'll use this method. Really strange limitation in SSRS, hope they get rid of it in the next release.|||

you need to bypass the SSRS Analysis Services provider and use directly the OLE DB Provider for Analysis Services 9.0

How do you do this, Please?

|||When setting up your data source, choose the Microsoft OLE DB Provider for Analysis Services 9.0.|||thanks, but that choice does not show up when creating a datasource in reporting services 2005. OLE DB provider for analysis service 9.0 is definitely installed on that machine. I do see that choice from Excel when creating a pivot table based on a cube. Thanks for your help.|||Chose OLE DB as Type, then click on the Edit button.|||Thanks, it works .

Reporting services

Is it possible to generate ad hoc reporting in reporting services from an analysis services database? I would like to be able to generate ad hoc models for use in report builder. Does anyone know if this is possible?

Yes, you can generate RB model for AS cube - just right-click on UDM data source in Reporting Services connected in management studio, as described for example here.

My personal experience is, that RB is good for simple ad hoc queries, but when you will want to do anything more complex, use some advanced filter or grouping, then you're lost. Also, RB doesn't generate the most efficient MDX code, so performance suffers as well. We've tested it for some time, but at the end we decided to use Proclarity.

Radim

Reporting Service with Analysis service

I compose two servers. One is for Reporting service and the other is for
Analysis Service. I developed one report with analysis services's cube as a
datasource.
but the report does not execute properly. the message is for datasource
connection error.
So I compose the server again as one server. The RS ans AS installed in this
server.
The report works properly!
Is it impossible to install at seperate server ?
I used SQL server 2000 with current sevicepack.Hey,
It does work with 2 different servers. You may be having issues with
authentication or permissions.
If possible use an admin account from Analysis server in the connection
properties of the datasource.
Bye
Sumit Pilankar
"fogcity" <fogcity@.discussions.microsoft.com> wrote in message
news:FA13FF31-94D4-4619-8E9B-212252B372EF@.microsoft.com...
>I compose two servers. One is for Reporting service and the other is for
> Analysis Service. I developed one report with analysis services's cube as
> a
> datasource.
> but the report does not execute properly. the message is for datasource
> connection error.
> So I compose the server again as one server. The RS ans AS installed in
> this
> server.
> The report works properly!
> Is it impossible to install at seperate server ?
> I used SQL server 2000 with current sevicepack.
>

Reporting Service MDX problem

Hi all,
I am using Analysis Services cubes as backend to my Reporting Service
reports currently. I have a situation where I select
measures in axis 0
and
a heirarchy members & a level(in another heirarchy) in axis 1.
Now the result set will look like...
heiarchymember1 heirarchymember2 heirarchymember3 level measure
-- -- --
-- --
but my report requires the values of level column above to be column names
and the measure value to be values for those columns. In simple words I want
to transpose a column to rows and measure values should appear as data under
those transposed columns.
I cannot make use of matrix dataregion to do this as I have other
restriction. I can use only table dataregion. Is there a way (say some
function) available in mdx to do this kind of transpose with cube data in
reporting services.
Adventure works query which is good to explain my situation is
SELECT [Measures].[Total Product Cost] ON COLUMNS,
{([Product].[Product Categories].MEMBERS,
[Geography].[Geography].[Country].MEMBERS)} ON ROWS
FROM [Adventure Works]
Pl run the above query from reporting service report builder because if you
run in management studio u will see in different format.
Now when you run the above query in RS you can see Category, SubCategory,
ProductName, Country,Total Product Cost columns. What I want is
Category, SubCategory, ProductName,country1,country2,... as columns and the
relevant Total Product Cost measure values under the country names as data.
If it is sql I can use Case statement to do this kind of transposing. But
here in reporting service MDX we can only use measures no other dimension in
axis 0, which actually leads to this kind of transposing requirements.
Pl let me know what is the best way to accomplish this.
ThanksHi Wei Lu,
Since our reporting requirements are such that we cannot use matrix and we
have to use table with different groups all placing one below the other, I
tried a different solution. I found somewhere that If I make use of OLEDB
provider instead of MS Analysis Services Provider so that I dont have
restriction to put only Measures on columns axis. Now my question is what is
flexibility that I loose if I use oledb provider for analysis services 9.0
than Microsoft Analysis services provider?
Thanks
"Wei Lu [MSFT]" wrote:
> Hi ringt,
> Thank you for your posting!
> From your description, my understanding of this issue is: You want to use
> the MDX in the report and you want to show the heirarchymember in the
> column. If I misunderstood your concern, please feel free to let me know.
> I suggest you use the Matrix control in your report to show the data.
> The attachment is the Report I designed using the Matrix. Please check and
> let me know whether this meet your requirement.
> The datasource is the Adventure Works and the MDX query is the one you
> posted.
> I put the Country in the column group and Total Product Costs in the
> Detail, others in the Row group.
> Please let me know the result so that I can provide further assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights|||Hi ringt,
Thank you for the update.
Based on my research, this issue is by design. Although the MDX you want to
use Multi Dimensions in the columns axis could run in the Analysis
Services, the data is extracted as a flattened row-set and can be used by
the report builder only as a row-set. So the Report Builder will not care
about certain things related to OLAP.
I suggest you use the OLEDB for AS 9.0 driver so that you could add Multi
Dimensions in the columns axis.
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Ringt,
How are you doing on this issue, does Wei's last reply help clarify the
problem further or have you got further progress on this? If there is still
anything we can help, please feel free to post here.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.

Reporting Service

I am trying to use Reporting Service with Analysis Service but I'm not too
sure as to how to go about doing that.
Can someone point some articles or tutorials to me ?
Thanks in advanceFirst problem is configure correctly data set source.
You must use MDX language for obtain data information from olap cube.
You must use some tip&trics for provide data using matrix or table object.
For example when you get data from "MDX query" you have a list of records
with
additional lines for all totale dimensions and sub-dimension.
This additional line can be omitted if you want usa table object or you can
use it by matrix object for provide a dimensional total.
It's most important use expression code on text object (of your matrix code
for example) for personalize Caption label, filter etc.
"Nestor" wrote:

> I am trying to use Reporting Service with Analysis Service but I'm not too
> sure as to how to go about doing that.
> Can someone point some articles or tutorials to me ?
> Thanks in advance
>
>|||check this:
http://msdn.microsoft.com/library/d.../>
asandrs.asp
"Dario Concilio" wrote:
[vbcol=seagreen]
> First problem is configure correctly data set source.
> You must use MDX language for obtain data information from olap cube.
> You must use some tip&trics for provide data using matrix or table object.
> For example when you get data from "MDX query" you have a list of records
> with
> additional lines for all totale dimensions and sub-dimension.
> This additional line can be omitted if you want usa table object or you ca
n
> use it by matrix object for provide a dimensional total.
> It's most important use expression code on text object (of your matrix cod
e
> for example) for personalize Caption label, filter etc.
>
> "Nestor" wrote:
>

Reporting server is not so smart as Analysis server

Hi,

I have a fact table with 2 columns value A and value B

In Analysis server I add a calculated column C as A / B

Now my data looks like this

A B C

0 10 0

5 10 0.5

If you add totals in Analysis server you get

5 20 and 0.25 which is correct

If you add totals in Reporting server you get

5 20 and 0.5 which is not correct.

How can I fix this ?

Thanks in advance

Constantijn

If you are using a table report, in your group footer you could add an expression for C that equates to

=sum(Fields!A.value)/sum(Fields!B.Value)

|||

Or, assuming that the report uses the SSAS cube, instead of using SUM use Aggregate as explained here.

|||

Teo,

Many thanks for that tip - I had totally missed that little trick.

Will

|||Thanks, I wasn't aware of the that function

Reporting server is not so smart as Analysis server

Hi,

I have a fact table with 2 columns value A and value B

In Analysis server I add a calculated column C as A / B

Now my data looks like this

A B C

0 10 0

5 10 0.5

If you add totals in Analysis server you get

5 20 and 0.25 which is correct

If you add totals in Reporting server you get

5 20 and 0.5 which is not correct.

How can I fix this ?

Thanks in advance

Constantijn

If you are using a table report, in your group footer you could add an expression for C that equates to

=sum(Fields!A.value)/sum(Fields!B.Value)

|||

Or, assuming that the report uses the SSAS cube, instead of using SUM use Aggregate as explained here.

|||

Teo,

Many thanks for that tip - I had totally missed that little trick.

Will

|||Thanks, I wasn't aware of the that function

Reporting on an Analysis Services data cube

Hello,

The problem I am having is I am trying to report on an Analysis Services data cube.

I connect fine but for some reason the "MDX query designer" does not appear as an option for creating the query for this data source. Is there a component of SQL 2000 or visual studio I am missing

Any help Would be great

Thank you

One good starting point might be this link:

http://www.microsoft.com/downloads/details.aspx?FamilyID=f9b6e945-1f4c-4b7c-9c83-c6801f0576ff&DisplayLang=en

It is a rs 2000 sample that uses cubes. I hope this helps

|||Just to confirm - are you using RS 2005 with the Analysis Services Provider - otherwise you won't get the MDX Query Designer?|||

Thank you for your help. I unfortunately am only using RS2003.

SO i need to upgrade to RS 2005 and install Analysis Services Provider before ill get the designer ?

|||

You would need to upgrade RS 2005 - the Analysis Services Provider should get added as part of the upgrade:

http://msdn2.microsoft.com/en-us/library/ms170417(SQL.90).aspx

>>

What's New in SQL Server 2005 > Reporting Services Enhancements

Reporting Services Design-Time Enhancements

New Analysis Services Query Designer

Report Designer includes a new query designer for creating MDX queries. You can use the integrated query designer for Analysis Services to build queries by dragging and dropping server metadata onto a report layout and previewing the results.

>>

Reporting on a Cube -- Time Dimension Issue

Hello,

I have an Analysis Services Cube that I would like to report on. However, the Time Dimension currently only has four columns, Day of Month, Month(name) , Year, and DateKey (DateTime representation at midnight for every day). Thus when I drag the month attribute onto the report, it is sorted April - August - December - etc. instead of Jan - Feb - Mar. How do I fix this? I remember reading something in the MSDN Library about it but I can't find it again now.

ThomasJust remove sorting for Time group in Report. It's well known problem.|||Hmm, I haven't added any sorting, this is just drag-and-drop from the list of dimensions in the "Report Builder" tool for Reporting Services and in Visual Studio. So I haven't added any sorting, however in VS I can modify the User Sort - Value property. Unfortunately if I remove it or set it to another field (my date key field) VS crashes when I try to preview. Any other suggestions?|||The Month attribute KeyColumns should be defined as a composite key consisting of the Year and Month column. This should get sorting right because if no Sort attribute is specified UDM will sort by the key. Take a look at how the Month attribute is defined in the AW sample cube.|||Thanks a bunch, but here's a funny story. I actually figured that out and was coming here to post the answer in case any one else needed it. Seems like you beat me to the punch. Thanks again anyways!