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.
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.
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.
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
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.
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
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?
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/
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.
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;
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...
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.
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?
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.
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))
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.
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.
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!
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.
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.
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)
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):
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
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
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
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.
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?
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????
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.
