I have a parameter in my report called SortType and depending on the value of the parameter I want to sort by a particular column. I have tried doing this by using a custom function as follows:
Function SortTypeValue() As String
If Report.Parameters!SortType.Value = "1" Then
Return "=Fields!StockDesc.Value"
else Return "=Fields!StockCode.Value"
End If
End Function
Then in the sorting tab I have entered in the expression field: =Code.SortTypeValue().
However this is not doing anything. The data is just displayed in the order it is taken from the database. Can this be done? If so can anybody please tell me what I am doing wrong?
I have a report that contains a Chart called "SummaryChart" and a table called "DetailsTable" the data for the summary chart comes from a data set called dataSourceSummary and the data for the details table comes from a data set called dataSourceDetails.
The summary chart presents counts of items broken down by a category and the details table shows the details of the items that make up the counts in summary chart.
I have a situation where I need to sort the data in the table in a non alpha-numeric way. So I have added a user sort item to the table column header and set the SortExpression to something like the following
where SortProvider is a C# class and GetUserSortOrder is a static method the method essentially finds a row in a database, get the display order for that row and returns it.
I have tested this in another report and it works as expected.
Now finally to the problem:
What happens is when I click on the table heading, values from fields in the dataset for the chart are being passed into my method, followed by values from fields in the dataset for the table and then an exception is thrown.
The only difference between this report and ones that I have tested this on and it works is that this report has two dataregions (Chart and Table) and two datasets.
I have tried setting the ExpressionScope to DetailsTable and the SortTarget to dataSourceDetails but that results in the sort not working at all.
I would have thought that it should only be passed values from the data that it is sorting, especially considering that the data that is being passed in from the chart is a completely different field.
Has anyone else experienced this? Has anyone got any ideas?
I've made another topic before concerning this problem, but since it was really confusing, I will made one clearer (it was about orthodromic formula, in case you read it, but the problem change during the topic, so thats why im creating this new one too). I have a stored procedure with custom pagin method inside, and I want to sort my records on a fields I create myself (which will receive a different value for each record.) Now, I want to sort on this temporary field. And since this is a custom paging method I can choose between many page. Now, for the first page, it sorts fine. But when I choose a page above the first one, the sorting is not right (the results all are wrong). So my real question is: is it really possible to sort on a Temporary Field in a custom paging method (because I know I can do it without any problem on a real field from my table, it just doesnt work right when I try on a temporary field). I tried to solve my problem with this little SQL instruction, but it didnt give me any result yet: SELECT TOP 20 PK, test = field_value FROM Table WHERE PK not in (SELECT TOP 10 ad_id FROM Table ORDER BY ?) ORDER BY ? well thanks for taking the time to read this, any help woulb be appreciated.
I have a report where I am giving the users a parameter so that they can select which field they would like to sort on.The report is also grouping by that field. I have a gruping section, where i have added code to group on the field I want based on this parameter, however I also would like to changing the sorting order but I checked around and I did not find any info.
So here is my example. I am showing sales order info.The user can sort and group by SalesPerson or Customer. Right now, I have code on my dataset to sort by SalesPerson Code and Order No.So far the grouping workds, however the sorting does not.
SSRS 2012 - VS2010...The report compares two years with a sort order on a value that has been engineered based on text switched to int. When sorting A-Z this is the result in the horizontal axis is: 5th, K, 1st, 2nd, 3rd, 4th, 5th..When sorting Z-A the result in the horizontal axis is:5th, 4th, 3rd, 2nd, 1st, PreK..Z-A is correct but A-Z sorting shows 5th as the start and end. The magnitude of the PreK location is correct but the label is wrong on the A-Z sort order. The sorting is implemented using the Category Group sorting option.
I am creating a custom transformation component, and a custom user interface for that component.
In my custom UI, I want to show the custom properties, and allow users to edit these properties similar to how the advanced editor shows the properties.
I know in my UI I need to create a "Property Grid". In the properties of this grid, I can select the object I want to display data for, however, the only objects that appear are the objects that I have already created within this UI, and not the actual component object with the custom properties.
How do I go about getting the properties for my transformation component listed in this property grid?
What I want to accomplish is that at design time the designer can enter a value for some custom property on my custom task and that this value is accessed at executing time.
I am writing a custom task that has some custom properties. I would like to parameterize these properties i.e. read from a varaible, so I can change these variables from a config file during runtime.
I read the documentation and it says if we set the ExpressionType to CPET_NOTIFY, it should work, but it does not seem to work. Not sure if I am missing anything. Can someone please help me?
In the Editor of my custom task, under custom properties section, I expected a button with 3 dots, to click & pop-up so we can specify the expression or at least so it evaluates the variables if we give @[User::VaraibleName]
Ok this is what I have $query = $query = "SELECT date, file name FROM comment WHERE file name ='', AND CorS='C', AND Approved='Y', ORDER BY file name DESC";
But what I want is to return all results based on the file name from the most recent to the oldest. Can anyone help????
here's a good one for you...I want to return the last 20 records I have modified. I have adatemodified field - excellent.So I run a query to select the top 20 when ordered by datemodifieddesc.But now I have these results I want them sorted by companyname.Is this possible?Yes I could use my GUI to do the second sort, but can it be done justin a query?ThanksTim
I have a gridview that has AllowSorting="true" however I need to implement my own sorting because I have DateTime and Integer data types in several of the columns and I don't want an int column sorted like 1,12,2,23,3,34,4,45,5,56, etc. So, I've added SortParameterName="sortBy" and adjusted my stored procedure to accept this. For only ASC sorting, I've got ORDER BY CASE WHEN @sortBy='' THEN DateCreated END, CASE WHEN @sortBy='DateCreated' THEN DateCreated END and so on. However, columns can also be sorted with DESC. I tried CASE WHEN @sortBy='DateCreated DESC' THEN DateCreated DESC END, but I get a syntax error on DESC. How can I do this?
I am trying to set up custom paging and sorting with my gridview. All is well but the sorting. The problem is with the stored procedure. If I pass in the value @sortExpression as, for example "discussions_Posts.post_time", i does not sort it at all. But if I replace the @sortExpression with discussions_Posts.post_time in the actual stored procedure, it gets sorted. how do I sort this query with a input parameter with values like "discussions_Topics.topic_title" or something? ALTER PROCEDURE discussions_GetTopicsSubSet@startRowIndex as int,@maximumRows as int,@sortExpression as nvarchar(50),@board_id as intASDECLARE @Topics TABLE(RowNumber INT,topic_id INT,topic_title VARCHAR(50),topic_replies INT,topic_views INT,topic_type INT,topic_time DATETIME,post_id int,post_time DATETIME,Topic_Author_UserName nvarchar(256),Topic_Author_ID uniqueidentifier,Post_Author_Username nvarchar(256),Post_Author_ID uniqueidentifier)--DECLARE @TopicsFrom Datetime--SELECT @TopicsFrom = CASE @TopicsDays WHEN '1' THEN DATEADD(day,-1,getdate()) WHEN '2' THEN DATEADD(day,-7,getdate()) WHEN '3' THEN DATEADD(day,-14,getdate()) WHEN '4' THEN DATEADD(month,-1,getdate()) WHEN '5' THEN DATEADD(month,-3,getdate()) WHEN '6' THEN DATEADD(month,-6,getdate()) WHEN '7' THEN DATEADD(year,-1,getdate()) ELSE DATEADD(year,-1,getdate()) END-- populate the table CAST(getdate() as int)INSERT INTO @TopicsSELECT ROW_NUMBER() OVER (ORDER BY @sortExpression), discussions_Topics.topic_id, discussions_Topics.topic_title, discussions_Topics.topic_replies, discussions_Topics.topic_views, discussions_Topics.topic_type,discussions_Topics.topic_time, discussions_Posts.post_id, discussions_Posts.post_time, user_1.UserName AS Topic_Author_Username, user_1.UserId AS Topic_Author_ID, user_2.UserName AS Post_Author_Username, user_2.UserId AS Post_Author_IDFROM discussions_Topics INNER JOIN discussions_Posts ON discussions_Posts.post_id = discussions_Topics.topic_last_post_id INNER JOIN aspnet_Users AS user_1 ON user_1.UserId = discussions_Topics.topic_poster INNER JOIN aspnet_Users AS user_2 ON user_2.UserId = discussions_Posts.poster_idWHERE (discussions_Topics.board_id = @board_id ANDdiscussions_Topics.topic_type NOT LIKE '1' )SELECT * from @TopicsWHERE RowNumber BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1
This is more of a SQL question than a .NET question, but if you could indulge me, I'd appreciate it. I have a table that has 2 columns of particular interest for the purposes of this question. One is a foreign key to another table (int), the other is a name (varchar(50). I want to sort the results set in a specific way. I want to sort it in such a way that all entries that have the foreign key = 0 come first (sorted ASC by name) then I'd like all the other results with foreign key column > 0 to be sorted ASC by name. I was trying to be cute and tried an order by statement like this: "ORDER BY (foreignKey > 0), name" but it's a syntax error (as I initially thought it might be). I know I could probably do a stored procedure that will create a temporary table and I could insert a new column to help put these in order, and I also know I could put all the results into an array, then sort the array in code, but I was just wondering if there was a simpler, slicker way (tricky SQL query perhaps).
Hi, I hope I can explain what I am trying to achieve (in MS SQL Server): The results set that shows Names and Dates (plus others, but not relevant here), that needs to be sorted by Date and then by name. I have tried: ORDER BY date, surname, forename (gives dates in right order but not names) and ORDER BY surname, forename, date (gives names in right order but not dates)
I noticed when looking at the execution path that the SQL sort (order by) was taking up 16% of the total for the query. However the execution time for the sort was like .007 ms in SQL so I doubt it was really that much overhead.
Is it more effecient to sort a dataset within my asp.net program?
i have a simple table with NAME and a SORTORDER. Now i want to sort my table on the NAME desc and then insert a number into the SORTORDER. The number has to correspond with the number the NAME has in the list.
EXAMPLE
Initial situation: BAKKER 0 AKKER 0 CARNIE 0
Now my question is how can i update the table with a sp in wich te outcome is:
I'm trying to sort by domain user id, I can pull the user id fine but now I want to sort my SELECT by that name. How do I put the value of getUserIdentity into my SELECT statment. Thanks <script language="C#" runat="server"> protected string getUserIdentity() { return HttpContext.Current.User.Identity.Name.ToString().Replace("DOMAIN\", ""); } protected void Page_Load(Object sender, EventArgs e) { username = getUserIdentity(); SqlConnection myConnection = new SqlConnection("Data Source=CLIENTELE;Initial Catalog=forms;Integrated Security=True"); SqlDataAdapter myCommand = new SqlDataAdapter("SELECT * FROM formTable WHERE userID = @username ORDER BY status DESC", myConnection); DataSet ds = new DataSet(); myCommand.Fill(ds, "names"); MyDataGrid.DataSource=ds.Tables["names"].DefaultView; MyDataGrid.DataBind(); } </script>
i am trying to figure out how to sort the data in a column in an ascending way when the column contains numeric and char data. In the example I want to sort by category,lookup value. However, as you can see the top two rows are sorted right but the others are all messed up. I would really appreciate any hints you can give me. thanks Rozina
Category ID lookupvalue
CPU Type4CPU Type 1 CPU Type4CPU Type 2 CPU Number51 CPU Number516 CPU Number52 CPU Number532 CPU Number54 CPU Number564 CPU Number58
I have a view of a table which contains users. the SQL is:
CREATE VIEW dbo.vwTravellers AS SELECT TravellerID AS ID, LTRIM(RTRIM(LastName)) + ', ' + LTRIM(RTRIM(FirstName)) + ' - DOB:' + LEFT(RTRIM(DOB), 11) AS Value FROM dbo.tblTravellers
I would like to be able to sort the view by LastName, Firstname, but it doesnt allow a SORT BY in the view. Does anyone know of a cheeky little trick I could use to get around this?
Cheers Guy
I wish I'd listened in that SQl Server training course I attended :(
I am trying to sort a matrix by column. I first tried adding an interactive sort to the column heading, but it either gave me a run-time error, or there was not response. I then tried adding sorting to the row groups, and that doesn't seem to work either. I feel I have done every combination of groupings and sort expressions. Has anyone been able to accomplish this? Thanks, Kris
I'm not sure why this is happening, but I'm trying to sort/order on this special field/column that I have created to allow precise ordering. However, for all of the values that are returned, if there are any NULLS, they are returned first/ahead of the values of what I want to be returned first. This field/column I created is called 'POW' (stands for Priority on Web). The values are just integers, 1-whatever, and we try to make sure that they're all unique. So for example, if we have a 1 in that field, then that row should be returned first, and if another row has 2, then it should be next. However, this is not working exactly - all of the NULL values are returned FIRST, then the proper values in the correct order. I want the NULLS to be last, because it may take some time for someone to go into each record, and assign it a unique value. My current SQL statement reads:
mySQL = "SELECT SignTypes.Id, SignTypes.SignType, SignTypes.SignDescription, SignTypes.FinalSignWidth, SignTypes.FinalSignHeight FROM SignTypes JOIN PropLocSigns ON SignTypes.Id = PropLocSigns.SignTypeID WHERE PropLocSigns.PropLocID = " & Session("PropID") & " AND (RestrictOnWeb = 0 OR RestrictOnWeb IS NULL) ORDER BY SignTypes.POW; SELECT @@ROWCOUNT"
So if someone knows of a way to put NULLS last, and still have my proper ordering, that would be great! I would think that there's some simple way of doing this, but not being an expert, I don't know. Thanks for the help!
hi all..just a quick question about sorting a query. let us say that i am running a select statement that involves 3 or 4 tables and one of the columns in one of the tables is a divisions column. based on the records, there is a specific division that i want to appear first in the list. its not alphabetically possible nor is it the keys associated with it allow it to be selected first. how would i go about pulling that particular record from the divisions column to be listed first in my query? thanks alot
I have an appointment table that stores the appt time as varchar. For example, 1:00 PM or 9:00 AM. When I say "ORDER BY Appointment.Time", it puts 1:00 PM before 9:00 AM. How do I make it put 9:00 before instead? Could you please help me out.
Please note, I store time as varchar because it is much more convenient for me through out the application that way.
i want to modify this table: employees----address----phone1----phone2----phone 3 ---------------------------------------------------------------- John A. keas 19 9515678 John A. keas 19 9515670 Mike B. highway 2 8652332 Mike B. highway 2 8656543 Mike B. highway 2 8723165
like this : employees----address----phone1----phone2----phone 3 ---------------------------------------------------------------- John A. keas 19 9515678 9515670 Mike B. highway 2 8652332 8656543 8723165
_________________________________________________ may seem silly,i know,but i am stuck here days now and i dont know with what to fill my loop. thank you in advance.
