I have a group and a subgroup in a report. in the subgroup there are repetitive rows. But i want to display them at only once within the subgroup. So I hide them. Now I want to have the sum of a field viz. "quantity" group wise, having the only single value of the subgroup. But problem is in the sum the values which are hidden also included. But i don't want them to include in the sum of the group. Can anybody give the suggestions how to resolve the problem.. I am fighting with it from a long back but i am not able to solve the issue.. plz help me out..
This is what i want... for invoice inv/del/1 there are 3 items, i hide rest of two rows using "Hide Duplicates", But the group sum i.e. Location Total in the report is coming out as '13' as it includes the quantities of hidden rows also. But in actual I want '5'.
I need to get the groupwise distinct count . For eg. Lets assume we have group as Category and under this there are two childs Category1 and Category2. I need the distinct count under Category1 and Category2 separately with the count resetting after Category1 and Category2. Any help will be greatly appreciated.
is there any more efficient way for example to implement the next query?
SELECT s1.article, dealer, s1.price FROM shop s1 JOIN ( SELECT article, MAX(price) AS price FROM shop GROUP BY article) AS s2 ON s1.article = s2.article AND s1.price = s2.price; WHERE dealer = 'dealer sample'
SOURCE TABLE ID________COMMENT 123_______I am joe 123_______I am programmer 124_______I am Wang 124_______I am programmer 124_______I like cricket
DESTINATION TABLE
ID_____SEQ______COMMENT 123_____1_______I am joe 123_____2_______I am programmer 124_____1_______I am wang 124_____2_______I am programmer 124_____3_______I like cricket can somebody please advise the easiest way to do this in sql 2000?
How to insert a row number for a zone wise(ie group by zone column) in ssrs report in zone column  i should get zone1 only once (should not get Zone1,zone1, zone1 -3 times)
sl.no   Zone   District   no.of.region
1            hyd      24 2      ZONE1 chn      12 3            bang     2 1            raj       4 2      ZONE2  vizag    3 3            bbb     34
I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' Â values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) Â must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName          Clm ABC-X
X
Page2:
GroupName          Clm ABC-Y
Y
Page3:
GroupName          Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
I've been asked to properly schedule some jobs on our system for a database following a full recovery model. I realized that the order for some of the jobs might matter and came up with the solution, which is described below.
Please let me know if this ordering makes sense and/or if you have any general comments/suggestions:
1. Backup all dbs [in case defragmentation messes something up] 2. Defragmentation [this will grow the transaction log] 3. Backup transaction log [backs up trans log and truncates inactive portion of it] 4. Shrink log file
Also, I've been previously suggested not to shrink the log file, unless absolutely necessary, as it is resource intensive. In my experience, however, this operation does not seem to be resource intensive at all. It took me 1 second to shrink the transaction log file from 25GB to 2GB.
Is it regrowing of the log file that will affect performance?
Also, if I do shrink the transaction log file, is there a suggested value to shrink it to - possibly a function of the database file size?
I have the following three tables : Account (Id int, AccountName nvarchar(25)) Role (id int, Rights int) AccountRole (AccountID, RoleID)
In Role table - Rights Column is a bit map where in each bit would refer to access to a method. One account can be associated with multiple roles - AccountRole table is used for representing the N:N relation.
I want to develop a store procedure - which would return all AccountName and their Consolidated Rights. Basically I want to do a BitWise OR operation for all the Rights in the Aggregation instead of the SUM as shown in the following statement.
SELECT Account.Name, SUM(Role.Rights) FROM Account WITH (NOLOCK) JOIN RoleAccount ON RoleAccount.AccountID = Account.Id JOIN Role ON RoleAccount.RoleId = Role.Id GROUP BY Account.Name
Need to write a query which would select data based on weekly wise based on the column Date.And week starts from Monday. Hence would reqiure a function or a query which would select only those records which would fall under respective Weeks.
Dear All, is there any way to generate daywise scripts in database? i mean all the transactions wether insert update or delete.... i need all the changes on that particular date(with in 24 hours).... is there any procedure or function for this?
i've almost 1200 tables in that database.
note : due to replication is not possible for my scenario, i'm thinking in different ways to achieve my purpose.
this is my requirement http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96959
Vinod Even you learn 1%, Learn it with 100% confidence.
I have a table. Highlight------------- Id Name Detail StartDate EndDate Priority
I want to make a query which returns 1 Highlight in the current date. But remember I have already set the Hightlight Priority 1 to 5. And I want that Hight Priority rows select more times than Low Priority Rows.
Hi, I have a requirement where I need to display the items category wise I have 3 tables, one stores category_id, parent_id and category_name second table stored item_id,item_name and proce 3rd table stores category_id and item_id I need to display the values like this Category name, Item name with first parent category ans ites items should be displayed and next sub category and its item By category table with contain 2 levels of sub category i.e Category1 Category11 Category111 any help with the query would be much appreciated, I wrote the query something like this.... select c.category_name,parent_id,item_name from category c,items i,Category_item ci where ci.item_id=i.item_id and c.category_id=ci.category_id order by parent_id, category_nameBut it will display all parent category items then the sub category items I want to display like thiscategory1 items1category11 item11category1 item12category11 item111category11 item112 category2 item2 Thanks
How can I create a row wise trigger in MSSQL Server .
CREATE TRIGGER trigName ON tableName for INSERT , UPDATE , DELETE AS ...
For a multiple delete , I got only one trigger invocation . But I need individual trigger calls for each row ... How can I do this in t-sql ? Is there any usage like FOR EACH ROW in Oracle ? Is it possible through INSTEAD OF TRIGGER ? Please help !!!!!!!!
I am trying to get count of  records by month wise when they select year .It was showing the out put correctly but its showing months arer in numbers,but I want to display Jan,Feb ...
SELECT DISTINCT Standard, COUNT(Standard) AS Total,month(ReportDate) Month FROM CPTable where year(ReportDate) = '2015' GROUP BY Standard, Standard , month(ReportDate)
I'm using Reporting Services 2005. In my report I'm binding data in a matrix by grouping 3 row fields (nested within each other) and 1 column field. Now I get the subtotals alright but I also want to get percentage at row1 only rather than grouping it with either in row or in column. I've tried so many ways but am not able to do it. Can anybody help me on this or is there a way in SSRS 2005 to get over this.
Also please suggest something instantly, as I'm terribly in the need Thanks
Hi, The Report Manager portal has many folders. For each folder there are specific users with different roles. I am trying to figure out the way to extract User, folder wise security data. I want to run a query and retrieve users name, the folders they have access to and the user role corresponding to that folder.
Use ReportServer
SELECT u.UserName, r.RoleName FROM users u, policyuserrole pur, roles r
WHERE pur.UserID=u.UserID AND pur.RoleID=r.RoleID The above query fetches all the users and their roles. The folder information corresponds to Path column of Catalog table. Am unable link this table with the above query. TIA
I'm new to SQL Server 2005 encryption security. I developed a simple login form using .NET 2 framework. I'm encrypting the user's password on the execution of the INSERT statement using SQL Server EncryptByCert(Cert_ID('abc'),'password').
My only concern is that of decrypting the password on the execution of the SELECT dtatement in a stored procedure using the DecryptByCert(Cert_ID('abc'),val,N' certificate password'). Anyone who has rights on executing that particular stored procedure in SQL Server can easily return the user's password.
Can I still use the SQL encryption, whilst the login application handles the decryption (meaning I embed the DecryptByCert in the application ).
If anyone has other ways of implementing this please forward them? (I preferable would like to store all the certificates, private keys, etc on the database side )
The below query is for november month: (Apr to Nov)
select * from maintable where period between '01-04-2014' and '01-11-2014' then for December month : (Apr to Dec)
So I want to pass second row
select * from maintable where period between '01-04-2014' and '01-12-2014' ..... .... select * from maintable where period between '01-04-2015' and '01-12-2015'
Like wise I want to get month wise data of YTM data.
