Suppress Repeating Data In SQL Report
Jan 10, 2007
I am trying to write a report that displays a hierarchy such as:
Director, Manager, Employee
I want to suppress the repeating Director, Manager for the employees.
This is my code:
sSQL = "SELECT FY99Info.MDName, FY99Info.MgrName, FY99Info.firstname,
FY99Info.lastname, FY99Info.grade
FROM FY99Info
WHERE left(FY99Info.orgcode,5) = '" & left(session("MOrg"),5) & "' and
FY99Info.mgmtlevel < 4
ORDER by FY99Info.MDName, FY99Info.orgcode, FY99Info.mgmtlevel desc "
This is the SQL table layout (FY99Info).
Emp#OrgDirectorManagerEmployeeMgmtlevel
110336088Brian SmithMichael SmartBill Mitchell0
210336088Brian SmithMichael SmartHeidi Rainey0
310336088Brian SmithMichael SmartPonita Asnor2
410336088Brian SmithMichael SmartMarcus Jones0
510607655Rick DoeGlenn ThomasHelen Kelley0
610607655Rick DoeGlenn ThomasGaston Knight2
710607655Rick DoeHelen BlackPrentis Parker0
810607655Rick DoeHelen BlackBillie Spike0
910739900Jason SmartTim SnowJoe Monty3
1010739900Jason SmartTim SnowRandi Bull3
1110739900Jason SmartTim SnowMisty Wonton2
This is how I want it to display on the report
MDManagerEmployee
Brian SmithMichael SmartBill Mitchell
Heidi Rainey
Ponita Asnor
Marcus Jones
Rick DoeGlenn ThomasHelen Kelley
Gaston Knight
Helen BlackPrentis Parker
Billie Spike
Jason SmartTim SnowJoe Monty
Randi Bull
Misty Wonton
View 2 Replies
ADVERTISEMENT
Mar 28, 2006
i'm retrieving addresses from a database and displaying them in my report. i have an addr line 2 for addition address data if needed. i have placed this addr line 2 on its own detail row. however i do not want that row to display if there is no data. the following is happening even though i have set the visibility on the row and text field to =iif(fields!addr2="",false,true)
the name prints on the first line, the main address on the second line, i have a space where addr line 2 would have been, finally i get the city, state, zip on the last line.
expected outcome i would like is that addr line 2 does not appear for those addresses that addr line 2 does not have any data. if addr line 2 does have data then print.
any help would be greatly appreciated.
Chuck
View 7 Replies
View Related
Apr 21, 2015
I have a SSRS report with 4 sub reports embedded. Previously i faced a situation where sub reports was getting displayed with static data even if there is no data being pulled from data set. I solved this problem according to solution provided in my previous post here. Now blank space is getting displayed in place of empty sub report. But still the main report that hosts empty sub report (Which has a header with header name coming from main data set) is getting displayed. Is there any way wherein the whole main report can be suppressed when the sub report has no data?
View 3 Replies
View Related
Jan 30, 2008
I'm trying to suppress whitespace in a drilldown for textboxes that have suppress duplicates applied.
I have a matrix report that is showing whitespace in a drilldown because I am supressing duplicates. Based on what I read in other forums, if I set the ToggleItem to Len(FieldName)=0 that should supress the whitespace, right?
I can see that I have a field in the toggleitem called: Firstname. If I put the value Len(Firstname)=0 in the toggleitem property, then I get the error: The textbox 'textbox21' has Len(Firstname)=0' as a toggle item. Toggle items must be text boxes that share the same scope as the hidden item. I think the code 'Len' is throwing it off.
If I put the value "Firstname" in the toggleitem property, then it doesn't return the error, so I know that firstname is a valid value for toggleitem, but setting the value to firstname doesn't suppress anything.
If someone can tell me how to supress a textbox based on a value, then this may get rid of the whitespace I'm trying to suppress. Any ideas? Thanks...
View 3 Replies
View Related
Sep 5, 2007
Hi,
In Crystal Reports, you could suppress the printing of a report if no rows were retrieved from the database server (ie: zero pages would be sent to the printer). This was done by opening the Report Properties dialog in the report designer and setting the "Suppress Printing if No Records" to true.
Is there a way I can reproduce this behavior in SQL 2005 Reports?
Thanks!
Stephen
View 2 Replies
View Related
Jan 30, 2006
Since there is no way to pass value from subreport to the main report, how to suppress the subreport when it is blank?
Thanks.
View 29 Replies
View Related
Jul 6, 2007
Hi,
I am getting unusable warnings from dataflow task while running. Say for example I am getting column A, B, C from source and I am using only column C in destination. Whenever I run the package, I am getting a warning saying that
"The output column "A" (67) on output "Output0" (5) and component "Data Flow Task" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance."
Can any one help me how to resolve these warnings?
Thanks in advance.
View 11 Replies
View Related
Feb 12, 2008
Hi everybody!
I've got this little problem.
I need to insert data from a table to another table.
The scenario looks as follows:
I've got 'Company' table (no duplicated records there) and 'Contacts' table (one to many relation: for one company there can be more than one contact).
The following statement retrieves the data but it shows me everything, including all contacts and therefore I get duplicating values, e.g. company name.
Is there any way of changing the following query so it works?
INSERT Projects(
CompanyID,
ContactID,
CustomerName,
EntryTime,
SetupFee,
ForecastRevValue06_07,
ClosureProbabilityID,
ExpectedClosingDate,
TechnologyID,
Service_ProvidedID,
Dropped,
AgreementTerm,
AgreementTermDisplayOnly,
ForecastStartDate,
ForecastEndDate,
LongTerm,
UserID
)
SELECT Distinct
CI.CompanyID,
CC.ContactID,
--CC.FirstName + ' ' +CC.LastName,
getDate(),
0,
0,
5,
DateAdd(month,2,getDate()),
2,
1,
0,
2,
'2 month(s)',
DateAdd(month,2,getDate()),
DateAdd(month,4,getDate()),
0,
2
FROM
CompanyInfo CI
left outer JOIN CompanyContacts CC ON
CC.CompanyID = CI.CompanyID
Kind Regards
View 11 Replies
View Related
Sep 4, 2007
Hi
Pulled all my hair out now, can someone please offer some help before I go totally mad...
I have no page header, this is all in the body. I have a List with account number and job number in it at the top, called list1. In list1 I have list2, with the job details, basically a list of people who attended the job. The fields in list2 are textboxes, they're not in a table.
EG:
Acc No: 12345
Job No: 54321
Name
Fred Bloggs
Joe Bloggs
Etc
When my list2 of names goes over 1 page, I want to repeat the account number and job number on each subsequent page. In my real report I actually have 10 fields I want to repeat.
I then want to be able to export this report to a PDF.
I can't put the header in the page header and RepeatWith doesn't work when exporting to PDF, or at all depending on what mood it's in.
Does anyone have a workaround for this please?
Cheers
View 2 Replies
View Related
Dec 3, 2007
Hi,
I have a matrix which displays like this
Title1 A Book
Another Book
Title2 Yet another book
My Book
How can I display it like this:
Title1 A Book
Title1 Another Book
Title2 Yet another book
Title2 My Book
So that when I export this to excel I can sort and filter data easier. Thanks very much.
View 4 Replies
View Related
Aug 3, 2007
I have run into a strange issue that I believe is a SQL Reporting Services issue.
I have a report laid out in landscape setting that has 4 columns of text. Two of the columns are sub-reports (due to the complexity and size, we did not flatten out the data in the stored procedure) and two of the columns are regular fields.
The 2 columns of regular fields are smaller, and normally only grow to about 1/2 the height of he page. One of the two sub-reports contains large amounts of text, and at time grows larger than the height of the page.
When the sub-report grows larger than the current page, it correctly starts up on the next page. But the 2 fields of data from the main dataset (not the sub-report columns) repeat themselves on the next page as well.
What is even more strange is the 2 fields of data from the main dataset only repeat data to grow vertically as far as the sub-report needs to grow. So if there is more data in either of these 2 fields than is needed for the sub-report to grow on the 2nd page, it will cut off the data in both of these fields.
I have tried placing the information in a group header. Turned the "Repeat on new page" both True and False, Took away the table header and footer, forced a page break after each group, tried using the "Hide Duplicates" property on the field within the details section, and nothing has seemed to fix the issue.
If anyone has run into this and found a work around, let me know.
Thank you,
T.J.
View 1 Replies
View Related
Jan 31, 2008
Can someone tell me why my stored procedure is repeating the Name in the same column?
Here's my stored procedure and output:
select distinct libraryrequest.loanrequestID, titles.title, requestors.fname + ' ' + requestors.lname as [Name],
Cast(DATEPART(m, libraryrequest.requestDate) as Varchar(5)) + '/' + Cast(DATEPART(d, libraryrequest.requestDate) as Varchar(5)) + '/' + Cast(DATEPART(yy, libraryrequest.RequestDate) as Varchar(5)) as RequestDate,
Cast(DATEPART(m, libraryrequest.shipdate) as Varchar(5)) + '/' + Cast(DATEPART(d, libraryrequest.shipdate) as Varchar(5)) + '/' + Cast(DATEPART(yy, libraryrequest.shipdate) as Varchar(5)) as ShipDate
from LibraryRequest
join requestors on requestors.requestorid=libraryrequest.requestoridjoin titles on titles.titleid = requestors.titleidwhere shipdate is not null
Output:
ID Title Name Request Date Ship Date
29 Heads, You Win Brenda Smith 1/18/2008 1/18/200835 Still More Games Brenda Smith 1/22/2008 1/22/200851 The Key to.. Brenda Smith Brenda Smith 1/29/2008 1/29/200852 PASSION... Brenda Smith Brenda Smith 1/29/2008 1/29/200853 LEADERSHIP Brenda Smith Brenda Smith 1/29/2008 1/29/2008
Going crazy ugh...
View 3 Replies
View Related
May 13, 2008
I am querying several tables and piping the output to an Excel spreadsheet.
Several (not all) columns contain repeating data that I'd prefer not to include on the output. I only want the first row in the set to have that data. Is there a way in the query to do this under SQL 2005?
As an example, my query results are as follows (soory if it does not show correctly):
OWNERBARN ROUTE DESCVEHDIST CASE
BARBAR TRACKING #70328VEH 32832869.941393
BARBAR TRACKING #70328VEH 32832869.941393
BARBAR TRACKING #70328VEH 32832869.941393
DAXDAX TRACKING #9398VEH 39839834.942471
DAXDAX TRACKING #9398VEH 39839834.942471
DAXDAX TRACKING #9398VEH 39839834.942471
TAXTAX TRACKING #2407 40754.391002
TAXTAX TRACKING #2407 40754.391002
TAXTAX TRACKING #2407 40754.391002
I only want the output to be:
OWNERBARN ROUTE DESCVEHDIST CASE
BARBAR TRACKING #70328VEH 32832869.941393
DAXDAX TRACKING #9398VEH 39839834.942471
TAXTAX TRACKING #2407 40754.391002
Thanks,
Walt
View 4 Replies
View Related
Oct 1, 2007
Hopefully this makes sense, not sure what to even begin researching...
I'm trying to optimize all facets of this process, as it will take over the resources on my server if not done efficiently.
I have CSV files containing INTs that I need to upsert (match to an existing/earlier imported array or create a new record set) millions of times a day. To be clear, this data is a small subset of the actual import, this arrays contents are not the main data of the process, and the value of the entire array is meant to be related to higher level tables.
The contents of the CSV array are 99.9+% repeating, meaning they will very often share the exact same contents as a a previously imported array. A rough guess is there are 20k combinations existing, and less than 1k new per month, and will range from 6 cols x 15 rows to 6 cols x 50 rows.
So current plan is to use a MD5 hash during the (not SQL related) export process to identify the contents of this CSV file, and export only the md5 (32 digit hex) as a lookup to identify the contents. If the SQL import process finds a new (unknown) MD5 it will request the actual contents, otherwise it will simply use the MD5 as a key/id/code for the actual array contents that are already stored.
There's probably a certain terminology I'm not familiar with for this type of thing.. I've never heard of something like this. I realize collision is a threat, but I'm unsure how much I should be worried about it with this type of data (similar size/contents, but a relatively small amount of possibilities). I think up to even 0.1% collision would be acceptable which is probably way more than enough.
Does this sound like a bad idea to anyone? Are there certain hash functions I should use for this type of thing? Anyone have suggestions of where to look next?
Thanks!
View 1 Replies
View Related
Oct 5, 2006
I'm using RS2000 SP2 and am getting an issue when exporting to PDF. If I have a table that spans more than one page and I set the RepeatHeaderOnNewPage to True, then on occasion the table header will be displayed on top of the first few rows of data. It does not happen on all the pages or all the time and I can not find any information on this issue. Has anyone come across this before and solved it?
View 2 Replies
View Related
Dec 16, 2006
Hi,
I have a report which contains a subreport placed inside a list. when the subreport returns no data. it appears as a blank space in the main report. So i want to suppress the list when the subreport returns no data. Can somebody help me with this?
thanks
shri
View 1 Replies
View Related
Jan 25, 2008
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...
View 2 Replies
View Related
Jun 16, 2006
SQL server 2005 express reporting problem.
error message:
This feature "remote access to report data sources and/or the report server database" is not supported in this edition of reporting service
I got this error message when I try to connect to database hosted in another PC running SQL server 2000.
Is it true that SQlL server Express can only use Local Database Engine to host the database?
View 5 Replies
View Related
Nov 28, 2007
Is there a way to suppress a text box if there is no data to display. I've created a letter that may or may not have address2 address3. The format looks odd with a bunch of blanks lines in the company info section. I use to be able to do this in crystal don't know how to do this in SRS.
Thanks
View 11 Replies
View Related
Apr 24, 2002
Hello, I have 3 servers sql 7 sp3, 1 editor, a distributor and an subscriber. the base on the editor is removed, how to make to remove all traces of replication on the distributor and the subscriber.
thank you. Pascal
View 1 Replies
View Related
Aug 19, 2001
I wrote a stored procedure that executes a dbcc sqlperf(logspace) statement many times. Therefore, I would like to suppress the output, "DBCC execution completed...". Can this be done? I have checked Books Online for a trace flag or set command to no avail.
View 1 Replies
View Related
Apr 24, 2006
I just scanned the OSQL articles in BOL and I am not sure if there is a way to do this but I want to suppress the empty black DOS windows when calling my batch files that contain osql commands. Any ideas?
View 2 Replies
View Related
Jan 12, 2004
When I call osql -S server -E -h-1 with a -i option, the feedback I get is prefaced with "1> 2> 3>".
The test script is simply:
set nocount on
use master
select name from sysdatabases
How can I suppress the "1> 2> 3>"?
Thanks
View 2 Replies
View Related
Sep 18, 2007
Is there any way to suppress the whole package from failing when a file is not found while doing an FTP get (other then setting the MaxErrorCount higher)?
My situation is that I know what a file is named but the file may not exist yet on the server. So, I use an FTP task to do a Get on that file. If the file does not exist then the FTP Task increments the error count even if I force the ExecutionResult and/or ExecutionValue.
This wouldn't be a very big deal but I am looping to getting and processing multiple files. It's acceptable that the file does not exist, but I want to be able to have the package actually error in other places. So far, the only thing I can do to stop the package from failing is setting the MaximumErrorCount for the Loop and the Package which isn't going to cut it.
I am hoping there is away around this without having to use a 3rd party FTP Task.
Thanks,
-Ryan
View 6 Replies
View Related
Nov 28, 2007
Hi,
I am running SSIS package using command line. However I want to suppress the messages that come and I want to display my own messages. How can I do that?
For eg: I am using File System task to transfer a file from source to destination. How will i tell this to the user who is seeing this executed at the command line?
thanks.
View 1 Replies
View Related
Apr 8, 2008
Hello All,
I have an SSIS package which uses a third party RMS driver. It is not licensed and so it throws a warning messagebox when the package is run and every time the connection is used. Is there a way to suppress these messages so that the package can be scheduled and run without user interaction?
Thanks
Arun
View 10 Replies
View Related
Feb 26, 2007
This is a problem I usually solve with procedural code, but I am wondering how/if it could be done with SQL queries.
A simple one to many query like:
Select inv.invnbr, inv.freight, invline.quantity, invline.partnbr, invline.cost from inv inner join invline on inv.id = invline.InvID
Returns something like:
invnbr freight quantity partnbr cost
100 50 3 abc 50
100 50 6 def 65
100 50 10 ghi 70
Is there way I can rewrite the query, or add a subquery such that the result set would be:
invnbr freight quantity partnbr cost
100 50 3 abc 50
100 0 6 def 65
100 0 10 ghi 70
Eg, the freight value, which comes from the one/header table, would show up in only one of the lines for invnbr 100?
Many thanks
Mike Thomas
View 9 Replies
View Related
Mar 31, 2008
Hi,
I have a sequence container with a bunch of tasks in it. Right now, it's set up so that if any of the tasks in the sequence container "errors out" it sends out an email. However, there are certain tasks in the container that I would prefer NOT to send an error message out if/when they fail. How do I turn off the error message for certain tasks?
Thanks
View 2 Replies
View Related
Feb 13, 2008
I have a Derived Column transformation that pads some string fields with spaces, out to the field's maximum length using an expression similar to
rt_tran_date_bill + REPLICATE(" ",20 - LEN(rt_tran_date_bill))
This gives me a warning
"
Warning: 0x800470D8 at Merge Assessments and Invoices, Derived Column 1 1 [11661]: The result string for expression "rt_tran_date_bill + REPLICATE(" ",20 - LEN(rt_tran_date_bill))" may be truncated if it exceeds the maximum length of 4000 characters. The expression could have a result value that exceeds the maximum size of a DT_WSTR.
Is there any way to suppress the warning, or construct the expression so that it doesn't generate the warning?
In this example rt_tran_date_bill will never exceed 20 characters
View 10 Replies
View Related
Sep 2, 2004
I really must be missing something here...
Trying to cross-update 2 tables.
Picture a checkbook reconcilliation without common check numbers. The checkbook has uniqueids and the bank has transaction ids but they are different. So the match is on date/payee and amount. So I wrote 2 checks to the same person, on the same day, for the same amount but forgot to enter one in the register.
when i run the update statement:
update b set b.bankid=c.myid
from checks c
join bank b on c.cdate=b.cdate
and c.payee=b.payee
and c.cost=b.cost
Both bank statement records would be updated to my one check record [can't happen]
Also: this will be running on a hundred thousand records per month with potential for duplication/ommission on either side.
What's a poor newbie missing??
I'm doing something similar on a lesser volume by running sequential statements through an ASP script but performance is poor. I know SQL can do this, just not how to approach it.
Thanks for any guidance
Dale
View 4 Replies
View Related
Jun 27, 2014
I have a simple update statement that will update one field, and that field is part of the primary key. During the update process, some of the rows will cause duplicate error. Is there a way to update the table and suppress the error? What I am looking for is a way to update the records that it can and ignore those it cannot. Right now, the entire process is terminated if duplicate error occurs.
View 2 Replies
View Related
Sep 14, 2006
Is there a way to supress output on one column in a SP, using data from the same row?
Like This:
SELECT Last, First, DOP, dbo.fnDueDate(DOP, 3, GETDATE()) AS NextQDue, dbo.fnDueDate(DOP, 6, GETDATE()) AS NextNSPDue, DATEADD(m, 1, DOP)AS InitialNSPDue, DATEADD(m, 1, DOP) AS InitialAssessDue, DOT, DisReason, DATEADD(m, 1, DOT) AS DisSummDue, Facility, Active
FROM dbo.tblResidents
But which returns null for some of the columns if DOT is not null?
DOT is the Termination Date, so the only columns that have any meaning once there is data in the DOT column are DisReason and DisSummDue. Also, if DOT *is* null, then the above columns also have no meaning.
I tried several variations of the following, but I can't figure it out
CREATE PROCEDURE [dbo].[spTesting] AS
BEGIN
SELECT Last, First, DOP, dbo.fnDueDate(DOP, 3, GETDATE()) AS NextQDue, dbo.fnDueDate(DOP, 6, GETDATE()) AS NextNSPDue, DATEADD(m, 1, DOP) AS InitialNSPDue, DATEADD(m, 1, DOP) AS InitialAssessDue, Facility
FROM dbo.tblResidents A
WHERE DOT IS NULL
UNION
SELECT Last, First, DOP, DOT, DisReason, DATEADD(m, 1, DOT) AS DisSummDue, Facility
FROM dbo.tblResidents I
END
GO
----------------
-Stephen
View 4 Replies
View Related
Jan 16, 2008
I have a SP that has a cursor, and loops. Each loop executes SQL, and inserts values into a temp table on each loop. I want to just have the values in the temp table to show up in QA not the loop's sql results.
I thought:
SET NOCOUNT ON
at the begining of my proc would suppress the results, and then
SET NOCOUNT OFF
prior to my select on my temp table would do this, but the resultset of the loop still shows up in QA. It does suppress the count values on the message tab of QA, but that is it.
I tried something simple like:
SET NOCOUNT ON;
GO
SELECT * FROM [Contact]
And it still show the results in the grid
Any thoughts?
ThanksEric
View 4 Replies
View Related
