report

SQL Server Reporting Services

Index ‹ SQL Server

- Previous
  - 1
    - calling a procedure from another procedure Maybe this isn't possible, but I'm trying to figure out how to call a procedure in a select statement where I can pass a new id each record found. Here is the procedure code that returns the desired results for any given ID: CREATE PROCEDURE proc_amEnrollmentTrackingDetailGet_cst @syStudentID INTEGER AS DECLARE @SATACT DATETIME, @HSTranscript DATETIME, @AcceptLetter DATETIME, @FAFSA DATETIME, @FinAidApp DATETIME --Get the last DateDue for each document type SELECT @SATACT = CASE WHEN cmDocTypeID IN (81,110) THEN DateDue END, @HSTranscript = CASE WHEN cmDocTypeID IN (83) THEN DateDue END, @AcceptLetter = CASE WHEN cmDocTypeID IN (126) THEN DateDue END, @FAFSA = CASE WHEN cmDocTypeID IN (150) THEN DateDue END, @FinAidApp = CASE WHEN cmDocTypeID IN (155) THEN DateDue END FROM cmDocument (NOLOCK) WHERE syStudentID = @syStudentID ORDER BY cmDocTypeID, DateDue -- Return the results SELECT S.syStudentID, S.StuNum, @SATACT AS SATACT, @HSTranscript AS HSTranscript, @AcceptLetter AS AcceptLetter, @FAFSA AS FAFSA, @FinAidApp AS FinAidApp, S.FirstName, S.LastName FROM syStudent S (NOLOCK) WHERE S.syStudentID = @syStudentID GO Now maybe there is a better way to do this, but what I want to do is to call the above procedure and have it return a row for every ID in the following code: SELECT DISTINCT S.syStudentID FROM syStudent S (NOLOCK) JOIN cmActivity_vw A (NOLOCK) ON A.syStudentID = S.syStudentID AND A.cmTemplateID IN (16,195,283,290,291) --Application received WHERE S.StartDate BETWEEN '5/1/07' AND '9/1/07' -- Expected Start Date AND S.amPrevEducID = 1 --High School Grad AND S.syCampusID = 5 --Main Campus Tag: report
  - 2
    - SQL DB Cleanup We have SQL SERVER 2000 Runnin on our server. We are trying to do a db cleanup, ie all tbls/views that were created earlier and are not needed any more, need to be deleted. However, is there a way, to do a cleanup in a better method, other than going thru the whole bunch of tbls/views manually,and determining which ones are needed or not and delete the ones that are not needed. Tag: report
  - 3
    - Slowly Changing Dimension index usage We're using slowly changing dimensions to control a number of data tables in our system. Each table has five or six business keys, but the indexes of the tables are built so they're as efficient as possible (i.e. the fields with the highest diversity are listed first). How does the SCD wizard determine the order of the business key fields Is there a way I can view or manipulate the statement the SCD task is using to make sure either (a) the indexes match the statement, or (b) the statement matches the indexes Tag: report
  - 4
    - Add prior period data to matrix in MDX I have the following MDX that returns a balance per product type on the date the user selects from a parameter SELECT NON EMPTY { [Measures].[EOD Book Balance] } ON COLUMNS , NON EMPTY { ([Book Balance Type].[Balance Type].[Balance Type]. ALLMEMBERS * [Products].[ProductID].[Product ID]. ALLMEMBERS * [Accounts].[Acct Rev Group ID].[Acct Rev Group ID]. ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION , MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET (@TimeSimpleDate, CONSTRAINED ) ) ON COLUMNS FROM [DailyBalances]) WHERE ( IIF ( STRTOSET (@TimeSimpleDate, CONSTRAINED ). Count = 1, STRTOSET (@TimeSimpleDate, CONSTRAINED ), [Time].[Simple Date]. currentmember ) ) CELL PROPERTIES VALUE , BACK_COLOR , FORE_COLOR , FORMATTED_VALUE , FORMAT_STRING , FONT_NAME , FONT_SIZE , FONT_FLAGS This returns something like Product | Balance How would I got about adding what the balance was 1 month prior to the selected date I can't seem to get my head around the PARALLELPERIOD function, although I figured that's what I may have to use to get this data to get something like Product | Balance | Last Month Balance Tag: report
  - 5
    - Project 2007 Server Cube Build Fails Connecting To Another SQL 2005 Analysis Server Have spent days on this. Followed install guides and troubleshooting thouroughly. Here are the error messages I get when trying to build a cube. THE PROJECT SERVER ERROR WHEN BUILDING THE CUBE: ===== Initiating cube build process ===== [8/21/2007 12:45 PM] Cube build request message has been added to the Project Server queue ===== Verifying and running pre-build server event handler ===== [8/21/2007 12:45 PM] Verifying and running pre-build server event handler ===== Determining database and cube structure ===== [8/21/2007 12:45 PM] Cube build initialization started [8/21/2007 12:45 PM] OLAP database and cube structure was determined successfully ===== Building database and cubes ===== [8/21/2007 12:45 PM] Cube build session started [8/21/2007 12:45 PM] Analysis Services session started [8/21/2007 12:45 PM] Analysis Services session failed with the following error: Failed to connect to the Analysis Services server dalacctlib. Error: Your permissions on the server computer do not allow you to administer this Analysis server. Error: ===== Process Completed ===== [8/21/2007 12:45 PM] Failed to build the OLAP cubes. Error: Analysis Services session failed with the following error: Failed to connect to the Analysis Services server dalacctlib. Error: Your permissions on the server computer do not allow you to administer this Analysis server. Error: ---------------------------------------------------- THE EMAILED ERROR: Your CBSRequest job failed. Its current state is FailedNotBlocking. It was 0% complete. To get more information about the job failure, please go to Project Web Access. Select Personal Settings from the left menu. Then select My Queued Jobs. The errors returned from the queue are as follows: Error ID: 17003 Error ID: 26000 Detailed error below - send it to the administrator for more detailed troubleshooting. < xml version="1.0" encoding="utf-16" > <errinfo> <general> <class name="CBS message processor failed"> <error id="17003" name="CBSASConnectionFailure" uid="159956a3-840b-84af-9b29-dccf8649c22c" QueueMessageBody="Setting UID=00007829-4392-84b3-b533-5a5a4977e3c9 ASServerName=sql2005 ASDBName=ProjectServer_Repository ASExtraNetAddress= RangeChoice=0 PastNum=1 PastUnit=0 NextNum=1 NextUnit=0 FromDate=08/21/2007 12:28:15 ToDate=08/21/2007 12:28:15 HighPriority=True" Error="Analysis Services session failed with the following error: Failed to connect to the Analysis Services server dalacctlib. Error: Your permissions on the server computer do not allow you to administer this Analysis server. 
       
      Error: " /> </class> <class name="Queue"> <error id="26000" name="GeneralQueueJobFailed" uid="cd4cb275-d48f-4a1e-bf43-0a3ccccd629a" JobUID="b4c3228f-97c1-5c39-9606-d32bbc423198" ComputerName="PROJECT" GroupType="CBSRequest" MessageType="CBSQueueMessage" MessageId="2" Stage="" /> </class> </general> </errinfo> Tag: report
  - 6
    - Upload a Sql website to a host server Hi I have built a site in Visual Web developer Express 2005. it works fine on my local machine but when I load it up to the server I get the following error message: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) My connectionstring in my web config file: < add name = " ConnectionString " connectionString = " Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True " providerName = " System.Data.SqlClient " /> I have enabled remote connections with TCP/IP only on my sql express SAC I.ve tried hours to resolve this problem, Please help. Tag: report
  - 7
    - SQL 2005 Transact-SQL Full Outer Self Join Can someone please help me to find out the problem with the following Self Join using Full Outer Join SELECT T1.product, T1.vendor, T2.product, T2.vendor FROM ( product_vendor T1 FULL OUTER JOIN product_vendor T 2 ON T1.vendor = T2.vendor ) WHERE T1 . product = 1 AND T2.product = 2 This query always returns only the matching rows (as if INNER join). Is there a way to get the non-matching rows too, using a single query, without using temp tables If I put the results for Product 1 and Product 2 in two different temp tables and then do a FULL OUTER JOIN on the temp tables, I could get the desired result. I tried putting the WHERE clause condition in the Join, having <> etc., - but, nothing worked. Thanks. Sample Data in product_vendor table (I need to compare ONLY two products at any time - in this example Product 1 and 2): Product Vendor 1 101 1 102 1 103 2 101 2 103 2 104 3 101 4 102 .... .... Desired Output: T1.product T1.vendor T2.product T2.vendor 1 101 2 101 - Products 1 and 2 having same vendor 1 103 2 103 - Products 1 and 2 having same vendor 1 102 2 NULL - Vendor 102 having product 1 but no Product 2 1 NULL 2 104 - Vendor 104 having product 2 but no Product 1 Tag: report
  - 8
    - Data entry in a datetime field. My other thread containg this same topic seems to have some error. It doesn't show up in the main 'SQL Server Database Engine' group at all. So I had to start a new thread instead. Here's the link to the original - http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1136124&SiteID=1 Here are the contents of the same - Post#1 Orginally posted by me - Hi, I am using SQL Server Management Studio Express for creating my database. I also use the GUI tools for data entry instead of using T-SQL. Whenever I try to enter a value in a field that has a smalldatetime data type, it gives me an error message - "Invalid Value for cell (row 1, column 2). The changed value in this cell was not recognised as valid. .Net Framework Datatype: Datetime Error Message: Index was outside the bounds of the array. Type a value appropriate for this data type or press ESC to cancel the change." Now I have tried entering all different combinations in which one can enter a date or a time or both, including in the format I have specified in the windows regional settings, but I always get the same error message. How do I input data into the field Post#2 Originally posted by Jens K. Suessmeyer - Hi, you are using a format SQL Serve ri snot expecting. Which User language did you configured for the accessing user and which date are you trying to insert HTH, Jens K. Suessmeyer. --- http://www.sqlserver2005.de --- Post#3 Originally posted by me - Hi, I use only English as the default language for all programs, OS included. The date in question is not just one particular date, it is any date or time value, and I've been facing this issue ever since I started using SQL Server Express a year ago. Only I haven't used it much since, and need to seriously develop something now, that I bothered to ask the question here. Some examples of the values I tried entering yesterday, and none of them worked. (I got the same error message for each value) - 1-1-2007 01-01-2007 01/01/2007 01,01,2007 01:01:2007 Jan 01, 2007 (this is the format I have specified for short date in the Win regional settings - MMM dd, yyyy) I even tried entering time alongside with all those above figures in the format - 11:35 PM 11:35 AM 23:35 The only time value(s) I didn't try was/were - hh:mm s tt - which is the time format specified in my Win regional settings, neither standalone, nor with the date values. I also tried entering a time value standalone, without the date. Still no go. (Also, as I have gleaned from previous posts, if all goes well and SQL Server accepts your data, then if you enter just a date value in a datetime field, then the field automatically gets populated with the value of the system time at that moment, and if you enter just a standalone time value, then the date part will be filled by the system date value on the date of the entry. Am I correct so far ) Now I'm in a real fix because of the inability to enter date/time values. Could you please help me out Post#6 Originally posted by me - I don't understand. I can enter the datetime values in all the above mentioned combinations if I use an Insert or Update statement using T-SQL, but I can't seem to enter data in the datetime/smalldatetime fields at all if I try to use the GUI of Management Studio Express to enter data. (right click the table, select show table, and the grid view pops up). Ditto for using GUI tools of Visual Studio Express. Is this a bug in the Visual Studio (Express) software Also, my other concerns are, once I develop a front-end for data entry using VB, will I face the same issues with data entry in the datetime fields using the GUI of the front end I can't test that right now, because I'm still learning VB, and cannot create the front end just yet. Post#7 Originally posted by me this afternoon, but it never showed up on the page - Alright, I have done a complete reinstall of Win XP, and have also reinstalled SQL Server Express. Prior to this, I had SQLExpress SP1, but now I have directly installed SP2. Now I continue to face the same issue. SQL Server will not accept any value for any datetime field, unless it is entered as an SQL insert statement. It won't accept the value entered in the exact same format as the insert statement from the GUI mode of Management Studio Express, nor will it take any values from any GUI developed using VBExpress and the fill dataset method. It throws an error everytime. I don't know any other way of databinding and updating/inserting using VB at this time. I am at my wits' end because of this. I ask again, is this a bug in SQL Server Express If so how do I report it to MS and get my issue resolved If not, even then how can I solve this problem Could someone please help me out I am stuck and cannot use the product at all, without the datetime field. Tag: report
  - 9
    - Upgrade Hey guys - I need your help. We are using SQL Server 2000- do you think, we should upgrade to some later release of Microsoft SQL Servier or Migrate to Oracle, IBM or Sybase. I am also wondering, what do you think of Open Source MySQL and PostgreSQL. Have anyone of you using these databases. Thanks guys for your help - Mark Smith Tag: report
  - 10
    - Matrix Report Not Showing All Columns I have a matrix report that I am using with MonthName(Fields!Month.Value) across the top of the report. However, when there is no data under the column, the month is skipped. So, for example, I get this: January March Actual Goal Actual Goal 25 25 30 35 I have tried adding iif(Sum(Fields!Total.Value, "DatasetName" )> 0, Sum(Fields!Total.Value, "DatasetName" ), "N/A" ) But it still is not showing up. Any ideas Thanks, SHP Tag: report
  - 11
    - Access denied starting SQL Server agent I've been struggling to figure out how to set the "Log on as" login for a SQL Server agent that isn't a member of the Administrators group. Is this even possible No matter what policy settings I assign the the user, I get an access denied error when I try to start the SQL Server Agent service. If that user is part of the administrators group, there are no errors. I have no problems with other services and "log on as" logins... Tag: report
  - 12
    - Can I/Should I turn off logging on some tempdb transactions? I am doing some tempdb work for reporting only, and the trans log is getting to 5GB and I dont really see why I need rollback. If it fails I can just tell the user and the #temp tables will be deleted when the SP returns anyway. So, can I tell SQL Server not to bother with the transaction logging on these tempdb inserts/updates. Have to admit something feels a bit iffy about doing this, but I cant see the problem logically. Someone please tell me I'm being supid!! Tag: report
  - 13
    - Released CozyRoc SSIS+ 1.1 Beta 3 Released a new beta of SSIS+ 1.1 library. It includes a new SSIS Zip Control Flow Task for compression and decompression of Zip, GZip, BZip2, Unix, Tar archives. Tag: report
  - 14
    - OLAP, DTS and SQL Hello, I have some dimensions and I want to automate them to be launched every week (for exemple). How can I do it I have no idea,l and don't know where to start looking for in google... Should I do a stored procedure to launch a dts, that launch them And then program a job to be launched weekly Is there any way from the Visual Studio 2005 to schedule the dimensions and cubs Any help would be great and much apreciated as I am completely lost!! Thank you! Tag: report
  - 15
    - sp_ListFiles works differently based on the O/S level. Be sure to test "sp_ListFiles" carefully if you use it. It will fail on some versions of Windows because "DIR" returns different time formats. For example, Windows NT 5.2 (3790) does not return the required time format. "usp_Clean_Backup_Files" uses "sp_ListFiles" and that is how I came across this. Tag: report
  - 16
    - rda dropping primary key index I am using RDA to download a tables I use TrackingOffWithIndexes the issue is that when I download a small set of data the primary key index is there when I download a larger set. the index gets dropped (no error) just slowness when querying the table the max database size is set to 1024mb and the temp database size is set to 1024mb the actual db size (with the missing index) is 262 mb... lot's of room left! when I try to create the index manually after the download in sql server management studio I get this error: Not enough storage is available to complete this operation I am using 2GB sd cards and nothing else is on the card. any ideas Regards, eric [at] westgen com Tag: report
- Next
  - 1
    - counting all records in a database Is there an easy way to count all records in a database Tag: report
  - 2
    - copy index statistics from production to development. I have a very large database that I need to be able to copy the statistics only from the production database server to my develoment environment. I need to be able to reproduce the execution plans on development without moving all the data. MS does this when they need to review the exeecution plan for large complex databases. Need help. Tag: report
  - 3
    - An attempt to attach an auto-named database ....\aspnetdb.mdf failed I've installed VWD 2005(Beta 2) and MS SQL Express and also followed the aspnet tour at http://beta.asp.net/guidedtour2 . After trying new webcontrol (login control) and trying to execute the page, I've got an error such as below: An attempt to attach an auto-named database for file c:\inetpub\wwwroot\Demo\App_Data\aspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: An attempt to attach an auto-named database for file c:\inetpub\wwwroot\Demo\App_Data\aspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share. Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace: [SqlException (0x80131904): An attempt to attach an auto-named database for file c:\inetpub\wwwroot\Demo\App_Data\aspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +684979 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +207 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1751 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +32 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +601 System.Data.SqlClient.SqlInternalConnectionTds..ctor(SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +159 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +346 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +445 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +304 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +85 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +126 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +239 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved) +815 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +80 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +78 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +161 System.Web.UI.WebControls.Login.AttemptLogin() +94 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +101 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +134 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5670 can anyone help me to solve the problem... thanks Tag: report
  - 4
    - Database in recovery I have a database currently showing to be in recovery. I have not been able to find a GUI method to monitor the progress (to determine when it might complete) so is there a command line T-SQL method to monitor the recovery process Tag: report
  - 5
    - SQL Server Replication License Requirments I'm setting up SQL Server 2005 Replication. I have purchase a processor license for SQL Server 2005. Do I need a Windows CAL for each device that will be accessing the replication via IIS Thanks! Matt Tag: report
  - 6
    - how to enable a user account I found the the status of guest account in model database is abnormal,but i don't know how to make it active. any suggestions thanks Tag: report
  - 7
    - Why can't I use columns based on case down in my where clause I am writing a fairly simple sql, and I would like to write something like Code Snippet select firstname as firstname, case when firstname = 'Peter' then 'yes' else 'no' end as whatever from MyTable where whatever = 'yes' And this should then select out the rows where column number 2 is 'yes'. It doesn't work, and I have to copy the firstname = 'Peter' into the where clause. But why Tag: report
  - 8
    - The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine. (System.Data) I am on an XP Pro 32-bit laptop, running SQL Server 2005 Developer Edition. I want to import an Excel spreadsheet into one of my databases using (formerly known as) DTS. The Excel spreadsheet was created in 2003 (I have Office 2007 installed on this laptop). Whenever I try and do any OleDb import, I get the error: TITLE: SQL Server Import and Export Wizard ------------------------------ The operation could not be completed. ------------------------------ ADDITIONAL INFORMATION: The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine. (System.Data) ------------------------------ BUTTONS: OK ------------------------------ I cannot install/reinstall MDAC because I have the latest version. What else can I try As additional information, I also get the error when trying to import anything into or export anything out of Access 2007. I'm pretty sure MDAC is awful, but I still need OleDb providers for much of the work I am doing. Tag: report
  - 9
    - Service accounts, local admin, and sysadmin question! Hi, Re: SQL Server 2005 We have defined a local administrator to be the SQL Server and SQL Server Agent services user, and is also the job step owner for some SSIS packages I am running. My question is, isn't by default a local administrator ALSO granted sysadmin in SQL Server According to this link, it seems to imply this: http://msdn2.microsoft.com/en-us/library/ms143504.aspx However, I am having some permissions problems with the local adminstrator account (i.e. SQL Server agent account) when it runs the job. The error is that it doesn't have execute permissions on sp_dts_addlogentry. How can this be, if it's granted sysadmin Thanks Tag: report
  - 10
    - Incorrect Totals Here's my cube structure in a nutshell. I've got a measure group called MG1 which contains measure "Units". I've got another measure group called MG2 which contains measure "Price". I want to add a new measure called "Value" that should correspond to Units * Price. I'm trying to use MDX script to do this calculation: [Measures].[Value] = [Measures].[Units] * [Measures].[Price] This seems to produce the right results at leaves level, but when i check the Totals in the Cube Browser they are incorrect because in the totals calculation Prices are summed up and multiplied by the sum of Units. The Totals should instead be calculated by doing a sum of all the leaves Values if you see what i mean. Is there anything obvious i'm missing here Tag: report
  - 11
    - Error: Subreport could not be shown. I work for a Microsoft Gold Partner and I am having a problem with subreports in SSRS 2005 SP1. Essentially, I have a report that contains subreports. The main report has a table that passes a parameter to a subreport in each row. The report renders properly when I preview it in Visual Studio, but once deployed to the server it only renders the first subreport for the first row, and every subreport thereafter displays the error, "Error: Subreport could not be shown." I tested each subreport individually on the SSRS sever and each executes and displays fine. I turned on traces in the web.config file and found some additional information: Tag: report
  - 12
    - Converting XML Datetime to SQL Datetime I have an XML colmn in SQL 2005 table which looks like: <abc> <abcdate>2007-01-31T13:47:27.25-05:00</abcdate> </abc> The following query : SELECT xmlColumn . value ( '(/abc/abcDate)[1]' , 'nvarchar(30)' ) FROM abcTABLE Returns 2007-01-31T13:47:27.25-05:00 --------- SELECT CAST(xmlColumn . value ( '(/abc/abcDate)[1]' , 'nvarchar(30)' ) AS DATETIME) FROM abcTABLE Returns Msg 241, Level 16, State 1, Line 1 Conversion failed when converting datetime from character string. ----------------- SELECT CAST(xmlColumn . value ( '(/abc/abcDate)[1]' , 'nvarchar(19)' ) AS DATETIME) FROM abcTABLE Returns 2007-01-31 13:47:27.000 because length of 19 trims the milliseconds ------------------ Is it possible to convert this type of XML data type and still acheive accuracy to the milliseconds Thanks Gary Tag: report
  - 13
    - how to get YYYYMMDD formate Hello, I use CAST ( PYBTHC as varchar ( 2 ))+ CAST ( PYBTHY as varchar ( 2 ))+ CAST ( PYBTHM as varchar ( 2 ))+ CAST ( PYBTHD as varchar ( 2 )) to get a string like 199232. How to get 19920302 What function I can use to pad '0' left when it less than 10> Thanks Tag: report
  - 14
    - Comparison of SQL Server Reporting Services to Access Reports, Crystal Reports, Cognos or other options Hello SQL Server Experts, Data Analysts, and Report Writers et al: re: Reporting Options with SQL Server I wanted to propose an offshoot to the pryor thread: Would anyone take a stab at comparing Access Reports, Crystal Reports, Cognos or other options to all the Reporting Services and its components offered as part of SQL Server , especially as to extracting data from SQL Server into a report format I guess this is a far as capabilites, ease of use, limitations, and especially formatting or presentation of the end report product Thank you to all, and I hope this is a beneficial discussion to others. Hal1490 Hal9000 Tag: report
  - 15
    - Changing the default database files path fails i have the following code segment which works fine as is: try { ServerConnection connection = new ServerConnection ( "localhost" ); Server server = new Server (connection); if (!server.Databases.Contains( "SMO_TEST" )) { Database database = new Database (server, "SMO_TEST" ); //FileGroup fileGroup = new FileGroup(database, "PRIMARY"); //database.FileGroups.Add(fileGroup); //DataFile dataFile = new DataFile(fileGroup, "SMO_TEST_Data"); //dataFile.FileName = "D:\\SMO_TEST_Data.mdf"; //fileGroup.Files.Add(dataFile); //LogFile logFile = new LogFile(database, "SMO_TEST_Log"); //logFile.FileName = "D:\\SMO_TEST_Log.ldf"; //database.LogFiles.Add(logFile); database.Create(); } } catch ( FailedOperationException ex) { Console .WriteLine(ex.Message); } when i uncomment the lines commented above it fails. Any help Tag: report
  - 16
    - SSRS in Sharepoint, WrapperReportRenderingException/JobCanceledException for large Matrix (OLAP) reports Hi all - please help! I'm running SSRS 2005 (9.00.3027.00) in Sharepoint integrated mode on a Win2003 SP1 box. This works fine for most reports, including matrix reports sourcing data from SSAS 2005. However, I have a couple of large matrix reports (7 columns per group, 12 column groups, 150 rows, 1 row per group, data from SSAS) that run fine when I run them in VS2005, but as soon as I try and run them from within Sharepoint/Report Server I get " An unexpected error has occurred.". Sometimes this happens after 60 seconds, sometimes more (up to 3 minutes) If I filter them down to 7 column groups they work perfectly (altbeit slowly,1-2 mins), 8 groups works sometimes, 9 never works. I've checked that the report execution timeout is set to 10 minutes. The SSRS log shows the errors as WrapperReportRenderingException and JobCanceledException. I'm not sure whether the the rendering error causes the cancelled error or vice versa. does anyone know what I can do about this By the way, from looking at the CPU usage of the server, it seems as though SSRS does carry on rendering the report long after Sharepoint has given up and returned with the helpful error. Many thanks in advance Alex Log extract as follows: w3wp!library!7!01/25/2007-11:11:44:: Call to GetPermissionsAction( http://uk-ls-it-02/Reports/Triangles/Gross Triangle Treaty Property.rdl). w3wp!library!7!01/25/2007-11:11:44:: Call to ListParentsAction( http://uk-ls-it-02/Reports/Triangles/Gross Triangle Treaty Property.rdl). w3wp!library!7!01/25/2007-11:11:49:: Call to GetPermissionsAction( http://uk-ls-it-02/Reports/Triangles/Gross Triangle Treaty Property.rdl). w3wp!library!1!01/25/2007-11:11:50:: Call to GetPermissionsAction( http://uk-ls-it-02/Reports ). w3wp!library!13!01/25/2007-11:11:50:: Call to GetSystemPropertiesAction(). w3wp!library!13!01/25/2007-11:11:51:: i INFO: Call to RenderFirst( 'http://uk-ls-it-02/Reports/Triangles/Gross Triangle Treaty Property.rdl' ) w3wp!runningjobs!7!1/25/2007-11:13:20:: i INFO: Adding: 1 running jobs to the database w3wp!runningjobs!7!1/25/2007-11:14:20:: i INFO: RunningJobContext.IsClientConnected; found orphaned request w3wp!reportrendering!13!01/25/2007-11:14:20:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: An error occurred during rendering of the report., ; Info: Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: An error occurred during rendering of the report. ---> Microsoft.ReportingServices.ReportRendering.ReportRenderingException: An error occurred during rendering of the report. ---> System.Threading.ThreadAbortException: Thread was being aborted. at Microsoft.SharePoint.Library.SPRequest.OpenWebInternal(String bstrUrl, Guid& pguidID, String& pbstrRequestAccessEmail, UInt32& pwebVersion, String& pbstrServerRelativeUrl, UInt32& pnLanguage, UInt32& pnLocale, String& pbstrDefaultTheme, String& pbstrDefaultThemeCSSUrl, String& pbstrAlternateCSSUrl, String& pbstrCustomizedCssFileList, String& pbstrCustomJSUrl, String& pbstrAlternateHeaderUrl, String& pbstrMasterUrl, String& pbstrCustomMasterUrl, String& pbstrSiteLogoUrl, String& pbstrSiteLogoDescription, Object& pvarUser, Boolean& pvarIsAuditor, Int32& plSiteFlags) at Microsoft.SharePoint.SPWeb.InitWebPublic() at Microsoft.SharePoint.SPWeb.get_ServerRelativeUrl() at Microsoft.SharePoint.SPWeb.get_Url() at Microsoft.ReportingServices.SharePoint.Server.Utility.GetSPItemMetaDataAndContent(String path, UserContext userContext, Boolean returnContent, Guid& id, ISecurableObject& secObj, SharePointImpersonatedWeb& wrapper, Byte[]& content, String& mimeType) at Microsoft.ReportingServices.SharePoint.Server.Utility.GetSPItemType(String objectName, UserContext userContext, Guid& id, ISecurableObject& secObj, SharePointImpersonatedWeb& wrapper) at Microsoft.ReportingServices.SharePoint.Server.Utility.GetSPItemType(String objectName, UserContext userContext, Guid& id) at Microsoft.ReportingServices.SharePoint.Server.Utility.GetSPItemType(String objectName, UserContext userContext) at Microsoft.ReportingServices.SharePoint.Server.SharePointServiceHelper.Microsoft.ReportingServices.Diagnostics.IPathManager.IsSupportedUrl(String path, Boolean checkProtocol, Boolean& isInternal) at Microsoft.ReportingServices.Diagnostics.CatalogItemUrlBuilder.IsReportServerPathOrUrl(String pathOrUrl, Boolean checkProtocol, Boolean& isPath) at Microsoft.ReportingServices.ReportRendering.ReportUrl..ctor(RenderingContext reportContext, String initialUrl, Boolean checkProtocol, NameValueCollection unparsedParameters, Boolean useReplacementRoot) at Microsoft.ReportingServices.ReportRendering.Action.get_DrillthroughReport() at Microsoft.ReportingServices.ReportRendering.Action.get_DrillthroughID() at Microsoft.ReportingServices.Rendering.HtmlRenderer.BaseRenderer.GetActionUri(Action action) at Microsoft.ReportingServices.Rendering.HtmlRenderer.HTML4Renderer.HasMultipleActions(ActionCollection actions, Int32& actionIndex, RenderActionInfo& firstActionInfo) at Microsoft.ReportingServices.Rendering.HtmlRenderer.HTML4Renderer.RenderTextBoxPercent(TextBox textBox, Boolean renderId) at Microsoft.ReportingServices.Rendering.HtmlRenderer.HTML4Renderer.RenderReportItem(ReportItem reportItem, Int32 borderContext, Boolean renderId) at Microsoft.ReportingServices.Rendering.HtmlRenderer.HTML4Renderer.RenderRangeCellsContent(MatrixCell lastCell, Int32 cellBorderContext) at Microsoft.ReportingServices.Rendering.HtmlRenderer.BaseRenderer.RenderRangeCells(Matrix matrix, Int32 indexRow, Int32 startCell, Int32 endCell, IntList hiddenColumns, Int32 borderContext, Boolean dirLefRight) at Microsoft.ReportingServices.Rendering.HtmlRenderer.BaseRenderer.RenderNonSplitMatrixLevel(Matrix matrix, MatrixMember currMember, Boolean& newRow, Boolean partialItem, Int32 level, IntList hiddenColumns, SizeCollection cellHeights, Boolean changableRows, Int32 borderContext, Boolean dirLefRight, MatrixContext matrixContext) at Microsoft.ReportingServices.Rendering.HtmlRenderer.BaseRenderer.RenderNonSplitMatrix(Matrix matrix, MatrixMember member, Boolean& newRow, Boolean partialItem, Int32 level, IntList hiddenColumns, SizeCollection cellHeights, Boolean changableRows, Int32 borderContext, Boolean dirLefRight, MatrixContext matrixContext) at Microsoft.ReportingServices.Rendering.HtmlRenderer.BaseRenderer.RenderMatrixContent(Matrix matrix, Boolean partialItem, Int32 borderContext, Boolean dirLefRight, Boolean expandLayout, MatrixContext matrixContext) at Microsoft.ReportingServices.Rendering.HtmlRenderer.HTML4Renderer.RenderMatrix(Matrix matrix, Boolean partialItem, Int32& borderContext, Boolean renderId) at Microsoft.ReportingServices.Rendering.HtmlRenderer.HTML4Renderer.RenderReportItem(ReportItem reportItem, Int32 borderContext, Boolean renderId) at Microsoft.ReportingServices.Rendering.HtmlRenderer.BaseRenderer.RenderCellItems(ReportItemCollection repItemCol, Hashtable partialItems, Hashtable expandItems, PageTableCell currCell, Int32 borderContext, Int32 linkToChild, Boolean layoutExpand) at Microsoft.ReportingServices.Rendering.HtmlRenderer.HTML4Renderer.GenerateTableLayoutContent(PageTableLayout rgTableGrid, Hashtable partialItems, ReportItemCollection repItemCol, Boolean bfZeroRowReq, Boolean bfZeroColReq, String docMapId, Boolean inDocMap, Boolean renderHeight, Int32 borderContext, Int32 linkToChild, Boolean layoutExpand) at Microsoft.ReportingServices.Rendering.HtmlRenderer.HTML4Renderer.GenerateHTMLTable(ReportItemCollection repItemCol, Double dxParent, Double dyParent, PageData pageData, String docMapId, Boolean inDocMap, Int32 borderContext, Int32 linkToChild, Boolean expandLayout) at Microsoft.ReportingServices.Rendering.HtmlRenderer.HTML4Renderer.RenderRectangle(Rectangle rectangle, PageData pageData, Int32& borderContext, Boolean renderId) at Microsoft.ReportingServices.Rendering.HtmlRenderer.BaseRenderer.RenderPage(Int32 index) at Microsoft.ReportingServices.Rendering.HtmlRenderer.BaseRenderer.RenderPages() at Microsoft.ReportingServices.Rendering.HtmlRenderer.BaseRenderer.RenderBody(HtmlTextWriter htmlTextWriter) at Microsoft.ReportingServices.Rendering.HtmlRenderer.ViewerReportArea.Render(HtmlTextWriter outputWriter) at Microsoft.ReportingServices.Rendering.HtmlRenderer.Html40RenderingExtension.InternalRender(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream) at Microsoft.ReportingServices.Rendering.HtmlRenderer.RenderingExtensionBase.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream) --- End of inner exception stack trace --- at Microsoft.ReportingServices.Rendering.HtmlRenderer.RenderingExtensionBase.Render(Report report, NameValueCollection reportServerParameters, NameValueCollection deviceInfo, NameValueCollection clientCapabilities, EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions, CreateAndRegisterStream createAndRegisterStream) at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderReport(IRenderingExtension renderer, DateTime executionTimeStamp, GetReportChunk getCompiledDefinitionCallback, ProcessingContext pc, RenderingContext rc, CreateReportChunk cacheDataCallback, Boolean& dataCached) --- End of inner exception stack trace --- w3wp!runningjobs!13!01/25/2007-11:14:20:: i INFO: CancelableJobExecution.Execute caught our thread abort exception w3wp!library!13!01/25/2007-11:14:20:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.JobCanceledException: An administrator has canceled the job., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.JobCanceledException: An administrator has canceled the job. ---> Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: An error occurred during rendering of the report. ---> Microsoft.ReportingServices.ReportRendering.ReportRenderingException: An error occurred during rendering of the report. ---> System.Threading.ThreadAbortException: Thread was being aborted. --- End of inner exception stack trace --- w3wp!webserver!13!01/25/2007-11:14:20:: e ERROR: Reporting Services error Microsoft.ReportingServices.Diagnostics.Utilities.JobCanceledException: An administrator has canceled the job. ---> Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException: An error occurred during rendering of the report. ---> Microsoft.ReportingServices.ReportRendering.ReportRenderingException: An error occurred during rendering of the report. ---> System.Threading.ThreadAbortException: Thread was being aborted. Tag: report

jovica-kar

i need example of report, for some busines solution, do you know where i can to find it.

Top

Re: report

Andrew - T4G

Try www.microsoft.com/downloads/ - there are plenty report packs there.

Also search for Project Real.

cheers,

Andrew

Top