InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 24 May 2012 11:46:59 GMT20http://www.sqlservercentral.com/Forums/Topic1306029-1633-1.aspxWe created a subscription to deliver a report in excel format (File share delivery) programatically from a web application (Asp.Net/C3). From report manager url we could see the entry of the subscription from web app, however, at the scheduled time, the delivery fails with the error message - [i]"A log on error occurred when attempting to access the file share. The user account or password is not valid"[/i]. Now, when we try to create a subscription from report manager url and making use of same UserName and Password and same file share which we used in our web app, it sucessfully delivers the report rendered in excel to the file share mentioned.Do we need to add any thing new when we create subscription from Web App!?Appreciate your help!ThanksThu, 24 May 2012 11:35:46 GMTsvrprakashhttp://www.sqlservercentral.com/Forums/Topic1305918-1633-1.aspxIn a new SSRS 2008 r2 report that I just created, I would like to have a page header on each page of the report. I am not referring to repeating column headers for a tablix. The tablix is working fine. I would like to the following header repeat on each page. The header would look like the following: report title subreport title tablix table.Can you tell me how to accomplish this goal?Thu, 24 May 2012 08:47:16 GMTwendy elizabethhttp://www.sqlservercentral.com/Forums/Topic1305928-1633-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 StreetDetailDetail1 Some Street Total2 Some StreetDetailDetail2 Some Street TotalLondon Total2nd Option shows London 1 Some Street1 Some Street Total2 Some Street2 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:52 GMTAndy Reillyhttp://www.sqlservercentral.com/Forums/Topic1305784-1633-1.aspxI am using SSRS 2008 r2. I have a report with multiple parameters and I want to display a message for the end users prior to inputting parameter criteria. Can this be done with Custom Code under Report Properties and does anyone have an example they can share?Thu, 24 May 2012 06:45:15 GMTDonald Callahanhttp://www.sqlservercentral.com/Forums/Topic1303737-1633-1.aspxI am wondering if SSRS 2008 R2 has the ability to "focus" on groups when drilling, or to hide the groups that were not expanded. As an example, I will show enrollment in a list of courses at a university, which rollup to departments, which rollup to colleges. +College of Arts 1234+College of Sciences 2345+College of Engineering 3456Then I want to expand the College of Sciences and hide all the other colleges data.+College of Arts +College of Sciences 2345 +Biology 987 +Chemistry 234 +Physics 456+College of Engineering Then I want to expand the Chemistry department and hide all the other departments.+College of Arts +College of Sciences 2345 +Biology +Chemistry 234 +CHEM100 99 +CHEM200 88 +CHEM300 47 +Physics +College of Engineering I know this may require complex logic in the hide/show expressions, so any help would be appreciated. MikeMon, 21 May 2012 14:46:27 GMTmikeg13http://www.sqlservercentral.com/Forums/Topic1304453-1633-1.aspxhi, i am showing my pie chart label outside bu custom properties ->PieLabelStyle->outside its showing in design surface,but when i run report it still showing inside. what other properties do i need to set up?Tue, 22 May 2012 13:23:29 GMTriya_davehttp://www.sqlservercentral.com/Forums/Topic1298040-1633-1.aspxGood Day Everyone,One of my customers came to me with the following request to be dealt with using SSRS: presenting on the same column chart: a.) aggregated data for the current year, broken down per monthb.) consolidated data for the last four full years.Here is a representation of the actual goal, created with MS-Excel (cf. image attached)[img]http://www.sqlservercentral.com/Forums/Attachment11485.aspx[/img]Has anyone come across such a request? I suspect this would not be achievable as mixing grouped and ungrouped data on the X-axis seems nowhere to be found.Thank you for your feedbacks folks !Thu, 10 May 2012 10:43:23 GMTdonbuzhttp://www.sqlservercentral.com/Forums/Topic1303599-1633-1.aspxhii am getting extra 1 blank page in print layout.i am seeing data on first ,then second blank and third i have data.how to avoid this page?Mon, 21 May 2012 12:36:33 GMTriya_davehttp://www.sqlservercentral.com/Forums/Topic1161339-1633-1.aspxWhen exporting a report to a csv I am getting three control characters appended to the beginning of the file. the characters are:[code="other"][/code]Now here is where it gets stranger. If you open the file in a text editor everything looks fine, however, when it is opened in Excel you see the characters in the first field. It also causes the Access import utility to choke.The intended use of the file is for our Accounting department to have something to upload into a Hyperion session for our corporate office, and I would like to make it as easy as possible for them.We are running SQL 2008 (not R2)ThanksI attached a sample of what is outputWed, 17 Aug 2011 11:41:33 GMTMicah Ritchiehttp://www.sqlservercentral.com/Forums/Topic1304022-1633-1.aspxHello All,When I put visibility expression on a tablix I noticed that I cannot see the actual number of pages on report viewer (pagination). It just shows Page 1 of 1. If I remove visibility expression then I can see Page 1 of 9. It is happening on both, 2005 and 2008. Is this a bug?Tue, 22 May 2012 04:25:59 GMTdbmehtahttp://www.sqlservercentral.com/Forums/Topic1303309-1633-1.aspxin ms reporting services how & where do i get Print Layout button so that all the 3 columns of my report can be viisbleas currently inspite of layout having 3 columns only one column is displayedi am not getting the Print Layout button anyhwere in reporti am using sql server 2008Mon, 21 May 2012 05:33:47 GMTssurekha2000http://www.sqlservercentral.com/Forums/Topic1303811-1633-1.aspxI want to be able to open the report builder 3.0 files on the report server (sql server 2008 r2) using the bids environment located on my workstation. I prefer to use bids (visual studio.net 2008 for ssrs reports) since you can drag and drop items from the toolbox in visual studio onto the designer surface. I also would like to use the 'properties' for various SSRS objects since I think the way to access these properties is easier than using report builder.Thus can you tell me how to open report builder 3.0 files that are located report server using bids (visual studio.net) on my workstation?Mon, 21 May 2012 20:45:01 GMTwendy elizabethhttp://www.sqlservercentral.com/Forums/Topic1303114-1633-1.aspxEvery time I try to launch ReportBuilder, it promptsme for installing .Net Framework 3.5...I download it and launch the exe but nothing seems to happen :-(I already have .Net Framework 4 installed (as well as .NetCompact Framework 3.5 and .Net Compact Framework 2.0 sp2).Any idea?I'd love to test that report builder... :-)Sun, 20 May 2012 14:38:28 GMTEric Mamethttp://www.sqlservercentral.com/Forums/Topic1301825-1633-1.aspxhiihave 1 textbox, in which i need to get value like filed1.value + field2.valuebut i am just getting first field valuehow to add two field in expression,i tried sum(field.value + field.value)nothing worked.Thu, 17 May 2012 08:44:33 GMTriya_davehttp://www.sqlservercentral.com/Forums/Topic1303253-1633-1.aspxI have a requirement where in i want to show the commulative sum till only this month I have created cummulative sum using runningvalues in expressions.Now I dont want to show the graph for next month i.e after may as we dont have completions for those months yet. Any help please?Mon, 21 May 2012 04:00:29 GMTvinay4444http://www.sqlservercentral.com/Forums/Topic1302856-1633-1.aspxI've got an interesting question. When I build a regular report, and populate it, report comes back with 19 pages or so for one client. However, when I want to turn it into a drill down report, and have grouping, it turns every group into a single page. This would be fine in most cases, however it turns 19 pages into one really long one. Is it possible to have drill down reports and preserve amount of pages when it expands, or am I stuck with one group per page, regardless of how many rows it returns.Fri, 18 May 2012 15:15:23 GMTDVSQLhttp://www.sqlservercentral.com/Forums/Topic1302806-1633-1.aspxI have obtained a 'line' report item from the toolbox in SSRS 2008 r2 by following the directions from the following url: http://msdn.microsoft.com/en-us/library/ms159128(v=sql.100) This link is called 'Add and Modify a Line (Report Builder and SSRS)'. I placed the 'line' report item in extra rows I have setup between groups on the SSRS report. I have been able to change the color and width of the line by clicking on the line and working with its properties However, the line is 'jagged' and I want the line to be 'straight'. I have tried changing the endpoint values and that has not worked. **Note: I have even tried coming up with an extra row on the report in the 'group' section and making the row as narrow as possible. However, I think this line is too wide. Thus can you tell me how to make this line straight? If not this way, can you tell me how to 'draw the staight line.Fri, 18 May 2012 13:39:20 GMTwendy elizabethhttp://www.sqlservercentral.com/Forums/Topic1291463-1633-1.aspxI have a table with one row group and one column group. I would like to be able concatenate all of the values in the grouped cell. If I use Count(Fields!AttrValue.Value) I am returned with the correct number of values for each of the cells in my grouping. I was hoping to take that to the next step and get the all of the values that were counted, something like ConCat(Fields!AttrValue.Value,”|”)I have been told that you cannot aggregate values across both a row group and a column group in a single expression. OK how do I do it in more than one expression :)I have tried to use the RunningValue function along with some custom code but this function doesn’t work right when using both the row and column groups.Is there a way to concatenate these values?Fri, 27 Apr 2012 06:37:46 GMTslpgmahttp://www.sqlservercentral.com/Forums/Topic1302973-1633-1.aspxDoes anyone knows a good place to find sql server samples to generate Actuaries or the so called Financial Triangles?I'm trying to do a report using report builder but it seems that i can't find any info anywhere. I don't want to use excel i want to keep it very restricted to SSRS or Report Builder.thank you:w00t:Fri, 18 May 2012 23:26:51 GMTpeseta30http://www.sqlservercentral.com/Forums/Topic1303113-1633-1.aspxI am trying to create a small report in SSRS 2008R2 (my first report with that version) and I keep getting the error "The report preview failed because the report could not be built. Read the errors, warnings, and messages in the Error List window for specific build failures".I tried building a simple report using the wizard but I get the same error msg.However, I can deploy the reports and they work fine?!SSRS 2005 is happy...Any idea?ThanksSun, 20 May 2012 14:08:59 GMTEric Mamethttp://www.sqlservercentral.com/Forums/Topic1303082-1633-1.aspxHello... I no clue about this URL authentication, I am new to this RSWhen trying to access URL http://localhost/ReportServer through User Account ( Administrator account ), I am given a pop window to authenticate, where it asks me to pass in Login and password details. But I have no Password enabled on User account(this is a administrator account) accessing this.Please let me know, if there is any thing I to figure out... I am tired with this MStools, these are not coll at all.Sat, 19 May 2012 23:41:47 GMTquillis131http://www.sqlservercentral.com/Forums/Topic1302853-1633-1.aspxHello -For the last two days I have been struggling to figure out how to divide two cells in a Matrix. Maybe this is the wrong way to think about it....another way of saying it...dividing a conditional total sum by a total row sum from the data set. Here is where I am at:I have the following matrix built:[URL=http://imageshack.us/photo/my-images/707/testmatrixdesignview.png/][IMG]http://img707.imageshack.us/img707/4646/testmatrixdesignview.th.png[/IMG][/URL]And here are the results:[URL=http://imageshack.us/photo/my-images/716/testmatrixpreview.png/][IMG]http://img716.imageshack.us/img716/9021/testmatrixpreview.th.png[/IMG][/URL]I need to build an expression to populate the G% column correctly, which is simply (SUM(Sales) WHERE Indicator = "G")/SUM(Sales). So in this example it would be the following calculations:23,386 / 28,574 = 81.84%23,602 / 28,810 = 81.92%11,839 / 16,025 = 73.88%12,476 / 16,837 = 74.10%12,023 / 15,974 = 74.27%So far my expression looks like this: =(IIF(Trim(Fields!Indicator.Value) = "G",Fields!Sales.Value, 0)/SUM(Fields!Sales.Value))But it is obviously returning 0.00% for all fields. Another funky thing is if I change the expression to look for Indicator.Value = "B" it seems to work just fine:[URL=http://imageshack.us/photo/my-images/521/testmatrixpreviewb.png/][IMG]http://img521.imageshack.us/img521/4760/testmatrixpreviewb.png[/IMG][/URL]Any help would be greatly appreciated!Fri, 18 May 2012 15:08:41 GMTTrialityhttp://www.sqlservercentral.com/Forums/Topic1302869-1633-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 15:40:44 GMTchornunghttp://www.sqlservercentral.com/Forums/Topic1302990-1633-1.aspxhttp://p2p.wrox.com/content/articles/creating-mailing-labels-sql-server-reporting-serviceswith reference to above i have created mailing labels of 3 columns.the desgin time appears same as shown in the linkbut when i execute i dont get any records in 2nd and 3rd column all the records gets displayed only in first column & if n\more then it goes to page 22)i have created the mailing label excetly as givenfirst put a table & in the details cell added a rectangle & in that added the fieldsif the content length is more than it appears out of teh rectangleSat, 19 May 2012 01:18:27 GMTssurekha2000http://www.sqlservercentral.com/Forums/Topic1302854-1633-1.aspxHi all,I have a chart report. I had created a drill down sub report inside this chart report, such that if any we click on the "series label data(bars)" then we will be redirected to sub report. I have bars as weeks(like week1,week2,.....) inside this chart report. My question : How can i pass (label data)bar value(weekofyear) as a parameter to the sub-report. we can say that (#VALX = weekofyear).I had tried passing label data(#VALX) as parameter to the subreport but i am seeing #VALX as parameter value in the sub-report but not week1. I want 'weekofyear' which is the label data value for the bar which we have clicked.Please help meFri, 18 May 2012 15:13:18 GMTtinnusqlserverhttp://www.sqlservercentral.com/Forums/Topic1302795-1633-1.aspxhi,i am getting this errorThe Value expression for the text box ‘txtReportDate’ refers to the report item ‘ReportDate’. Report item expressions in a Page Header or Footer can only refer to report items within the same ReportSection. Letters in the names of report items must use the correct case.Fri, 18 May 2012 13:27:52 GMTriya_davehttp://www.sqlservercentral.com/Forums/Topic1302341-1633-1.aspxHi all,I have a report where the user can select year values (for ex 2008, 2009, 2010, 2011). I also have a chart where I only want to show only the last 3 values of the years against a measure, therefore showing 2009 - 2011.. Is there a way to filter using a range of values? If not any workaround?Thanks in advance!JonFri, 18 May 2012 02:51:49 GMTJonathan Malliahttp://www.sqlservercentral.com/Forums/Topic1300896-1633-1.aspxSSRS 2008 - using windows autentication I have a confidential report which I have set up as "Credentials supplied by the user running the report" to force a login. The report then uses the USer!userid collection to display information for this user, but the problem is that the user!userid is getting the windows login id rather than the login id used when running the report. Anyone know how I can populate my parameter with the correct ID. I assume I need to get the datasource login credentials through VB report code but do not know how to do this(the consquence of the present situation is that if employee X runs report when logged in as employee X, the correct data is displayed, but if Employee Y runs the report giving his credentials while Employee X is logged in then SSRS authentcates the user but displays the data for employee X rather than Y. and this is what I am trying to avoid by forcing a login on this report) Thanks in advance Paul Wed, 16 May 2012 04:11:01 GMTpaul.ruane2http://www.sqlservercentral.com/Forums/Topic1302299-1633-1.aspxCan we write values in the bar chart as shown and change there background color with SSRS 2008 R2Fri, 18 May 2012 00:45:52 GMTVishwaniDevhttp://www.sqlservercentral.com/Forums/Topic1302147-1633-1.aspxI am working on a new SSRS 2008 r2 report. Right now in the details section there is a line for the column headers before each detail line. I would like the column header to appear only when there is a control break (row group) change. Can you tell me how to fix this issue and/or point me to a url that will solve the issue?Thu, 17 May 2012 14:27:26 GMTwendy elizabethhttp://www.sqlservercentral.com/Forums/Topic1301976-1633-1.aspxI have an ssrs 2008 r2 report that I am working with that was created using the report wizard. I would like to place some company logos in the header section for each page of the report. Do the pictures needs to be in a certain format like .gif, .png, a bitmap or some other picture format? Thus can you tell me and/or point me to a reference on how to accomplish this goal?Thu, 17 May 2012 10:53:12 GMTwendy elizabethhttp://www.sqlservercentral.com/Forums/Topic1301764-1633-1.aspxWhen I use the SSRS 2008 r2 report wizard, I can not find a way to setup a stored procedure to pass a parameters to the report. When I setup the report, the only way to get a query is to build query.I want to use a stored procedure since I want the user to enter a start date and an end date? Thus can you tell me how to use the report wizard initially to usesetup a stored procedure that requires input parameters? If this is not possible, can you tell how how to change the code and/or the designer so I can use a stored procedure in the report that was setup using the report wizard initially?Thu, 17 May 2012 07:44:02 GMTwendy elizabethhttp://www.sqlservercentral.com/Forums/Topic1301141-1633-1.aspxHi friends,I need to develop a report in which selection option : User name as dropdown and also In a given report I have an option called "Show details" radio buttons for true or falseBased on selection of "show details" for true , I need to show detail report for that UserDetail report has columns entry_id ssn.... etc columnscan any one tell me how to do this functionality ??Thanks,KomalWed, 16 May 2012 09:26:58 GMTkomal145http://www.sqlservercentral.com/Forums/Topic1301450-1633-1.aspxHey guys, trying to build a drill-down report which would have multiple groups, and make the rows hidden unless I expand the row by clicking the button. I get the report ready, add the first group, and make the row below it hidden with toggle by that group. Everything works fine at that point, when I preview it. The items in the group are hidden, and all you can see is the group header. As soon as I add the second parent group, and apply the same thing, preview shows the first row of any group, where it was hidden before. What am I doing wrong here?Wed, 16 May 2012 19:00:53 GMTDVSQLhttp://www.sqlservercentral.com/Forums/Topic1301291-1633-1.aspxI would appreciate it if someone could convert the following T-SQL to VB so I could place it in the Custom Code of a VS Report:SET @InvoiceRecordCount = (SELECT MAX(LIne_No)FROM VW_RPTSalesInvoiceWHERE SalesInvoiceNo = @InvoiceNo);SET @rowNo = CASE WHEN 15 - (@InvoiceRecordCount % 15) = 15 THEN 0 ELSE 15 - (@InvoiceRecordCount % 15) ENDI have already declared the variables in the report variables.The objective is to be able to get @rowNo into the query for the report.Thank you.SteveWed, 16 May 2012 12:39:39 GMTnopollahttp://www.sqlservercentral.com/Forums/Topic1300835-1633-1.aspxHi,I would like to have alternate colors, but in my report I have both row and column grouping. So in my case expression "= IIf(RowNumber(Nothing) Mod 2 = 0, "Silver", "Transparent")" counts rows like in following example:|No| C1 | C2 | C3|| 2 | aa | bb | &nbsp &nbsp || 5 | aa | bb | cc |where "No" is row number and C1,C2,C3 - column names.Do you know how to number rows one by one in my case? I also tried this expression: "=IIF(RunningValue(Fields!DepartmentName.Value,CountDistinct,Nothing) Mod 2, "Silver", "White")", but because of report specification it also doesn't count rows correctly.Wed, 16 May 2012 01:56:59 GMTrussellxuphttp://www.sqlservercentral.com/Forums/Topic1300546-1633-1.aspxHello,I designing a report:I have the columns numberOfCases...My report is displaying NumberOfCases Percentage (Calulated as expression) --------------- ------------Total Sum(NumberOfCases) ????How can I calculate the total percentage....I do not want to divide SUM(NumberOfCases)/SUM(NumberOfCases) *100I want to sum that column.Thank youTue, 15 May 2012 13:27:43 GMTBeginner2012http://www.sqlservercentral.com/Forums/Topic1300960-1633-1.aspxHi, Basically I want the chart y axis to show negative control limit if it has a lower negative limit . Thanks for your help.I used min max range to resolve it. thanks.Wed, 16 May 2012 06:39:56 GMTSQL_pathhttp://www.sqlservercentral.com/Forums/Topic1300487-1633-1.aspxThe publish option on ssrs 2008 r2 does not work. Due do that fact, can you tell me what to look into to see where the problem lies? Could there be a permissions problems?Tue, 15 May 2012 12:05:22 GMTwendy elizabethhttp://www.sqlservercentral.com/Forums/Topic1299948-1633-1.aspxHello,Is it possible to use the Primary Interop Assemblies for Office with an SSRS report? I'm trying to use Excel formulas in my report. Or would I use the SQL CLR to do that?Thank you,Elaine ShaferMon, 14 May 2012 17:09:49 GMTElaine Shafer-401422