Dropdown - Multi-select Won't Show Values If Only One Item Is Available
I have a report that has 2 dropdowns, selecting from the first dropdown populates the second one. This works fine in the BI Studio.
When I deploy this report to the 'Report Manager' and make a selection from the first dropdown, the second dropdown loads (as expected). I tried to select from the second dropdown (which has only 1 item - which is correct), the dropdown does not appear correctly - as in, I can't see that item.
Since we can't attach anything here, below is the link to a screenshot of my issue:
http://docs.google.com/View?docid=ddd6j2xn_52c5qd5
If you look closely at the screenshot from the link above, you'll see that there is a value in the second dropdown - it just won't show completely - as if the dropdown is not rendering correctly. I can view source on the page and see that the dropdown has a value.
What appears to be happening is the if only 1 item is in the second dropdown and that item is longer than the size of the dropdown, the dropdown won't render.
Here is my value for the second dropdown '012 Candy Gadler David Thapero'. This is the only value in the second dropdown. - You can try 35 chars or more in the dropdown to confirm.
Notes:
+ No special chars are in either dropdown
+ I am using IE 7 (Also had someone test this on IE 6 - same problem)
+ Using Visual Studio 2005 to build report - where this works fine
Work around:
+ If I add another item to the dropdown via a UNION query, I see the original value + the new item in the dropdown #2
Please share your thoughts
Thanks,
h.
View Complete Forum Thread with Replies
Related Forum Messages:
Removing &&<Select A Value&&> Item From The Singlevalued Parameter Dropdown
Hello All, I am developing reports using SQL Reporting Services 2005. These reports have some single valued parameters and these parameters will be provided in report viewer itself. These parameters should have "All" as default. "Select All" comes by default in Multivalue report parameter. But for single value parameter it always shows "<Select a value>" and if the user does not select a value,it prompts for one.I need to remove <Select Value> and show "All" as default thereby user does not have to select a value. I can include "All" as a part of the select query in the parameter's datasource so that it can be shown in the parameter dropdown . However, I am not sure how I can remove "<Select a value>" from the dropdown. Does anyone have any pointers? Thanks in advance. Ashish
View Replies !
Multi Value Values In Varchar Field, To Show Each Value Separately In A View
Hi thereGot a interesting problem (depends on a point of view.....).Background :-Agent (within Domino) is run daily basis which extracts data fromDomino Notes application to SQL Server 2000 database. Agent firstremoves ALL contents and then appends ALL data.Reporting Tool is SQL Reporting Services (very cool !!).Problem :-Within Domino Notes, it can have a field which is mult-value fieldi.e. contain multi valuese.g.(from Helpdesk application)!HowTo!!Access Email;!HowTo!!Access the Web;etc..Need to create a view, then use sql to create stored proc, to be usedas the dataset for report within SQL Reporting services.Format. (using above as data as example)!HowTo!!Access Email; (1 row) WWL/SDR/04023/010 (DocID)!HowTo!!Access the Web; (2 row) WWL/SDR/04023/010 (DocID)I have a unique KEY within table called DocID. The report will have 8separate datasets (i.e. using subreports), all linked back to UniqueKey. That's easy.The dataset causing me hassle is the 1 above. How do u split outvalues as separate rows ?Name of field called --> "ImpFunctionsImpacted".Also, notes expert, who's working on the agent, tell's me the data canbe split either as a comma or semi-colon.Any suggestions most welcome.
View Replies !
Unable To Execute Stored Procedure When New Item Is Selected In Dropdown.
I'm using a form that has a dropdown control. This dropdown control has items that can be selected that serves as a filter for displaying results on the page that is returned from a stored procedure call. I'm having trouble finding a way to execute the stored procedure to display the filtered results everytime a different item in the dropdown gets selected. Currently, the form does get submitted and the selected item does get saved, but the stored procedure never gets executed on a postback. Any ideas on resolving this issure? Your help is much appreciated.
View Replies !
Select Statement Using Multi-list Box Values For WHERE IN SQL Clause
I have a gridview that is based on the selection(s) in a listbox. The gridview renders fine if I only select one value from the listbox. I recive this error though when I select more that one value from the listbox: Syntax error converting the nvarchar value '4,1' to a column of data type int. If, however, I hard code 4,1 in place of @ListSelection (see below selectCommand WHERE and IN Clauses) the gridview renders perfectly. <asp:SqlDataSource ID="SqlDataSourceAll" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT DISTINCT dbo.Contacts.Title, dbo.Contacts.FirstName, dbo.Contacts.MI, dbo.Contacts.LastName, dbo.Contacts.Suffix, dbo.Contacts.Dear, dbo.Contacts.Honorific, dbo.Contacts.Address, dbo.Contacts.Address2, dbo.Contacts.City, dbo.Contacts.StateOrProvince, dbo.Contacts.PostalCode FROM dbo.Contacts INNER JOIN dbo.tblListSelection ON dbo.Contacts.ContactID = dbo.tblListSelection.contactID INNER JOIN dbo.ListDescriptions ON dbo.tblListSelection.selListID = dbo.ListDescriptions.ID WHERE (dbo.tblListSelection.selListID IN (@ListSelection)) AND (dbo.Contacts.StateOrProvince LIKE '%') ORDER BY dbo.Contacts.LastName"> <SelectParameters> <asp:Parameter Name="ListSelection" DefaultValue="1"/> </SelectParameters> </asp:SqlDataSource> The selListID column is type integer in the database. I'm using the ListBox1_selectedIndexChanged in the code behind like this where I've tried using setting my selectparameter using the label1.text value and the Requst.From(ListBox1.UniqueID) value with the same result: Protected Sub ListBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged Dim Item As ListItem For Each Item In ListBox1.Items If Item.Selected Then If Label1.Text <> "" Then Label1.Text = Label1.Text + Item.Value + "," Else Label1.Text = Item.Value + "," End If End If Next Label1.Text = Label1.Text.TrimEnd(",") SqlDataSourceAll.SelectParameters("ListSelection").DefaultValue = Request.Form(ListBox1.UniqueID) End Sub What am I doing wrong here? Thanks!
View Replies !
Store Multi-Select Values In An Image Data Type?
I was working on figuring out where a certain application wasstoring the multiple selection choices I was doing through the app.I finally figured out that they were being store in an IMAGEdata type colum with the variable length of 26 bytes.This is the first time I ran into such way of storing multipleselections in a single Image data type.Is this a better alternative than to store into a One-to-Manytables? If so then I'll have to consider using the Image datatype approach next time I have to do something like storing1 to thousands of selections.Thank you
View Replies !
Selecting Multi-value Report Parameters From Dropdown
Hello, I have a report with a multi-value parameter. In its' drop drop box, I can select ALL or I can individually select single items, but it doesn't appear that I can select a range of items using the shift key. The problem is; occasionally the user will want to select everything, and them eliminate an item or two. The list it too long to select every one individually. It would be an easy solution if hey could select the entire list, then deselect what they don't want. Is there a way? Thanks.
View Replies !
Is It Possible To Make A Filter Dropdown Only Show Items Satisfying Any Existing Filters?
Hi all, In my cube there is a customer dimension and a product dimension. If I filter on a single customer, and then click on the filter dropdown for product, all of the products for every customer are shown. Is it possible to make it so it only show the members for the customer selected? I suspect the answer is no, as that would mean you can't untick any previously ticked members, but our account management team have got the right hump about them showing so I need to at least attempt it. Regards, Phil
View Replies !
Multi-Select String Parameter Values Are Converted To N'Value1', N'Value2' In Clause When Report Is Run
I understand that Multi-Select Parameters are converted behind the scenes to an In Clause when a report is executed. The problem that I have is that my multi-select string parameter is turned into an in claused filled with nvarchar/unicode expressions like: Where columnName in (N'Value1', N'Value2', N'Value3'...) This nvarchar / unicode expression takes what is already a fairly slow-performing construct and just drives it into the ground. When I capture my query with Profiler (so I can see the In Clause that is being built), I can run it in Management Studio and see the execution plan. Using N'Values' instead of just 'Value1', 'Value2','Value3' causes the query performance to drop from 40 seconds to two minutes and 40 seconds. It's horrible. How can I make it stop!!!? Is there any way to force the query-rewriting process in Reporting Services to just use plain-old, varchar text values instead of forcing each value in the list to be converted on the fly to an Nvarchar value like this? The column from which I am pulling values for the parameter and the column that I am filtering are both just plain varchar. Thanks, TC
View Replies !
Multi-value Parameter Dropdown Horizontal Scrollbar Problem
Hi, I wonder if anyone ecountered (and successfully solved) the following problem: I have a query-based multi-valued parameter (let's call it "A"). When query that populates the parameter returns only one value and that value is a long text, then it's almost impossible to select this value through the Report Manager UI. It's because the horizontal scrollbar covers the value. For now I have found two workarounds to this problem: 1. Cut the lenght of the Parameter Label value (Instead of 'Large Power Transformer", I show "Large Power Tran...") 2. Make the label in the multi-value dropdown smaller by using custom style sheets for report manager. I added a "LABEL { font-size: 7pt; }" section to the Htmlviewer.css and modified the RSReportServer.config file to point to the Htmlviewer.css (for info on how to do this please see: http://msdn2.microsoft.com/en-us/library/ms345247.aspx) So far I am using the No 2. workaround. Any other suggestions on final solution to that matter would be highly appreciated.
View Replies !
Expanding The Multi-Valued Parameter Dropdown Menu
Hello Everyone, I am trying to expand the multi-valued parameter menu object so I can display the selection list in a more user-friendly format since the text is fairly long. If left at the default setting then I have to scroll to the right to see the complete string(s). For a single-select parameter I can just CAST the string to CHAR(100) and the menu object will expand. However, it seems that this approach is not working for multi-valued parameter menu objects. Is this a bug in SSRS, and is there another way to accomplish this. Please let me know. Thank You!!!
View Replies !
How To Show Data In A Label Item?
SqlCeConnection cn = new SqlCeConnection("Data Source=\Program Files\test\tel.sdf"); try { SqlCeCommand cmd = cn.CreateCommand(); cmd.CommandText = "SELECT * from phone where PhoneNo = '0912312345'"; SqlCeDataAdapter adp = new SqlCeDataAdapter(cmd); DataSet ds = new DataSet(); adp.Fill(ds); this.dataGrid1.DataSource = ds.Tables[0]; label1.Text =Convert.ToString(????????????); label2.Text =Convert.ToString(????????????); } finally { if (cn.State != ConnectionState.Closed) { cn.Close(); } } I can show the result in the DataGrid1 however, I want the label1 to show the Name, and label2 to show the phoneNo. how to do in here, label1.Text =Convert.ToString(????????????); label2.Text =Convert.ToString(????????????); thanks
View Replies !
Firefox In Mac Fails To Render Scrollbar In The Multi-value Parameter Dropdown List Generated By ReportViewer.
Our clients working with the Firefox browser on a Mac are unable to use the Multi-Value parameter drop down lists that the Report Viewer control generates. Of course I realize that the multi-select dropdown lists are not really dropdown option lists using the standard HTML select tag, but are rather tables within div tags with cells that contain spans, inputs, and labels. Originally the report viewer displayed these lists in the wrong position within Firefox on any platform (Mac or PC). Furthermore, there were other visibility problems with those lists that made it virtually impossible to select a checkbox within the list. Fortunately, Microsoft fixed this problem with the latest version of Report Viewer, which we downloaded from the following link: http://207.46.19.190/downloads/details.aspx?FamilyID=cc96c246-61e5-4d9e-bb5f-416d75a1b9ef&displaylang=en So currently we have SQL Reporting Services Report Viewer 9.0.21022.8 installed on our web server. And the dropdown lists do appear as expected, and they work properly in Firefox on a PC. But, when the control is rendered in Firefox on a Mac, the list is not scrollable. The scroll bar that should appear on the right-hand side of the dropdown list, which would enable users to select values toward the bottom of the list, does not appear. That scrollbar is missing in Firefox. This is likely related to a Firefox rendering issue with the overflow:auto style. There are numerous entries on the web that indicate Firefox for Mac has a problem with overflow:auto. For example: http://www.webdeveloper.com/forum/archive/index.php/t-96958.html http://www.daniweb.com/forums/thread44144.html http://iamthewalr.us/blog/2007/04/20/firefox-on-the-mac/#comment-2321 http://www.errorforum.com/mozilla-firefox-error/3503-will-float-mac-firefox-scrollbars-floating-pop-up-windows.html https://bugzilla.mozilla.org/show_bug.cgi?query_format=specific&order=relevance+desc&bug_status=__open__&id=187435 That being the case, it seems that there should be some workaround to address this, either via a style or through some alternate control. Or perhaps there is a property that we can apply to the ReportViewer control that I'm unaware of which addresses this. If you know of a workaround, or can suggest an alternate approach that we could implement quickly, please respond. Thanks.
View Replies !
Dropdown List Values
Dear Readers of this post, I have a dropdownlist that is populated with an sqldatasource as follows: SELECT [Project_ID], [Title] FROM [Projects] WHERE [Username] = @Username AND Hide ='false' The Datavalue vield of the DDL is populated with the [Title]. When the user submits the form [including the value of the of the drop down list] i want to be able to add the Project ID and the Title Values into another database table. any ideas Sat
View Replies !
DropDown List Values
For a controlParameter in the ASP code, how do I retreive the selectedValue of the drop down list?Would this work? <asp:controlParameter Name="InvoiceNumber" Type="String" ControlID="ddAdSize.Value" />
View Replies !
Select Statement For Dropdown List
I have a dropdown list that is populated by two columns in a database.select (firstname+surname) AS Fullname from table where id = 'id'; It works fine but i want to know how i would get a space between the firstname and the surname because at the moment all the values come back as JoeBloggs....without any spaces
View Replies !
Help With Multi Join Or Multi Tier Select.
Hello,I am trying to construct a query across 5 tables but primarily 3tables. Plan, Provider, ProviderLocation are the three primary tablesthe other tables are lookup tables for values the other tables.PlanID is the primary in Plan andPlanProviderProviderLocationLookups---------------------------------------------PlanIDProviderIDProviderIDLookupTypePlanNamePlanIDProviderStatusLookupKeyRegionIDLastName...LookupValue....FirstName...Given a PlanID I want all the Providers with a ProviderStatus = 0I can get the query to work just fine if there are records but what Iwant is if there are no records then I at least want one record withthe Plan information. Here is a sample of the Query:SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,p.ProviderID, p.SSNEIN, pl.DisplayLocationOnPCP,pl.NoDisplayDate, pl.ProviderStatus, pl.InvalidDate,l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,pl.InvalidDataFROM Plans plnINNER JOIN Lookups l3 ON l3.LookupType = 'REGN'AND pln.RegionID = l3.Lookupkeyleft outer JOIN Provider p ON pln.PlanID = p.PlanIDleft outer JOIN ProviderLocation pl ON p.ProviderID = pl.ProviderIDleft outer JOIN Lookups l1 ON l1.LookupType = 'PLRM'AND pl.ReasonMain = l1.LookupKeyleft outer JOIN Lookups l2 ON l2.LookupType = 'PLX1'AND pl.ReasonSub = l2.LookupkeyWHERE pln.PlanID = '123456789' AND pl.ProviderStatus = 0ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNumI know the problew the ProviderStatus on the Where clause is keepingany records from being returned but I'm not good enough at this toanother select.Can anybody give me some suggestions?ThanksDavid
View Replies !
Querying Most Recent Values Per Category Per Item, Alternatives To Subqueries?
Hopefully someone can suggest a better solution than what I'm currently hobbling along with.Basically, I've got a table that has rows inserted (with a timestamp) whenever there is a change to one of the values of a particular "item". So, what I want is to return a dataset of the latest value for each category, for each particular item. I'm guessing that what I'm trying to acheive is doable in some elegant and performant fashion. Something maybe involving a ROLLUP or WITH CUBE or something amazingly obvious. But for the time being, I've got a less-elegant query that returns the correct data. It just uses subqueries.Here's the T-SQL to run my scenario: DECLARE @actionHistoryTable TABLE ( itemID int, actionType int, actionValue nvarchar(50) NULL, actionTime datetime )INSERT @actionHistoryTable VALUES( 1000, 1, 'fork', '1/1/2008')INSERT @actionHistoryTable VALUES( 1000, 2, '27', '1/2/2008')INSERT @actionHistoryTable VALUES( 1000, 3, '200', '1/12/2008')INSERT @actionHistoryTable VALUES( 1000, 2, '1', '1/1/2008')INSERT @actionHistoryTable VALUES( 1000, 3, '204', '1/1/2008')INSERT @actionHistoryTable VALUES( 1000, 1, 'ball', '1/3/2008')INSERT @actionHistoryTable VALUES( 1026, 2, '20', '1/10/2008')INSERT @actionHistoryTable VALUES( 1026, 2, NULL, '1/5/2008')INSERT @actionHistoryTable VALUES( 1026, 1, 'hotdog', '1/6/2008')INSERT @actionHistoryTable VALUES( 1026, 3, '2511', '1/8/2008')INSERT @actionHistoryTable VALUES( 1026, 3, '375', '1/7/2008')INSERT @actionHistoryTable VALUES( 1026, 1, 'mustard', '1/5/2008')INSERT @actionHistoryTable VALUES( 1013, 1, 'rock', '1/2/2008')INSERT @actionHistoryTable VALUES( 1013, 1, 'paper', '1/21/2008')INSERT @actionHistoryTable VALUES( 1013, 3, '10', '1/20/2008') -- JUST DISPLAY THE RAW TABLE FOR THIS EXAMPLESELECT * FROM @actionHistoryTable -- THIS RETURNS THE RESULTS I'M WANTING, IT ROLLS-UP THE LATEST VALUE FOR EACH ACTION_TYPE FOR EACH ITEMIDSELECT aht.itemID ,( SELECT TOP 1 aht2.actionValue FROM @actionHistoryTable aht2 WHERE aht.itemID = aht2.itemID AND aht2.actionType = '1' ORDER BY aht2.actionTime DESC ) as 'latest type 1 value' ,( SELECT TOP 1 aht2.actionValue FROM @actionHistoryTable aht2 WHERE aht.itemID = aht2.itemID AND aht2.actionType = '2' ORDER BY aht2.actionTime DESC ) as 'latest type 2 value' ,( SELECT TOP 1 aht2.actionValue FROM @actionHistoryTable aht2 WHERE aht.itemID = aht2.itemID AND aht2.actionType = '3' ORDER BY aht2.actionTime DESC ) as 'latest type 3 value'FROM @actionHistoryTable ahtGROUP BY aht.itemID Is there a better way?-Steve
View Replies !
Select .... Where The Item.ID Is In This Array?
Hiya,I want to populate a list view (in a variety of ways), but I would rathernot execute my function once for every record I want to show in the list.What I want to do is pass an array of "keys" into a stored procedure andhave that procedure return one recordset with all of the records in. Sowhat I want to do is something like:CREATE PROCEDURE MySuperProcedure@MyArrayOfIDs (some way of specifying an array of integers),ASSELECT *FROM MyTableWHERE MyArrayOfIDs.ContainsTheInteger ( MyTable.ID )Obviously this syntax is not available. Is there another way of doing this?
View Replies !
Retrieving Only One Record Per Item Using A Select
The following select retrieves multiple reoords for each i.number. How can I select just the first record for each i.number? SELECT i.number, i.desc, i.it_sdate, v.entry_date FROM itemsnum as I INNER JOIN Inventor as V ON SUBSTR(i.number,1,5)=v.catalog WHERE v.entry_date<ctod("04/01/06") AND i.it_sdate < ctod("04/01/06") order by number, it_sdate desc Thanks in advance!
View Replies !
Getting Textbox To Show All Values
Is there a way in rs to have a textbox more than the first value when it is dragged on the rs form when creating a report? currently when I drag a text box on the rs form, it only shows the first record on a page/form. There are twenty records on the table where the first value came from. How can I make so that twenty text boxes representing twenty records appear on twenty different pages. In other words, I am trying to build a report with a non - tabular structure that can repeat on different pages.
View Replies !
All Measures Show No Values
I made a number of dimension and calculated member mods and notice that all now no base or calculated measures display values, they just show blanks in the browser and query window. To try to isolate, I've (1) removed every dimension and calculated member (2) verified there are no defaultmembers for dimensions (3) verified there are no dimension attributes set to IsAggregatable = False (4) verified there are no deploy or script errors (5) fully processed every dm and fact (6) verified there are dim and claim records loaded with values in all appropriate columns When I process the cube, it shows that rows are read, etc... All that looks normal. Just when I browse or query, all measures display as blanks or nulls. What could cause this behavior? Any ideas on how to futher isolate? I can go to a backup but very concerned that this can happen with no error messages or warnings, so I want to find the cause.
View Replies !
Help With Joining/selecting Values To Show
I am editing a pre-existing view.This view is already bringing data from 40+ tables so I am to modify itwithout screwing with anything else that is already in there.I need to (left) join it with a new table that lists deposits and thedates they are due. What I need is to print, for each record in theview, the due date for the next deposit due and the total of allpayments that they will have made by the next due date.So this is how things are. I join the table and it obviously bringsmultiple records for each record (one for each matching one in the newtable). I need, instead, to be able to make out what due date I shouldprint (the first one that is GETDATE()?) and the total of deposits upto that date.Now, payments can be either dollar amounts or percentages of anotheramount in the view. So if it's an amount I add it, if it's a % Icalculate the amount and add it.Example:for group X of clients...Deposit 1 due on oct 1: $20Deposit 2 due on oct 15: $30Deposit 3 due on nov 15: $40Deposit 4 due on nov 30: $50for group Y of clients...Deposit 1 due on Oct 30: $200Deposit 2 due on Nov 30: $300Deposit 3 due on Dec 30: $400So when if I execute the view today (Nov 7th) each client from group Xshould have:Next Due Date: nov 15. Total: $90 (deposit 1 + deposit 2 + deposit 3)Group Y should have:Next Due Date: Nov 30, total: $500 (Deposit 1 + deposit 2)And so on.
View Replies !
Show 0 Values If The Field Is NULL
Hi All, I want to show 0, if the field contains NULL values. I use the following expression. =IIf(Fields!MTD_TotGrossBKCOAmt.Value = "NULL" , SUM(Fields!MTD_TotGrossBKCOAmt.Value), 0 ) But this works if the field contains only NULL values. If it has a value, then it shows as #Error Can anyone tell me how to make this work? Thanks
View Replies !
Report Doesn't Show Up Any Values
Hi, I've designed a report and assigned it a datasource that binds with a SP with a parameter. In the XML code of the dataset I can see the input parameter has been recognised by the wizard. Then, I built a report with that Dataset as the source. Everything is fine till this time. Now through my aspx code I pass on the value of the parameter to the report. But the report doesn't show any values at all. The aspx code is like Code Snippet Dim reportParameter As New ReportParameter("@UserID", Convert.ToInt32(TextBox1.Text), True) Dim test() As ReportParameter = {reportParameter} ReportViewer1.LocalReport.SetParameters(test) Any ideas what I could be doing wrong? VS 2005 & SSRS 2005. TIA.
View Replies !
Show All Sales Reps Even If There Are No Values
I know this is an easy one, but for some reason i keep gettin the wrong results. This displays something like this : Question_description Visit_Activity_Id SR_Name Vacation 5 Judy Smith Sick 2 Judy Smith Visit 1 Tom Mathews Training 3 Karen Williams But i want it to show all the SR_Name's ..like this: Question_description Visit_Activity_Id SR_Name Vacation 5 Judy Smith Sick 2 Judy Smith Visit 1 Tom Mathews Training 3 Karen Williams NULL null Tom Jones NULL null Kim Jones NULL null Jon Travis Any help will be grately appreciated! thanks! Code Block ALTER PROCEDURE [dbo].[PROC_RPT_SR_DAILY_ACTIVITIES] (@Region_Key int=null, @Daily_activity_statistics_datetime datetime ) AS BEGIN SELECT Customer.Name as Store_Name, Tbl_Daily_Activity_Statistics.SR_Code, Tbl_Daily_Activity_Statistics.Territory_Code, Tbl_Daily_Activity_Statistics.Customer_code, Tbl_Daily_Activity_Statistics.in_progress_time, Tbl_Daily_Activity_Statistics.completed_time, Tbl_Daily_Activity_Statistics.Visit_Activity_Id, Tbl_Daily_Activity_Statistics.dial_in_datetime, Tbl_Daily_Activity_Statistics.question_code, tbl_Questions_to_RC_Question.RC_Question_description, Qry_Sales_Group.SR_Name, dbo.Qry_Sales_Group.Region, Qry_Sales_Group.Region_Key, Tbl_Daily_Activity_Statistics.Daily_activity_statistics_datetime FROM Tbl_Daily_Activity_Statistics INNER JOIN tbl_Questions_to_RC_Question ON Tbl_Daily_Activity_Statistics.question_code = tbl_Questions_to_RC_Question.question_code INNER JOIN Qry_Sales_Group ON Tbl_Daily_Activity_Statistics.SR_Code = Qry_Sales_Group.SalesPerson_Purchaser_Code COLLATE Latin1_General_CI_AS LEFT OUTER JOIN customer ON dbo.Tbl_Daily_Activity_Statistics.Customer_code = dbo.customer.customer_code WHERE Region_key=@Region_key AND Daily_activity_statistics_datetime = @Daily_activity_statistics_datetime END
View Replies !
Select Most Recently Edited Item AND A Certain Type If Another Doesn't Exist
I've got a big problem that I'm trying to figure out:I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those.I don't need this for an individual ID select, I need it applied to every record from the table.My address table has some columns that look like: [AddressID] [int][LocationID] [int][Type] [nvarchar](10)[Address] [varchar](50)[City] [varchar](50)[State] [char](2)[Zip] [varchar](5)[AddDate] [datetime][EditDate] [datetime]AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table.So there will be multiple addresses to one LocationID, but each address will have it's own AddressID.How can I do this efficiently with perfomance in mind???Thank you in advance for any and all replies...
View Replies !
Select Most Recently Edited Item AND A Certain Type If Another Doesn't Exist
I've got a big problem that I'm trying to figure out: I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those. I don't need this for an individual ID select, I need it applied to every record from the table. My address table has some columns that look like: [AddressID] [int] [LocationID] [int] [Type] [nvarchar](10) [Address] [varchar](50) [City] [varchar](50) [State] [char](2) [Zip] [varchar](5) [AddDate] [datetime] [EditDate] [datetime] AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table. So there will be multiple addresses to one LocationID, but each address will have it's own AddressID. How can I do this efficiently with perfomance in mind??? Thank you in advance for any and all replies...
View Replies !
Show Number Of Values As % Of Total Records..?
HiI'm migrating from Access til MySQL.Works fine so far - but one thing is nearly killing me:I got the count of total records in a variabel - (antalRecords)I got the count for the Field Q1 where the value value is = 'nej'Now I just need to calculate how many % of my records have the value 'nej'I access this worked very fine - but with MySQL ( and ASP) I just cant getit right!!! I go crazy ....My code looks like this :strSQL="SELECT COUNT(Q1) AS Q1_nej FROM Tbl_evaluering " &_"WHERE Q1 = 'NEJ' "set RS = connection.Execute(strSQL)antal_nej = RS("Q1_nej")procent_nej = formatNumber((antal_nej),2)/antalrecords * 100Hope ...praying for help ...Please ;-)best wishes -Otto - Copenhagen
View Replies !
Chart Doe Snot Show X-axis Values
Hi, In SSRS 2005, I do not see why the dates on the x-axis of the chart do not get shown. I have checked that there is indeed data and the graph does get drawn on the chart. Any thoughts please?
View Replies !
Multi Select Parameter From Function - Select All?
I am using RS 2000. I have a multi select parameter where I can select multiple states by separating with a comma. I am trying to figure out how to incorporate an "All" parameter. Query: Select [name], city, state, zipcode From Golf inner join charlist_to_table(@State,Default)f on State = f.str Function: CREATE FUNCTION charlist_to_table (@list ntext, @delimiter nchar(1) = N',') RETURNS @tbl Table (listpos int IDENTITY(1, 1) NOT NULL, str varchar(4000), nstr nvarchar(2000)) AS BEGIN DECLARE @pos int, @textpos int, @chunklen smallint, @tmpstr nvarchar(4000), @leftover nvarchar(4000), @tmpval nvarchar(4000) SET @textpos = 1 SET @leftover = '' WHILE @textpos <= datalength(@list) / 2 BEGIN SET @chunklen = 4000 - datalength(@leftover) / 2 SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen) SET @textpos = @textpos + @chunklen SET @pos = charindex(@delimiter, @tmpstr) WHILE @pos > 0 BEGIN SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1))) INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval) SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr)) SET @pos = charindex(@delimiter, @tmpstr) END SET @leftover = @tmpstr END INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover))) RETURN END GO Anyone have any ideas? Thanks, Deb
View Replies !
Sum Up Multi Values Same Column
Hi All, I have the following Ex: Table A Col A <- Col that I need to check against Col B Col C <- Sum this col So if Col A has the following values: Col A Col C 2 10.00 4 15.00 2 25.00 4 15.00 3 10.00 3 5.00 7 4.00 9 20.00 I need to bring back the sums of 2, 4 and 3 in one resultset. How would I do this? Thanks, JJ
View Replies !
I Want To Show OpenQuery() Result With Local Table Values
Hi, I have two queries as under:QUERY 1:SELECT * FROM OPENQUERY(MYSERVER, 'SELECT * FROM SCOPE() WHERE FREETEXT(''Text to Search'')') AS DocsQUERY 2:SELECT MediaID, LawID, LawDate, Agreement, Name, NameSearch, LawType, LawNo, RegID, IssueNo, AttachmentFrom Dept_LegalLawINNER JOIN Dept_LegalMinistries ON Dept_LegalLaw.RegID = Dept_LegalMinistries.RegIDINNER JOIN Dept_LegalLawType ON Dept_LegalLaw.LawID = Dept_LegalLawType.LawIDWHERE 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1both queries are working fine separately and I can generate the desired results separately, but I cannot merge them to get one single result for example, after a FREETEXT search I want to get some values from my local table (Query2) to display as one result. Like in Query1 FREETEXT will search the web page based on the given text and in Query2 will select the remaining data from the local database (Title, ID, Name etc etc).Try many thing but no success yet.Need urgent help.Thanks
View Replies !
Show Zero Values For Missing Data In Query Resultset
Hello, I have the following query which grabs monthly usage data which is logged to a database table from a web page: SELECT Button = CASE ButtonClicked WHEN 1 THEN '1st Button' WHEN 2 THEN '2nd Button' WHEN 3 THEN '3rd Button' WHEN 4 THEN '4th Button' WHEN 5 THEN '5th Button' WHEN 6 THEN '6th Button' WHEN 7 THEN '7th Button' WHEN 8 THEN '8th Button' WHEN 9 THEN '9th Button' ELSE 'TOTAL' END, COUNT(*) AS [Times Clicked] FROM WebPageUsageLog (NOLOCK) WHERE DateClicked BETWEEN @firstOfMonth AND @lastOfMonth GROUP BY ButtonClicked WITH ROLLUP ORDER BY ButtonClicked The results look like this: TOTAL 303 1st Button 53 2nd Button 177 3rd Button 10 4th Button 4 6th Button 18 7th Button 19 8th Button 21 9th Button 1 If a button is never clicked in a given month, it never gets logged to the table. In this example, the 5th button was not clicked during the month of December, so it does not appear in the results. I want to modify my query so it displays the name of the button and a zero (in this case "5th Button 0") in the results for any buttons that were not clicked. For some reason I am drawing a blank on how to do this. Thanks in advance. -Dave
View Replies !
Displaying Multi Values From Different Dataset
HI, I have 2 datasets A and B now i need to populate a cell in a table with the data from the 2 data sets say A and B. table has been given a dataset B. we need dataset A's column A1 value.All A1 values needs to be populated in the cell. please help me.
View Replies !
Displaying Values For Multi-value Parameter
At the top of my report, if I want to display the selected value for a given parameter, I can put this expression in a text box: ="The xyz param's value is: " & Parameters!paramXYZ.Label(0).ToString How do I go about displaying all the selected values for a multi-value parameter (one that has mulitple checkboxes in the drop-down list)? Is it possible to do a for loop and iterate through the values of the param within an expression? Something like for i = 0 to Parameters!paramXYZ.Count - 1 mystring = mystring & ", " & Parameters!paramXYZ.Label(i).ToString And then display mystring in the textbox? Or can I use a list control on the report? Thank you,
View Replies !
Show The Rows Containing Same Values Rate As Column In Html Table
Hi All, I have following problem please help me to resolve it. There is table for shipping as follows: City Kg Rate(rs) Rajkot 1 25.00 Rajkot 2 30.00 Rajkot 3 42.50 Ahemedabad 1 42.50 Ahemedabad 2 55.00 Ahemedabad 3 67.50 Ahemedabad 4 80.00 Goa 1 90.00 Goa 2 105.00 Goa 3 120.00 Gondal 1 25.00 Gondal 2 30.00 Gondal 3 42.50 Morbi 1 25.00 Morbi 2 30.00 Morbi 3 42.50 Gandhinagar 1 42.50 Gandhinagar 2 55.00 Gandhinagar 3 67.50 Gandhinagar 4 80.00 We need to display the table in html page using any Asp.Net language. The result table should be: City(s) 1 2 3 4 Rajkot,Gondal,Morbi 25.00 30.00 42.50 - Ahemedabad,Gandhinagar 42.50 55.00 67.50 80.00 Goa 90.00 105.00 120.00 - Baruch 70.25 80.00 92.00 120.50 Description: - Display all the cities together for which all the shipping charges for (1-4) kgs are same. The range of kgs is not fixed. Reply as soon as possible. Thanks
View Replies !
Report Parameter Won't Show Values With Stored Procedure Results
Hi, I wrote a stored procedure in C# to return results that I could use to populate the report parameter list. The problem occurs when I set up the parameter. Under "Available Values -> From Query", I can set the dataset fine but no entries appear under the Value field or the Label field. When I run the query (whether it's thru the data tab or in SQL server management Studio or the SQL Server project), I get the correct results. If I try to type in the name of the field, I get an rsInvalidDataSetReferenceField error when I preview the report. Below is the steps I took to get to where I'm at: 1. The stored procedure was supposed to extract certain areas from the area path, with the TFSWarehouse as my data source. The areas of interest were top-level areas, i.e. \TeamProjectTopLevelAreaSubAreas... The CommandText attribute of my SqlCommand instance is as follows: @"SELECT DISTINCT Area.[Area Path] FROM Area INNER JOIN [Current Work Item] ON Area.__ID = [Current Work Item].Area"; 2. The results are returned in an SqlDataReader. I read thru each record of the reader and manipulate some of the data and send it back using SqlContext.Pipe.SendResultsStart/Row/End when appropriate. The structure of the record consists of one column, called "TopLevelArea". 3. I compiled it into an assembly using the Visual Studio command prompt, ran a T-SQL query to create the procedure, so that it appears in the Stored Procedures list. And then I created a dataset whose command type is StoredProcedure, and all it says is "TopLevelAreasTest". Then I set up the parameter and that's where everything went all wrong. Please help.
View Replies !
Show Multiple Values In Single Textbox Comma Separated
I have a field called "Owners", and it's a child to an "Activities" table. An Activity can have on or more owners, and what I'd like to do is some how comma separate the values that come back if there are more than one owners. I've tried a subreport, but because the row is colored and if another field, title, expands to a second row (b/c of the length) and the subreport has just one name, then the sub-report has some different color underneath due to it being smaller in height. I'm kinda stuck on how to do this. Thanks!
View Replies !
Multi-value Parameter Default Values Not Working
I have a parameter that gets it's available values from a dataset. I use this exact same data to populate the default values. When I run the report, the available values get populated; however the default values are not being selected. This works on other parameters on the same report. However, on this parameter, it is not working. I have tried ltrim/rtrim (Been burned with that before when the field type is a char) I change the data field in the query, without changing the parameter setup, and it works... Here is my query: Code Snippet Select distinct CityName from dimHotel dH (NOLOCK) Inner join factHotel fH (NOLOCK) on dh.HotelKey = fH.HotelKey Where dH.CityName <> '' and dH.CityName is not null and fH.ClientKey in (@ClientID) Order by CityName The parameter is setup correctly, and matches the setup of another parameter on the same report that is working fine. I have tried deleting the parameter and re-adding it. This did not work. I also deleted and re-added the query. No luck. Any ideas?? Thanks!! Thanks
View Replies !
I Need Same Help With Multi-values Parameters To DDS With Analysis Services
Hi, I have a SP that he returns 3 parameters for a DDS on Analysis Services. Query is the Following one: SELECT DISTINCT '[Edicao].[Editor].&[A0036]' as cod_editor, '[Edicao].[Publicacao].&[0001]' as cod_publicacao, '[Edicao].[Edicao].&[100105]&[0001], [Edicao].[Edicao].&[100106]&[0001]' as cod_edicao I created a report that receives same the 3 parameters in MDX. When executing the subscript gives me error. If run the subscript with the following a query functions well, SELECT DISTINCT '[Edicao].[Editor].&[A0036]' as cod_editor, '[Edicao].[Publicacao].&[0001]' as cod_publicacao, '[Edicao].[Edicao].&[100105]&[0001]' as cod_edicao but I need that the report receives more than what 1 edition in cod_edicao They can me help please Thanks MAX
View Replies !
Multi Selected Values And Oracle PL/SQL Funciton
Hi, I have developed a report that uses oracle as the database. I have wrote my query by creating a dataset. In the query I am also calling a function. query: select EmpName, get_Client_Count(:cities), POS from ... --function definition create or replace function get_Client_Count(cities in varchar2) return number is .... From the report I have multi selectable dropdown check box list for Cities. the function returns count when I select only one city. Works fine with no problem. As soon as I select multiple cities I am getting the following error ORA-06553: PLS-306: wrong number or types of arguments in call to 'get_client_Count' I know this error was thrown since multiple values were selected. Has anyone come across this situation and came up with workaround or solution to this? I really appreciate your inputs. Thank you.
View Replies !
Show/Hide Report Parameters Based On Selected Values In Different Parameter
Hi All, I have requirement where first I need to show only one report parameter. Based on user selection I need to prompt or show the user another report parameter. Say suppose I have 3 parameters. User selects first value in first parameter I should not show the other 2 parameters. If user selects second value in first parameter I should show second parameter and hide third parameter. There is no relationship between these 2 parameters except user selection. Similarly if user third value in first parameter then I should show third parameter and hide second parameter. Is this possible? I can not see any Visible property for report parameters. If yes, how to achieve this functionality? Appreciate your help. Regards, Raghu
View Replies !
|
TRACKER
