Excel weekly report

Question

Excel weekly report

kshatriya24

SSC Eights!

Points: 887
More actions
September 12, 2011 at 8:31 am

#257154

Is there any way I can generate SQL SSIS package to generate weekly report like the following format.
CaseType 09/10 09/11 09/12 09/13 09/14 09/15 09/16
Others 2 0 3 0 0 0 0
Watering Day 2 0 3 0 0 0 0
Hardsurface 2 0 3 0 0 0 0
There are five casetypes fixed.
Thank you

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

it really depends on your data source and how the data is structured. but in theory it is possible by using ssis, though i will say that it is much easier to create a csv 'report' than an excel 'report'

or else you could do this fairly easily in SSRS and even setup a schedule for the delivery in Excel.

kshatriya24 SSC Eights! Points: 887 More actions · Answer 2

Thank you for your prompt reply.

here is my SQL statement which am running on daily basis to get count. End user wants information in format which i posted earlier.

SELECT C.CREATIONDATE, C.CASETYPE, C.CASESUBTYPE, T.LONGDESC, COUNT(C.CASETYPE) AS 'Total Count'

FROM CASEFILE C

LEFT OUTER JOIN CASEFILE_SUBTYPE T ON C.CASESUBTYPE = T.CASESUBTYPE

/*THIS IS OTHER WAY TO GET COUNTS FROM THE DAY BEFORE FROM CURRENT DATE*/

--WHERE CREATIONDATE = CONVERT(VARCHAR(10), GETDATE() -1 , 120) + ' 00:00:00.000'

/*THIS IS IF WE WANT TO PULL RECORDS STARTING ON THE DAY BEFORE FROM CURRENT DATE*/

WHERE (C.ROWADDEDDTTM BETWEEN CONVERT(VARCHAR(10), GETDATE() - 1, 120) + ' 00:00:00.000'

AND CONVERT(VARCHAR(10), GETDATE()-1, 120) + ' 23:59:59.000')

AND C.CASETYPE in ('CONNS', 'CONCO')

AND C.ROWADDEDOPRID = 'User1'

AND (T.CASETYPE = C.CASETYPE AND T.CASESUBTYPE = C.CASESUBTYPE)

GROUP BY C.CREATIONDATE, C.CASETYPE, C.CASESUBTYPE, T.LONGDESC

ORDER BY C.CREATIONDATE, C.CASETYPE, C.CASESUBTYPE

steveb. SSC-Forever Points: 46733 More actions · Answer 3

do you need a new excel workbook created each time you run the package?

or do you need the existing workbook updated?

kshatriya24 SSC Eights! Points: 887 More actions · Answer 4

Existing excel need to be updated daily basis with addition of date column.

CaseType 09/10 09/11 09/12 09/13 09/14 09/15 09/16

Chrissy321 SSCoach Points: 15490 More actions · Answer 5

I don't believe SSIS can handle a dynamic number of column names.

Perhaps you could add a static header in row A. Column1 - Column32.

The B would be your true header with your varying dates.

Then c, d and e would be your data.

kshatriya24 SSC Eights! Points: 887 More actions · Answer 6

Thank you all for your prompt reply. I think I was not clear initially with my questions. End user has requested to count of all the product information on weekly basis in the following format.

Product Information Description 09/04/11 09/05/11 09/06/11 09/07/11

Product 1 Sprinkler 0 5 7 0

Product 2 waterhose 3 6 3 0

And so on for past seven days. Can someone please advice how to achieve this result from SQL 2005 on excel report. I worked on date logic for past seven days, rest I need guild lines.

SELECT datename(DW,nDays) TimelineDays,

Convert(varchar(10), nDays, 101) TimelineDate

FROM (Select GETDATE() AS nDays

union Select GETDATE()-1

union Select GETDATE()-2

union Select GETDATE()-3

union Select GETDATE()-4

union Select GETDATE()-5

union Select GETDATE()-6) AS tDays

Any help appreciated.

Chrissy321 SSCoach Points: 15490 More actions · Answer 7

You may find that the use of a calendar table will make you query simpler.

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

However you get results it looks like you want to pivot the TimeLineDate field to become the columns in your report. Look at the PIVOT function to do this. http://msdn.microsoft.com/en-us/library/ms177410.aspx

Once pivoted you column names will be dynamic which SSIS won't like. You could concievably UNION your pivoted results with a static row of data that would serrve as static column names which you could then use in SSIS.

Product Information Description Day1,Day2,Day3...