I am having a bit of trouble with the sorting of grouped data.
I have a data set that returns sales data for stock items between a user specified date range. As a result I have to group by the stock items (description or code depending on a parameter value) to prevent displaying duplicate stock items and to get aggregate sales data. However I also need to be able to sort this group by three other aggregates but have not been successful. I have done the following:
The group expression is as follows:
Function Group4(fields As Fields) As String
Dim Group As String
If( Report.Parameters!ReportSetting_ReportType.Value <> "1" AndAlso Report.Parameters!ReportSetting_DetailSummary.Value = "1" ) Then
Group = fields!Group2Desc.Value
Else
If Report.Parameters!ReportSetting_SortType.Value = "1" Then
Group = fields!StockDesc.Value
Else
Group = fields!StockCode.Value
End If
End If
Return Group
End Function
The sort expression for this group is as follows:
=Sum(Code.SortExpressionNumeric(Fields), "table1_Group4")
where SortExpressionNumeric is as follows:
Function SortExpressionNumeric(fields As Fields) As Decimal
Select Case Report.Parameters!ReportSetting_SortType.Value
Case "3"
Return fields!TillSalesValue.Value
Case "4"
Return fields!TillSalesQty.Value
Case "5"
Return GrossProfit(fields)
Case Else
Return 0
End Select
End Function
where GrossProfit is another function that returns a decimal value.
When I preview my report with ReportSetting_SortType = 1 or 2 everything displays as expected. However when I set ReportSetting_SortType = 3,4 or 5 the data does not sort how I need it to.
I have a new SQL 2005 (SP2) Reporting Services server to which I've just upgraded and deployed some SSRS 2000 reports.
I have a subreport that contains a matrix with two groups. The report data seems to be inexplicably repeating the data for the first row in the group for all rows in the group. Example:
ID1 ID2 DisplayData
1 1 A
1 2 B
1 3 C
2 1 A
2 2 B
2 3 C
Parent group is on ID1, child group is on ID2, report would show:
1 1 A
2 A
3 A
2 1 A
2 A
3 A
Is this a matrix bug in 2005 SP2, or do I need to do something differently? I can no longer pull a comparison version from an SSRS 2000 server to verify, but I believe it was working as expected before...
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.
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 expect to get a record below with a count of 0 (and I do), but when I take the comments out (--) of lines 1 & 6 I don't understand why I get no records at all. I need to be able to see all teams in EvalAnswers even if none of the records satisfies the where clause.1 select Count(*) as cnt--, TeamID 2 from EvalAnswers 3 where CoID=@CoID 4 and EvaluatorID=@EvaluatorID 5 and (Scr0=0 and Sugg0 is NULL) 6 --group by TeamID 7
Hi, I would like to create groups in SQLServer2000.Do i need to do this by roles or can i create groups for each database.I read some where that there where no groups for SQLServer7.0. Is this option available for the latest versions. Any help will be appreciated.
I have a Attendance table named say Attendance which has Class and Status along with some other fields.Class will have the name of the class the student is in,whereas the status will have the Absent/Present data.There is another table named say ClassMaster which is the master which will have all the classnames and there is a column of ClassTeacher which will have the EmployeeCode.This employeeCode comes from a table EmployeeMaster.My requirement is i want to get the count of Absent students in Attendance table depending on the Class.For Example if there are two class say ClassA and ClassB,then i should get the count of the Students from Attendance table that belong to Class A and Class B.How cn i do this in one Query.
I am looking to find out when to use file groups when backing up. When should you use this, what's the benefit over just doing a full db backup? Is it better when you are dealing with large db's?
Also this question has been on my mind for a while. Why shouldn't you shrink the db after every full backup? What is the negative in doing so?
My question is, is this possible using a Matrix? I have used matrix in some of my reports but have always used automatic/generic SUM feature it provides.
Note carefully that there are no values for Employees in Demand Column Group. Basically I would have to SUM Department groupings to get the Project level total and then SUM all Project Groupings to get the grand total. The Allocation column is okay since the Employees have values in that column. Can this be done using InScope( ) or some other function rather than simply doing the Generic SUM of Rows and Columns of the matrix which would probably throw error when trying to sum a blank value in demand column.
How do I put information only at the top of a page and have it change whenever a group occurs.
I want to do something like this. Have this on the top of every page:
Country: current country
State: current state
City: current city
There is a group on country, state and city. Whenever city changes the next page will display a new city. Whenever state changes the next page will display the new state in the header.
I have already put Country, State, and City into their own group headers. However, if either of them change in the middle of the page then the group header repeats. I have group header repeat on so I can see it on each page. If I choose the Page break at start then it breaks but I don't want it to break. I want it to run on the same page if it passes a group and then have the next page display relevant information for the group.
Putting it in the table header works as far as keeping them all on the top of the page but it doesn't change when a grouping ends.
I have my first SSRS report going, after years on Crystal and Access. It has several groups, each nested within the next...I think. I'm having a little trouble with the groups...well, grouping the way I thought I had it set up. The groups come out looking like my primary group is second instead of first, although when I look at the groups in "edit groups" it appears I have them set up correctly. In Crystal, on the preview page you could see the groups idendified so if there was a problem in a report you could tell what group it was coming from. You could also select and change objects in preview, which made it nice to see the impact of changes. I cannot see either of these features in SSRS. I'm hoping that there's a setting I need to turn on, and also hoping that this product has these user friendly features. Any ideas on that?
Also, in Crystal, when you set up a group or a formula, it became available in the field list within the formula builder, so you could use them by picking them instead of typing the whole thing in again, and that's not available either. Or I can't find it!
Since I'm new to SSRS maybe there are features and settings I haven't found yet, but would appreciate any comments from more experienced users about the overall usability of this tool.
Is there any way to keep a table group together. MSFT says no but I can't believe this limitation exists. It's a basic reporting function in any reporting software I've used.
I have one group in a table. I don't want the detail rows to be split when it gets to the bottom of the page.
I have a 3rd party app that needs me to add a user to the SQLServer2005SQLAgentUser group for the instance I placed the DB in. This group doesn't exist on my server for any of my instances. Is there a simple way to generate it or a document that can tell me how to manually create it?
I'm wondering if there is any way in RS to create what Crystal reports calls Custom or Named Groups. This functionality allows you to define groups based on the values found in one of the fields on your report. For example, if I were grouping on a person's last name and I want 3 groups based on the first letter: A-G, H-P, Q-Z. I would want to keep it contained in one table.
I have a challenging report to create. I need to create a report where I'll have groups then I need to have a ceiling on the group to print only 10 records (if there are more than 10 records) then go to the next page and print the final records for that group. I can do the grouping and I can do the ceiling, but I am having a hard time doing both. I'm open for any suggestions.
Hi everyone, While creating our database in only one disc(C or D), suppose that we create more than one file group in order to group our data files. However, in this situation; I wonder that whether it brings any benefit or advantage to us.
Also, I wonder that why we always have to put our data file into separate file group if we use separate discs for data files. Is not it allowed to use only one file group even if we use separate dics ?
Hello, in a report in Reporting Services 2005 I want to create a top count 100 for groups. I have a report sheet with a table containing some groups. Under the groupings there are other groupings and so on. Now I want that only the top 100, ordered by a value on group level of the 1st group are contained in the report.
Is it possible that i can create a SQL Server Group in sql 2005. Eg. in 2K when u right click (in Enterprise manager) on Microsoft Sql servers you can see "New Sql server Group". I am talking about this group. I have many servers in NYC and Dallas and in VA. I want to group them in the group names NYC DAL VA. is it possible.
I am thinking of updating my SQL monitoring application to use Service Broker.
Right now I loop through my list of servers performing various checks on each server. Things like 'check last database backup', 'check for new databases', 'check for server restart'. I loop through, one server at a time, doing one check at a time. The more servers I have the longer it is taking.
So, I want to multi-thread the servers, but single-thread the checks on each individual server. This way I can check say, 5 servers at a time, but on each server I will only do one check at a time. This way I won't flood an individual server with multiple checks.
Is this possible? It looks like Conversation groups might be the way to go but I'm not sure.
I'm having some troubles with conversation groups. I need to send two messages on the same conversation group so I have the following in my SP....
BEGIN DIALOG CONVERSATION @providerConversationHandle FROM SERVICE [ProviderDataService] TO SERVICE 'CalculatedDataService' ON CONTRACT [ProviderDataContract] WITH ENCRYPTION = OFF , LIFETIME = 600;
BEGIN DIALOG CONVERSATION @curveConversationHandle FROM SERVICE [ProviderDataService] TO SERVICE 'CalculatedDataService' ON CONTRACT [ProviderDataContract] WITH RELATED_CONVERSATION = @providerConversationHandle , ENCRYPTION = OFF , LIFETIME = 600;
SEND ON CONVERSATION @providerConversationHandle MESSAGE TYPE [ProviderDataMessage] ( @providerMessage );
SEND ON CONVERSATION @curveConversationHandle MESSAGE TYPE [ProviderCurveMessage] ( @curveMessage );
When I query the queue I see two messages, but they don't have the same conversation_group_id.
Using: SSRS 2005, SSAS 2005Question: Can we use Filter Top N on Groups? Detail:I have a table in RS with data sourced from an AS cube that has a structure like this:
I want to return only the top 10 categories by descending quantity. I can't get the filter to work on either the table or group level properties.
FILTER:Expression: =sum(Fields!Quantity.Value)Operator: TopNValue: 10ERROR:Failed to evaluate the FilterValue
Do the Top and Bottom functions only work on the detail level? Is there some other method I should be using?
I've tried to do this with the query using the MDX AGGREGATE function but having trouble getting it to return proper results. (Details in this thread: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3203246&SiteID=17 )
So as a workaround to get the project delivered on time, I'm reluctantly bringing in the date level and trying to do the aggregations and filtering in the RS table.
I know how to hide groups using Expressions that use parameters that can be passed in, but can you hide groups that are inside other groups. Here is my situation. Our company has different organization levels. On reports the users want to select which organization levels they want to see the #s for. So I set up groups for each org level like so....
Org Level 1 Org Level 2 Org Level 3
The data results are like so
Org Level 1 Org Level 2 Org Level 3 Category $ Amount USA Midwest Kansas 1 500.00 USA Northeast Maine 1 200.00
And I sum the $ Amount Per Category for each Org Level using grouping in the report.
Then based on the selections they make it hides the org levels they don't need to see. The issue is that the report will not show org level 2 or org level 3 if org level 1 is not visible, and org level 3 can't be visible if org level 2 isn't visible, etc.....Anyone know a solution to this situation, I know this has had to have come up.
We have began to use Windows AUthentication for our SQL 2000 Server. When we llok atthe groups in AD we do not see any Domain Local groups? Is there a reason for that? Can I just add the group without browsing? I assume DLG will work with SQL for security.
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).