In my report I have used the "jump to url" for the report output but when I export this to excel I get a column with the correct data and also the url.
Is there anyway of exporting the data without the url?
I want my users to be able to view the report and click on the column the want to URL jump to but also be able to export just the raw data without the URL?
Has anyone had experience formatting an excel file (i.e. run a macro) after it is (created &) outputted from a DTS package?
Also an easier question:
What is the best (easiest) way to create a unique filename in Excel with a datetimestamp in the file name (i.e. MyFile-20040608.xls)
Thanks!
I have created SSRS report which has many overlapping objects, the output in PDF format seems to good but in word format it is not giving the required output.
View 5 Replies View RelatedHi All
I've been googling this for a while now and can't seem to find any elegant answers.
I'm looking for an automated way to present a FORMATED Excel Spreadsheet to the Customer from a stored procedure output.
Can anyone advise me the best method of doing this - should I / can I assign an Excel Template to the DTS Task output ?
His mind is set on Excel and the formatting is basic and easy to write in a Macro which I've done, but this requires human interaction to finish the task (Automated Run Once on opening etc).
In an ideal world an individual would send an email to the Server with two formated parameters (@FromDate & @ToDate) and would be emailed back a ready formatted S/Sheet. But I believe he would be willing to just select the relevant SpreadSheet for the Daily / Weekly / Monthly periods dumped.
Thanks
GW
Hello everyone:
There is query output like:
1
2
3
4
How to specify it to be like"
1,2,3,4
?
Thanks
ZYT
Hello All,
I am new to SQL Server. I would like to know how to format output using isql on SQLServer7.0
e.g.
I want this selection
CUST_ID CUST_NAME CUST_TYPE ADDRESS
----------- ----------- ----------- -----------
1 CUST 1 CREDIT RISK ADDRESS1
1 CUST 1 CREDIT RISK ADDRESS2
2 CUST 2 GOOD ADDRESS1
2 CUST 2 GOOD ADDRESS2
2 CUST 2 GOOD ADDRESS3
3 CUST 3 BAD ADDRESS1
to display like this
CUST_ID CUST_NAME CUST_TYPE ADDRESS
----------- ----------- ----------- -----------
1 CUST 1 CREDIT RISK ADDRESS1
ADDRESS2
2 CUST 2 GOOD ADDRESS1
ADDRESS2
ADDRESS3
You are help will be much appericated.
Thanks & Regards
Nitin.
Using sql server
I want to create a txt file from a table i have. I have the data in the correct formats but i want to include some padding around my selected four columns.
Im not sure about the best way to go about this?
Hi,
I have the following problem.
I have made a parametered report where i ask a begin date (@datefrom) and an end date (@dateto).
when i enter my dates via the little calendar is see the format dd/mm/yyyy
When i do a preview of report i have a textbox with the following expression :
="Total work time from " & Parameters!DateFrom.Value & " to " & Parameters!DateTo.Value
but the output of the date format is mm/dd/yyyy.
What can i do to have the output format dd/mm/yyyy
Vincent
I am trying to format the result output from my query so that it looks like currency. Does anyone know how to do this?
Here is my very simple select statement:
SELECT Field_Value
FROM tbl_all_results
I was thinking some kind of cast/convert would do the trick but I can't seem to get anything to work.
Thanks for your help!
Month NoofAttendanceDays NoofPresentDays
January 25 20
Febrauary 23 21
March 26 20
April 24 21
Thanx in Advance
I have the following Query which gives the output in XML format.
select * from <Table_Name> For XML auto
I wanted to store this output in a .Xml file at my local machine.
I would prefer to have a proc when called generates the xml and stores it on the local m/c. Any ideas ?
Thanks,
I manually build my url for my reports. In the url I build, I can specify the output format I would like it to automatically use when the report opens. However, it always first opens the dialog box to either Save, Open, or Cancel. Is there anyway to bypass this and just open it in the format I have coded in the url? Thanks.
View 2 Replies View RelatedHi Kids,
I've just upgraded a 6.5 database to version 7.0 and I'm having a problem with default date output formats.
Under 6.5 the output format was: Sep 2 2001 12:00PM
Under 7.0 the same field is output as: 2001-09-02 12:00:00.000
Is there a way to permanently set the default output format for dates on a database or server wide?
Thanks
Ron
I have sample data like 'J.S.xxxx','J.xxxx.'
Want to separate the . in the middle of word with single space and display results in following way.
J. S. xxxx
J. xxxx
Need the code in optimized way.
Hi All,Currently the query returns 2006-03-27 00:00:00, can I make it output03/27/2006, I want to truncate the time, and replace the hyphens withforward slashes. Any ideas?Thanks In Advance,~CK
View 6 Replies View RelatedA few details first:
The report server is remote to the development server (VS 2003).
The web application that will be calling it is ASP.NET 2.0 and developed in VS 2005.
I have a couple of questions:
a: Can I link to the report from the web application using a basic Hyperlink control rather than using a ReportViewer control?
b: How can I force which format it opens in? Say I want it to be rendered as a PDF in one instance but at an XSL document in another.
Many thanks and kindest regards,
TwoForTea
Hi all,
I'm Trying to replicate the creation of an "xml" file that is currently created using a C++ application. I want to take that application out of the picture, but need to create the same format XML file because a step later in the production process uses this file, and I cannot change it.
The output format I am looking for is:<?xml version="1.0" encoding="utf-8"?>
<FUNDS>
<AMRGX>
<NAME>AMERICAN GROWTH D</NAME>
</AMRGX>
<AHERX>
<NAME>AMERICAN HERITAGE FUND</NAME>
</AHERX>
<AMRVX>
<NAME>AMERICAN INVESTORS GROWTH FUND</NAME>
</AMRVX>
.
.
.
</FUNDS>The problem I am having is that I cannot seem to get the level/node of the fund symbol (AMRGX, AHERX, and AMRVX in the example above) as it needs to be. I think this must be some non-standard use of XML, since the tag is really the data itself (?)
The closest I have been able to get so far is:
<FUNDS>
<SYMBOL>AMRGX</SYMBOL>
<NAME>AMERICAN GROWTH D</NAME>
</FUNDS>
<FUNDS>
<SYMBOL>AHERX</SYMBOL>
<NAME>AMERICAN HERITAGE FUND</NAME>
</FUNDS>
.
.
.As you can see (hopefully) I am able to get the data I need but cannot get:
(1) the FUNDS tag(s) to be the very highest level/root.
nor (2) the SYMBOL part (tag label?) to be the actual variable stock fund.
Am I 'splaining this well enough? I don't necessarily need all the code, since I know I haven't given enough info to help with that, but my basic question is - - Is it possible to get a variable TAG based on the table DATA?
I want my SYMBOL tag to be the actual SYMBOL for the stock fund.
Confused? Not as much as I am *LOL* I am new to the use of all but XML EXPLICIT use, so any help would be appreciated - at least regarding my two formatting questions.
Yes, I have (and am still) searching around BOL for my answers, but so I have found nothing that helps me out. Meanwhile, suggestions are welcome!
Thanks!
I have the following code and the result set is coming out as nvarchar. So, when I create a report in SSRS, it is not formatting as a date.
, CASE
WHEN isnull(cv2.Accepted,0)='True' AND cv2.Visit ='V2' AND cv2.StepNo='3' THEN 'Y'
ELSE CONVERT(varchar,[dbo].[fn_Get_WorkingDays] (co.PlannedGoLiveDt, -10))
END AS 'System Verified'
Hello folks,
A while back I wrote a .NET assembly with a COM wrapper to provide an interface between Report Services and legacy MS Access applications. Basically, it returns the byte arrays from the .render method (output format emf) to be written into an Access image control.
This worked flawlessly on any 8.5"x11" RS report we threw at it on the first two Report Services I tried it against. When we set up a third Report Service at another facility, the same reports were coming out with the physical dimension metrics on the emf as10"x13", so the Access version of the report is clipped significantly on the edges.
I trapped the output into a System.Drawing.Image object in the .NET assembly and put a watch on it. The two servers that work as I expected show HorizontalResolution and VerticalResolution as 96.0946, while the one that doesn't has these values as 81.2799. The PhysicalDimension property shows 21542.3672 x 27939.4316 on the working servers and 25531.25 x 33031.25 Otherwise, the properties of the images seem to be identical. The PhysicalDimension values I see are completely consistent with the size units of 0.01 mm, as the the emf documentation indicates. The resolution of 96 is consistent with the "Windows default" values of 96 dpi. The ratio of the resolutions is correct for an 85% scaling factor for the "misbehaving" Report Service.
I have not been able to find any reason for the differences between the Report Services, and I have not been able to figure out how to control it. I tried setting DpiX and DpiY as 96 in the DeviceInfo for the render call with no change in the behavior of either server.
Can anybody enlighten me on what is happening and what I need to do to get consistent behavior between the servers? Or why the DpiX and DpiY settings for the emf rendering extension don't seem to work?
Thanks,
Tim Bailey
Energy Laboratories, Inc.
SELECT membername, outputval
case when choice = 0 then outputval else null end as outputval
from MyDatabase
group by membername, outputval
how to format outputval:
if outputval < 40000
format outputval as:
5 - 5.78 - 6.9 - 6,778 - 4,567.8 - 12,456.78 - etc.
if outputval >= 40000
format it as a scientific.
I am trying to output a number in a specific format. I am playing with CAST() and CONVERT() but have not been able to get what I need.
Current: 0.019891
Desired: 000199
It doesn't have to remain in a number format, as i will be output to a CSV in order to bulk load into another system.
I have a table Test123 having three column EmpID,AttribName,AttribValue.
run the below query to generate table structure and data.
Create Table Test123(EmpID int,AttribName varchar(50),AttribValue varchar(50))
insert into Test123 values(1,'Name','X')
insert into Test123 values(1,'Age',50)
insert into Test123 values(1,'Salary',1000)
insert into Test123 values(2,'Name','Y')
insert into Test123 values(2,'Age',30)
insert into Test123 values(2,'Salary',2000)
insert into Test123 values(3,'Name','Z')
insert into Test123 values(3,'Age',35)
insert into Test123 values(3,'Salary','One Hundred')
And I want output in below format.
AttributeValueType
=================
AGE | NUMERIC
NAME | ALPHABET
SALARY | ALPHANUMERIC
==================
I need to display the output of a table in a specific format, I have attached the sample screenshot and code for reference.
I tried with pivot but does not seems to be working.
I am working on a code that will convert the query output into a html query output. That is the output of the query will be along with html tags so that we can save it as a html file.
The stored procedure that i have used is downloaded from symantec website and is working fine. Below is the code of that stored procedure.
/****** Object: StoredProcedure [dbo].[sp_Table2HTML] Script Date: 12/21/2011 09:04:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Table2HTML] (
@TABLENAME NVARCHAR(500),
@OUTPUT NVARCHAR(MAX) OUTPUT,
[Code] ....
The code works fine and i am able to get the output with html tags. The problem occurs when i insert stylesheet in the code. I tried to enforce styles using a stylesheet for the table returned in my sql code, so that it will look good. below is the stylesheet code that i inserted between <head> and </head> tags.
<style type="text/css">table.gridtable { font-family: verdana,arial,sans-serif; font-size:10px; color:#333333;border-width:1px; border-color: #666666; border-collapse: collapse; } table.gridtable td {border-width: 1px; padding: 8px; border-style: solid; border-color: #666666; background-color: #ffffff;}</style>
If I run the procedure without the style sheet code, it works fine. but when i run the procedure with style sheet code i am getting the below errors.
Msg 105, Level 15, State 1, Line 98
Unclosed quotation mark after the character string '</table></body><'.
Msg 102, Level 15, State 1, Line 98
Incorrect syntax near '</table></body><'.
[Code] .....
I checked the code and i am not able to find what is the mistake. I tried changing the quotation mark but it didn't worked.
Hi,
I am using a sql command to produce outputs having 3 columns
Before running the sql I am spooling the output in .xls format
But when I open the .xls file (i.e. the spooled file) data of all the 3 columns appear in a single column with blank spaces between them
Is there a way by which I can ensure that each output column of the SQL appears in a seperate column in the excel Sheet
Thanks
Sanket
I need to get the output of a SSIS package in an Excel.
I have made a Stored Procedure for getting the output, since the output has variable number of columns hence i have made a table through the stored procedure which is dynamically created when the procedure is executed.
Now i have a table in SQL with the output data.
Any pointers, how to put it into an excel??
i m creating one google map application using asp.net with c# i had done also now that marker ll be shown from database (lat,long)depends on the lat,long i wanna display customer,sales,total sales for each makers in html table format.
View 2 Replies View RelatedI am using bcp to get the query output in excel file. But I am not able to see the column headings. Is there a way to get the column heading also in the output file.
the command I am using is this.
declare @x varchar(300)
set @x = 'bcp "select * from Northwind..orders" queryout c: est.csv -S local -U sa -P passwd -c -C RAW -t "," -r '
exec master..xp_cmdshell @x
go
OK, another problem but the same member, i am feeling REALLY thick now!
I have a job set up that extracts data from a table and puts to an Excel sheet, this is then emailed to certain recipients.
why is the data appending in the Excel file rather than overwriting? How can i prevent this?
Is there a way of running an OS command via xp_cmdshell and sending the results to a spreadsheet within a DTS package ?.
View 2 Replies View RelatedI would like to create a SQL job that runs a basic query monthly and saves the results as a .xls file and emails the results to a user. I've been unsuccessful with sp_send_dbmail in that the output file is always a single column. Any suggestions?
Thanks, Dave
I use bcp command to output to excel, it works. But I want to format the excel, some column width are too small,user need adjust the column width, otherwise it shows ######.
How can I set columns width when I use bcp output to excel.
Also, can bcp command output to multiple excel sheets and add report title in each excel sheet?
I have this script which outputs a combination of financial data. I have recently joined a table that includes a narrative and when I run the query, it runs perfectly. However, when I copy and paste the output into Excel, not all the data is showing. After the process of elimination, it is because Excel doesn't like the narrative column and not all data is copied across which is very annoying. If I remove the narrative column, then all the data copies over correctly.
I am using Excel 2010 which is quite up-to-date.