InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 17 Jun 2013 23:02:23 GMT20http://www.sqlservercentral.com/Forums/Topic1464292-1633-1.aspxhello all, if there is chnge of column datatype from varchar to nvarchar and from char to nchar in the table,i guess stored procedures will get affected...does the rdls need to updated as well or not...pls help.Mon, 17 Jun 2013 11:45:17 GMTLucky9http://www.sqlservercentral.com/Forums/Topic1464274-1633-1.aspxHi,I have a like a mail merge (2 page report) linked to 4 datasets that have just the one parameter (learner_id) for each dataset; which is working fine. The reports is built up using Text boxes (for all items in dataset1) and a 3 tablix's (for datasets2-4).However i now want to take it up another level and bulk print them; so want to change the paremeter to say when the learner records were created (so print out all records created on a certain day), what would be best way about going to do this.The results in the datasets will vary per learner.dataset1 - this will alway have one row per learnerdataset2 - the results in this will vary depending on whats in the dbdataset3 - the results in this will vary depending on whats in the dbdataset4 - the results in this will vary depending on whats in the dbAny help and suggestions will be greatly appeciatedRegardsKetanMon, 17 Jun 2013 10:36:25 GMTketan.mistryhttp://www.sqlservercentral.com/Forums/Topic1463739-1633-1.aspxMy data is: UID (Unique Identifier)UsernameTimestampCPU %I want to have an area chart or a line chart for each UID. I have about 20,000 rows per day and want to trend the CPU usage per UID. Is that possible without having to create a dataset for each and apply that to a new chart?Fri, 14 Jun 2013 12:49:58 GMTCovyhttp://www.sqlservercentral.com/Forums/Topic1464009-1633-1.aspxI have created Report using ssrs 2008 in BIDS with Report Model Template.After creating and deploying Report Model report I tried to accessthrough installed Report Builder 2.0 but could not open it.How do I do it?What is required to do?Mon, 17 Jun 2013 01:19:40 GMTsej2008http://www.sqlservercentral.com/Forums/Topic1463338-1633-1.aspxUpdate: Added attachment (PDF)Disclaimer: Noob in the house. I'll try to phrase the question correctly.SSRS Picking Report uses dataset in matrix. Data Set is a Pick List with varying numnber of Sales Orders having multiple lines per order. Lines contain Bin location and Item Quantity. Item Quantity is aligned with each corresponding column header(order). The Orders are grouped in columns which expand dynamically to the right. The Line Items comprise the rows. The Tablix is sorted by the rows (Bin location).I would like to autonumber, dynamically, the columns (distinct orders). "1" for the first column (order), "2" for the second column, "3" and so on.I've tried several suggestions online, generally involving getCount in Code, and expression referencing the code. None work. If I have 10 columns, columns are ordered non-sequentially based on logic I'd spin my wheels trying to figure out.I also tried CountDistinct(OrderNumber).I'm throwing darts, blindfolded, at this point. One will surely hit me on the head without your help.Thanks!Thu, 13 Jun 2013 15:59:53 GMTmahler9http://www.sqlservercentral.com/Forums/Topic1463632-1633-1.aspxHey guys,I am having a real difficult time making my bar charts look the way I want. Here are the pictures to make this conversation easier :)First Picture: Simple bar graph. I want the Users on the Y-Axis and the MB Usage on INSIDE the bar. I also want the bar to be thicker like a normal bar chart[IMG]http://i.imgur.com/rgo5k82.png[/IMG]What I don't like: The bars are skinny, the users do not correlate perfectly with the bars, the MB is outside the bar itself. Second Picture: Simple Bar Graph. I want the same thing, but this is after MORE data is queried (i.e., 7 days instead of single day). I dynamically resized the chart (didn't do a great job, just took the number of days / 2 + original height to add more height to it). It ended up looking REALLY bad. It also removed some of the user names :([IMG]http://i.imgur.com/UeFZKqB.png[/IMG]Hope someone can help, I'm completely frustrated trying to figure it out! :)Thanks in advance!CovyFri, 14 Jun 2013 09:05:50 GMTCovyhttp://www.sqlservercentral.com/Forums/Topic1463667-1633-1.aspxHi,I have created an SSRS report that is called from a stored procedure. The SP is in server A, it query 2 databases. One in server A and the other in server B. A and B are linked servers. The report is running fine both in SSRS and report server. But after every few minutes I get a log on error when I am trying to run the report from SSRS or from the report server. If I manually execute the stored procedure and then try to run the report, it works fine then. Can anyone please help me with this issue? Thanks a lot in advance.Here is the error that I am getting -An error occurred during local report processingAn error has occurred during report processing.Query execution failed for dataset "Sales".Login failed for user 'NT AAUTHORITY ANONYMOUS LOGON'.Sales is the name of the main dataset in this SSRS report. It is created from the stored procedure.Fri, 14 Jun 2013 10:16:46 GMTsabah_seattlehttp://www.sqlservercentral.com/Forums/Topic1463563-1633-1.aspxHi,I am using SSRS 2008 R2. Dataset has date field "MyDate". "MyDate" field contains NULL(nothing) values. I am doing sorting on different fields. I am able to sort data with "MyDate" fields also.[b]Issue [/b]: I want Null(Nothing) data at end of list for "MyDate" field sorting. How it will possible. OrderPlease provide me solution if anyone have.Fri, 14 Jun 2013 07:29:06 GMTvirenkumar.patelhttp://www.sqlservercentral.com/Forums/Topic1463344-1633-1.aspxHi everyone, I was wondering if anyone knows how to best deal with original dundas charts (from 2005 ssrs) after they get upgraded to ssrs 2008R2... Namely, I have an issue with graph, it's percent bar graph, and it shows properly on scale 0-100 in the old version however the 2008R2 bar has those percentages multiplied by 1000.I noticed that the datatype in the properties gets set to P1 after opening the report in 2008R2. In original, there's nothing there. My research led me to the fact that P1 takes a number, multiplies it by 1000 and adds percent sign. I can modify the data type and it displays the correct number but then I lose the percent sign.I have several reports that needs some fix to that. What's the best approach?ThanksThu, 13 Jun 2013 16:21:15 GMTdanka_6786978http://www.sqlservercentral.com/Forums/Topic1463222-1633-1.aspxWe have a DLL that we use for formatting accross muitlple reports (added from Report Properties).When I added a new function, I didn't change version - I just built and deployed.I get the error "Details: Could not load file or assembly The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)"I can't believe I'd have to update all the rdls to the reference (which is the same location, name and version) so I must be doing something wrong - I'm not sure what that is.ThanksThu, 13 Jun 2013 11:33:40 GMTd_georgehttp://www.sqlservercentral.com/Forums/Topic1462873-1633-1.aspxHi,Please let me know how to display disclaimer message in end of the report in SSRS report.Like...<<<DISCLAIMER: confidence and probability information......>>Thanks,Wed, 12 Jun 2013 18:32:02 GMTSaintmount.Sqlhttp://www.sqlservercentral.com/Forums/Topic1462839-1633-1.aspxHello, using SSRS2008 with the Sharepoint (MOSS2007) integration. I'd like to develop a report and have a clickable link to a PDF attachment which is stored in a BLOB field in SQL2008. Does anyone know a work around to accomplish this?Wed, 12 Jun 2013 15:26:55 GMTdwittyhttp://www.sqlservercentral.com/Forums/Topic1462594-1633-1.aspxHello, How to calculate the formula [b] Sum([PRODUCT_VAL] ) for Distinct( [ID]) [/b] in SSRS? ( [PRODUCT_VAL] and [ID] - are two columns in the dataset ) I think I need to iterate through rows of DataSet in custom reports code, to find Distinct - [ID] and make SUM of theirs [PRODUCT_VAL] , but is it possible? Or is it a better solution? ThanksWed, 12 Jun 2013 06:39:23 GMTadigdahttp://www.sqlservercentral.com/Forums/Topic1462498-1633-1.aspx Hi, I have a drill down report and I want to highlight the drill down link. 1. I want drill down link get underline when curser on it. 2. And it's color become pink once you click the link.Wed, 12 Jun 2013 02:24:10 GMTsaurabh.deshpandehttp://www.sqlservercentral.com/Forums/Topic1462432-1633-1.aspxHello,I am facing a problem in SQL server reporting that i place a text box on header that can be shrink and grow it works on fly but when i try to export pdf it behaves wrong, mean the header does to grow and shrink with control so the header miss the part of text box can any one suggest me what i am missing.Thanks,SaintTue, 11 Jun 2013 18:53:18 GMTSaintmount.Sqlhttp://www.sqlservercentral.com/Forums/Topic1461243-1633-1.aspxIs there a way to "stack" columns in a table on a report?My example would be getting some info out of [msdb].[dbo].[sysjobhistory]. I'd like to see the name of the job (and _id), the step_id, step_name, run_date, run_time and run_duration on one "row", with the message on the next (see attached).I can't create different datasets as there would be multiple rows per job_id and I'd like them to "meld".[code="sql"]select j.name ,j.job_id ,jh.step_id ,jh.step_name ,jh.run_date ,jh.run_time ,jh.run_duration ,jh.messagefrom sysjobs j inner join sysjobhistory jh on j.job_id = jh.job_idwhere jh.job_id = '<some job id>'[/code]Fri, 07 Jun 2013 15:49:11 GMTSQLDCHhttp://www.sqlservercentral.com/Forums/Topic1461593-1633-1.aspxHello I am having a few issues with a Matrix report I am developing:Firstly I have two queries:One:[code="sql"] SELECT Sales_Export.Country, Sales_Export.AccountNumber, InstrastatCustomers.AccountName, Sales_Export.ContractCode, Sales_Export.SalesValue, Sales_Export.SalesDateFROM Sales_Export INNER JOIN InstrastatCustomers ON InstrastatCustomers.AccountNumber = Sales_Export.AccountNumberWHERE (Sales_Export.SalesDate BETWEEN @StartDate AND @EndDate)GROUP BY Sales_Export.Country, Sales_Export.AccountNumber, InstrastatCustomers.AccountName, Sales_Export.ContractCode, Sales_Export.SalesValue, Sales_Export.SalesDateORDER BY Sales_Export.SalesDate[/code]I also have another query called Running Sum:Two:[code="sql"]SELECT SUM(SalesValue) AS SalesValue, AccountNumberFROM Sales_ExportGROUP BY AccountNumber[/code]My matrix report is as follows:Country, AccountName, Account, ContractCode, SalesValue, MTD Sum(SalesValue)The matrix report produces sales of each individual day. In my month to date column I would like the sales for all the days added up in this column. I attempted to create an expression but this is summing the whole column.=RunningValue(Fields!SalesValue.Value,Sum, "DailyExportSales")Does anyone know how i can achieve the sum for each day in that column.Mon, 10 Jun 2013 08:45:20 GMTdbmanhttp://www.sqlservercentral.com/Forums/Topic1461795-1633-1.aspxOK,I have been trying to put this off at work, but it's now become a report that they need for planning purposes. I need to create a very large matrix that has between 200 and 250 columns across the page and can have between 100 and 150 rows. What they want is to be able to click on any number(in a cell) anywhere in the matrix and to display the detail behind that number.Creating the Matrix and populating it is not a problem, my problem is adding the 'Subreport Action' to each cells. Is there a quick way of doing this or do I have to set it up manually for each one ?ThanksMon, 10 Jun 2013 16:18:50 GMTitwhizhttp://www.sqlservercentral.com/Forums/Topic1461849-1633-1.aspxHi, I have a drill-down report with has 7 plus level of drill-down. I want to keep main page visible always and open drill-down reports in new window.for example if there are 6 levels of drill-down then it should open 6 windows.I want this functionality in both BIDS while preview the report and also while running report in report manager through Internet explorer.ThanksSaurabh Mon, 10 Jun 2013 21:32:58 GMTsaurabh.deshpandehttp://www.sqlservercentral.com/Forums/Topic1458459-1633-1.aspxI'm trying to create a report that compares data from a SQL Server 2008 R2 Database to data in a IBM DB2 database and only shows rows where the data is [b][u]not[/u][/b] the same and that is able to run in real time as opposed to one that loads the data at some other time than when it is run. I think this may be possible if the data set for the DB2 database is called from a stored procedure which first imports the data into a staging table in SQL Server (SSIS?) and then produces a dataset between the staging table and the main SQL Server table using a SQL Union Query.I know how to display the data from my two different data sources in the same table using the lookup feature, but can't figure out how to do this where I only want to show rows where certain fields do not match. This is used to verify data entries between our accounting databases (JD Edwards DBS on an AS/400) and our project management software (Prolog running on SQL Server 2008 R2).If all rows from both data sources are a match, then the report would not show any data, indicating that there were no issues with that job. It should only display a report when there are inconsistancies in the two data sources indicating something that needs fixing.I'm still new at creating reports with SSRS and would appreciate any advice, suggestions, or help anyone could give me.ThanksThu, 30 May 2013 14:23:13 GMTflbottshttp://www.sqlservercentral.com/Forums/Topic1460571-1633-1.aspxHi,I am struggling on a requirement that involves grouping matrix rows with the same resultant dataset.For example:Accnt MatrixColA MatrixColB MatrixColC1 0 1 02 0 1 03 2 1 04 0 0 1Is there a way to group accnt 1 and 2 as they share the same values for matrixColA, matrixColB, and matrixColC? This would mean that for those values {0,1,0}, there is a count of two. For the other values {2,1,0} and {0,0,1}, there is a count of one respectively.As it is a matrix there could be any number of matrix columns and I'd need the solution to work.The underlying dataset for the above would look like:Accnt MatrixCol1 B2 B3 A3 A3 B4 CCan anybody see a solution to this? I have tried in the SQL to group the records by making use of "STUFF" to create a new column such as A,A,B (for accnt 3) but this takes too long when I have a large dataset.Thanks in anticipation,StuThu, 06 Jun 2013 02:16:30 GMTstuart.coxhttp://www.sqlservercentral.com/Forums/Topic1460357-1633-1.aspxIn our web application, we have several sub applications. In one of the sub application say "Application1", we have added Webreference to http://localhost:80/ReportServer/ReportExecution2005.asmx. In one of ASPX page(Report.aspx) in this sub application, we have used this webreference to render the report directly in excel format. We don't use this webreference in any other pages. I ran this page and was able to generate the report in excel format without any issues. But, after sometime around 10 or 20 mins later, application starts throwing error "The report execution vw5tgt45dqxidj455nrhbyzx has expired or cannot be found. (rsExecutionNotFound) ". Strange thing is that all the pages in "Application1" starts throwing this error, even though this webservice is called only in one page Report.aspx. I logged out off the application and logged in again and it still continues to throw this error in all pages under Application1. Please help me understand why this error comes even though this web reference was not called explicitly in the code. Below is detailed error. Exception Details: Microsoft.Reporting.WebForms.ReportServerException: The report execution vw5tgt45dqxidj455nrhbyzx has expired or cannot be found. (rsExecutionNotFound)Source Error: [No relevant source lines]Source File: c:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\54630efd\e239d1ad\App_Web_004dtuxw.1.cs Line: 0 Stack Trace: [ReportServerException: The report execution vw5tgt45dqxidj455nrhbyzx has expired or cannot be found. (rsExecutionNotFound)] Microsoft.Reporting.WebForms.ServerReportSoapProxy.OnSoapException(SoapException e) +82 Microsoft.Reporting.WebForms.Internal.Soap.ReportingServices2005.Execution.ProxyMethodInvocation.Execute(RSExecutionConnection connection, ProxyMethod`1 initialMethod, ProxyMethod`1 retryMethod) +770 Microsoft.Reporting.WebForms.ServerReport.EnsureExecutionSession() +252 Microsoft.Reporting.WebForms.ServerReport.LoadViewState(Object viewStateObj) +286 Microsoft.Reporting.WebForms.ServerReport..ctor(SerializationInfo info, StreamingContext context) +536Wed, 05 Jun 2013 11:50:03 GMTsathyaprakashghttp://www.sqlservercentral.com/Forums/Topic1459917-1633-1.aspxI'm new to SSRS, but I've been given a task that involves getting a directory listing from a remote server (e.g. DIR \\server\incoming\data\archive\*.dat). Is there any way to do this that does not involve calling a sproc that invokes xp_cmdshell? If so, can you provide a link or two that will point me in the right direction?Thanks in advance,AlexTue, 04 Jun 2013 13:22:03 GMTalex_pixleyhttp://www.sqlservercentral.com/Forums/Topic1459774-1633-1.aspxHi,I was just wondering if anyone had a solution to the double scroll bars in SSRS?Tue, 04 Jun 2013 07:44:16 GMTTJThttp://www.sqlservercentral.com/Forums/Topic1459141-1633-1.aspxHi All,How to add two matrix reports that are pointing to two different datasets?I am ok doing it this way=Sum(Fields!JobBilledExVat.Value, "Dataset1") + Sum(Fields!JobBilledExVat.Value, "Dataset2")But my matrix report populates the column field by data and the report grows based on the selected dates. when I use the above expression , it is not grouped by week.How to add two textbox values in a matrix report grouped by a column.any help plsMon, 03 Jun 2013 03:30:02 GMTvar05http://www.sqlservercentral.com/Forums/Topic1459335-1633-1.aspxI have a report that has more than one subreport. I am wondering if it is possible to pass a value from one subreport to another subreport in the same main report?Mon, 03 Jun 2013 08:52:54 GMTstevenpleehttp://www.sqlservercentral.com/Forums/Topic1459127-1633-1.aspxI've been asked by a customer to have the same group on primary and secondary axis, but with different labels. The goal is to have a period as primary axis, and a number related to this period on the secondary axis. Is it possible to accomplish this?Mon, 03 Jun 2013 03:05:47 GMTokbangashttp://www.sqlservercentral.com/Forums/Topic1201631-1633-1.aspxHello,One report is running fine in BIDS and taking some seconds in BIDS. BUT it is taking very long time on report manager and failing. Can you tell me what is the reason? OR how to fix this.Thank you,BMRMon, 07 Nov 2011 12:06:37 GMTbmr270http://www.sqlservercentral.com/Forums/Topic1458005-1633-1.aspxI have from and to dates as parameters to my reports. I need to enforce the users to either enter both values, or enter none. They can't enter value for just one of these. leaving the other param to be NULL. How can I do this?Additionally, how can I enforce validation to make sure my FromDate is less than or equal to ToDate?Thanks for your help in advance.- RexWed, 29 May 2013 17:48:49 GMTRexHelioshttp://www.sqlservercentral.com/Forums/Topic1457346-1633-1.aspxPlease help! I have an SSRS report with multiple shared data sources pointing to web services. If all the web services are up, then the report works fine. However, if just one of the web services is down, then the report fails with a connection error. Is there any way around this so that if one of the services (data sources) is down, then the report will still run, showing data from the available datasources. Thanks.Tue, 28 May 2013 08:34:03 GMTjenkimphttp://www.sqlservercentral.com/Forums/Topic1458468-1633-1.aspxI have a tablix with variable width ranging anywhere from 3 columns to many columns (mostly expected to be around 15-20). I have a bar chart right below my tablix, representing the same data as that of the tablix. For aesthetic pleasure and consistency, I would like to keep the width of my bar graph to align with the tablix. Can anyone tell me how to accomplish this dynamically?Thanks,RexThu, 30 May 2013 14:57:25 GMTRexHelioshttp://www.sqlservercentral.com/Forums/Topic1457999-1633-1.aspxI have a report that returns a big result set (100s of pages long). Is there a way to make the first page generated (rendered) first for faster response time? The next page should be rendered only when the user tries to navigate to it. I am sure someone must have done this before. Can anyone shed some light on this, please?- RexWed, 29 May 2013 17:19:10 GMTRexHelioshttp://www.sqlservercentral.com/Forums/Topic1458131-1633-1.aspxHi All,I have a main report and ten sub reports and everything is working fine. If the data source of one sub report is down, then it displays error message for that sub report in the main report,Is there any means by which we cant handle the error message and show a custom error message in the main report??any help ?ThanksThu, 30 May 2013 05:08:04 GMTvar05http://www.sqlservercentral.com/Forums/Topic1457609-1633-1.aspxI've created a report that shows number of visits to our websites by month. However, its not showing the month names for each month, only january, november and january. How do I show every month number? There is visits(data) every month since Jan 2011 to present. I've included screen shots to help explainWed, 29 May 2013 02:24:07 GMTkelster101757http://www.sqlservercentral.com/Forums/Topic1457630-1633-1.aspxHi, I'm wondering if anybody has any tips on increasing load speeds in report files that use numerous datasets. I am aware of the following methods:1) Only query for the rows you need and no more2) Only query for the column you need and no more3) Order results in the report not in the query.4) Group the data in the query to reduce the number of rows being returned.Have you any other tips? Creating indices on parameter fields maybe? Thanks, DaveWed, 29 May 2013 03:34:11 GMTseatedElephanthttp://www.sqlservercentral.com/Forums/Topic1457256-1633-1.aspxHow do I email a report I've created in Reporting Service 2008 R2 every month to a email address. I have configured email settings in Reporting Services Configuration Manager. ThanksTue, 28 May 2013 04:31:00 GMTkelster101757http://www.sqlservercentral.com/Forums/Topic1457401-1633-1.aspxHey all,I have a matrix. It has qtrs and months along the top.It has a drillthrough; So when you click through - at the moment it always passes the month to the detail report.When the data is expanded to the month level - thats fine. But when its compressed to the Qtr level - then how do i get it to pass only the Qtr. Currently it passes the first month.Basically how do i know if its toggled or not....Any ideas? TIADanTue, 28 May 2013 09:30:14 GMTdanielfountainhttp://www.sqlservercentral.com/Forums/Topic1456010-1633-1.aspxHi, I have deployed the ssrs 2012 report to sharepoint 2010. I have problem Header Logo.If i remove the Header logo from report the report is getting executed. but if i kept the logo the report did not get executed.can any one help me in this?ThanksThu, 23 May 2013 07:40:17 GMTvax09http://www.sqlservercentral.com/Forums/Topic1456808-1633-1.aspxHI,I am working on SSRS Grouping with Subreport. I’m trying to get solution for the issue. But I'm unable to achieve it.Please let me know if anyone know regarding the below issue. Please find attached screen for more clarity. Thanks in advance.Regards,SaintMount.Sat, 25 May 2013 13:02:22 GMTSaintmount.Sqlhttp://www.sqlservercentral.com/Forums/Topic1456355-1633-1.aspxHello,I have previously been using SQL Reporting Service 2008 R2. We have now switched over to SQL Reporting Services 2012. When a user goes to run a report then go to save in Excel format, they get the message: Do you want to save or open [b]report.xlsl [/b]However in 2008, we get the message that users can save in xls format. Is it possible to default users to be able to save in xls format in SQL 2012 Reporting Services.RegardsFri, 24 May 2013 03:42:18 GMTdbman