Reporting Services + Analysis Services Data Source
May 15, 2007
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
View 3 Replies
ADVERTISEMENT
Apr 24, 2015
I am trying to configure the reporting for TFS using SQL Server. But I get following error when viewing any report:
So I try to manually process the cube to check if it works. I am following this article: [URL] ....
When I click on GetProcessingStatus and invoke it (with last field set as TRUE) I get following error:
How to resolve this issue and be able to see the reports.
View 5 Replies
View Related
Dec 10, 2007
My requirement for the parameter is multivalue parameter with a text box. for example when user enters aa15 it need to include product aa15. when the user enters aa15, aa16, zz15 than it needs to include all the three products. the last case is when the user enters AA** than i need to inclued all the products start with AA. when i use default multivalue parameter with data source analytical services than i am getting a drop down box. I dont want that. I need a text box where user can enter the value.
1. In sql we have a like key word to query . for example select * from product where product like "AA%".
what is equavalent mdx query to get such results ?
2.How to impliment the multivalue parameters without using dropdown box?
View 1 Replies
View Related
Aug 28, 2006
Hello everybody.
I want to perform a Analysis Services Processing Task using a connection manager created from a Analysis Services data source. I create the data source using the Oledb provider for Analysis Services 9.0, and it works fine. Then I create a connection manager using the "New connection from data source" option, and it seems to create it ok.
The problem is that I can't then asign this connection to the Analysis Services processing task. I get the error:
"Connection Manager MyConectionManagerFromDataSource is not a Analysis Services Connection Manager"
However, if I create directly a connection manager using "New Analysis Services Connection" it works fine.
Is it at all possible to create a Analysis services connection from a Data Source to perform a Analysis Services Processing Task?
Thanks in advance,
View 4 Replies
View Related
Apr 10, 2006
Trying to go through the Analysis Services tuturial. Logged in as Administrator on a 64 bit W2K server. SQL Server 2000 AS and SP4.As I come to the Design Storage step I get a message sayingData source provider error: ; Time:2006-04-10 19:51:47There are no further details given and the Event Logger has nothing.Grateful for any help.Screenshot: http://i23.photobucket.com/albums/b366/biund/sql/DesignStorageproblem.pngAnd the version Screenshot:http://i23.photobucket.com/albums/b366/biund/sql/DesignStorageproblem2.png
View 7 Replies
View Related
Feb 21, 2007
Hello,
Our company is doing some basic research into using analysis services as a solution for our customers. I've taken our database and built an analysis services project in BI Studio and deployed it to Analysis Services on my local machine I connect to Reporting Services in Management Studio, create a folder for the cube, give my Windows user acct. Content Manager permission, create the Data Source (conn string = "Provider=SQLNCLI.1;Data Source=(local)SQLServer2k5;Integrated Security=SSPI;Initial Catalog=STI-PDB" -- copied from the Analysis Services project in BI Studio), and go to "Generate Model". What I get is an rsModelGenerationError, sub [Create Perspective From Cubes] with a connection issue at the base:
Either the user, <computername>Thomas, does not have access to the STI-PDB Database, or the database does not exist. () (Microsoft SQL Server 2005 Analysis Services)
Now, in the Database Engine, my Windows account has the db_owner database role for STI-PDB and it's default schema is dbo, which all the objects are created under. What am I missing here?
View 4 Replies
View Related
Jun 7, 2007
We have been a Crystal shop for ages; we are currently doing a proof-of-concept for a conversion to MS Reporting Services. As such, we are developing some Analysis Services 2005 cubes to drive some new SSRS reports, which our users will access through Report Manager. Unfortunately, we are all MDX noobs here, so we are making heavy use of the Wizards until we can come up to speed.
The problem we are running into is when we develop a report with Date Parameters. When we deploy this report, the date parameter box is a dropdown box instead of a date picker. I've seen a couple of other posts on this topic, but when I try to apply the fixes mentioned in them, I throw errors.
I have two quick questions:
Why does this happen? Is it a limitation in the MDX language, in SSAS, or SSRS? Are there any planned fixes?
Can someone please show me how to fix this on my actual query string for one of our basic reports? I've highlighted the date parameters.
Code Snippet
SELECT NON EMPTY { [Measures].[Lead] } ON COLUMNS, NON EMPTY { ([Store].[Store ID].[Store ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@StoreStoreID, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOMEMBER(@FromLeadCreationDateCalendarDate, CONSTRAINED) : STRTOMEMBER(@ToLeadCreationDateCalendarDate, CONSTRAINED) ) ON COLUMNS FROM [Referral Leads]))) WHERE ( IIF( STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED).Count = 1, STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED), [Lead Source Type].[Lead Source Type].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
I'm afraid, given my user community, that if I can't get the date picker to work properly, it could be a deal breaker.
Thanks very much in advance for your help.
Regards,
Steve
View 7 Replies
View Related
Jan 8, 2008
Hi ,
I am using sql server 2005 standard edition and i have installed MSXML core services 6 and also ADOMD.net for analysis services.
i am using visual studio 2005 version of 8.0.5072742 and the problem that i am facing is when i use to develop reports using Analysis services as a data source , the error message pops up and VS 2005 gets crashed.
i could define the dataset , layout part and when i prees the preview , it gets crashed.
could any one help me on this problem to sort out.
Regards
RMP
View 1 Replies
View Related
Dec 3, 2006
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
View 8 Replies
View Related
Sep 16, 2007
Hi All,
I'm trying to create reports in RS2005 using AS2000 as my data source. I understand that if I use RS2005 on AS2000, I wont be able to enjoy the OLAP based parameters as in using AS2005. Does anyone know an easy way to easily use Parameters in RS2005 while still using AS2000?
Regards,
Joseph
View 1 Replies
View Related
Jul 26, 2007
Hi,
I'm trying to learn about analysis, integration and reporting services. I have install SQL server 2005 management Studio Express. but I cant find these in the Start menu as mentioned in the tutorial Click Start > SQL Server 2005 > Business Intelligence Development Studio.(for reporting services).
what do I need to do? Please help me.
Regards.
View 1 Replies
View Related
Jul 30, 2007
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
View 3 Replies
View Related
May 19, 2015
I want to run SSRS report directly by connecting Excel as a data source. The report runs properly from SSDT, but when it is run from Report manager, some users cannot access the Excel connection and the report execution fails. I am using SSRS 2012 and Excel 2010.I have created the ODBC data connection in SSRS and when I use 'Windows Integrated Security' option for credentials, the connection gets successful. But the problem is that all users does not have permissions to access the Excel inline, but they should get the report working.How can I store the credentials to access the Excel in SSRS with a specific username and password?
View 4 Replies
View Related
Apr 3, 2008
I have set up a report on reporting services and deployed it on a server. (server A)
The data comes from 2 databases - one on Server A and one on server B
I can view the reports when logged into Server A, but if I try to view them from a networked PC I get the following message:
An error has occurred during report processing.
Cannot create a connection to data source 'byvshs01'.
For more information about this error navigate to the report server on the local server machine, or enable remote errors
Byvshs01 is Server B.
However, if I remove the data from server B from the report, I can view it ok. So it seems you can view data from a DB located on the server that hosts the reports, but not if the DB is located elsewhere.
Is there any way around this?
View 6 Replies
View Related
Apr 21, 2015
An error has occurred during report processing. (rsProcessingAborted)...The execution failed for the shared data set 'DataSet1'. (rsDataSet ExecutionError )...Cannot create a connection to data source ' Data source for shared dataset'. (rsErrorOpeningConnection)...the above error message I get on the published report, but everything works fine when I do a preview in Visual Studio.How can I see exactely what is wrong?
View 22 Replies
View Related
Nov 5, 2015
When Datazen caches a data source, it sometimes says that the data was truncated. What factors are involved in the truncation and what I might be able to do to expand the size of cached elements to avoid truncation? My only current solution is to make the data source "Real Time" rather than cached.
View 3 Replies
View Related
Jan 27, 2004
Hi
What is the diff between the above 2?
Thanks.
View 1 Replies
View Related
Jan 24, 2007
Setup: Reporting Services and Analysis Services are on the same server. Active Directory User. Default configuration of Reporting Services.
Problem: Reporting Server does not apply Analysis Services Role after login.
Details:
I have created a report that retreives data from the Analysis Service. I created a role and added my user company/myUserName to the role.
On the Reporting Server I set the data source to :
* Credentials supplied by the user running the report
and
Use as Windows credentials when connecting to the data source
Internally when I connect devsql2005/Reports/ReportName I get the user and password fields. When I fill them in I get the report but I can see all the data while my role was restricted to see only few results.
Can anyone suggest what may be wrong? Any articles or tutorials on how to setup and deploy solutions where Report Server is talking to Analysis Services and NOT Sql Server.
Thank You,
Pavel Hlobil
View 3 Replies
View Related
Jul 10, 2015
I have 4 Tablix and 2 of the Tablix get data from Server 1 and other 2 get the data from Server 2.I have set NoRowsMessage "=Data Not Available for the Selected Values" for all the 4 Tablix.Now if data is not available from Server 1 then I must show "Data Not Available for the Selected Values" only once in the outputbut now its appearing twice in the output because of the 2 tablix that had no rows.Similarly if data not available from Server 2 then it should show "Data Not Available for the Selected Values" only once in my output.If Data not avilable from all the Tablix then also i t should show only once as "Data Not Available for the Selected Values" in the report output.
View 3 Replies
View Related
Dec 19, 2006
I have data on server A and the report server on server B.
I have created reports that I can run through report manager (on server B).
I have depolyed the reports but when I try to run the reports on client computers I get the following error:
An error has occurred during report processing. (rsProcessingAborted) Get Online Help
Cannot create a connection to data source 'ABCD'. (rsErrorOpeningConnection) Get Online Help
SQL Server does not exist or access denied.
I have tried setting different credentials ... windows security, specified username and password to credentials not required ...
All give above error.
TIA
View 4 Replies
View Related
Aug 2, 2011
Is it possible to configure a datasource using data from a web service (java) requiring basic http authentication?
View 4 Replies
View Related
Jun 16, 2015
i have to to combine two dataset in single matrix my sample scenario in below attached for reference.
in this first matrix values from one server and second one is another one server.
how to connect these to for getting desired result of thrid matirx.
in this 1,2,3,4,5,6 is in column grouping
how to solve this in using LOOKUP or CUSTOM CODE
View 9 Replies
View Related
Apr 28, 2015
I have a question about ssrs security. In report manager I have set a list of users as browsers, and the builtin administrators are content manager in the parent folder where reports are. A user that is not an administrator and neither a user I added, has access to reports.
I'm thinking this person is having access because the data source of the reports use a administrator account to connect to the analysis services using the option Credentials stored securely in the report server with the 2 checkboxes marked (use as windows credentials when connecting to the data source and impersonate the authenticated user).
I simply marked this option because SSRS is in 1 server and SSAS in other and I think kerberos need to be configured and I haven't looked into it, but I think the report access is separated from the data source.
View 2 Replies
View Related
Jul 16, 2009
I am building a webapp that calls an SSRS instance to display a report based on another servers web service.I can make a call from the ssrs server using this in the RDL:
<Query>
<Method Namespace="http://machine/webservices" Name="HelloUser">
<Parameters>
<Parameter Name="user">
<DefaultValue>Craig</DefaultValue>
</Parameter>
</Parameters>
</Method>
<ElementPath IgnoreNamespaces="true">*</ElementPath>
</Query>
This works no problem. but.. I want to have the user parameter "Craig" be a parameter passed in from the web app. If it were a regular SQL data source you would put @user in the query. How do you do it with XML text queries?
View 5 Replies
View Related
Nov 29, 2010
I am attempting to pass report parameters to my query. My report's data source is a WCF web service. I can run normal queries fine, but I have found that without the know-how to pass parameters to my query I am limited with my capabilities.
Assume a string parameter named "Name" with a specified default value of "Jmachol90", how would I pass that into the following query at the designated place:
<Query>
<Method Namespace="http://schemas.microsoft.com/sqlserver/masterdataservices/2009/09" Name="SecurityPrincipalsGetRequest">
<Parameters>
<Parameter Name="Criteria" Type="XML">
<DefaultValue>
[code]....
View 17 Replies
View Related
Jun 15, 2015
I am looking command (cmd/powershell/c#) for setting/updating credential for SSRS data source.
View 2 Replies
View Related
Nov 8, 2015
As the title states I have a 64bit DSN but when I try to create ODBC datasource in Reporting Services it does not appear in the dropdown box. I have created as a system DSN.
SQLServer 2008R2
Reporting Services 2008
View 2 Replies
View Related
Oct 30, 2007
First off, let me just say that I'm a complete newbie to SQL Reporting Services, and .NET in general. We have a VB 6 application that is trying to launch an SRS 2005 report in a viewer window. This was accomplished by creating A VB.NET "wrapper" window that launches the report, and allows the report to be previewed, exported, etc. I did not write any of this.
The report is using a Shared Data Source, which points to a specific database. My problem is that the users can select which db they want when they launch the VB 6 Application, so I need to find a way to edit the connection string in the report to specify which database to use on the fly. I have the db name in the "wrapper" application, but I can't figure out how to pass it to the report.
thx,
Paul
View 3 Replies
View Related
Aug 25, 2015
I have a SSRS 2012 report which references a custom c# assembly. This report exists in multiple environments (alpha, beta and production ) which are each associated with different data sources.
Is there a way for the assembly to determine the datasource used by the calling report so it can also connect to it?
View 2 Replies
View Related
Oct 9, 2008
I have published SSRS reports to sharepoint and used the Reporting Services Report viewer to present the reports on a Page. Everything works fine for the day, however in the morning, when I go to view the page, I get an error message
The report server cannot process the report. The data source connection information has been deleted. (rsInvalidDataSourceReference)
View 9 Replies
View Related
Jun 2, 2015
I want to create a datasource but the credentials I want to use will be based on the user running the report rater than hard coded.
I have tried using Windows Integrated Security but all i can is cant log in as NTAnonymous User?
View 8 Replies
View Related
Oct 12, 2015
how to trigger report's refresh following its underlying SSAS cubes process?
I want to keep reports data updated at all times, especially when this happens while user is already browsing the report..
I don't want to set an auto refresh for the report every 5 minutes as my cube is processed only one time during the day...
View 8 Replies
View Related
Jul 14, 2015
How to set default values for selection list navigator without using parametrized queries for data source view.
View 2 Replies
View Related