June 21, 2009 at 9:26 am
Hi,
I have two issues on which I'm looking for some help.
1 ) I have scheduled a report using subscription. It is mailed to me as an excel attachment at a specific time. But This Excel sheet has "Blanks". How do I modify the query so that it shows NULL instead of Blanks in the report.
2) In the same report which i get in Excel, All the columns are expanded according to the content. How do i make the changes in the query / design so that Both Height and Width of Cells are fixed? I see that the width is fixed but only the height expands according to the content.
I'd appreciate if someone can help.. Pleaseeeeeee.
June 22, 2009 at 9:32 pm
Can someone help please?... It shouldn't ne a tough one for sql geeks 😀
June 23, 2009 at 5:34 am
On 1.
Use a case statement to make it equal the value where one exists but if Null then put in the string of 'Null'. This string will then be preserved in Excel.
Haven't got time to give an example at present but if you need one, please shout.
Steve.
June 23, 2009 at 7:56 am
As Steve said, use isnull() for your first question. As for your second question, I don't think that your sql query will have any effect on the column width or row height of the spreadsheet. Perhaps you should try and look at it from an Excel point of view. You could probably create a macro that would format your columns and rows the way you want them.
----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
June 23, 2009 at 8:15 am
Hey steve
This is a loud shout for the example 🙂
And hey Grasshopper
since the reports are geberated and sent by SSRS how wud i include excel formatting/ , macro. it really doesnt matter how the formatting shows in sql but it sgould not expand colum height in excel
And one add on.... is it possible to schedule reports so that muultiple reports can be emailed (zipped). so extract into excel, zip it and email ... possible?
June 23, 2009 at 8:56 am
Attached against some data that was handy. I have shown you what it was prior and then a column once the case is taken into account. If the period is 'Ad-Hoc' then display 'NULL' in the output (as a string). If you swap 'Ad-hoc' for Null and change teh table name, columns to your schema then you will get a string Null instead of a blank in Excel output.
SELECT DISTINCT Period,
CASE
WHEN Period='Ad-hoc' THEN 'NULL'
ELSE Period
END AS PeriodYYYYMM
FROM Statements
PeriodPeriodYYYYMM
Ad-hocNULL
042009042009
102007102007
June 24, 2009 at 8:47 pm
Thanks Steve. I'll work with your example.
I have one more question. How do I schedule multiple reports? I mean multiple reports should go in one mail (preferrably zipped / compressed). Is that possible?
June 25, 2009 at 3:59 am
Not aware of a way of doing that in RS. A subscription is per a report. If you had multiple subscription push the reports out to a file share you could use another process to pick them up into one mail? Someone else may have a better idea.
June 25, 2009 at 9:36 am
It may be simpler in the long run to make a new report that is a combination of all of the requested reports, perhaps using each report as a sub report. This way it's still a single sbscription, and arrives as a single Excel file. Of course you still have to separate the data once you get it, but, as stated in an earlier post, that could be done by Macro/VBA.
Since you are outputting the data to an excel sheet, a completly different idea would be recreate the reports as views in SQL server, and use SSIS to export each of the views as an excel sheet. From inside SSIS you can do any and all of the items you've asked about, combine, zip, email, even column widths in Excel using the MSOffice object library.
June 25, 2009 at 12:37 pm
Guys,
Journeyman's feedback makes sense. I have starting using SSRS recently.
I have created views for each report. So I got to a project -> run the report server wizard and create single reports by placing select * from View1 in the query. I do it for each each view. Is this the correct way?.
To continue, is it possible to extract reports in excel to different sheets in excel. I mean View 1 on sheet 1, view 2 on sheet2 and so on. In this way only a single file will be created with different report on different sheets. Did I make some sense ...? 😎
June 25, 2009 at 1:02 pm
Not from SSRS, saving files to Excel are whole files only.
Now, in SSIS you can create an Excel Destination task, which specifies the name of the sheet to export to, which would serve your purpose.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply