Trouble Rendering Lines On The Sides Of A Table In Reports
Mar 26, 2008
Hello!
I've been playing around with local reports for a while now. Trying to convert alot (about 800) reports made in a custom format(not XML) to the .RDLC used in MS Reports. I've been able to solve most converting problems by first figuring out how to make the different appearances of the report editor and testing, and afterwards implementing these in my converter program and testing until it makes the desired appearance on screen. Unfortunately I haven't been able to design all the reports similar to the originals(with the editor), so I was hoping somone here might be able to help me with this.
I'm trying to make a box around my table(the repeating part in the body) but find it kind of hard to do this. There are alot of data and the box should be on every page(I really hope this is possible).
I have made a simple report with a header and footer. In the header I have some textboxes and a horizontal line at the bottom. In the footer I have a horizontal line at the top, and som text at the bottom. This was intended for making the top and bottom lines in my 'box'. The rest of the box is made from two vertical lines, one on each side of the table in the body area of the report. These go down from the top of the body to the bottom.
The height of the header is 1 cm, the height of the body is 24.7 cm and the height of the footer is 4 cm making a total of 29.7cm (standard A4 paper height). Hope someone can help me solving this.
Greetings
Øyvind Johansen
Norway
View 3 Replies
ADVERTISEMENT
Oct 5, 2007
Hello all,
I have been unable to get the black vertical lines in a table to render properly, based on the following....
I'm developing a report that requires the following:
1. One group with the group header section having a BackgroundColor of "LightGrey". (any color other than "Transparent")
2. The details section of the report has a Bottom border with the same color as step #1.
3. The table should have black vertical borderlines that seperate some of the columns. The lines should run the length of the table.
The black lines look like they're being overlapped by both the GroupHeader BackgroundColor and the details section bottom border.
Has anyone been able to implement a similar report successfully?
Thanks for your help!
View 3 Replies
View Related
Aug 7, 2007
I need to create forms for my users containing boxes and lines. When I get the boxes/lines looking correctly for PDF printing, they look really whacked out in HTML view. I understand this is caused by overlapping objects (lines on top of boxes, etc.) I tried a test to see if I could use all lines. This is VERY difficult to get aligned correctly for HTML view. Once I got my test completed, the HTML looked ok -- not great, but the PDF rendering looked REALLY bad.
Is there a way to overlap objects and tell the package to 'group' all the objects as one (like in Word) for HTML rendering? I need to put a karge rectangular box with lines and textboxes on top of it.
Any suggestions are appreciated.
Thanks
View 3 Replies
View Related
Jan 11, 2007
Ever since I have installed IE7 I keep getting prompted to log in several times. It seems to work fine with IE6. Any help would be great
Thanks
Will
View 1 Replies
View Related
Dec 15, 2006
Hi,
We are using SQL Server 2005 Reporting Services for creating Reports. But report execution is taking bit time to give results.
Is there any way around to increase the rendering timing ?
Thx
View 1 Replies
View Related
Jan 29, 2007
Ladies and Gents,
I have a question with regards to the default rendering extensions for reports. I am using RS 2005 with SP1.
I am accessing a report as follows:
http://<ReportServerName>/Reportserver?/<Folder>/<Report Name>
Sample: http://TestServer/Reportserver?/Special Reports/Dealer Report
This displays the parameter bar and the view report button. When pressing the view report button, the report is automatically rendered into HTML 4.0. Is there a way to use the existing parameter bar and automatically render the report to a different extension (such as PDF) when the parameters are entered and the View Report Button is pressed.
Thank You for any advice you can give,
Raph
View 9 Replies
View Related
Dec 19, 2007
Hello,
We're using Reporting Services for lots of different reports at the moment.
Some of these reports are quite big with thousands of pages.
To speed them up I'd prefer using snapshots, but if I do so, it is necessary to defined a default-set of report parameters.
Unfortunately most of the reports parameters are query based, therefore I cannot choose a default parameter set for startup.
If I start one of my reports it always displays the whole bunch of data and it is rendered immediately after clicking the link.
I have two ideas how this problem can be solved, but I don't know if MSSQL Reporting Services supports my ideas
1) Suppress automatic rendering on report startup somehow. Is it possible to set a parameter somewhere to suppress this "feature" ?
2) Use query based parameters to limit the default startup parameters to only a subset of the available data
Does anyone have an idea how this could be done ?
Best regards
Robert
View 1 Replies
View Related
Aug 6, 2006
I apologize in advance if this is something already addressed elsewhere (if so - please point me in that direction).
I have created a really simple report that allows 'drill-down' at the group level. To this report I also selected the 'Repeat header rows on each page' and 'Header should remain visible while scrolling' features and all worked as expected after deploying.
I then added a Document Map referencing the group level and that too worked within the VS environment. VS displayed a page that contained the requested group in plain view and I could 'drill down' as expected.
But when I deployed the report and subsequently pulled it up in IE I noticed a slightly different behavior in that when selecting an entry from the document map, the start of that group got hidden behind the fixed header such that what was visible was perhaps 1 or 2 group records down. I had to scroll up to see the start of the group I had selected from the document map and then drill down from there.
Removing both the "Repeat header..." and "Header should remain ...." allows me to select a group from the document map (which is always at the top) - but in doing so I lose the fixed header feature.
Does anyone have any ideas what I am doing wrong?
Bob
View 2 Replies
View Related
Mar 13, 2008
The query probably speaks for itself. You can see that I had to use a sub select to get the last column. It seems like there must be a better way, but we could not find it. We are on sql 2000. below is the query is the first few rows of the the result set. This query works, being new to sql I am just curious if the sub select could have been avoided in this case. Thanks!
SELECT count(distinct a.store)as [# Stores doing at least 1 Verified Deposit],
b.division,
b.district,
(select count(store_no) from store where district = b.district and fiscal_year = 2008
and current_status = 'a' and owner ='company')as '# of Stores in District'
FROMdepositvariance a,
store b
WHERE a.verified_date >'10/25/2007'
and a.store = b.store_no
and b.fiscal_year = 2008
and b.current_status = 'a'
and b.owner ='company'
Group BYb.division,
b.district
# Store that did at least 1 Verified Deposit division district # of Stores in District
-------------------------------------------- -------- -------- -----------------------
42 200 201 44
28 200 202 40
38 200 203 45
View 5 Replies
View Related
Nov 24, 2015
In a new ssrs 2008 report that uses sql server 2012, I am suppose to create a report that prints on both sides of the same paper. The first side of the report will display custom data with a mailing address for the customer.
The second side (back side of the paper) will contain generic information. The second side of the paper will contain areas for the users within my large company to fill out and return to the user via inter-office mail.
Thus my question is can this happen in an SSRS 2008 report to print in a PDF and/or Word document on both sides of the paper? If this is possible would you tell me if the paper needs to be exported to PDF, Word, or if this can on both format(s)? If so, any reference how to accomplish this goal?
If this is not possible in SSRS 2008, is printing on both sides of the paper possible in ssrs 2008 r2 or ssrs 2012?
What version of ssrs report would work, and if the print (export) needs to be a PDF, Word, or either format?
View 5 Replies
View Related
Feb 28, 2007
Hey all,
I know very simple SQL queries but I need help with this one. I have multiple lines in a SQL database that I need to run. Basically, I need to run this (the bracketed text and the XXX are place holders):
DELETE FROM [tableName] WHERE [columnName] = 'XXXXX'
But I need to run it around 90 times where XXXXX is a unique variable each time. I could create 90 lines similar to this one but that would take way too much time to run. Any suggestions for a noob?
Thanks,
- MT
-=<>=-=<>=-=<>=-=<>=-=<>=-
Matt Torbin
President
Center City Philadelphia Macintosh Users Group
http://www.ccpmug.org/
View 3 Replies
View Related
Mar 16, 2008
I have two tables. OrderDetails and StockTransactions
OrderDetails contains:
Item, Description, QtyOrdered
StockTransactions contains:
OrderDetailID, ActionType, Qty, Date
I would like to create a view as follows:
View:
Item, Description, QtyOrdered, InStock, ToShip, TotalShipped, Backordered
the first three columns come directly from the OrderDetails table, InStock is computed by finding the sum of Qty of all of the lines in the StockTransactions table for this item. ToShip is a field for the user to enter a value they'd like to ship currently. TotalShipped is the sum of what is in the ToShip box and all previous entries in the StockTransactions table related to the current OrderDetail record. Backordered is computed by taking ToShip and subtracting InStock (if the value is a negative number, then display zero).
The objective is to create a user friendly way to enter lines into the OrderDetailsTable. the values place by the user in the Qty field for the items listed in front of them should be saved as entried in the StockTransactions table with ActionType = 1.
I'm not sure if a view is the best way to go about this.
Any comments or suggestions?
View 5 Replies
View Related
Mar 22, 2007
I am trying to sort out the member "Adams" from the committee members, and sum up the total amounts that were donated while he was the committee member.
The Table:
CREATE TABLE contribution_list
(contrib_date DATE NOT NULL,
donor_name VARCHAR(30) NOT NULL,
amount NUMBER(8,2),
program VARCHAR2(30),
committee_member VARCHAR2(20) NOT NULL,
PRIMARY KEY (contrib_date,donor_name,committee_member));
The Code that is giving me errors:(not a group by expression):
CREATE or REPLACE VIEW adams_conrtibution_total as
select program, committee_member, sum(amount)
from contribution_list
where committee_member = 'Adams'
group by program;
View 1 Replies
View Related
Jan 28, 2008
Hi,
I'm using the Business Intelligence Studio for designing reports in VS2005 with SQL-Server 2005 Standard used on a TFS server.
On my report I have several tables each related to a previously defined dataset. Now, if a dataset returns no data, I would expect, that the table shows the header but no rows. This is true, if the table is located on the first page of the report and used directly in the preview tab of a report project. If such a table is located on following pages, I get an rsInternalError when switching to the second page. Trying to export the report, e.g. pdf, directly results in this error. When trying to view the report on the report server, the error appears also at once.
If, in Visual Studio, clicking on the button "page layout" everything is fine: empty tables are shown correctly.
How can I avoid this error in my reports? I would like to publish my report (no fancy stuff, just simple text data), possibly containing empty tables, on the server that all team members can view it - without the error...
Thanks in advance,
Erik Steiner
View 3 Replies
View Related
Nov 15, 2012
I am in doubt if its possible to make a select statement which enables me to consolidate multiple lines in the same table.
I have a table with a lot of companies and figures for each company.
Some of the companies owns some of the other companies in the table and in reverse, some of the companies are owned by some of the companies in the table.
I have a lot of columns, but basically the most important columns are:
Company Name, Company Mother , Company Daughter.
Each company has also a revenue column.
What I want to do is to consolidate all figures for absolute mothers e.i. companies which are not owned by another company in the list.
I therefore need a select statement which says something like:
Get the revenue of companies not owned by another company (e.i. absolute mother). Add to this, the revenue of all its daughters. Add to this the revenue of all the daughters daughters etc. until there are no daughters left.
In other words - aggregate the revenue for all the companies in the group under the name of the ultimate parent company.
I can easily select and add the revenue for the first level of direct daughters, but I dont know how many more daughters the daughters has etc.
View 2 Replies
View Related
Apr 5, 2011
I have some HTML stored in a SQL Server table that I want to render in Reporting Services 2008,
HTML data contain HTML table. While generating report, SSRS not able to render it properly as table.
How we can display the HTML data as it is in SSRS 2008?
View 4 Replies
View Related
Jan 29, 2008
Can this be done in SSRS or must it be done in the query? Ideas? Suggestions? See Example Below:
Current table look:
1 Data.........
2 Data.........
3 Data.........
4 Data.........
5 Data.........
6 Data.........
7 Data.........
Etc...
The Way I need it to look
1 Data.........
2 Data.........
3 Data.........
4 Data.........
5 Data.........
6 Data.........
7 Data.........
Etc...
TIA (Thanks in Advance!)
View 4 Replies
View Related
Jun 19, 2007
First of all, this is not in reference to using SRS (SQL Reporting Services) to render a report and then use one of the extensions to render the complete report as a pdf, tiff, excel etc. We have an opportunity to render a list of claims and then embed the supporting docs for each of the claims within the report. We don't have an issue referencing jpg and gif images via URLs and then rendering them within the report after the grid information. We do have an issue rendering tiff images within the report.
You can insert an image object into SRS at design time and have it render and you can convert a complete report to a tiff image but I cannot find a way to be able to render a tiff image when running the report. All you get is the red "x".
My question is has anyone encountered the same issue and, if so, what did you do to resolve the issue?
Thank you,
J Z
View 3 Replies
View Related
Jan 9, 2008
This program gets the values of A and B passed in. They are for table columns DXID and CODE. The textbox GET1 is initialized to B when the page is loaded. When I type another value in GET1 and try to save it, the original initialized value gets saved and not the new value I just typed in. A literal value, like "222" saves but the new GET1.TEXT doesn't.
View 1 Replies
View Related
Nov 26, 2001
I'm a horrible noob everyone, so my apologies come up front.
I know Oracle allows me to just do an
INSERT INTO table
SELECT column1, column2
FROM table2
But I cannot get this to work in MS SQL 2000. (See question #3)
Here's my script that I'm trying to execute from Enterprise Manager:
From this table:
CREATE TABLE ISIS_DATA
(ISIS_STATUS_ID VARCHAR(15) CONSTRAINT ISIS_DATA_ISIS_ID_PK PRIMARY KEY,
ISIS_NAME VARCHAR(50),
ISIS_CLASS_EXPIR_DAT VARCHAR(20),
ISIS_SEX_BD_CAT_SCHL VARCHAR(20))
Where this is a sample row of data:
A123456789 THOMAS, CHARLES B. 009/11/01 M05/04/511G
I want to run that data through some substrings etc. and dump it into another table like this:
//THIS TABLE WILL BE WHERE WE PUT THE RESULTING DATA FROM
//EXECUTING THE SUBSTRs AND OTHER FUNCTIONS TO PREP THE DATA FOR BASIS.
CREATE TABLE ISIS_DATA_PREP
(ISIS_STATUS_ID VARCHAR(15),
STATUS VARCHAR(5),
STUDENT_ID NUMERIC(15) CONSTRAINT ISIS_DATA_PREP_STUDENT_ID_PK PRIMARY KEY,
LAST_NAME VARCHAR(25),
FIRST_NAME VARCHAR(20),
MID_INIT CHAR(1),
CLASS NUMERIC(2),
EXPIR_DATE VARCHAR(10),
SEX CHAR(1),
BIRTHDAY VARCHAR(10),
CAT NUMERIC(3),
SCHOOL VARCHAR(5))
Using this script so far:
INSERT INTO ISIS_DATA_PREP
SELECT B.STATUS, B.STUDENT_ID, B.LAST_NAME, B.FIRST_NAME, B.MID_INIT,
B.CLASS, B.EXPIR_DATE,
B.SEX, B.BIRTHDAY, B.CAT, B.SCHOOL, A.ISIS_STATUS_ID
FROM ISIS_DATA A,
(SELECT SUBSTRING(ISIS_STATUS_ID, 1, 1) STATUS, SUBSTRING(ISIS_STATUS_ID, 2, 9) STUDENT_ID,
ISIS_NAME LAST_NAME, SUBSTRING(ISIS_NAME, 5, 1) FIRST_NAME, SUBSTRING(ISIS_NAME, 5, 1) MID_INIT,
SUBSTRING(ISIS_CLASS_EXPIR_DAT, 1, 1) CLASS, SUBSTRING(ISIS_CLASS_EXPIR_DAT, 2, 8) EXPIR_DATE,
SUBSTRING(ISIS_SEX_BD_CAT_SCHL, 1,1) SEX, SUBSTRING(ISIS_SEX_BD_CAT_SCHL, 2, 8) BIRTHDAY,
SUBSTRING(ISIS_SEX_BD_CAT_SCHL, 10, 1) CAT, SUBSTRING(ISIS_SEX_BD_CAT_SCHL, 11, 2) SCHOOL,
ISIS_STATUS_ID
FROM ISIS_DATA) B
WHERE A.ISIS_STATUS_ID = B.ISIS_STATUS_ID
I keep getting this error:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
QUESTION #1 - Is there an equivalent in MS SQL 2000 to Oracle's TONUM function?
So, to get around this I change all the fields in the receiving table (ISIS_DATA_PREP) to a varchar so there is no conversion necessary at this time.
Now I get this message:
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
I look up this error on MS's knowledge base and get something along these lines:
...Because the truncated string is shorter than the maximum length, the char column that does not allow a null value and the char variable is padded with trailing blanks while the varchar column will not store trailing blanks....
QUESTION #2 -
What am I doing wrong on the field structure on the receiving table of the input command? I experimented by setting all fields to VARCHAR(25) so it would accept more data and not truncate, but still get the same message.
QUESTION #3 -
My main question was for the syntax for INSERTing into a table from another table the rows in MS SQL. Basically copying the data. I have it for Oracle, but MS SQL doesn't seem to take it in that format.
Sorry for the absolutely massive post. : ) And thanks for any feedback.
Lance
View 2 Replies
View Related
Mar 13, 2007
Hi There,
I am trying to add a new field to a table using enterprise manager by right clicking on the table, choosing design table and adding the new field/column. But when I choose save changes, I receive an error message:
------------------------------------------------------------
Errors were encountered during the save process. Some of the database objects were not saved.
'Comments' Table
-Unable to modify table
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL SERVER] ...
... Cannot drop the table 'dbo.Comments' because it is being used for replication
-----------------------------------------------------------
If it helps I am trying to add a binary (bit) field and a text field of 50 characters. I think the replication it refers to is a merge replication (not sure though is there a way i can find out in EM?)
I have read some articles which refer to this problem and suggest using stored procedures but I really dont know how to do that, having never used them before. I need an idiots guide please!
Let me know if I need to provide more info. Thanks.
View 3 Replies
View Related
Feb 7, 2001
I would like to know how to (if it is at all possible) in SQL (or even ADO) to retrieve all the data concerning database X's
a) Tables
b) Tables column names
c) Tables column's data types
I don't want to use the doa.tabledefs object.. .i would prefer to do it in SQL, but using ADO objects (since I am developing stuff in VB) would be ok too.
Please Help.. i have been going crazy trying to find anything useful.. email me back please!
Thanks,
Matt
View 2 Replies
View Related
Mar 9, 2007
I have a table where I want to delete some data from but I get this error.
You might have a record that has a foreign key value related to it, or you might have violated a check constraint.
What to do????
View 1 Replies
View Related
Mar 17, 2008
I created a package using the Import Data wizard and everything works fine when I re-execute it. I needed to change the name of the destination table so I went into SQL Server and renamed the table. I then went in and edited the DTSX file via a text editor and changed the OpenRowset setting from [CMBS].[dbo].[raw_Note] -> [CMBS].[dbo].[T_Raw_Note]
<property id="104" name="OpenRowset" dataType="System.String" state="default" isArray="false" description="Specifies the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">[CMBS].[dbo].[T_Raw_Note]</property>
There are no other references to the table in the DTSX, i saved the file and then re-ran.
When I run, I get invalid object name dbo.raw_Note (old table name). Is there some data being cached somewhere or hidden elsewhere that it would reference the old table? When I go back into the DTSX file, the correct name is in there so I don't know where it is getting the old name from? Any help would be appreciated.
Tim
View 3 Replies
View Related
Mar 17, 2008
Let me first describe my report: I have the following table
Header
Group 1 row--There is a textbox that in this row that when toggled makes visible the two below group 1 rows and the detail row.
Group 1 row
Group 1 row
Detail row
Group 2 row-- This row has a textbox that can hide the below group 2 row.
Group 2 row-- This row contains a subreport.
Currently, I have a parameter that allows the user to conditionally collapse or show the tables' information for printing purposes so the user doesn't have to go through and click on so many plus signs. My problem is I would like to use a parameter to conditionally hide both of the Group 2 rows so that the user cannot view or click on them. The information in Group 2 is extra and not always needed, so I would like to provide the user with a way to hide this information when they go to view the report. However, I imagined this would be easy enough. I went to group2's visibility tab through the table interface and set the expression to "=IIF(Parameter!ShowIndexes.Value, FALSE, TRUE)". However, when I view the report the report is hiding the detail information as well as the Group2 rows.
Anyone have any ideas?
View 1 Replies
View Related
Aug 13, 2007
How Do I fix View(below) or Multi-Table select(below) to use this Function to return distinct rows via qcParent_ID?
Following Function populates a field (with concat list of related titles) with other required fields:
Create Function [dbo].openItemsIntoList(@Delimeter varchar(15),@qcparent_ID varchar(1000))
Returns Varchar(8000) as
Begin
Declare @Lists as varchar(8000);
Select @Lists = '';
Select @Lists = @Lists + itemTitle + @Delimeter From z_QClocate_openAll_Qualifier
Where @qcParent_ID = qcParent_ID;
Return Substring(@Lists,1,len(@Lists)-len(@Delimeter));
End
works perfect against single table select (returning 54 distinct rows by qcParent_ID):
Select a.qcParent_ID, a.Facility, a.Modality, openItemListToFix
From dbo.a2_qcEntryForm a
JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i
on a.qcParent_ID = i.qcParent_ID
But data is needed from 3 tables...
- Created a VIEW that returns all (82) rows (negating distinct of the function on qcParent_ID)
- Failed Miserably Integrating Function call into a multi-table select (inexperienced with complex joins)
This VIEW returns ALL (82) rows in table:
CREATE VIEW z_QClocate_openAll AS
SELECT dbo.a1_qcParent.qcStatus, dbo.a1_qcParent.qcAlert, dbo.a3_qcItems2Fix.qcParent_ID, dbo.a3_qcItems2Fix.qcEntryForm_ID,
dbo.a3_qcItems2Fix.itemComplete, dbo.a3_qcItems2Fix.itemTitle, dbo.a2_qcEntryForm.Facility, dbo.a2_qcEntryForm.Modality
FROM dbo.a1_qcParent INNER JOIN
dbo.a2_qcEntryForm ON dbo.a1_qcParent.qcParent_ID = dbo.a2_qcEntryForm.qcParent_ID INNER JOIN
dbo.a3_qcItems2Fix ON dbo.a2_qcEntryForm.qcEntryForm_ID = dbo.a3_qcItems2Fix.qcEntryForm_ID AND
dbo.a1_qcParent.qcParent_ID = dbo.a3_qcItems2Fix.qcParent_ID
WHERE (dbo.a1_qcParent.qcStatus = 'Awaiting Attn') AND (dbo.a3_qcItems2Fix.itemComplete = 0) OR
(dbo.a1_qcParent.qcStatus = 'In Process') OR
(dbo.a1_qcParent.qcStatus = 'Re-Opened')
Calling like this returns ALL 82 rows (negating the functions distinct):
Select a.qcParent_ID, a.qcStatus, a.qcAlert, a.itemComplete, a.Facility, a.Modality, openItemListToFix
From z_QClocate_openAll a
JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i
on a.qcParent_ID = i.qcParent_ID
AND THEN THERES...
Failing miserably on Integrating the Function call into This SELECT ON MULTI-TABLES:
How to integrate the Function call:
JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i
on a.qcParent_ID = i.qcParent_ID
into the multi-table Select relationships (while maintaining Where & Order By):
SELECT dbo.a1_qcParent.qcStatus, dbo.a1_qcParent.qcAlert, dbo.a3_qcItems2Fix.qcParent_ID, dbo.a3_qcItems2Fix.qcEntryForm_ID,
dbo.a3_qcItems2Fix.itemComplete, dbo.a3_qcItems2Fix.itemTitle, dbo.a2_qcEntryForm.Facility, dbo.a2_qcEntryForm.Modality
FROM dbo.a1_qcParent INNER JOIN
dbo.a2_qcEntryForm ON dbo.a1_qcParent.qcParent_ID = dbo.a2_qcEntryForm.qcParent_ID INNER JOIN
dbo.a3_qcItems2Fix ON dbo.a2_qcEntryForm.qcEntryForm_ID = dbo.a3_qcItems2Fix.qcEntryForm_ID AND
dbo.a1_qcParent.qcParent_ID = dbo.a3_qcItems2Fix.qcParent_ID
WHERE (dbo.a1_qcParent.qcStatus = 'Awaiting Attn') AND (dbo.a3_qcItems2Fix.itemComplete = 0) OR
(dbo.a1_qcParent.qcStatus = 'In Process') OR
(dbo.a1_qcParent.qcStatus = 'Re-Opened')
View 3 Replies
View Related
Mar 6, 2008
Hi..
I am getting a xml stream of data and putting it to a object and then calling a big sproc to insert or update data in many tables across my database... But there is one Table that i am having trouble inserting it.. But if i run an update it works fine... This my code for that part of the sproc..
IF Exists(
SELECT
*
FROM
PlanEligibility
WHERE
PlanId = @PlanId
) BEGIN
UPDATE
PlanEligibility
SET
LengthOfService = Case When @PD_EmployeeContribution = 0 Then @rsLengthOfServicePS
ELSE @rsLengthOfService END,
EligibilityAge = CASE When @PD_EmployeeContribution = 0 Then @EligibilityAgePS Else @EligibilityAge End,
EntryDates = @EntryDates,
EligiDifferentRequirementsMatch = Case When @PD_EmployeeContribution = 0 Then 0
When @PD_EmployeeContribution = 1 and @PD_EmployerContribution = 0 then 0 Else 1 END, --@CompMatchM,
LengthOfServiceMatch = CASE When @MCompanyMatch = 0 Then @rsLengthOfServicePs ELSE @rsLengthOfServiceMatch END,
EligibilityAgeMatch = CASE When @MCompanyMatch = 0 Then @EligibilityAgePS ELSE @EligibilityAgeMatch END,
OtherEmployeeExclusions = @OtherEmployeeExclusions
WHERE
PlanId = @PlanId
END
ELSE BEGIN
INSERT INTO PlanEligibility
(
PlanId,
LengthOfService,
EligibilityAge,
EntryDates,
EligiDifferentRequirementsMatch,
LengthOfServiceMatch,
EligibilityAgeMatch,
OtherEmployeeExclusions
)
VALUES
(
@PlanId,
Case When @PD_EmployeeContribution = 0 Then @rsLengthOfServicePS
ELSE @rsLengthOfService END,--@rsLengthOfService,
CASE When @PD_EmployeeContribution = 0 Then @EligibilityAgePS Else @EligibilityAge End, --@EligibilityAge,
@EntryDates,
Case When @PD_EmployeeContribution = 0 Then 0
When @PD_EmployeeContribution = 1 and @PD_EmployerContribution = 0 then 0 Else 1 END, --having trouble here
CASE When @MCompanyMatch = 0 Then @rsLengthOfServicePs ELSE @rsLengthOfServiceMatch END,
CASE When @MCompanyMatch = 0 Then @EligibilityAgePS ELSE @EligibilityAgeMatch END, --EligibilityAgeMatch,@EligibilityAgeMatch,
@OtherEmployeeExclusions
)
END
Any help will be appreciated..
Regards,
Karen
View 6 Replies
View Related
Jan 22, 2008
Using MSSQL (pre 2005):
I have a Link table:
int id (primary key/identity)
varchar(50) linkName
varchar(255) linkHref
//some other stuff
and my Hits table:
int id (primary key/identity)
int linkId (foreign key to Link)
datetime dateCreated
//some other stuff
Hits gets an insert whenever a link is clicked. (All this works just fine)
I'm trying to create a report that shows each link by its name and href, a counter and the last date each link was visited. What I currently have is an accurate listing for those that have been clicked on, but it does not show anything for links that haven't been clicked on. Any suggestions as to how I can modify the following SQL to return "0" and "never" (or DBNULL) if no entries are found in Hits that have the same id? Or do I have to do this in a couple queries?
SELECT COUNT(h.id) as counter, MAX(h.dateCreated) as lastVisited,
l.id as id, l.linkName as linkName, l.linkHref as linkHref
FROM Link as l INNER JOIN Hits as h ON l.id=h.moduleId
GROUP BY l.linkName, l.id, l.linkHref
View 6 Replies
View Related
Jul 20, 2005
I have a large table that I recently purged a year of data from.However, the table size in sp_spaceused hasn't decreased as much as Iwould expect. (there are no text or large object columns on thistable, BTW) Running sp_spaceused on the table shows the following.rows reserved data index size unusedTABLE_NAME23470880 67790808 KB 18116312 KB 3211616 KB 46462880 KBI ran a dbcc indexdefrag on all indexes on the table last night,including the clustered index, and I'm still seeing pretty much thesame amount of space reported as unused.The one thing that I haven't done yet is to run the sp_spaceused onthis table with the @updateusage flag set to true as I need to do thisafterhours to reduce contention. I will attempt this tonight andreport on the results.Here's DBCC SHOWCONTIG OUTPUT for the table:DBCC SHOWCONTIG scanning 'TABLE_NAME' table...Table: 'TABLE_NAME' (917578307); index ID: 1, database ID: 7TABLE level scan performed.- Pages Scanned................................: 2264447- Extents Scanned..............................: 285484- Extent Switches..............................: 287092- Avg. Pages per Extent........................: 7.9- Scan Density [Best Count:Actual Count].......: 98.59%[283056:287093]- Logical Scan Fragmentation ..................: 0.04%- Extent Scan Fragmentation ...................: 3.19%- Avg. Bytes Free per Page.....................: 2418.9- Avg. Page Density (full).....................: 70.12%DBCC execution completed. If DBCC printed error messages, contact yoursystem administrator.Looking through the history of the group, the one thing that seems toremedy this problem consistantly is to bcp out all the data, trunc thetable and bcp all the data back in. This is not really a possibilityfor me due to the size of the table and the availability expectationsof my customers. Is there anything that I forgot to check? Do youthink that it's just a matter of incorrect statistics in sp_spaceused?Thanks in advance:Matt
View 4 Replies
View Related
Nov 5, 2015
we are currently migrating from a system we use to a similar product from a different company. Before we start using the new product, we need to migrate the data from the old server to the new one. This is fine, but obviously the column names, table names, and data types are all different. What the old server does have on it, is SSRS. We have configured a few reports to extract the data and present it in table format with the column names in the right order and with the right names.
The question is, is there an easy way to import these into a database without downloading each report as a csv and importing them one by one. Not only is this tedious due to the large amount of tables, but when using this method, I get all sorts of errors where the data type conversion has failed.
View 5 Replies
View Related
May 1, 2007
I would like to loop through a SQL Server table that contains the paths to all the reports we need to run and then execute the reports via SSIS. What task should I be doing to do this? Will the For Loop work for something like this?
View 9 Replies
View Related
Feb 22, 2007
Hi,
I have a summary table like this
Field1
Field2
Field3
Field4
Field5
AAA11
value1
value2
value3
value4
value5
AAB23
value6
value7
value8
value9
value10
BCD14
value11
value12
value13
value14
value15
GFD12
value16
value17
value18
value19
value20
SDL25
value21
value22
value23
value24
value25
AUD56
value26
value27
value28
value29
value30
BER11
value31
value32
value33
value34
value35
Columns are obviously fixed, but not rows.
I want to show this data using lables and SqlDataReader for report purpose like;
Label1.text=dr("value16").toString( )
Label2.text=dr("value28").toString( )
Label3.text=dr("value31").toString( ) etc
Do you have any idea how i can do it or am I approaching it in the wrong way????
Thanks.
Michelle
View 1 Replies
View Related
Mar 28, 2008
Hi,
I have a report that calls a stored procedure that creates an extract of data for use by various subreports. Now I have this problem:
If I save the extract data in a global temporary table, then it is automatically deleted before the subreports can use it, this means I have to create a normal table with a unique name that need to be deleted - but where do you do this in the report - there is no point where you can say it is now safe to delete a table?
I do not want to resort to external mechanisms, languages, jobs etc. to do this. I want to delete the table once the report is really finished in the report.
My main report uses a list that contains all the subreports as I need to group all sorts of information by vendor. The main report calls the stored procedure. Please do not tell me that I have to duplicate the main extract for every subreport. That will really eat resources.
Thanks in advance.
View 4 Replies
View Related
