InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 24 May 2012 10:47:55 GMT20http://www.sqlservercentral.com/Forums/Topic1305926-1063-1.aspxHi,I've got a report with 3 levels of data - Detail / Property level / City level. I have a parameter which determines how much data is expanded - 3 Options Expand All / Expand Groups / Expand None - So first option showsLondon 1 Some Street Detail Detail 1 Some Street Total 2 Some Street Detail Detail 2 Some Street TotalLondon Total2nd Option shows London 1 Some Street 1 Some Street Total 2 Some Street 2 Some Street TotalLondon Total3rd Option shows London London TotalSo I have set visibility on Detail group to an expression based on the parameter and set it to toggle from PropertyI have then set expression on property group again based on parameter and set toggle on CityAll works fine if I select All expanded - expand / Collapse works perfectly If I select Expand Groups / Expand None - Initially it looks fine and I can drill on city into property, but the drill down form property to details doesn't show any info.HELP - Am I missing something stupid here ?AndyThu, 24 May 2012 08:52:08 GMTAndy Reillyhttp://www.sqlservercentral.com/Forums/Topic1305476-1063-1.aspxAlright, short form, long story:I've been arguing with the fact that SSRS doesn't allow direct WYSIWYG development of a repeating section, so I've turned to heavy subreporting. After abusing my way out of newbie-ville, primarily driven by the fact that I haven't touched SSRS in over 2 years, I've come to the following dilemma.I have a 3 layer report setup. Layer 1 is the primary report and simply returns a list of the primary key as its dataset to send down to the subreport. Layer 1 has a table attached to this dataset and includes 3 columns during testing but will eventually just have one, the subreport.The two fields consisting of my key are returned from the procedure (all data being pulled is setup as procedures in the data tab) are INT and DATETIME, respectively. It's a repeated identity from the origination table and an audit date to isolate the row.Now, this ships down to the subreport both of these columns. After banging my head on SSRS 2k5 and its removal of milliseconds in datetime parameters/pieces (joy!) which screwed me up awhile, everything's working as strings in the parameters for the datetimes.This subreport has another subreport that goes and gets further data. Layer 1-2 is the part that's malfunctioning. I've got layer 2 and 3 behaving themselves and I can get Layer 2 and it's subreport (layer 3) to behave just fine when I enter in parameters directly for layer 2's preview.The values in question are 356949 for the INT and 2012-05-22 17:15:53.023 for the Datetime (string Parameter). This passes just fine from 2 to 3.I've confirmed that Layer 2's parameters (in Report-Report Parameters) are set as INT and String.Now, when I try to get Layer 1 to cough up the subreport, the parameters are failing the passthrough. I've even tried to directly input a set of parameters:[img]http://www.sqlservercentral.com/Forums/Attachment11594.aspx[/img]I'm at the end of my wits here with this thing, and am getting to more dakka territory. I've tried forcing the datetime through as a string with =FORMAT() and that flailed miserably. To wit, with those exact settings directly entered as parameters to Layer 2, Layer 2 and 3 work just fine. Layer 1 I cannot get to talk to it, I keep getting the error that we're not returning rows for the data set.As a tertiary note, if I default the values in the subreport, it'll appear just fine when called from Layer 1, but it will always use the default and not use the passed values. Not defaults in the parameter settings but when I originally set this up calling the proc as a string call with default values in the parameter pass in the data tab. (IE: EXEC proc @p1 = 123, @p2 = '1/1/1900' vs. just proc)Ignoring the fact that I'm going for beauty over optimization (I'm well aware I'm going to be calling a proc for EVERY row in Layer 2 and 3), what the heck am I doing wrong here?Wed, 23 May 2012 19:42:02 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1303279-1063-1.aspxHi - I've been tasked with trying to make an SSRS report make a noise when a value on a report is a certain value.I've written the following VB function.Public Function beep(ByVal value As String) As String If value = "foo" Then Microsoft.VisualBasic.Beep() End If Return valueEnd FunctionThen having the following expression to call the code=Code.Beep(Fields!bar.Value)Alas, this has been unsuccessful and I'm out of ideas - is this possible or should I give it up as a dead loss.Thanks in advanceStuartMon, 21 May 2012 04:45:54 GMTjohnstoshttp://www.sqlservercentral.com/Forums/Topic1302558-1063-1.aspxI have an SSRS 2008R2 report with cascading parameters (multi-select) where user selects one or more counties, then one or more cities, then one or more ZIPs from three sequential dropdown boxes.A text box at bottom of report lists counties, cities and ZIPs chosen so printed report will include identification of locations included in printed data, but the list of cities and ZIPs gets very long when all are selected.:unsure: Any way to have SSRS sense that [b]Select All[/b] has been checked in the dropdown list? If so, how would I modify my JOIN statement (below) to print the phrase [b]All ZIPs[/b] if Select All was checked, otherwise list individual ZIPs chosen?[code="plain"]=JOIN(Parameters!ZIP.Value,", ")[/code]Thanks! CarolFri, 18 May 2012 08:19:23 GMTchornunghttp://www.sqlservercentral.com/Forums/Topic1300178-1063-1.aspxI am developing a GRN label print report and have installed font 3 of 9 Barcode. I want the barcode for the product to be stretched so you stand a good chance of scanning it.How do I double or even treble the height of this field. Changing font size etc just increase the width as well as depth. I just want more depth.Tue, 15 May 2012 06:49:50 GMTKelvin Phayrehttp://www.sqlservercentral.com/Forums/Topic1299620-1063-1.aspxI have written a function and i want to use it in my SSRS report. The function retrieves data based on Item and a start and end dates. In SQL I have it working and in SSRS I have it working if I hard code the parameters to the select statement on the report. My dataset works is I do the below.Select * from Allocations (80830,'01/01/12','01/31/12') I now what the user to select the item (80830) and the dates. How can I add them in so they are dynamic? I am assuming it is possible.I am new to functions and a little lost.Any help is appreciated.Mon, 14 May 2012 08:59:04 GMTkjgreen1112http://www.sqlservercentral.com/Forums/Topic1299588-1063-1.aspxUsually when you set-up multiple report parameters within a report, you need to make a selection in each paramter on the live report to ensure it runs...Can you in any way set-up multiple parameters within a report, but you only need to make a selection in one parameter to run a report?I want to set-up mutliple parameters for the user to choose from, but i want the user to be able to just select one parameter and still have the ability to run the report?ThanksMon, 14 May 2012 08:14:10 GMT8-Bit Ninjahttp://www.sqlservercentral.com/Forums/Topic1297308-1063-1.aspxHi all, My query has data that drives off of @reportstart @reportend but for the same paramerters the dataset in the report runs blank, therefore available values are empty. What could be the reason. thanks.Wed, 09 May 2012 11:39:30 GMTSQL_pathhttp://www.sqlservercentral.com/Forums/Topic1296409-1063-1.aspxHello all, I have the following query in SSRS...SELECT a.JURISDICTIONCODE, a.NAME, to_number(REGEXP_REPLACE(REGEXP_REPLACE(b.value,'[a-zA-z]',''),',','')) as "Population"FROM DLSGATEWAY.JURISDICTION a, DLSGATEWAY.SE_DATA b, DLSGATEWAY.SE_DATA_VALUE_TYPE cWHERE b.JURISDICTION_CODE=a.JURISDICTIONCODE AND b.VALUE_TYPE_ID=c.VALUE_TYPE_ID AND c.VALUE_TYPE_ID = 51 AND b.YEAR = 2010and ((b.value BETWEEN(:MinPop)AND(:MaxPop))OR((:MinPop IS NULL)AND(:MaxPop IS NULL)))order by a.JURISDICTIONCODEAs you can see, I'm using a range population for b.value (or Population). The parameter works fine if BOTH parameter boxes (:MinPop) and (:MaxPop) have a value in them. Great. I want to know if there's a way to write this so that the user has the option to only use ONE of the parameter boxes, either :MinPop or :MaxPopThe above query is PART of this web report Im about to launch. However, if I can figure out the Population part, I can figure out the rest of my range parameters. https://dlsgateway.dor.state.ma.us/DLSReports/DLSReportViewer.aspx?ReportName=Comparative_Report&ReportTitle=Community+Comparison+ReportIf you can't click the link, cutting and pasting it into your browser works.Your time and consideration are much appreciated!MikeMany thanks!Tue, 08 May 2012 06:32:21 GMTmjbriggs03http://www.sqlservercentral.com/Forums/Topic1294759-1063-1.aspxHi,I need to create sub columns in a tabular report in SSRS 2005. My report should be something like below. I cannot use matrix reports as my report has dynamic columns and 3 levels of row grouping. Please can anyone let me know how to split a column into multiple sub-columns in a tabular SSRS reports.Name Vth Std VIth Std VIIth Std English Maths Science English Maths Science English Maths ScienceRam 90 80 70 70 90 80 80 70 90Regards,Ram.Thu, 03 May 2012 10:01:39 GMTramchaitanya.shttp://www.sqlservercentral.com/Forums/Topic1295068-1063-1.aspxHi All myself is karthi and i m new to SSRS.i need the Trend line for the follwing data in Line chart they are the follwing data.The following are the graph are my output and i need the trend line for these Key_gap value.This is the link I was refereed http://ericung.blogspot.in/2011/07/ssrs-2005-bar-chart-adding-linear-trend.html I need the same trend line for the Bar-Chart in SSRS 2005.Waiting for yours replyFri, 04 May 2012 00:47:32 GMTkarthi4021http://www.sqlservercentral.com/Forums/Topic1293697-1063-1.aspxHi, pls refer any sites or links to know "how to create dash board reports and kpi's using excel and sharepoint, thanksNiharikaWed, 02 May 2012 03:54:06 GMTniha.736http://www.sqlservercentral.com/Forums/Topic1291362-1063-1.aspxHello,We have table footer with the summary informations however that footer goes on to the new page? is there any propert i am missing to set ??ThanksFri, 27 Apr 2012 04:02:22 GMTConfickerhttp://www.sqlservercentral.com/Forums/Topic1285911-1063-1.aspxI have to create a report by using a stored procedure. stored procedure contains temporary table .At the time of creating a report. I am getting an error message : Invaild object name . Doesn't the report support the temporary tables. If so , is there any alternative way to create the report. I tried by using table variables .. my senior dba told me not use table variables .. Wed, 18 Apr 2012 12:15:27 GMTkodalisridevihttp://www.sqlservercentral.com/Forums/Topic759700-1063-1.aspxi am converting crystal report to SSRScrystal report has a report header and page header, how do i incorporate report header in SSRSSat, 25 Jul 2009 19:13:27 GMTkeywestfl9http://www.sqlservercentral.com/Forums/Topic1290955-1063-1.aspxHi,We're using SSRS 2008 R2and experiencing the problem with the report that contain subreport in it.We need to repeat Group header on every page, but when subreport prints on multiple pages it stops printing Group Header and resume printing only after subreport finishedI'm out of ideas to solve this.... Please help!!!!Thu, 26 Apr 2012 11:17:58 GMTmchernofhttp://www.sqlservercentral.com/Forums/Topic1290147-1063-1.aspxHi Everybody! :-)Having this strange problem with grouping in tabular report.The dataset looks like this:[b]--------------------------------------------SELECT customer_code, ship_to_code, address_name, contact_phone, dest_zone_code, territory_code, salesperson_code, stateFROM t1--------------------------------------------[/b]Trying to group by [b]territory_code[/b], and then by [b]state[/b]. And for some puzzling reason some of the states are listed more than once :crazy:Tried using wizard, adding groups manually, and got the same result in both cases.Any help would be greatly appreciated. Thank you for your time![img]http://i50.tinypic.com/212xxy1.jpg[/img][img]http://i49.tinypic.com/30t1mw9.jpg[/img]Wed, 25 Apr 2012 11:57:20 GMTjustforgroups2004http://www.sqlservercentral.com/Forums/Topic1287375-1063-1.aspxI'm in the process of creating a report to generate server rack elevations from an in-house asset management system, and am running into some report formatting issues I'm hoping there's a solution for. The data compiled for a page of the report looks like this (in part):[font="Courier New"]U__|__Host_Name__|__Manufacturer__|__Product_Name__|__Model__|__Part_#__|__S/N__|50_|_____________|________________|________________|_________|__________|_______|49_|_____________|________________|________________|_________|__________|_______|48_|_SERVER001___|Hewlett-Packard_|Proliant_Server_|_DL585___|413934-L21|721268Z|47_|_SERVER001___|Hewlett-Packard_|Proliant_Server_|_DL585___|413934-L21|721268Z|46_|_SERVER001___|Hewlett-Packard_|Proliant_Server_|_DL585___|413934-L21|721268Z|45_|_____________|________________|________________|_________|__________|_______|44_|_SERVER002___|Hewlett-Packard_|Proliant_Server_|_DL385___|438825-B21|365268Q|43_|_SERVER002___|Hewlett-Packard_|Proliant_Server_|_DL385___|438825-B21|365268Q|42_|_SERVER003___|Hewlett-Packard_|Proliant_Server_|_DL385___|438825-B21|314897V|41_|_SERVER003___|Hewlett-Packard_|Proliant_Server_|_DL385___|438825-B21|314897V|40_|_____________|________________|________________|_________|__________|_______|[/font]and I want it to appear in the report like this:[font="Courier New"]U__|__Host_Name______|__Manufacturer___|__Product_Name___|__Model__________|__Part_#_________|__S/N____________|50_|_________________|_________________|_________________|_________________|_________________|_________________|49_|_________________|_________________|_________________|_________________|_________________|_________________|48_|_SERVER001_______|Hewlett-Packard__|Proliant_Server__|_DL585___________|413934-L21_______|721268Z__________|47_|<blank/grey_fill>|<blank/grey_fill>|<blank/grey_fill>|<blank/grey_fill>|<blank/grey_fill>|<blank/grey_fill>|46_|<blank/grey_fill>|<blank/grey_fill>|<blank/grey_fill>|<blank/grey_fill>|<blank/grey_fill>|<blank/grey_fill>|45_|_________________|_________________|_________________|_________________|_________________|_________________|44_|_SERVER002_______|Hewlett-Packard__|Proliant_Server__|_DL385___________|438825-B21_______|365268Q__________|43_|<blank/grey_fill>|<blank/grey_fill>|<blank/grey_fill>|<blank/grey_fill>|<blank/grey_fill>|<blank/grey_fill>|42_|_SERVER003_______|Hewlett-Packard__|Proliant_Server__|_DL385___________|438825-B21_______|314897V__________|41_|<blank/grey_fill>|<blank/grey_fill>|<blank/grey_fill>|<blank/grey_fill>|<blank/grey_fill>|<blank/grey_fill>|40_|_________________|_________________|_________________|_________________|_________________|_________________|[/font]I've been able to get the Host Name field to show up correctly by using the "Hide Duplicates" property, but for the other fields (which will repeat on multiple servers), I can't do that. I tried getting around it by using "=(Previous(Fields!HostName.Value) = (Fields!HostName.Value))" as a formula to control the Visibility attribute on the other fields, which does hide the data correctly, but also hides the formatting (which "Hide Duplicates" doesn't). The grey fill is set for the Host Name field with "=IIf(Fields!HostName.Value = "","White",IIf((Previous(Fields!HostName.Value) = (Fields!HostName.Value)),"DimGray","White"))" as the formula for BackgoundColor, but that only works if I use the "Hide Duplicates" property; if I use the formula for the Visibility attribute, it hides all of the formatting.Is there any way to copy formatting from one textbox in a table to others in the same row (or is there another way to skin this cat that I don't know of)? The intent is to export this information into a spreadsheet that a team in the data center can use when auditing the contents of the racks (and make corrections on if necessary). I want to show which rows in the rack are filled (thus greyed out) but not repeat the data for all of the rows the server takes up.Thanks in advance for any help.JoshuaFri, 20 Apr 2012 11:37:24 GMTjmoldoverhttp://www.sqlservercentral.com/Forums/Topic1284533-1063-1.aspxI've mentioned here before that we've got a messed up SSRS 2005 environment in production. After struggling with it for a long time, we've decided to just abandon it. We've now got SSRS installed on another server, and users love being able to get reports they've not seen in years.We'd really love to move the reports off of the old server onto the new one, but try as we might we can't find where the .RDL files are. So, this leads me to ask, when you deploy a .RDL file to the ReportServer web service, where does it go?(We're working with SSRS 2005.)Mon, 16 Apr 2012 14:40:02 GMTRod at workhttp://www.sqlservercentral.com/Forums/Topic1273311-1063-1.aspxhi alli have an ssrs report, which is a dashboard type report with lots of sub reports off the charts etc.all was working fine when i had only only date parameter - Date from, now when i add a 2nd date parameter - Date To, i am having lots of issues..first example..i have a bar chart that has an action assigned > jump to report '2' with 3 parameters 1) user 2) date to 3) date from -- date to and date from both are assigned against the same database field called 'addeddate'report '2' also has these 3 parameters with a simple query [i]select (case when owneraccount is null then updatedby else owneraccount end)as owneraccount ,addeddate,Departmentname ,servicereqno,SubCategory,statefrom dbo.q_incidentswhere owneraccount = @owner and addeddate >= @date_fromand addeddate <= @Date_To[/i]when i click on the chart, report '2' opens, but the 'date to' parameter is getting passed the 'date_from' value eg i am getting date between ranges of 01/03/12 and 01/03/12i cant see why the date_to is getting the wrong date, is it getting mixed up due to being assigned the same database field?any ideas?cheersmalTue, 27 Mar 2012 03:19:25 GMTdopydbhttp://www.sqlservercentral.com/Forums/Topic1286195-1063-1.aspxHi;Team; Recently received a request from user to install MS SQL 2005 Business Intelligence Development Studio on client machine. user want to install client tool so that he can develop reports by connecting SQL Server.As we already have SQL Server 2005 enterprise edition and reporting services installed on server.Can anyone please confirm for the license cost for MS SQL 2005 Business Intelligence Development Studio (client tool), if it is a free tool and can be installed on many client machine please confirm as well.ThanksWed, 18 Apr 2012 23:58:44 GMTSQL_Helperhttp://www.sqlservercentral.com/Forums/Topic1284127-1063-1.aspxMy result set has 3 category of values.Collected.Not Collected.Delay by xyz mins.My Requirement is to set the green indicator on the status column for all the rows where value is collected, Yellow for Delay and Red for Not collected. I have added the indicators and it is allowing only numeric values but in my case i have defined data.Can any one throw some light in this regard.Also when i place my cursor on the status row where yellow color exists it should display that it is delayed by xyz mins.Regards,SandhyaMon, 16 Apr 2012 08:03:11 GMTsandhyarao49http://www.sqlservercentral.com/Forums/Topic1283180-1063-1.aspxHi All, thanks in advance for help. (Oops, just noticed I posted in 2005, but should not matter, Sorry)...I have SQL2008 Developer Edition Installed. For Visual Studio 2008, I have the following version on help/about. 9.0.0.30729 SP1.I am working with SSRS, building a report, studying for MCTS 70-448. I added a simple report based on a simple query (SELECT * FROM Sales.Customer), used wizard.When I right click on a text box and choose Text Box Properties, I am not getting the different options like General, Alignment, Interactive Sort, etc. The pane where those options should show up is blank.I don't know how to post the image.Is it b/c I only have the developer edition and thus have limited features?Thanks.JJFri, 13 Apr 2012 08:34:12 GMTJimiJetthttp://www.sqlservercentral.com/Forums/Topic1281410-1063-1.aspxHi, I am new to SSRS. Please provide me some links to go through how to create sub reports and explain it properly. I am stuck in between.Wed, 11 Apr 2012 04:04:33 GMTpradeep_agrawal02http://www.sqlservercentral.com/Forums/Topic1281411-1063-1.aspxHi, I am new to SSRS 2005. Please help me to find the tutorial for creating sub report. I am stuck in between. Please give some links to get the material which can explain the topic properly.Thanks,PradeepWed, 11 Apr 2012 04:10:26 GMTpradeep_agrawal02http://www.sqlservercentral.com/Forums/Topic1277754-1063-1.aspxI'm currently having issues writing a table report that shows the sum of the value in a group header in an enclosing group's header.I'm trying to display a table that shows time spent assisting customers at different companies, with the company's budget, and budget for all companies in each region.This is what I'm aiming at [img]http://i40.tinypic.com/23w8weg.jpg[/img]This is what I have currently (note the Region budget line is wrong - it should sum the company budget header, but is summing it for each customer)[img]http://i43.tinypic.com/1h836v.png[/img]Here's the SQL query[code="sql"]select client_customer.hours , client_customer.name , client_company.name as com_name , client_company.budget , region.namefrom client_customer inner join client_company on client_customer.company_id = client_company.company_id inner join region on client_company.region_id = region.region_id[/code]Here's the SQL result[code]hours | name | com_name | budget | region12 | J. Doe | Company 1 | 5000 | Region 143.5 | S. Neil | Company 1 | 5000 | Region 112 | A. Smith | Company 2 | 2000 | Region 14 | C. Rodgers | Company 2 | 2000 | Region 154 | K. Smith | Company 2 | 2000 | Region 1[/code]I'm creating the table currently by using [i]=First(Fields!budget.value)[/i] in the company header, then [i]=Sum(Fields!budget.value)[/i] in the region header.I'm sure the issue is that it's summing the budget for each company as many times as customers at each company, is there a best practice way of summing a group header that I could use above? I don't know what to put in the region budget field (I've thought of [i]=Sum(First(Fields!budget.value))[/i] and [i]=Sum(ReportItems!CompanyHeaderText.Value)[/i])It would be great to get advice on how to properly go about the above.Tue, 03 Apr 2012 19:48:31 GMTsqlservercentral.com 32358http://www.sqlservercentral.com/Forums/Topic1280727-1063-1.aspxI am using the below script to generate a market sales report that displays YTD, LYYTD, and YTD VAR.Now the powers that be want me to include a date parameter.How do I include a date parameter with out affecting my formula for my LYYTD (Last Year Year To Date)Thanks[code="sql"]SELECT cmp_name, sct_code, ord_no, tot_sls_amt, inv_dt, mfg_loc, orig_ord_type, Sector, LYYTD, YTD, YTDCredits, YTDQuotes, (YTD-(((YTDCredits*2))+YTDQuotes)) as GrandTotalYTDFROM(SELECT "cicmpy"."cmp_name", RTrim(Convert(Varchar(7),sct_code)) as sct_code, "oehdrhst_sql"."ord_no","oehdrhst_sql"."tot_sls_amt", "oehdrhst_sql"."inv_dt", "oehdrhst_sql"."mfg_loc", "oehdrhst_sql"."orig_ord_type", (Case when sct_code = '01' THEN 'Pool' when sct_code = '02' THEN 'Water Conditioning' when sct_code = '03' THEN 'Water Treatment' when sct_code = '04' THEN 'Animal Health' when sct_code = '05' THEN 'Car Wash' when sct_code = '06' THEN 'Industrial' when sct_code = '07' THEN 'Municipal' when sct_code = '08' THEN 'Food & Beverage' when sct_code = '09' THEN 'Irrigation' when sct_code = '10' THEN 'Metal Finishing' when sct_code = '11' THEN 'Institutional' when sct_code = '12' THEN 'Specialty' ELSE 'Misc.*' END) as Sector,(SELECT SUM(tot_sls_amt) AS Expr1 FROM oehdrhst_sql Subquery1 WHERE Subquery1.ID = oehdrhst_sql.ID and inv_dt between dbo.DateSerial(YEAR(GETDATE())-1,1,1) and dbo.dateserial(YEAR(GETDATE())-1,MONTH(GETDATE()),DAY(GETDATE()))) LYYTD,(SELECT ISNULL(SUM(tot_sls_amt),0) AS Expr2 FROM oehdrhst_sql Subquery2 WHERE Subquery2.ID = oehdrhst_sql.ID and YEAR(inv_dt) = YEAR(GETDATE())) YTD,(SELECT ISNULL(SUM(tot_sls_amt),0) AS Expr3 FROM oehdrhst_sql Subquery3 WHERE Subquery3.ID = oehdrhst_sql.ID and YEAR(inv_dt) = YEAR(GETDATE()) and orig_ord_type = 'C') YTDCredits,(SELECT ISNULL(SUM(tot_sls_amt),0) AS Expr4 FROM oehdrhst_sql Subquery4 WHERE Subquery4.ID = oehdrhst_sql.ID and YEAR(inv_dt) = YEAR(GETDATE()) and orig_ord_type = 'Q') YTDQuotes FROM "001"."dbo"."oehdrhst_sql" "oehdrhst_sql" INNER JOIN "001"."dbo"."cicmpy" "cicmpy" ON "oehdrhst_sql"."cus_no"="cicmpy"."cmp_code"WHERE ("oehdrhst_sql"."mfg_loc"='JX' OR "oehdrhst_sql"."mfg_loc"='KC' OR "oehdrhst_sql"."mfg_loc"='NY' OR "oehdrhst_sql"."mfg_loc"='RP') AND (Year("oehdrhst_sql"."inv_dt")>=YEAR(GETDATE())-1)) as AUX[/code]Tue, 10 Apr 2012 06:31:27 GMTabernuthttp://www.sqlservercentral.com/Forums/Topic593245-1063-1.aspxHi,I have generated the report and looks fine. But while printing the QA Smile wants the report headers(TABLE HEADERS) need to be print on each page. I can get this by doing some format in EXCEL, but that should be done using SSRS. Is this feature present in SSRS to make the table header showup on each page when printing.Any help would be greatly appreciated.Thanks,SumanTue, 28 Oct 2008 14:06:56 GMTrayabharapusumanhttp://www.sqlservercentral.com/Forums/Topic1280199-1063-1.aspxHello all, I'll try to make this short. I have several field reps assigned to several communities. So the data looks like this...Community Field Rep Boston WilliamsMelrose WilliamsMalden RileyCambridge FranklinLexington RileyReading FranklinStoneham WilliamsWakefield RileySo I set up my parameter and navigate to the Preview pane. When I click the drop-down, I see Williams three times, Riley three times, and Franklin twice. I only want to see the names ONCE. Any tips?Mon, 09 Apr 2012 09:24:09 GMTmjbriggs03http://www.sqlservercentral.com/Forums/Topic1278107-1063-1.aspxHello friends,I am working the SSRS 2005 Matrix report. My requirement is, I have to display the Avg of one column in the Total Field instead of Sum of all columns.Please provide me some solution.Thanks in Advance.Wed, 04 Apr 2012 08:23:16 GMTsandy1234http://www.sqlservercentral.com/Forums/Topic1272798-1063-1.aspxI created a schema I called MgmtRpt, so that I could group stored procedures related to reports for management into it. Then I created some stored procedures in that schema, tested them, and everything works fine.Now another developer wants to be able to write either a SSRS report, or a Visual Studio 2010 report application, against those stored procedures, so the managers can have their reports on demand. Looks like a win-win all around.Only problem, SSRS doesn't see my new MgmtRpt schema. Nor does VS 2010. Why is that? What have I left out? If SSRS (or VS 2010) isn't capable of seeing anything other than dbo, then what's the point of schemas?Mon, 26 Mar 2012 09:44:18 GMTRod at workhttp://www.sqlservercentral.com/Forums/Topic1273528-1063-1.aspxHI All , Below is my script:USE [DevSalesSecondDrawer]GO/****** Object: StoredProcedure [Neg].[UsersCreateStaticsGet] Script Date: 03/27/2012 09:47:59 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER Procedure [Neg].[UsersCreateStaticsGet](@UserID typeSalesID =null,@StartDate date ,@EndDate date)AsBegin if LEN(@UserID) = 0 set @UserID = null Select OwnerID,COUNT(*) as JobsCreatedinto #jobsCountfrom Neg.jobswhere (OwnerID = @UserID or @UserID is null ) and (CAST(CONVERT(CHAR(10),CreatedDate,120) as DateTime)between CAST (CONVERT (CHAR(10),@StartDate,120)as Date) and CAST (CONVERT(CHAR(10),@EndDate,120)as Date))group by OwnerIDselect AlternateOwnerID as OwnerID, COUNT(*) as AlternatesCreatedinto #AlternatesCountFrom Neg.Alternates where (AlternateOwnerID = @UserID or @UserID is null ) and (CAST(CONVERT(CHAR(10),CreatedDate,120) as DateTime)between CAST (CONVERT(CHAR(10), @StartDate,120)as Date) and CAST( CONVERT (CHAR(10), @EndDate,120)as Date))group by AlternateOwnerIDselect CreatedByUser as OwnerID , COUNT(*) as ItemsCreatedinto #ItemsCountFrom neg.Items where (CreatedByUser = @UserID or @UserID is null ) and (CAST (CONVERT(CHAR(10),CreatedDate,120) as DateTime )between CAST(CONVERT (CHAR(10),@Startdate,120) AS Date) and CAST(CONVERT (CHAR(10),@EndDate,120)as Date))group by CreatedByUser--Select * from #JobsCount--Select * from #ItemsCount--Select * from #alternatesCountSelect ISNUll (ISNULL(j.OwnerID, a.OwnerID), i.OwnerID )as OwnerID ,j.JobsCreated,a.AlternatesCreated,i.ItemsCreatedfrom #JobsCount Jfull outer join #alternatesCount aon j.OwnerID=a.OwnerIDfull outer join #ItemsCount ion a.OwnerID=i.OwnerID--drop table #JobsCount--drop table #Itemscount--drop table #alternatesCountEndthe above stored procedure is running fine SSMS.But , When I am running from the reports. I am getting the error message. Implicit conversion from data type sql_variant to date is not allowed. Use the CONVERT function to run this query. (.Net SqlClient Data Provider)Please help with this.Tue, 27 Mar 2012 08:32:06 GMTkodalisridevihttp://www.sqlservercentral.com/Forums/Topic1273370-1063-1.aspxI am trying to prepare a Win2K 2003 server edition as Reporting Server for 2005 SQL Server.http://localhost/Reports/throws this error:"The report server configuration file specifies localhost for the Web server name. The report server requires that the computer name be specified instead."Some sites suggested changing the RSWebApplication.config to remove localhost.and then I get this error."The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version."The Report Server seems to be behaving okay and I can Deploy Reports from Visual Studio.http://localhost/ReportServerlocalhost/ReportServer - /--------------------------------------------------------------------------------Microsoft SQL Server Reporting Services Version 9.00.1406.00 The current RSWebApplication.config file contains this:<Configuration><UI><ReportServerUrl>http://localhost/reportserver</ReportServerUrl><ReportServerVirtualDirectory></ReportServerVirtualDirectory><ReportBuilderTrustLevel>FullTrust</ReportBuilderTrustLevel></UI>I have tried many things so far and have searched Google a lot so far for any possible one solution that can work.Can someone guide me please to get this thing working?Thanks,BijTue, 27 Mar 2012 04:46:17 GMTkotharibijhttp://www.sqlservercentral.com/Forums/Topic1272956-1063-1.aspxI have a report that is run from a data driven schedule that can generate 50,000 to 60,000 lines and an excel spreadsheet for each report. There are about 750 report schedule records, each one generating a separate report and excel file. Quite a few reports fail to generate due to an out of memory exception. When I set the schedule file that the report uses to generate the reports to only have one of the failed reports, it generates fine. I see in the ReportServerService log "out of memory" failures for this report during the scheduled run. Just some. When theses are run separately they generate fine. This is the second of a series of report jobs like this. The job preceding this job finishes getting the excel files to the appropriate folder before the job I am talking about starts, even though the job step shows it starts at 13:46 the first entry in the log file is at 17:11. it looks like it loads 16 items in an internal queue at a time. Then we start getting intermittent "out of memory" messages.It looks to me like SQL2005 SSRS is not quite up to industrial strength. Any ideas? I can't go directly to Excel files as these reports generate hidden summary items. So these are multi-level reports.Mon, 26 Mar 2012 13:50:30 GMTjnichols-797753http://www.sqlservercentral.com/Forums/Topic1269802-1063-1.aspxI have a stored procedure that takes about 20 seconds to run when I run it in SQL Server Management Studio. There are 2 temporary tables in this stored procedure. When I run the report that calls this stored procedure the report takes about 15 minutes to return.When I looked at the SQL profiler when the report is run I see an exec sp_reset_connection every 4-6 seconds during this 15 minute wait. When the stored procedure/report finally runs the profiler shows a duration of 987854 for the stored procedure while the profiler duration for the stored procedure run directly through SSMS is 4600.Any thoughts?Tue, 20 Mar 2012 12:59:27 GMTaberhttp://www.sqlservercentral.com/Forums/Topic1270743-1063-1.aspxHiI've tried to research this for quite a few hours without much success, so hopefully someone here will have come across it before, or have experience to point me in the right direction.I've built a SSRS report that runs off a stored proc with a series of parameters. The parameters say what columns the stored proc will return, so I have 2 tables in the SSRS layout, each with slightly different columns. Depending on the parameters used to call the SProc, the report will hide one of the tables, this is set with an expression in the visibility.hidden property for the tables:[code="vb"]=IIF(Parameters!param1.value = "returnSet1", FALSE, TRUE)[/code]Now when exporting, it renders an xls file no problem, but for a csv it obviously outputs everything. I know this is by design but I'm thinking about a way around it. I've noticed that by setting the [b]visibility.hidden[/b] property to [b]true[/b] on a [i]column[/i] (not a table), it hides it from the csv output, and if I try to use an expression in this property it automatically defaults to TRUE, no matter what the expression says [b](anything except FALSE will automatically = TRUE)[/b].I'm thinking there might a way to use the custom code to set the property to either TRUE or FALSE in visibility.hidden, so the property does not have a formula in it, it is being set by a seperate function that is called elsewhere. So for example on the layout tab, we would go to [i]Report > Report Properties[/i], then click on the [i]Code [/i]tab, put a VB function in here to set the visibility.hidden property on each of the columns, and call this function from elsewhere in the report.I've never really used the custom code feature before, except for some dynamic colours which is well documented online, so if what I'm thinking is simply not possible then please let me know. Any help/input is welcome and appreciated :]EDIT: Sorry I forgot to include the main question I was getting at, if it is possible to set property values in VB from the custom code, what is the syntax to set the property? NOTE: I don't want to simply return TRUE or FALSE, hence only updating the property that has called the VB function, I want to set the property of other objects and other properties in the report, so it can be called from another property, and update each column's [i]visibility.hidden[/i], all from within the one function. Hope that makes senseThu, 22 Mar 2012 05:12:18 GMTrosssshttp://www.sqlservercentral.com/Forums/Topic1269241-1063-1.aspxHi All,I'm trying to trouble-shoot this event. I have a SQL 2005 report that uses a single datetime parameter. There is a query in the report that takes GetDate() and then grabs the Previous Business Day from a calendar table. That works fine and loads quickly when the report renders.However, if the user wants to look at a different date, the calendar control does not operate properly. Instead of displaying dates, the screen flashes as if the whole page was being re-loaded and then the report refreshes showing the same default date and associated data. Here's the thing: After several minutes, the calendar control becomes active and operates properly! :w00t: There is no problem with the speed of loading the report data, it is only the operation of the control that is wubby. Does anyone have any ideas what is causing this? I am running SQL 2005 64 bit Enterprise Manager SP3.Thanks,ElliottMon, 19 Mar 2012 15:05:07 GMTElliott Berkihiserhttp://www.sqlservercentral.com/Forums/Topic1269237-1063-1.aspxI have a report that contains 6 parameters:Start Date - DateTime|No available values|Default =DATEADD("D",-30, Today())End Date - DateTime|No available values|Default =Today()Store - String|Stored Procedure for available values|Default = NULLSOS - String|Stored Procedure for available values|Default = NULLCode - String|Stored Procedure for available values|Multi-value|Default = NULLPart# - String|No available values|Default = NULLWhenever a user changes the Start Date, for example, I'm able to see through SQL Profiler that the stored procedures for Store, SOS, and Code are no executed and the screen will flash when the data is brought back. Also, when the user finally has all of the parameters filled in and clicks on the View Report button I see, again through SQL Profiler, that the stored procedures for Store, SOS, and Code are actually executed 2 times before the stored procedure for the main report is run.Can anyone explain why this is happening? I've been reading a bit about cascading parameters and not sure if this is something from that or not as I'm not too familiar with it. Or something else altogether?Mon, 19 Mar 2012 14:58:00 GMTaberhttp://www.sqlservercentral.com/Forums/Topic959931-1063-1.aspxI am trying to implement a connection between two reports - the first being a pie chart and the second is a textual report.I have set up an action on my values in the pie chart to jump to a given report and configured my parameters. For one of my parameters if I have a value other than NULL the initial rendering of the pie chart works perfectly but as soon as I move the mouse on the rendered pane (this is just using preview in SSRS) the pane is replace with just the message "Parameter Is Not Valid".If I disabled the action the report works perfectly. I've made sure my data types match. I've manually run the query that both reports would use with no problem.I've even recreated my first report with a new pie chart & as soon as I add my problem parameter to the report & the action it all starts to fall over again.I've spent numerous hours trying everything I can think of but I'm completely stumped as to why I have a problem - I'd be most grateful for any pointers!I've also just tried setting the parameter on the action to a specific value - that works perfectly all the time the parameter in the parameter block remains null, when it changes to non null the report fails again - I've also tried changing the parameter name just in case of some hidden oddity with no change.ThanksWed, 28 Jul 2010 04:29:15 GMTBalancehttp://www.sqlservercentral.com/Forums/Topic1263123-1063-1.aspxI am adding a column to an existing report that would show sparkline based on 6 columns values in each row. I am trying to crate the line charts but not getting anywhere. here is example of data:Period01, Period02, Period03,......Period0650, -10, 0,......3020, 5, -1,......33I want to add a column to house sparkline in the detail section.I am using ssrs 2k5TIA.kr.Wed, 07 Mar 2012 09:54:27 GMTrehman-615909