Issue With Sorting Reports
Mar 22, 2007
Hi,
I'm having an issue with sorting reports. I have an order by in the query which returns the results how I want them, but if I use the auto generated columns and layout they are not in the order that the query returns them. However, if I manually create a new table and put the results in that they are ordered fine.
I am having this problem in reporting services for sql 2000, but my workmate has had exactly the same thing in his reporting for 2005.
Any help would be greatly appreciated.
Rob
View 2 Replies
ADVERTISEMENT
Aug 15, 2006
I've been banging my head against this for a while now and have searched through the documentation and google and posted to the newsgroups with no luck.
The ReportExecution2005 namespace contains a sort method, but the documentation doesn't indicate how it's used. The method accepts a string for the sort item, but that string apparently has to be an integer. I've called the method and regardless of the input, the result is the same:
* The sort method returns 1 and the report item passed in is the report item returned in the 'out' variable.
* The sorting is not reflected in the output of the Render method.
I can't simply use interactive sorting because, in our deployment, the SQL Server and Reporting Services are not accessible through the firewall. Requests go to a web server which calls the SSRS web methods and renders the report to an HTML placeholder.
View 7 Replies
View Related
Apr 30, 2008
Hi,
We have a matrix report which displays columns in a default sorting order. This report columns vary dynamically depending on the user input.
e.g. If user wants to see the report for column Alfa, Beta , Gama then a report will be genarted with column Alfa, Beta , Gama sorted in alphabetical order.
Site
%Risk
Alfa
Beta
Gama
X
2
1
2
3
Y
10
4
5
6
However the users want the Columns to be sorted in the order which they provide the inputs e.g. if the user entered Gama, Alfa, Beta the report should display the columns in the same order instead of applying the default sorting order.
Site
%Risk
Gama
Alfa
Beta
X
2
3
1
2
Y
10
6
4
5
Any thoughts on ways to achieve this in SSRS matrix report would really help.
Cheers,
Viv
View 3 Replies
View Related
Aug 20, 2015
I am using Report Builder 3.0 and i have report which has a drill down group. The report opens in a collapsed form.This report has interactive sorting on a column. But,when we drill down and sort the column, the group collapses and the details disappear.
In some posts, i have seen that instead of interactive sorting, people recommended a parameter. But i have to do interactive sorting only.
View 3 Replies
View Related
Feb 11, 2007
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.
Any suggestions would help.
Thanks
View 1 Replies
View Related
Jul 10, 2015
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.
View 6 Replies
View Related
Nov 5, 2007
Hello SQL Server Experts, Data Analysts, and Report Writers et al:
re: Reporting Options with SQL Server
I wanted to propose an offshoot to the pryor thread:
Would anyone take a stab at comparing Access Reports, Crystal Reports,
Cognos or other options to all the Reporting Services and its components offered as part ofSQL Server, especially as to extracting data from SQL Server into a report format?
I guess this is a far as capabilites, ease of use, limitations, and especially formatting
or presentation of the end report product?
Thank you to all, and I hope this is a beneficial discussion to others.
Hal1490
Hal9000
View 4 Replies
View Related
Mar 18, 2008
I have an already published application running several MS SQL Server 2000 Reporting Services report.
I need a way to find from either the reporting services log or the application server (IIS) logs or windows log
to know the frequency of each report being used.
Based on this info, the business needs to know which reports are being used and to what extend?
How can I acheive this?
I have already got the IIS logs and it did not give the required info.
I have looked into the Reporting Services logs but it does not provide the info either.
Any help is appreciated
View 2 Replies
View Related
Mar 18, 2008
I have an already published application running several MS SQL Server 2000 Reporting Services report.
I need a way to find from either the reporting services log or the application server (IIS) logs or windows log
to know the frequency of each report being used.
Based on this info, the business needs to know which reports are being used and to what extend?
How can I acheive this?
I have already got the IIS logs and it did not give the required info.
I have looked into the Reporting Services logs but it does not provide the info either.
Any help is appreciated
View 1 Replies
View Related
Jan 23, 2007
Hi does anyone know how to do the above with out going through reportserver url?
Preferably by using a cmd tool ? such rs.exe
or through the backend in the reportserver DB?
Thanks
Dave
View 2 Replies
View Related
Feb 13, 2008
Hi Friends,
Could I migrate the reports which are developed on SQL 2000 to SQL 2005. If Yes then How ...
Could anyone explain me. How it works.
Thanks
sqlferns
View 2 Replies
View Related
Mar 27, 2008
My issue is with converting multi-value parameters:
In Crystal Reports, you can set a parameter to accept multiple vales (Discrete, Range or Discrete and Range).
As an example:
I have a database table with a column called ID.
I can create a parameter called param_id and set the options of the parameter to "Allow multiple range values".
With this setup, I can limit the result set of the report by comparing the param_id parameter to the ID column in the database. Because param_id is a multi-value range parameter, I can pass it the following data:
1 - 50
60 - 80
150 - 127
This will only return results within those ranges.
Does anyone know if SSRS provides this kind of functionality?
Thanks,
Patrick Conway
View 9 Replies
View Related
Aug 7, 2007
Is there a way to copy reports from other users "My reports" folder? I am logging onto the management studio reporting services using an administrative account and I am able to view the reports from all users' "My Reports" folder. But I am not able to export them as an rdl file. I am able to export reports from other common folders, but not from the "My Reports" folder.
The reason I need to do this is some of the users have created some reports in one environment and the reports are available in their respective "My Reports" folder. I need to move these reports to their corresponding "My Reports" folder in another environment.
Is there a way to do this?
Thanks for your help.
View 3 Replies
View Related
Dec 31, 2007
Hi all,
I have two problems.
1. I downloaded ChartFXRSTrial and created one chart, and able to deploy it in ReportServer, but the problem is the reports are not showing there, i checked the configuration of .dll files in the help provided by chartFX, but couldn't get anything, so could help me on this.
2. How to give Tooltip for the X- axis or Y- axis values in the ReportServer , i tried using chartproperties of .rdl file, but didnt understand it. can help me on this, and one more i tried with Dundas too, If im giving tooltip as #valy then, it is showing samething in reportserver instead the values of 'Y-axis'.
Thanks,
Mahesh Manthena
View 1 Replies
View Related
Apr 10, 2006
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????
View 7 Replies
View Related
Mar 27, 2008
hi,
i need a query based on sorting.
i have date column and time as varchar in another column and other order details
datecol timcol ordername type
12/31/2007 1:00 pm order1 1
12/31/2007 5:00 pm order2 1
12/22/2007 6:00 pm order3 1
so i need to get query o/p as
12/22/2007 6:00pm
12/31/2007 1:00 pm
12/31/2007 5:00pm
based on date and time. pls help!
View 6 Replies
View Related
Jun 27, 2006
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
View 3 Replies
View Related
Oct 11, 2006
I have a text field in the form of numbers separated by periods like this: 1,1.2,1.2.1
When I sort, it works fine unless I get out to 10 digits.
Desired sort: 1,1.1,1.1.1,1.1.2,.....,1.1.9,1.1.10
Actual sort: 1,1.1,1.1.1,1.1.10,1.1.2,.....,1.1.9
The 10 is showing up smaller than the 2 because of the straight text comparison, any ideas on how to get around this?
View 6 Replies
View Related
Apr 18, 2007
Hi,
We just deployed a report solution and I would like to know when and how much each report is used. Is there a report for it ?
With regards,
Constantijn Enders
View 3 Replies
View Related
Aug 17, 2006
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?
View 2 Replies
View Related
Jun 25, 2007
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
View 1 Replies
View Related
Dec 13, 2007
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).
View 2 Replies
View Related
Jan 7, 2008
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)
Thanks for any advice!
View 2 Replies
View Related
Feb 26, 2004
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?
View 5 Replies
View Related
Aug 2, 2004
Hi,
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:
AKKER 1
BAKKER 2
CARNIE 3
Can someone please help me?
Greetings,
Mark
View 3 Replies
View Related
Aug 11, 2004
i have a question that is there any methods to order by the column which's datatype is ntext with t-sql?
View 1 Replies
View Related
Apr 3, 2006
Anybody know how to accomplish lexigraphical sorting? Any how-tos, docs, tuts or if you just want to paste code
THanks!
View 2 Replies
View Related
May 24, 2006
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>
View 1 Replies
View Related
Sep 24, 2001
hi,
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
View 1 Replies
View Related
Jul 7, 2000
I have a table with a composite primary key (ie a,b, autoInteger)
that includes an auto-incrementing system generated integer.
How do I sort out the records such that if a and b above repeat, I only
get the records with the highest autoInteger?
Thanks for any suggestions
Shelly
View 1 Replies
View Related
Sep 8, 1999
I need help sorting a date field. My field looks like this: dd/mm/yy. I want to sort by month. How would I do that?
Any help would be appreciated...
View 1 Replies
View Related
Jul 18, 2005
Hiya,
I'm back with another question.
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 :(
View 12 Replies
View Related
May 19, 2006
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
View 1 Replies
View Related
