Group By, Creating A Headache
Oct 12, 2006
I am using SQL server 2000.
While using query analyzer I am facing problem.
If a query has group by clause and if that query is not fetching any record (i.e. query is returning nothing), then in this situation, I want zero to be displayed where datatype of field is integer and "-" if datatype of field is varchar.
Please give me solution as soon as possible, a kind request.
Facing problem for the below mentioned query:-
select IsD.ItemCode,
case
when sum(IsD.IssuedQty) is null then 0
else sum(IsD.IssuedQty)
end as IssuedToday
from Inv_IssueMaster IsM, Inv_IssueDetail IsD
where IsM.IssueNo=IsD.IssueNo
group by IsD.ItemCode
In the above mentioned query, datatype of IssuedQty is int and for ItemCode it's varchar.
View 14 Replies
ADVERTISEMENT
Jul 23, 2005
Hi all,I'm trying to create a layout of our website for Marketing to review,and though I know how I want it presented, I'm not sure how to writethe SQL code to make it work. Here's a sample of the View I've writtenwith all our content:Level Title ID ParentID1 Clinics 11 Services 22 Surgery 1 22 ER 2 22 Radiology 3 22 Clinic 1 4 12 Clinic 2 5 12 Clinic 3 6 13 Heart 1 13 Lung 2 13 Physicians 3 43 Physicians 4 53 Physicians 5 6And the output would basically be this:Clinics- Clinic 1-- Physicians- Clinic 2-- Physicians- Clinic 3-- PhysiciansServices- Surgery-- Heart-- Lung- ER- RadilogyIs there anyway to do this in a SQL statement, or maybe Crystal orAccess? I even tried a Pivot table in Excel, but it wants to sumstuff, and I'm not working with numbers (sums, counts, etc). Alsosince I'm not using any aggrate functions MS SQL is complaining when iuse Group By.Thanks for any suggestions. Also though the layout is similar, theitems listed above are pulled outta the air. Hopefully I typed upsomething that makes since :)Alex
View 2 Replies
View Related
Apr 5, 2008
I have a table that I want to summarize the amount column and have it show the sub totals for the employee for each pay period. For example:
EMPLOYEE PerEndDate EarnCode Amt
A 8/1/2007 Hourly 100
A 8/1/2007 Peices 250
B 8/1/2007 Hourly 75
B 8/1/2007 Pieces 300
A 9/1/2007 Hourly 50
A 9/1/2007 Pieces 200
B 9/1/2007 Hourly 100
B 9/1/2007 Pieces 200
What I want to show is
Employee PerEndDate Amt
A 8/1/2007 350
A 9/1/2007 250
B 8/1/2007 375
B 9/1/2007 300
I'm using:
SELECT PayPeriodNo, EMPLOYEE AS PayEmpNo, MIN(PerEndDate) AS PerEndDate, SUM(AMT) AS Amt
FROM dbo._vPayroll
GROUP BY PayPeriodNo, EMPLOYEE
ORDER BY PayPeriodNo, PayEmpNo
And the quantity is showind grand totals for each instance
Employee PerEndDate Amt
A 8/1/2007 600
A 9/1/2007 600
B 8/1/2007 675
B 9/1/2007 675
Can anyone help me with this?
Thanks
Leo
View 2 Replies
View Related
Feb 22, 2008
Hello,
I've proposed to the head of IT at my organisation to head an database development export group to:
- Provide assistance in improving the performance of existing relational databases
- Provide assistance for the development of new database, e.g. correct construction of indexes; data contention, etc.
- Creation of database development standards
- Enforce the above standards for new databases so that the database is not deployed to production servers until it passes the standards.
I'd really like this to be a success as it should improve our rather crappy databases, basically because the people currently creating the databases don't know much about relational design.
Can anyone please post their experiences of setting something like this up, or working on such an expert group where they work? I'd really like to hear the good and bad experiences and what and (what not) to avoid.
Thanks
View 2 Replies
View Related
Aug 17, 2012
The requirement is to customize database admin activities by creating new user group.
Need to create a group of user / dbauser1 which will have restriction in seeing the data but they should be able to alter database - add / remove the data file , increase or decrease the data file space when required.
This requirement came we wanted to create a new dba group they should not be able to any user data / any table but increase / decrease / add / modify space etc.
View 1 Replies
View Related
Nov 9, 2015
I have the following column values (which represent the hours in a day) -
I want to be able to create a "child group" which would create 15 minute interval values under each hour, like -
0 15 30 45 60
So these would repeat under, each "hour" column value.
How can I do this ?
View 7 Replies
View Related
Mar 9, 2005
Hi All,
I have a table called Prizes. Here's how it looks in design view with some value placed inside for Illustration purposes.
PrizeID 1, 2, 3, 4, 5
PromotionID 1, 1, 1, 2, 1
PrizeName 10 Cash, 5 cash, 10 cash, 15 cash, 20 cash
My challenge is that I need to write a stored procedure for example, that will find the PrizeID associated with the 4th count of the PromotionID that equals 1 . So in this example, counting to the 4th PromotionID that equalls 1 give us a PrizeID of 5.
I hope I've made myself clear! Can anyone write out a mini SP on how to do this.
Many many thanks in advance,
Brad
View 2 Replies
View Related
Mar 13, 1999
I need to get this statement to run.
Declare @MiD datetime
-- The ATime is a datetime field in the table
-- @OrderBy is an input Parameter also, as is @MiD
SELECT @Statement = 'SELECT tblAudit.* FROM tblAudit WHERE ATime >= ' + @MiD + ' ORDER BY ' + @OrderBy
EXECUTE(@Statement)
@Statement then contains:
SELECT tblAudit.* FROM tblAudit WHERE ATime >= Mar 10 1999 ORDER BY Access_ID
I need the quotes around the Mar 10 1999.
Thanks for the help
View 2 Replies
View Related
Jun 24, 2004
Hallo
I am interrogating the structure of SQL Server database looking for the occurence of a particular phrase in the object names/definitions. This is in preparation for decommission/replacement...
When I run the script below in SQL Query Analyzer it returns about 200 results, but when I run the same query in my Access VBA code it returns much less!
SQL Script:
SELECT
so.ID,
so.name,
so.type,
CHARINDEX('insita',so.name) AS PosInObjName,
CASE WHEN CHARINDEX('insita',so.name) > 0 THEN 1 ELSE 0 END AS FoundInObjName,
CHARINDEX('insita',sc.text) AS PosInObjDef,
CASE WHEN CHARINDEX('insita',sc.text) > 0 THEN 1 ELSE 0 END AS FoundInObjDef
FROM
sysobjects so INNER JOIN syscomments sc on so.id = sc.id
ORDER BY [name]
VB Code:
Sub InterrogateDatabaseStructure(sServer As String, sDB As String, sSearch4 As String)
On Error GoTo err_IDS
Dim oCnn As ADODB.Connection
Dim oCmd As ADODB.Command
Dim oRs As ADODB.Recordset
Dim sSQL As String
Set oCnn = New ADODB.Connection
oCnn.ConnectionString = "driver={SQL Server};server=" & sServer & ";uid=;pwd=;database=" & sDB & ";dsn=;"
oCnn.Open
CurrentDb.Execute "DELETE * FROM PhraseSearchResults"
CurrentDb.Execute "DELETE * FROM PhraseInObjName"
Set oCmd = New Command
oCmd.ActiveConnection = oCnn
oCmd.CommandType = adCmdText
sSQL = "SELECT so.ID, so.name, so.type, "
sSQL = sSQL & "CASE WHEN CHARINDEX('" & sSearch4 & "',so.name) > 0 THEN 1 ELSE 0 END AS FoundInObjName,"
sSQL = sSQL & "CHARINDEX('" & sSearch4 & "',so.name) AS PosInObjName, "
sSQL = sSQL & "CASE WHEN CHARINDEX('" & sSearch4 & "',sc.text) > 0 THEN 1 ELSE 0 END AS FoundInObjDef, "
sSQL = sSQL & "CHARINDEX('" & sSearch4 & "',sc.text) AS PosInObjDef "
sSQL = sSQL & "FROM " & sDB & ".dbo.sysobjects so INNER JOIN syscomments sc on so.id = sc.id"
oCmd.CommandText = sSQL
Set oRs = oCmd.Execute()
With oRs
If Not .EOF And Not .BOF Then
.MoveFirst
Do Until .EOF
sSQL = "INSERT INTO PhraseSearchResults VALUES('"
sSQL = sSQL & .Fields(0) & "','" & .Fields(1) & "','" & .Fields(2) & "',"
sSQL = sSQL & .Fields(3) & "," & .Fields(4) & ")"
CurrentDb.Execute sSQL
.MoveNext
Loop
End If
End With
Set oCmd = Nothing
Set oCnn = Nothing
Exit Sub
err_IDS:
MsgBox "Error: " & Err.Description & vbCr & "Error Code: " & Err.Number
Screen.MousePointer = 0
End Sub
Can anyone please advise what I am doing wrong? Thanks.
View 1 Replies
View Related
Dec 5, 2005
Guys
I have a table 1 row, a start and end date of a period
create table xperiod(startdate datetime , enddate datetime)
insert xperiod (startdate , enddate)
values ('2004-04-01 00:00:00.000' , 2012-03-31 00:00:00.000)
I'm trying to retrieve a batch of 'smaller' periods from this where the relevant period is a number (of months) passed as a parameter (only ever 1, 3 or 6)
for example, if the parameter is 1 I will obtain the following rows each being a 1 month period starting at the xperiod.startdate value up to an end date of the xperiod.enddate value
startperiod endperiod
'2004-04-01 00:00:00.000' '2004-04-30 00:00:00.000'
'2004-05-01 00:00:00.000' '2004-05-31 00:00:00.000'
'2004-05-01 00:00:00.000' '2004-05-31 00:00:00.000'
and so on to
'2012-03-01 00:00:00.000' '2012-03-31 00:00:00.000'
if the parameter is 3 I will obtain the following rows each being a 3 month period starting at the xperiod.startdate value up to an end date of the xperiod.enddate value
startperiod endperiod
'2004-04-01 00:00:00.000' '2004-06-30 00:00:00.000'
'2004-07-01 00:00:00.000' '2004-09-30 00:00:00.000'
'2004-10-01 00:00:00.000' '2004-12-31 00:00:00.000'
and so on to
'2012-01-01 00:00:00.000' '2012-03-31 00:00:00.000'
Hope this makes sense !
I think I'll be ok on the logic for the while loop but my main problem is getting the endperiod value based on the startperiodvalue
Thx in advance
View 2 Replies
View Related
Aug 10, 2006
Hello - hope this is in the right group:We have just started with linked servers and have successfully createda view on SQL Server linked to a Progress database. I can query thisview happily in Query Analyzer.I have created an ASP.NET application to display this view in adatagrid but I get the following error:System.Data.SqlClient.SqlException: OLE DB provider 'MSDASQL' reportedan error. at System.Data.SqlClient.SqlConnection.OnError(SqlExc eptionexception, Boolean breakConnection) atSystem.Data.SqlClient.SqlInternalConnection.OnErro r(SqlExceptionexception, Boolean breakConnection) atSystem.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObjectstateObj) at System.Data.SqlClient.TdsParser.Run(RunBehaviorrunBehavior, SqlCommand cmdHandler, SqlDataReader dataStream,BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)at System.Data.SqlClient.SqlDataReader.ConsumeMetaDat a() atSystem.Data.SqlClient.SqlDataReader.get_MetaData() atSystem.Data.SqlClient.SqlCommand.FinishExecuteRead er(SqlDataReader ds,RunBehavior runBehavior, String resetOptionsString) atSystem.Data.SqlClient.SqlCommand.RunExecuteReaderT ds(CommandBehaviorcmdBehavior, RunBehavior runBehavior, Boolean returnStream, Booleanasync) atSystem.Data.SqlClient.SqlCommand.RunExecuteReader( CommandBehaviorcmdBehavior, RunBehavior runBehavior, Boolean returnStream, Stringmethod, DbAsyncResult result) atSystem.Data.SqlClient.SqlCommand.RunExecuteReader( CommandBehaviorcmdBehavior, RunBehavior runBehavior, Boolean returnStream, Stringmethod) atSystem.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehaviorbehavior, String method) atSystem.Data.SqlClient.SqlCommand.ExecuteDbDataRead er(CommandBehaviorbehavior) atSystem.Data.Common.DbCommand.System.Data.IDbComman d.ExecuteReader(CommandBehaviorbehavior) at System.Data.Common.DbDataAdapter.FillInternal(Data Setdataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords,String srcTable, IDbCommand command, CommandBehavior behavior) atSystem.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32startRecord, Int32 maxRecords, String srcTable, IDbCommand command,CommandBehavior behavior) atSystem.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at...The code in my ASP.NET application looks fine and works on non-linkedviews on the same server. Do I need to add a command or change asetting on SQL Server?
View 2 Replies
View Related
May 3, 2006
Hi Folks,
I'm still using the the SQL Server 2005 June CTP to develop. I'm working for a client that intends to upgrade, but has not done so yet. We believed that we had 365 days from the date of installation before it would expire (late July). But Visual Studio 2005 Beta 2 just expired, which is used by SQL Server Business Intelligence Development Studio, so now we can no longer develop SSIS packages or Reporting Services reports.
What can I do?
I thought that maybe installing Visual Studio 2005 express might work, but when I went there, it seems that it is split up into modules (e.g. Visual Basic 2005 express, Visual C++ express, etc...). I don't know if just one of these would be sufficient and if so, which one. Even if it would, it says I'll need to completely remove the SQL Server 2005 beta, as well as the Visual Studio 2005 beta, and I think the .net framework.
My contract is up at the end of June, and it was understood that the client was going to take care of the upgrade. Is there a quick fix here? What can I do with minimal effort? (I don't really have time to wait for the client to get the upgrade approved and done. I know, sniff, sniff! lol.)
View 1 Replies
View Related
Dec 10, 2005
This is more of an SQL server question than ASP.net.
A stored proc named MyStoredProc is created by admin account (sa) thus the owner of that proce is dbo.
When I log in to the database (Query Analyzer) as another login say User1, I can not use that stored proc since it was owned by sa. I get an error 'Invalid Object Name' or Object does not exist.
If I use the proper name qualifier like dbo.MyStoredProc, It works in the Query analyzser.
The problem I am facing is - My stored procedures, Views, Tables were created by sa on the development machine. Now its time for deployment. On the deployment machine, I am not the sa. I just belong to the db_Owner role. I have to log in as User1 and not as sa. In my c# code I have called the stored procedure as MyStoredProc. I get an error 'MyStoredProc does not exist' since I am loging in as User1 and not sa. In order to use the code, I have to change the ownership to this (and all other objects) to User1.
Is that the only way out or I am doing something wrong/missing someting? Your coments/opinions/suggestions welcome.
View 1 Replies
View Related
Sep 28, 2005
I have two tables, both with phone numbers and call times.
one is for incoming calls, one for outgoing calls.
I need to find all phone numbers from the incoming calls table where the number of calls exceeds 100 within the last 30 days, where the last call was within the last 15 mins, and where the number does Not exist in the outgoing call table within the last 30 days.
so far I have this...
(call record is the incoming, callout is the outgoin)
I believe this is giving me all records in the call record table that are within the last month, and not in the outgoing call table OR have not ben called within the last month..
SELECT cr.cli,min(cr.starttime)as "first call",max(cr.starttime)as "last call",count(cr.cli) as "number of calls"
FROM callrecord cr
LEFT JOIN callout co
ON cr.cli = co.cli
where (co.cli is null or datediff(dy,co.calltime,getdate())>30 )
and datediff(dy,cr.starttime,getdate())>30
group by cr.cli
order by cr.cli
i need to add in the 15 minute call check, and also only return those with a count of > 100
can anyone assist? i'm getting a headache :D
tia
a
View 4 Replies
View Related
Jan 4, 2006
Hello,
Harry Half wit here!!
I know that snapshot replication is the simplest form of syncing with SQL server and I can't even figure that out today!!.
I keep getting myself confused as to who should be configured to be a publisher, distibutor or subscriber etc etc.
My scenario is simple:
1 server creating a daily snapshot of a table and then 1 remote laptop (msde) pulls the snapshot into it's own database.
Heres what i did so far;
I configured the server to be a publisher and distributor (is that right?) and didn't set up any subscribers because i want to do that from my remote.
From the remote I did nothing but go into EM tools"create new pull subscription" but I cannot see the publication on the server.
SHould I set my remote to be a distributor to do this?
any help very much appreciated!!
View 1 Replies
View Related
Oct 12, 2006
hi all,
i am having a hell of a time trying to understand how im supposed to use the reporting services integrated with sql server 2005 express - advanced. i have searched high and low throughout vb 2005 express for the relevant controls, and i have searched the management studio trying to find the relevant information. there is no reporting server anywhere that i can see. i simply installed the sqlexpr_adv.exe and assumed that all the relevant features would be automatically installed. i cant find them anywhere (also something about a business intelligence thing? also missing). i would be extremely grateful if somebody could help me
regards
adam
View 2 Replies
View Related
Aug 8, 2006
I've got a stored procedure and one of the parameters is a DateTime. But no matter what I do to the string that's passed into the form for that field, it doesn't like the format. Here's my code: SqlConnection conn = new SqlConnection(KPFData.getConnectionString());
SqlCommand cmd = new SqlCommand("KPFSearchName", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = cmd.Parameters.Add("@DOB", SqlDbType.SmallDateTime);
param.Direction = dir;
param.Value = txtDOB.Text;
// also have tried this:
param.Value = Convert.ToDateTime(txtDOB.Text);
// and
param.Value = Convert.ToDateTime(txtDOB.Text).ToShortDateString;
No matter what I do I always get a formatting error - either I can't convert the string to a DateTime, or the SqlParameter is in the incorrect format, or something along those lines. I've spent a couple hours on this and hoping someone can point out my obvious mistake here...??Thanks for your help!!eddie
View 5 Replies
View Related
Nov 23, 2005
I'm trying to return an integer from the following table that returnsthe number of unique cities:tblEmployeesName CityJohn BostonFrank New YorkJim OmahaBetty New YorkThe answer should be 3.DECLARE @EmployeeCities intSELECT @EmployeeCities = SELECT ... ???How go I return one row/column into @EmployeeCities from a Count and aGroupBy?Headache already... Maybe it's too early...
View 3 Replies
View Related
Aug 13, 2007
There seems to be a lot of confusion around the groups about linkingto an Access mdb with the SQL Server Jet OLE DB provider and I haventbeen able to find a straight forward solution. Basically, I have anAccess MDB (A2K) on one server and a SQL Server DB (2005 std ed.) onanother - Both on the same network. I'm trying to create a linkedserver object in the SQL server to view data in the mdb. I've set itup and it works - but only from the server machine itself. If you tryto connect the the linked server from any other computer on thenetwork you get the usual access denied / file in use error.The fact that I can use the linked server from the server box itselfbut not from another pc on the network makes me think that it may be apermissions problem. But I am logging in with full Admin rights andstill no joy. Also there is no workgroup security on the mdb, so thatsnot the problem. I've used the surface editor to remove anyrestrictions that may cause problems, OLE DB connect, OPENROWSET etc.but still no joy.I've tried mapping the mdb's location on the server so I could use astandard filepath, rather than a //Server-Name/... path. Again, worksfrom the server, but not from any client PCs, so no joy there either.In frustration, I copied the mdb to the same server and viola - fullaccess to the linked server from anywhere. But this is no good, I needthe mdb file to stay where it is. An mdb full of linked tables wontwork... they don't show up in the linked server.So that's it - out of ideas! Am I just going to have to accept thatlinked server objects are limited just to mdb files on the same servermachine, or is there something I'm missing??? Firewalls, servicelogins, server settings.... something one of you gurus out there knowabout that might be the key to making my headache go away!!!Any input gratefully recieved!!!
View 1 Replies
View Related
Jul 20, 2005
Hi,I am having problems getting anything useful out of the index tuningwizard.I have created a table and inserted data into it. When i run the indextuning wizard i expect 2 indexes to be recommended so the book says(Index011 with a key on the uniqueid column and a non clustered indexnamed table02 with a key on the col03 and LongCol02)Instead i get nothing being recommended.What am i doing wrong????Please helpMaryamif exists (select name from dbo.sysobjects where name ='table01' andtype ='u')drop table table01create table table01(uniqueid int identity, longcol02 char(300)DEFAULT 'THIS IS THE DEfault column',col03 char(1))godeclare @counter intset @counter =1while @counter<=1000begininsert table01 (col03) values('a')insert table01 (col03) values('b')insert table01 (col03) values('c')insert table01 (col03) values('d')insert table01 (col03) values('e')insert table01 (col03) values('f')set @counter=@counter+1end
View 3 Replies
View Related
Oct 13, 2006
Hi,
I am currently importing (and exporting) binary flat files to and from Db fields using the TEXTPTR and UPDATETEXT (or READTEXT for export) functions. This allows me to fetch/send the data in manageable packet sizes without the need to load complete files into RAM first.
Given that some files can be up to 1Gb in size I am keen to find out a new way of doing this since the announcement that TEXTPTR, READTEXT and UPDATETEXT are going to be removed from T-SQL.
I had a quick foray into SSIS but couldn't find anything suitable which brings me back to T-SQL. If anyone knows a nice elegant way of doing this and is prepared to share, that would be grand.
Thanks for your time,
Paul
View 9 Replies
View Related
Jul 6, 2015
For a database, we have 4 data files in a particular file group and the file sizes are almost 70 GB each.
Do I come across any performance issues if I create/pre-allocate an additional data file in the same file group so that the existing files don't grow too much?
View 5 Replies
View Related
Sep 29, 2015
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, ...
View 4 Replies
View Related
Nov 27, 2007
Hi!
I've posted a feedback with Microsoft to see if we can get them to fix the issue described below, but so far no one from Microsoft has commented to let us know what they're doing about this problem! I'm posting this here to see if maybe we can get more people to rate this feedback or chime in on what a pain it is! Please feel free to add your own comments or how you had to work around this issue and whether or not you think this is something Microsoft should be addressing NOW.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=311679
Provide Individual Page Numbering per Group and Total Pages per Group
Currently in a Reporting Services report, you can't readily reset the page number for each group in a table, nor can you display the total number of pages per group. For example, if I'm printing invoices and each invoice is a separate group, I'd like to be able to print "Page 1 of 5" , "Page 2 of 5" etc. for the first invoice, then "Page 1 of 3" when the next invoice begins, and so on. This was easy in Crystal Reports. I realize that Crystal Reports has a two-pass process that enables that kind of pagination. However, this is REALLY important functionality that's just missing from Reporting Services and I'm hoping you'll provide it REALLY SOON! Yeah, I know there are work-arounds if you can know exactly how many rows of information there are on each page. But gosh! That's not practical, especially if you have second level groups inside the main group or text blocks in rows that can 'grow' to more than one line. I've read a couple of work-arounds, but none of them works correctly and consistently when more than one user is running the same report or when you print the report while you're looking at it on the screen. I still may need access to the overall report page number and the overall total number of pages, so don't get rid of that. It's just that if you're doing this already for the entire report, I don't see why you can't do it per group! Lots of people have been asking for this for years, and I don't understand why it hasn't been implemented.
I've read a few articles on this topic, but no one has come up with a decent work around. My theory is that Microsoft should be addressing this immediately. This is major functionality that's just plain missing from SSRS and should have been there from the start. If anyone from Microsoft can let us know what's going on with this issue or if anyone would like for me to clarify this further, feel free to let me know.
Thanks!
Karen
View 1 Replies
View Related
Nov 6, 2015
I have an SSRS report with groups that when exported to excel contains drill-in's (plus marks on left side). The issue I have is that for all the groups in the drill-in, those cells become merged. I want to keep the group drill-in but have the cells UNMERGED. I have heard this can be done with the RDL XML but I don't know what to modify to accomplish this.
View 4 Replies
View Related
Jan 10, 2014
I'd like to ask how you would get the OUTPUT below from the TABLE below:
TABLE:
id category
1 A
2 C
3 A
4 A
5 B
6 C
7 B
OUTPUT:
category count id's
A 3 1,3,4
B 2 5,7
C 2 2,6
The code would go something like:
Select category, count(*), .... as id's
from TABLE
group by category
I just need to find that .... part.
View 3 Replies
View Related
Sep 25, 2003
I have a user in SQL Server with a NT login of Mike
I changed his NT account to Mikel in User Manager
Now when I try to add Mikel, Im getting error 15401.
Do I need to delete NT login in SQL Server 'Mike' account first ?..before adding 'Mikel' ?
Can I go into the Master database and just change Mike login to Mikel ?
Thank you
View 3 Replies
View Related
Feb 28, 2014
I'm having a fight with Reporting Services at the minute when trying to compute an average at the row group level for a value summed in a column group.I have the following column groups:
Year
Month
Date
And the following row groups:
Region
Product
SubType (hidden, data at the date level is summed to Product)
At the moment I'm computing the average for SubType for each Date at the Product level (giving a decimal value), so for each day I end up with a nice average, that works. However I am unable to average that average over the whole Year for a Product. The issue being that I'm trying to combine Row Groups (Product) and Column Groups (Date/Year)
View 0 Replies
View Related
May 1, 2014
select top 15 count(*) as cnt, state from table
group by state
order by cnt desc
[code[...
Can the above three queries be combined into one and still be fast, if so how?What i am trying to go is an item count, by group, similar to ones Inbox in Outlook.
View 9 Replies
View Related
Nov 21, 2007
I have a need to show a row inside a table group to simulate a header row for the data rows inside the group. The table will not have a real header or footer. Thanks for the help.
View 1 Replies
View Related
Jan 5, 2008
I have one domoain in the forest. The domain level is set to Windows 2000 native mode and forest level is set to mixed mode. My SQL server 2005 server joined to this domain. I added a brand new domain local group and add a normal user account to this domain local group. I login to the SQL server 2005 server and make a query "SELECT * FROM sys.login_token". I cannot see my domain local group in sys.login_token. However, if I add my account to a global group, I can see it there.
Then, I setup another forest. This time, I have domain level set to Windows 2003 mode and forest level is set to Windows 2003 native mode. I do the same testing. This time, I can see my domain local group in sys.login_token.
Why does SQL server 2005 has this limitation? Is it a bug?
View 1 Replies
View Related
Feb 6, 2008
HiI am new to SQL and am having a problem. I need to fix my query to do the following...2) get a total of the number of rows returned.
DECLARE @StartDate varchar(12)DECLARE @EndDate varchar(12)DECLARE @Region varchar(20)
SET @StartDate = '01/01/2002'SET @EndDate = '12/31/2008'SET @Region = 'Central'
SELECTA.createdon,A.casetypecodename,A.subjectidname,A.title,A.accountid,A.customerid,A.customeridname,B.new_Region,B.new_RegionName
FROM dbo.FilteredIncident AINNER JOIN dbo.FilteredAccount B ON A.customerid = B.accountid
WHERE (A.createdon >=@StartDate AND A.createdon <= @EndDate)AND (B.new_RegionName = @Region)AND (A.casetypecode = 2)
View 1 Replies
View Related
Jan 29, 2007
I'm new to MSSQL 2005 and want to get a summary of a log table. I want to count all the rows for each date based on a DATETIME field called 'post_date' that holds the date and time of each record's creation.
this is the best I can come up with:
Code:
SELECT
DISTINCT(LEFT(post_date,11)) AS post_date, COUNT(DISTINCT(LEFT(post_date,11))) AS total_posts
FROM log_directory_contacts
GROUP BY post_date
The results show each date but the count column ('total_posts') returns '1' for every row even when I know their are more than 1 record on that date.
What am I doing wrong? Thanks!
View 9 Replies
View Related
