Layout Question. If Statement To Determine Formatting.
May 10, 2007Is there a way to setup a field so that if it equals 'Yes' then it highlights the row. And if its 'No' it leaves it in plain text.
If this possible?
ADVERTISEMENT
SP To Determine Layout, Population And Sample Data In Table(s)
Nov 1, 2007Hi! I am new to SQL Server... looking for some veteran assistance.
"Data Integrity Report"
I need a Stored Procedure that takes a table name as a parameter and returns a cursor suitable as a data source for a pre-built Report Services report (I guess Report Services would call the SP?).
The cursor/report needs to have the following columns:
Ordinal_Position (I.E. Column number)
Column_Name
Number Of Blank Rows (how many missing values for this column in this table)
Difference (Between total rowcount and population of this column)
Data_Type
Column_Length (either Character_Maximum_Length or the numeric widths rolled up with COALESCE?)
Sample Data (The contents of the "first" row in the table, based on a TOP(1) and ORDER BY xxx)
The report should look like this (for a table with 100 rows):
Col Num Col Name # Blanks Difference Data Type Col Length Sample Data
1 Name 12 88 varchar 30 Sally Smith
2 Address 34 66 varchar 45 123 Main St Apt 45
3 Acct_ID 0 100 varchar 4 AB12345
Using the "Information_Schema.Columns" I can get everything I need except for #3 (blanks count) and #7 (Sample data).
Is it possible to do this as 1 query, with a CTE or APPLY or something, or do I need to do a table variable based on the Information_Schema and then use dynamic SQL and row-by-row COUNT(*) for each column? And the same for the Sample Data.
Sorry for the long post, and thanks in advance!
John
Formatting Numbers In An SQL Statement
Aug 23, 2005Hi,I have a table that has an ID field which is automatically incremented as each new record is added, so if I do a SELECT * FROM Table1 I get:ID, Name1, Billy2, Bob3, TonyYou get the idea. What I want to do is format the number differently when it's returned from an SQL statement so I get:ID, Name0001, Billy0002, Bob0003, TonySo I need something like SELECT FORMATNUMBER(ID, 4), Name FROM Table1 - Does anything like this exist?Little 'un.
View 2 Replies View RelatedView Layout Is Different Than Print Layout
Nov 21, 2007
I designed a report in reporting services 2005. When i look at the view layout for the report everything is aligned correctly but if i go to print preview one matrix is shifted up or down. I have tried to put the matrix inside a rectangle but the issue is still present. Does anyone know why this is happening or any suggestions on what to do to fix it.
Thanks
Scott
Number Formatting In A SQL Select Statement
Apr 6, 2005
Hi
I'm trying to convert and format integer values in a SQL Server select statement to a string representation of the number formated with ,'s (1000000 becomes 1,000,000 for example).
I've been looking at CAST and CONVERT and think the answers there somewhere. I just don'tseem to be able to work it out.
Anyone out there able to help me please?
Thanks,Keith.
Formatting Case Statement Results
Mar 11, 2008I am trying to format the Sys field (which is Int) in case the first byte is zero. But I'm getting an error msg;
Msg 156, Level 15, State 1, Procedure sp_Pull_Cancels, Line 43
Incorrect syntax near the keyword 'Right'.
CASE
WHEN substring(PCRACT, 5, 1) = '4' THEN 3641
WHEN substring(PCRACT, 5, 1) = '5' THEN 2012
WHEN substring(PCRACT, 5, 1) = '6' THEN 9694
END as Right('0000' + Cast(Sys as varchar (4)),4)
Any ideas how I construct this CASE statement to get the desired results.
Thanx,
SQL 2012 :: Select Statement For Date Formatting
Apr 6, 2015Select '"' + CAST( GETDATE() as varchar(100) ) + '"' as Obs_dt_1
I get this output---> "Apr 6 2015 4:07PM"
But what I really need is for it to show in this format---> "2015-04-06 16:08:05.317" .... How do I do the select ?
Using Case Statement To Determine Order By Field And Direction (asc Or Desc) When Using Row_number
Aug 21, 2007I am trying to order by the field and direction as provided by input parameters @COLTOSORTBY and @DIR while using a CTE and assigning Row_Number, but am running into syntax errors.
Say I have a table called myTable with columns col1,col2,col3,
Here's what I'm trying to do
with myCTE AS
(
Select
col1
,col2
,col3
,row_number() over (order by
case when(@DIR = 'ASC') then
case when @COLTOSORTBY='col1' then col1 asc
when @COLTOSORTBY='col2' then col2 asc
else col3 asc
end
else
case when @COLTOSORTBY='col1' then col1 desc
when @COLTOSORTBY='col2' then col2 desc
else col3 desc
end
end
from myTable
)
Please let me know what i can do with minimal code repetition and achive my goal of dynamically sorting column and direction. I do not want to use dynamic SQL under any circumstance.
Thanks.
New SQL Layout Issue.
May 15, 2008I have a form that I created that groups make and model and it totals all the fields to make 1 entry on the Layout. I then am trying to get the layout to SUM those values but it does not. For example we have 2
Make, Model, Totals
CV, P2100, 50,000
CV, P2100, 52,000
CV, P2100, 48,000
CM, R3200, 50,000
CM, R3200, 53,000
CM, R3400, 20,000
CM, R3400, 22,000
As you can see above we have 2 Makes CV and CM, with Diff models.
The code i used totals the make, model, and totals for the parts.
Results look like this.
Make, Model, Total
CV, P2100, 150,000
CM, R3200, 103,000
CM, R3400, 42,0000
I tried to use the SUM function and it takes all older totals and places that at the bottom creating a Larger value than what should be posted.
the CM total should be 145,000 and the CV would be 150,000.
Here is the code I am using for this and this parts right I am just having issues with the layout not posting the correct numbers.
SELECT equip.eqpphybr, equip.kequipnum, equip.kmfg, equip.kmodel, equip.kserialnum, equipdet.glsrc, equipdet.glamt, equipdet.gldate, equipdet.action,
CASE WHEN action = 'b' THEN glamt ELSE 0000000.00 END AS RentalBilling,
CASE WHEN action = '6' THEN glamt ELSE 0000000.00 END AS Depreciation,
CASE WHEN action = 'a' THEN glamt ELSE 0000000.00 END AS Repairs, equip.eqprecdt, equipdaily.cnvf001, equipdaily.cnvf00109, equip.eqpstatus
FROM equipdet INNER JOIN equip ON equipdet.kequipnum = equip.kequipnum INNER JOIN equipdaily ON equip.kequipnum = equipdaily.kequipnum
WHERE (equipdet.gldate BETWEEN @fromdate AND @thrudate) AND (equip.eqpstatus <> 'SO')
ORDER BY equip.kmfg, equip.kmodel
__________________________________________________________________-
For the layout its looks like this with 2 groupings.
Below is the headers in same order as the =Fields
Br,EQ#, MFG, Model, S/N, ACQ Date, ACQ Cost, Book Value, Rental Billing, Depreciation, Repairs
---------------------------------------------------------------------
(This is row 1 inserted as a group with expression =Fields!kmfg.Value)
=Fields!eqpphybr.Value, =Fields!kequipnum.Value, =Fields!kmfg.Value, =Fields!kmodel.Value, =Fields!kserialnum.Value, =Fields!eqprecdt.Value, =Fields!cnvf001.Value, =Fields!cnvf00109.Value, =SUM(Fields!RentalBilling.Value), =SUM(Fields!Depreciation.Value), =SUM(Fields!Repairs.Value)
---------------------------------------------------------------------
(This row is set as a group by =Fields!Kmodel.Value)
ACQ Cost, Book Value, Rental Billing, Depreciation, Repairs
---------------------------------------------------------------------
Totals: =SUM(Fields!cnvf001.Value), =SUM(Fields!cnvf00109.Value), =SUM(Fields!RentalBilling.Value),=SUM(Fields!Depreciation.Value) / -1, =SUM(Fields!Repairs.Value)
---------------------------------------------------------------------
(I added a row from the =Fields!Kmodel.Value to get percents.)
Depreciation, Repairs
---------------------------------------------------------------------
=IIF(Sum(Fields!RentalBilling.Value)<>0,Sum(Fields!Depreciation.Value)/Sum(Fields!RentalBilling.Value),0)/-1, =IIF(Sum(Fields!RentalBilling.Value)<>0,Sum(Fields!Repairs.Value)/Sum(Fields!RentalBilling.Value),0)
Layout Question
Jun 1, 2007We have a report that is printed landscape and contains a main table with values. Inside this table we also want to create a "sub-report" table based on two other criteria (charges and credits), but this data is still grouped with the main table. Here is a simple schematic of the report
XXXXXXX XXXXXXXX XXXXXXXXXX XXXXXXXXX Charges
XXXXXXXXX
XXXXXXXXX
Credits
XXXXXXXXX
XXXXXXXXX
XXXXXXX
We have placed a rectangle in the last column of the main table to accomodate two other "sub-tables": one for Charges and the other for Credits. We need to keep the heighth of each row in the main table the same (without extra white space) so we cannot move the second table for Credits below the table for charges.
I hope this makes sense and anybody with any suggestions, it is appreciated.
Complex Layout
Jun 15, 2007I'm developing a system in .NET to record and manage marks for classroom attendance. To enable staff to print-out the registers, I need to output the data in a printable format. My first thought on this was to use Reporting Servcices, however there are a number of caveats.
I'm using the matrix control to handle the displaying of the students with the class dates and attendance marks. After 25 columns are output this should wrap to a new page, is this possible?
When wrapping to a new page, the first 3 columns should remain the same with the other 18 being the next lot of class dates.
There needs to be 25 rows shown, regardless of whether or not there are actually 25 rows returned from the dataset.
Essentially the layout for this is strictly controlled. Is RS the correct tool for this or is there something else I can/should use?
Example register layout
NO. STUDENT ID 01/01 08/01 15/01 22/01 29/01
1 ADAM SMITH 123456 X X X X X
2 JANE WILSON 748386 X O O X X
Table Layout
Oct 10, 2007Hey, guys
I have got problem of designing a report table. The table is created based on 5 field dataset output, student name, semester, unit name, unit code and exam mark. e.g.
StudentID Semeter UnitName UnitCode Mark
------------------------------------------------------------------------------------------------------------------------------------
123456 1, 2006 Database IT001 85
123456 1, 2006 XML IT002 82
123456 2, 2006 Web Development IT003 76
123456 2, 2006 ............
etc
and there is supposed to one table for one semester and the student ID and Semester must be in the first 2 rows and all the other data is column based as normal, which is like
StudentID 123456
Semester 1, 2006
------------------------------------------------------------------------
UnitName UnitCode Mark
Database ITN001 85
XML ITN002 82
StudentID 123456
Semester 2, 2006
------------------------------------------------------------------------
UnitName UnitCode Mark
Web Development ITN003 76
I only can create the table using grouping but the layout is column based.
Does anybody know how can I design the table to achieve the above table layout.
Thanks in advance.
Regards,
Jeff
How To: Freeform Layout
Mar 14, 2008I started the project with the goal of using 2005 Reporting Services as the reporting mechanism. Our graphic designer has created a layout that has been approved by the company and I am attempting to translate that layout to a report design that gives similar output.
I have discovered during the course of working with reports that true free from data layout is not possible. What I was expecting was the ability to design a layout for data in the body section of the report, and that layout being repeated for each row of data in the database.
I understand that tables and lists exists that allow repeated data to be displayed, but is it possible to repeat data in a custom freeform layout with 2005 Reporting Services.
Imagine if you were creating a report that looked like an ID card for each record. Would such a layout be possible with this reporting service?
If not, could someone direct me to a reporting application that does? Does Crystal Reports satisfy this requirement? I liked Reporting Services because it allowed one to design once and output into several popular formats, but the design portion doesn't appear to be as freeform as I was lead to believe.
Thanks
Report Layout
Sep 12, 2007
Hi,
When I preview my Reporting Services Report in VS2005 the report uses 2 separate pages. Even when there is very little data on Page 1 it will display something on Page 2.
How do I get all data on the one page?
Thanks.
The Layout Changes After Deployment
Aug 16, 2007I'm having a problem with my deployed reports. Once they have been deployed to a server then the layout changes and some parts of the report move around to line up with the edge of a matrix or the bottom of a table even through they do not actually overlap. The layout also changes depending on whether report items are visible or hidden, with tables moving away from hidden items but lining up witht hem when they are visible.
This also sometimes happens when moving between the layout and preview tabs in reporting services although this is not so much of a problem.
Has anyone else experienced the same problems and if so did you find a solution?
Report Layout On PDF
Sep 11, 2007I have had a lood through the forum but although folks seem to be having a lot of issues with layout I can't see a problem quite like this one.
We have a number of customer reports which need to be exported to PDF and to look quite smart. They are single page reports with things like address and customer info at the top, followed by the main report, all of which can have a variable number of rows. Then at the bottom is some supplementary information which can also vary quite substantially in size. All I want is for the main report to hug the top of the page and the supplementary one to hug the bottom of the page - and I only really care what it looks like in PDF format. The problem is that I can't seem to achieve this. As the area at the top expands the bottom section moves up and down the page. I'd be quite happy to set aside a fixed area at the bottom of the page for the supplementary report, but I can't even work out how to acheive that! You would think that the obvious answer would be to put it in to a footer, but for some reason you can't put data elements in to headers or footers (why?). Any help would be greatly appreciated.
SQL Layout View Issue
May 14, 2008I have a report that i have totaled and working correctly and I am able to get the 2 columns to sum up but I need to get those 2 sumed columns to be divided to get the gross prof %.
The columns that I have summed are
=Sum(Fields!eqpsldamt.Value)
=Sum(Fields!grosspro.Value)
I need to take those summed values and divide them but I do not know the propper syntax to do this. I thought it would have been
=SUM(Fields!grosspro.Value) / SUM(eqpsldamt.Value)
But this did not work any help would be great thanks!
SQL Layout View Issue
May 14, 2008I have a report that i have totaled and working correctly and I am able to get the 2 columns to sum up but I need to get those 2 sumed columns to be divided to get the gross prof %.
The columns that I have summed are
=Sum(Fields!eqpsldamt.Value)
=Sum(Fields!grosspro.Value)
I need to take those summed values and divide them but I do not know the propper syntax to do this. I thought it would have been
=SUM(Fields!grosspro.Value) / SUM(eqpsldamt.Value)
But this did not work any help would be great thanks!
Calculating Intrest In Layout.
May 19, 2008I have some old code that was used in cristal reports and thought it might help with my problem. I need this to calcuate intrest for every piece of equipment.
IF ({equipdet.ACQUIRED_DATE} < Date(2007,01,01)) THEN ({equipdet.Book Value}*(CurrentDate - Date(2007,01,01)) * .07/365) ELSE IF ({equipdet.ACQUIRED_DATE} > Date(2007,01,01)) THEN {equipdet.Book Value}*(CurrentDate - {equipdet.ACQUIRED_DATE})*
.07/365
I am just not sure how to write this in the layout view.
Duplicate DB Layout On Another Server
Sep 28, 2007Hi
Is there anyway of copying an entire database (table layout, stored procs etc) without the data that the tables contain please?
I have a SQL7 DB (production) that I need to replicate on a SQL200 Server (development) but the DB is very large and all I need is the table structure and the stored procs on the development Server...
Cheers
Database Layout Question
Dec 2, 2007Hi Guys,
Can I hear your views on this type of DB layout? I have a DB that contains tables which are not linked by FK's etc but rely on one another through SP's. I've never came across this layout before so was hoping you could shed some light on it with your experiences etc.
Also what if many of the tables didn't have PK's this would lead to duplicate data being allowed to be entered into the database. Are there any other issues here?
Regards Butterfly
How To Print The Table Layout
Jul 20, 2005Hello to everyoneI am using SQL Server Enterprise Manager and I would like to print thestructure of each table of a database, with the purpose to see the wholerecord layout. (In Access there is Analyzer which does that) butapparently I acnnot find anything similar in EM.Who Could help me?Thank you to everyone!RegardsFabio*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies View RelatedReport Layout Design Tip
Mar 25, 2008I'm trying to create a report like following format. Any idea how can I design it?
'ID', 'Title', 'Answer' and 'Comment' have to span multiple rows which will list 'Response'.
----------------------------------------------------------------------------------------------------------------------------
ID Title Answer Response Comment
------------------------------------------------------------------------------------------------------------------------------
1 | Q1 | B | A | aaaaaaaaaaaaaaaaa
| | | B | aaaaaaaaaaaaaaaaaaaaaaaaa
| | | C | aaaaaaaaaaaaaaaaaaaaaaaaa
| | | D |
---------------------------------------------------------------------------------------------------------------------------------------
2 | Q2 | C | A | bbbbbbbbbbbbbbbbbbbbbbbbbb
| | | B | bbbbbbbbbbbbbbbbbbbbbbb
| | | C | bbbbbbbbbbbbbbbbbbbbbbb
| | | D |
---------------------------------------------------------------------------------------------------------------------------------------
Flow Layout In Rs2005
Jan 10, 2007I have a report that needs to show postal addresses. The address is broken down into several fields. The problem I have is some of the address parts are optional. If they are empty, I'm left with nasty gaps in the address. I'd really like next label to reclaim the space of any empty labels.
a quick example
A full address would look like this..
customer name
address line 1
address line 2
town
county
post code
if address line 2 isnt given, I get:
customer name
address line 1
town
county
post code
but I want:
customer name
address line 1
town
county
post code
Can anyone help?
Many thanks,
Paul
Report Layout Question
Jun 21, 2007win server 2003
sql server 2005
Hi, I need to create a report with the following layout
f1title f2title f3title f4title f5title
+field1 field2 field3 field4 field5
clicking on the + will display
f6title f7title f8title f9title
field6 field7 field8 field9
is this possible?
If it is please describe how.
TIA
Dynamic Layout && Whitespace
Dec 19, 2006Hi,
I have a report that, among other things, contains 3 tables laid out side-by-side horizontally. The first table is conditionally visible and when it is hidden the report contains whitespace instead of the table. The problem is that this causes an unappealing visual look because the other 2 tables look shifted too much to the right.
What is the recommended way for dealing with situations like this? There doesn't seem to be support for specifying the location (i.e. top, left) of elements based on expressions. Am I "stuck" with having to create multiple flavors of the report?
Thanks,
Vitaly
Calendar Report Layout Help
Jun 28, 2007Hello
I need to create something like the following table:
MON TUE WED THU FRI SAT SUN
01/01/07 02/01/07 03/01/07 04/01/07 05/01/07 06/01/07 07/01/07
Blank Field Blank Field Blank Field Blank Field Blank Field Blank Field Blank Field
08/01/07 09/01/07 10/01/07 11/01/07 12/01/07 13/01/07 14/01/07
Blank Field Blank Field Blank Field Blank Field Blank Field Blank Field Blank Field
The user would enter the start date, in this case the 1st Jan 07 and then this would populate a table. This seems like it should be so simple but I can't work it out, can anyone help please?
Cheers
Groupings In Matrix Layout?
Mar 28, 2006Hello,
I am trying to come up with a Matrix report using RS 2005 that looks like this one below. I have row data: question, columns data: Ethnicity and Gender, detail data: Gender average response and Ethnicity average reponse. I utlilized Report Wizard to create a report in a Matrix format , however I wasn't able to have Gender and Ethnicity column on the same line. The report wizard groups columns so Gender is a subset of Ethnicity or Ethnicity is a subset of Gender. How can is solve this issuee so that Gender and Ethnicity data is presented in a Matrix format and Gender and Ethnicity are not part of each other.
Thank you!
F
M
Asian
Multi-cultural
Non-resident
Unknown
White
Black
Hispanic
Native American
a. Worked on a paper or project that required integrating ideas or information from various sources
3.95
3.99
3.54
4.50
3.28
4.20
4.18
4.04
3.92
3.90
b. Used library resources
4.26
4.09
4.12
4.33
4.10
4.33
4.26
4.22
4.10
4.10
c. Prepared multiple drafts of a paper or assignment before turning it in
3.97
3.76
3.80
4.50
3.58
3.86
3.95
4.09
4.00
4.00
Layout Incorrect When Using ReportViewer
Jul 4, 2007Please can someone help me with this.
I have created a very simple SQL report which has a title, Image, 3 subtitles and some data fields.
When I view it is my VS designer and preview it looks perfect, however when I deploy and view the the report through my ReportViewer control all the labels get moved around and the entire thing is a mess.
If I then export it to PDF it looks perfect again... I am sure I am missing something obvious but I can't find it.
I have done absolutely no expression formatting or coding at all. It's a plain SQL query and plain result textboxes.
Can I Use The Same Group In A Layout For Two Datasets
Apr 23, 2008when i add a calculation in the group from my second dataset, it is summing the whole dataset, instead of just for that group. any suggestions?
View 1 Replies View RelatedProblems With Print Layout
Jan 16, 2007Hi all,
In my report I create a group of matrixes and remove the space between them. They have the same size and when I make the preview it looks just fine. They apear all together without any space between them. My problem is when I make the print layout. They appear with a huge space between them and I don't know why.
Has someone passed by the same? Any solution for this kind of problem?
Thanks in advance.
vjn
A Challenge: Check Layout
Aug 7, 2007I have a questions for those familiar with the tools and work-arounds of SSRS 2005. My task is to produce a report that has page(s) consisting of three parts that are equal divided vertically amoung the 11 inches of the report height. The first section on the top section has a a check on it. The second and third sections have two seperate yet identical tables.
The trick for this is that each table (in sections part two and three) must not leak into each other's section. Essentially each section has X amount of rows that are shown on each page with the next X amount of rows on seperate pages after that. The problem, as you can most likely guess, is that you cannot force a table to continue only into it's appriopriate section on the next page. I've tried many, many combinations of tricks to try and trick SSRS into working. I know that SSRS most likely doesn't support this directly, but I'm looking for a genius or just anybody who might have a work-around. I'll update this as I find out more. Please feel free to ask for more of an explaination if needed. Thanks!
Display Various Report Layout, From 1
Jan 17, 2008Hi to all,
My idea is to create one main report. Now, depending on the parameters the report is receiving, my matrix will shift and change groups/rows.
For example, using only one dataset:
Client1 sees only activities/costs
Client2 sees activity/costs/blabla1/blabla2
Client3 sees activity/costs/subtotal for costs/blabla1/blabla2/blabla3
Basically, without having to create various reports, I will have one major, dynamic report.
The best that I have accomplished is hiding the non-wanted column, although the end result isn€™t good.
Thanks in advanced, personally I think it€™s a good challenge€¦ Any suggestion?