am doing proof-of-concept for reporting services reports getting data via web services.
got it working for simple examples (passing back strings etc)
also woking passing back an object
but cant get it working with a dataset - am having trouble filtering out the XML resultset
this is my webmethod
[WebMethod]
public DataSet DSetExample()
{
DataSet ds = new DataSet("XMLTest");
DataTable dt = new DataTable();
DataColumn dc;
dc = new DataColumn("IntegerColumn1", System.Type.GetType("System.Int32"));
dt.Columns.Add(dc);
dc = new DataColumn("StringColumn1", System.Type.GetType("System.String"));
dt.Columns.Add(dc);
dc = new DataColumn("DecimalColumn1", System.Type.GetType("System.Decimal"));
dt.Columns.Add(dc);
dc = new DataColumn("DateTimeColumn1", System.Type.GetType("System.DateTime"));
dt.Columns.Add(dc);
DataRow drow = dt.NewRow();
drow["IntegerColumn1"] = 1;
drow["StringColumn1"] = "WTF";
drow["DecimalColumn1"] = 205.299;
drow["DateTimeColumn1"] = DateTime.Parse("22/May/1966");
dt.Rows.Add(drow);
ds.Tables.Add(dt);
return ds;
}
this is my basic query
<Query>
<ElementPath IgnoreNamespaces="true">*
</ElementPath>
<Method Name="DSetExample" Namespace="http://localhost/testservice1">
</Method>
</Query>
this just gives me the first node details
but when ever i try and specify the elements i want by using the ElementPath
nothing comes back....
just need to know how to specfiy the ELEMENTPATH to return the dataset details...
I was wondering if it's possible to use a sharepoint list as datasource in reporting services ? If yes, what's your trick ? Is it possible to use a directory as datasource in reporting services ?
I am new to both Reporting Services and Webservices implementation so I am just trying to use a simple example. I found a Webservice on the internet that returns movie information:
I've created a SSIS package with a DataReaderDestination and a SSRS report that points to it.
I referenced these links during development and I have everything working as expected locally. **I changed the config files as stated on both my local machine and our server. My config matches the example exactly.
When I attempt to deploy my report project to the server I get this error message: "An attempt has been made to use a data extension 'SSIS' that is not registered for this report server."
In the report manager data source properties page it reads: "The data processing extension used for this report is not available. It has either been uninstalled, or it is not configured correctly."
In SQL Server Management Studio data source properties page SSIS is not a data source type option.
I've tried restarting both the Reporting Services and Integration Services on the server to no avail.
Any suggests? My problem is I can't get the SSIS to be recognized source on the server.
Data Source of the report is AS/400 and I need to create a report in visual studio 2013 with ODBC Connection type and deploy report onto share point 2013. How to create report, data source and deploy on share point.
My problem is how we will create ODBC connection with AS/400 and connect report with AS/400.
A customer use SCSM 2012 RTM and recently they changed the password of the account that connects with the datasources that use Service Manager to generate reports. There are several datasources namely DWStagingAndConfig, ConfigurationManager, DWDataMart and other ones.The process to change the password of the account, done via web in the SSRS URL instance of Service Manager, requires to enter in the Manage option of each DataSource and replace the password with the right one in the Properties section; is a really simple step. However, after the change, the only Datasource that not allows to access again to the Manage option is DWStagingAndConfig (appears a message that points out to a report server internal error);I append a extract of the SSRS log related to the problem with this Datasource.
library!ReportServer_0-109!3024!01/22/2014-14:54:14:: Call to GetPermissionsAction(/SystemCenter/ServiceManager/DWStagingAndConfig). library!ReportServer_0-109!8820!01/22/2014-14:54:14:: Call to GetSystemPermissionsAction(). library!ReportServer_0-109!8820!01/22/2014-14:54:14:: Call to GetSystemPropertiesAction(). library!ReportServer_0-109!8ab8!01/22/2014-14:54:15:: Call to GetDataSourceContentsAction(/SystemCenter/ServiceManager/DWStagingAndConfig). library!ReportServer_0-109!8ab8!01/22/2014-14:54:15:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
When creating a report using reporting Services with a cube as a datasource, visual studio 2005 hangs. I have applied SP1 to visual studio.net 2005 .
This is the query generated by query designer in reporting services. When I try to drag and drop another dimension to slice it by , visual studio hangs.
The fact table has just 10,000 records. Are the number of dimensions used to slice the cube too many? How do I optimize this query?
SELECT NON EMPTY { [Measures].[Avg MT Rate - Speech], [Measures].[Change in Avg MT Rate], [Measures].[Edited Lines Per 1000], [Measures].[Speech Utilization], [Measures].[Time Saved %], [Measures].[Speech Edited for Rev 1], [Measures].[Change in Account Productivity], [Measures].[Hours Saved], [Measures].[Lines Per 1000], [Measures].[Average MT Rate - Total Production], [Measures].[Typed Lines], [Measures].[Productivity Time in hours - Edited Docs],[Measures].[Productivity Time in Hours - Typed Docs],[Measures].[Productivity Time in Hours]} ON COLUMNS, NON EMPTY { ([Dim Customer].[Customer Name].[Customer Name].ALLMEMBERS * [Dim Customer].[Dictator Site Name].[Dictator Site Name].ALLMEMBERS * [Dim Date].[The Month].[The Month].ALLMEMBERS * [Dim Date].[The Year].[The Year].ALLMEMBERS * [Dim MT].[Creator Last Name].[Creator Last Name].ALLMEMBERS * [Dim MT].[Creator First Name].[Creator First Name].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@FromDimDateTheYear, CONSTRAINED) : STRTOMEMBER(@ToDimDateTheYear, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOMEMBER(@FromDimDateTheMonth, CONSTRAINED) : STRTOMEMBER(@ToDimDateTheMonth, CONSTRAINED) ) ON COLUMNS FROM [ETL DW])) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
I am new to SQL Reporting services and learning from website tutorials. I could easily connect to Adventure Works database and could create some reports. Now I want to create one report from Oracle database in my network on one of my server (Network computer). I could not connect to database as do not know what to write (is path like Server name or IP address 152.168.1.25 Oracle database name) is required in Server name field? Server Type should be OLE DB or Oracle and OLEDB Provider should be Microsoft OLE DB Provider for Oracle? Then Server of filename? Is there any tutorial or material available, which can help me in establishing this connectivity? Any help is greatly appreciated.Do I have to install any thing on my local computer for oracle ( as read in one of forum for Oracle Client or something like that)
I am trying to migrate my reports from SQL server 2000 reporting services 32bit to 2005 64bit. I am following the migration steps that MS specified. Restored my Reportserver and ReportserverTempDB databases Then I was using the configure Report services to upgrade these databases but I always end up getting the follwoing exception when I run the upgrade on the "Database Setup" configuration for 'ReportServerTempDB' database System.Data.SqlClient.SqlException: Could not locate entry in sysdatabases for database 'ReportServerTempDBTempDB'. No entry found with that name. Make sure that the name is entered correctly. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Common.DBUtils.ApplyScript(String connectionString, String script) at ReportServicesConfigUI.SqlClientTools.SqlTools.ApplyScript(String connectionString, String script)
It's driving me crazy, why is it looking for 'ReportServerTempDBTempDB' in the catalog instead of 'ReportServerTempDB'? Is it possible to migrate from 32bit to 64bit?
created an Oracle datasource (tried both ole db & oracle client) in Visual Studio 2005 report project - works fine connecting and preview of report within ide , deployed to Reporting Services web server, received the following error when attempting to run the report on web server:
An error has occurred during report processing. (rsProcessingAborted) Cannot create a connection to data source 'oracle'. (rsErrorOpeningConnection) System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.
web server has oracle client 9i client installed and connecting to db ok through sql plus (this server also is loaded with VS2005 and the SqlServer2005 db)
I'm just starting to use SQL Server 2005 Reporting services, and need to use an XMLdocument as the datasource. I will get the xml data from a webservice and then need to bind it to the report. Here is what I am doing:
1. I created a webservice at the following location: http://localhost/myWebDirectory/myService.asmx. this webservice has a method called "GetXMLDocument" that has 1 parameter (sql). This method uses the sql statement to fill a dataset from a SQL Server database, converts it to an xmlDocument, and returns the xmlDocument. Here is a sample of the results:
<?xml version="1.0" encoding="utf-8" ?> - <NewDataSet> - <Table> <ReportName>GirlsNamedMary</ReportName> <ReportFriendlyName>All Girls Named Mary</ReportFriendlyName> <ReportDescription>This is a test SQL Server Reporting Services report. It has no parameters.</ReportDescription> </Table> </NewDataSet>
2. I created a datasource in my reports project as follows: - Name: TestDataSource - Type: XML - Connection string: http://localhost/myWebDirectory/myService.asmx
3. I created the following dataset: - Name: TestDataSet - Data source: TestDataSource - Query string: <Query xmlns="http://tempuri.org/Service/GetXMLDocument"> <Method Namespace="http://tempuri.org/Service" Name="GetXMLDocument"/> <ElementPath /> </Query>
I am getting this error: ------------------------------------------------------------------------------------ An error occurred while executing the query. Failed to execute web request for the specified URL.
Additional Information: ----> Failed to execute web request for the specified URL. (Microsoft.ReportingServices.DataExtensions). ------------------------------------------------------------------------------------
I cannot figure out why I have this error. The service runs fine from the browser, but I just can't get it to work in Reporting Services. What am I doing wrong?
How must I change the mdx that is generated for the available values for a user parameter in order to get the content sorted?
Regards,
Henk
BTW the exact mdx query is given below (and the label field of the parameter is set to 'ParameterCaption'), but I would already appreciate an example of a simple mdx.
MEMBER [Measures].[ParameterCaption] AS '[Organisatie].[Level 5 naam].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[Nummer en Naam] AS '[Organisatie].[Kosten Nummer].CURRENTMEMBER.MEMBER_CAPTION +": "+ [Organisatie].[Level 5 naam].CURRENTMEMBER.MEMBER_CAPTION'
--MEMBER [Measures].[ParameterValue] AS '[Organisatie].[Level 5 naam].CURRENTMEMBER.UNIQUENAME'
MEMBER [Measures].[ParameterLevel] AS '[Organisatie].[Level 5 naam].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT {[Measures].[ParameterCaption],
[Measures].[Nummer en Naam]
, [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS
, filter ([Organisatie].[Level 5 naam].MEMBERS,[Measures]) ON 1 ,
[Organisatie].[Kosten Nummer] on 2
FROM ( SELECT ( STRTOSET(@OrganisatieLevelnaam, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@KalenderFactuurPeriode, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@KalenderFactuurJaarNummerLang, CONSTRAINED) ) ON COLUMNS FROM [FMR DWH Afgenomen Dienst])))
Can somebody on the SSRS product team double check me that I've written that up right? That seems like a pretty important change and I can't see it mentioned in BOL anywhere.
I'm attempting to obtain a cost effective solution for my existing customers to develop reports on their SQL 2000 Server installations using their Reporting Services 2000. With products like Visual Basic.NET 2003 becoming almost impossible to obtain, I have at least one customer who is running into a dead end.
One option possibly is the SQL Express with Advanced Services download, which has Reporting Services. My questions are as follows:
Can the report designer component of SQL Express Reporting Services be configured to connect to an external database (which would happen to be a SQL 2000 database) to establish its datasets? Does the resultant designed report end up in an RDL file? If the customer published this report via the Reporting Services 2000 Report Manager, would the report be able to run?
Sorry for asking a question like this that I could probably answer on my own, but my customer needed this answer yesterday. I have scoured the web and microsoft sites - and posted a question on the official SQL Reporting Services cateogy ... in an attempt to answer the basic question of how to design reports for Reporting Services 2000 in the absence of Visual Basic.NET 2003 (or other .NET 2003 tools) with no success.
Hello,Can I import an OLTP (Reltional DB) as a Data Source into SQL ServerAnalysis Services 2005 and then use the Cube Wizard and the new DataSource View feature to create the OLAP model ?Or do I have to first design an OLAP Data Warehouse with a Star Schemaand then import this DW as a Data Source into my Analysis ServicesProject.With SQL Server 2000 , OLAP would be the way to go..but with SQLServer 2005 , it seems as though the wizard and data source viewfeatures do half the work for you.I have an OLTP DB and am not sure which route I should take ! Anysuggestions / input would be much appreciated.Thanks in Advance...RegardsRusszee
I work in a big project and we will begin in using reporting services as the base technique for reports and I will be responsible for this part. but I have a problem I will discuss in the following:
Currently: We use currently devexpress reports and we have 2 languages(Arabic and English). the data in tables saved in two ways (Arabic and English). when the end user change the language of the web site the report data language changed when run it.
Example:
we have table with (ID, NAME_AR, NAME_EN, JobTitle_AR, JobTitle_EN). designed report will display(ID, NAME_EN, JobTitle_EN) . but the end user change the language of the system the report will
Our DBA has installed reporting services on a server and now in order to access the report manager, one has to be an Admin on that Server. I am guessing that there is a mistake in the configuration of Reporting Services. Usually it should allow anybody who was added to the roles in the properties section of the Report Manager, right? I have also added the users to the DB..
Also I am using Windows Authentication to access Report Catalog items (Reporting Services is installed on Server2) from a web Application(deployed on Server1) and displaying the report using report viewer. For some reason, server1 has to be in an Admin role on Server2 to access the report catalog/report. This is kinda strange for me as I don't want everybody to be an Admin on Server2. Can anybody please point in the right direction?
We have installed SQL Server 2005 with reporting services.When tried to open getting errors as below: how to avoid such errors See the end of this message for details on invoking just-in-time (JIT) debugging instead of this dialog box.
ReportServicesConfigUI.WMIProvider.WMIProviderException: A WMI error has occurred and no additional error information is available. ---> System.Runtime.InteropServices.COMException (0x8000000A) at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)
I have some problem about reporting service add-in.
After I install reporting service add-in for SharePoint, reporting service menu does not appear in Application Management Tab in SharePoint Central Administration.
I try to uninstall and re-install again, it remain not work.
We've got a requirement to build the real time report. user can browse report at any point of time and need to see the latest data(stock market) in the report.
I've few options down...
1. Directly point to OLTP database as source and write stored procedure to show result set.
2. Replicate the database and write the SP's to reports. To avoid pointing directly to OLTP db.
3. To build the datawarehouse with dim & facts to show it in reports. I prefer this as a standard method, but this would have some latency depending on trasaction load which will differ from the requirement.
We are required to gather certain information regarding reporting services.
Which reports were requested, by whom , how long did they run for, what parameters were passed etc etc.
I see that the system tables do provide some of this information, is there a 3rd party tool or something we can use to gather all this type of information for us?
Hi, I have just install SQL 2005 SP2 and trying to get Window SharePoint Services V3 integrated with SQL 2005 SP2 reporting services. In SharePoint Central Administration, I select the Reporting Services Integration page and have setup the Report Server Web Service URL and Authentication Mode. I then goto Grant database access, specify the SQL server name, get promted for a username and password that has access SQL Reportserver and get the following error "The group name could not be found" Does anyone have any ideas? Thanks
We built our prod server [vm] with SQL Server 2008 R2 on Server 2008 R2. It works nicely. Then we made a copy of the VM and renamed it (so our test environment would be IDENTICAL to production). After that, SSRS was broken: I get "HTTP Error 503. The service is unavailable." I can't connect via http, or SSMS.
We have tried:
* Running SSRS config tool (several times)
* Running with/without the IIS Server Role
* Dropping & recreating the SSRS keys
* Recreating the ReportServer database, etc
* Checking all of the accounts, permissions, etc
* Running SQL Repair
* Going through the registry to fix any references to the machine's old name
* Uninstalling, reinstalling SSRS
* Completely uninstalling ALL of the parts of SQL Server 2008 R2, deleting all directories, removing references to SQL Server from the registry, rebooting, reinstalling everything.
None of this has worked. SSRS [R2] is still 503 on our test box.
the SSRS [NT] service seems to run, without error. The Event Viewer doesn't seem to be recording any errors. The SSRS logs say that the default URL is wrong, but we get the same error in Prod, and Prod works fine. The other SQL Logs say something about not being able to contact the service. However, as I said, the [NT] service seems to be working fine.
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]
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?
I'm using Reporting Services 2005 SP1 and wrote some code to render reports server-side. My sample report has few parameters that must be passed so I pass these parameters with code.
byte[] report = rs.Render("PDF", "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>", out extension, out mimeType, out encoding, out warnings, out streamID); </My code>
Actually, I have several more parameters but I've checked the collection and all the parameters I want are there and have values. Their names match those found in executionInfo.Parameters. But when I run my code I get the following error on Render():
<Error> "This report requires a default or user-defined value for the report parameter 'pClass'. To run or subscribe to this report, you must provide a parameter value. ---> This report requires a default or user-defined value for the report parameter 'pClass'. To run or subscribe to this report, you must provide a parameter value. ---> This report requires a default or user-defined value for the report parameter 'pClass'. To run or subscribe to this report, you must provide a parameter value." </Error>
I've double-checked and my parameter array does have the problematic parameter, I passed it a value but when I check executionInfo.Parameters I see that it's the only parameter that hasn't been give a default value after calling SetExecutionParameters(). So my parameters do seem to be passed but one seems to refuse getting a value. If I comment out the SetExecutionParameters() line I get, as expected a similar error but on another parameter (first one in the executionInfo.Parameters collection). So I'm left to beleive that the SetExecutionParameters() has some kind of bug as it works for all but one parameter. I've checked casing, spelling, tried passing phony parameters and from what I've seen I should be getting an error when I pass the parameters if I got something wrong.
I am invoking RS web services to render reports, using Apache Axis to generate stub classes from Reporting Service WSDL.
Please let me know if I can integrate Report Viewer control in the jsp where I am writing the report output. Else do I have to create my own custom tags simulating ReportViewer functionality.
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?
I am facing sever Refresh issue in PPS Reports. I have Two Dashboard
Dashboard_One and Dashboard_Two
I have few Filters on both the Dashboard .
In Dashboard_One I have 2 Filters
1)Year filter where Year 2012 is my Default value 2)City Filter where "CityOne" is Default Filter Value
If I select Year"2010" in Period Filter and "CityTwo" In City Filter.I see Related reports . Now I navigate to Dashboard_Two to See Other Reports where I have few Other Filter Like "Country" where I select "CountryThree" . When I navigate Back to Dashboard_one I do not see Dashboard with Default value given to them
I still see Filter value Year=2010 and CountryFilter="CountryTwo" in Dashboard Dashboard_One .. where as I should have seen it based on the Default value given to the Filter.. How should I resolve this refresh issue which I am facing in PPS Dashboard. I do not see Default value in the Filter ,It always give the filter value which was selected later when Navigated back.
I started my Reporting Server and if I use the Internet Explorer as a Administrator everything works fine.But if I don't open it as an admin it calls "rsAccess Denied The user [...] does not have the permission...".Problem is that I want to run it in an SAP Program with an HTML Viewer. how I can get those Permissions ?By the way somehow I'm not able to create or change roles in the Microsoft SQL Server Management Studio.