SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Select statement - Effective running


Select statement - Effective running

Author
Message
Informer30
Informer30
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 36
Hi All,

I have an select statement which needs to be run 15 times with a slightly different where clause and outputted
to excel.

Rather than me running 15 select statements manually is there an easy way I can do this as an one off and a
monthly task?

Many Thanks in advance...
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65342 Visits: 17980
Informer30 (4/28/2014)
Hi All,

I have an select statement which needs to be run 15 times with a slightly different where clause and outputted
to excel.

Rather than me running 15 select statements manually is there an easy way I can do this as an one off and a
monthly task?

Many Thanks in advance...


Not enough information here to offer exact advice. Maybe you can use a stored procedure here? Not sure. If you need to do this monthly then you could set up an SSIS package to export query results to excel and schedule is as a job.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Informer30
Informer30
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 36
Hi Sean,

thanks for reply, please let me know what further information is required...

I have a select statement that I need to run 15 times with a different where clause and the output to be excel.

SSIS will ideal going forward, for now I need to cater for the previous 18 months. Rather than running 15 select statements for 18 months = 270 statements I trying to find a quick to run these statements and save me a lot of time...

Thanks
J Livingston SQL
J Livingston SQL
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12675 Visits: 37696
Informer30 (4/28/2014)
Hi Sean,

thanks for reply, please let me know what further information is required...

I have a select statement that I need to run 15 times with a different where clause and the output to be excel.

SSIS will ideal going forward, for now I need to cater for the previous 18 months. Rather than running 15 select statements for 18 months = 270 statements I trying to find a quick to run these statements and save me a lot of time...

Thanks


maybe if you shared your select statement then perhaps we could help

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65342 Visits: 17980
Informer30 (4/28/2014)
Hi Sean,

thanks for reply, please let me know what further information is required...

I have a select statement that I need to run 15 times with a different where clause and the output to be excel.

SSIS will ideal going forward, for now I need to cater for the previous 18 months. Rather than running 15 select statements for 18 months = 270 statements I trying to find a quick to run these statements and save me a lot of time...

Thanks


Unless you are willing to provide details we can't help. Keep in mind that I can't see your screen, I have no idea what your tables are like, I have no idea what you are trying to accomplish. I only know what you have posted, which is next to nothing. In your second post you have added details about "catering for the previous 18 months". I have no idea what that means. Do you need to adjust your where clause to have some date checks? You say you need to adjust your where clause slightly. Does that mean you need to use a different value for the same column or do the columns being filtered changed? I am not saying you need to post your entire enterprise structure here but you must post enough details so that we know what the question is. At this point, all I know is that you want some help trying to figure out how to automate running some queries that need to change a little bit for each select statement.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
David Burrows
David Burrows
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17072 Visits: 10138
If you have SSRS and the non filtered data is not too large then you could create a report with one unfiltered dataset and 15 tablix each with a filter, StartPage and a Pagename. When exported to excel it will create one workbook with 15 tabs.

*Edited*

Or you could have one report, 15 datasets and 15 tablix.


Far away is close at hand in the images of elsewhere.

Anon.


Informer30
Informer30
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 36
Thanks for your replies....sorry for the lack of infomation..

David, please can you advise further how I can achieve one ms excel report and 15 tabs via SSRS output?
David Burrows
David Burrows
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17072 Visits: 10138
Informer30 (4/30/2014)
Thanks for your replies....sorry for the lack of infomation..

David, please can you advise further how I can achieve one ms excel report and 15 tabs via SSRS output?


Only avaliable in 2008R2
The report project property Target Server Version must be set to SQL Server 2008 R2
Tablix and groups have a PageName property (for groups expand the group property)
PageName is used to name the worksheet
PageName can be an expression


Create a dataset
Add a tablix which uses that dataset
Add rows and columns to the tablix
Set the tablix pagename

Repeat for each new dataset/tablix you need
Note make sure you set PageBreak on subsequent tablix


Far away is close at hand in the images of elsewhere.

Anon.


Informer30
Informer30
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 36
ok thanks, new to SSRS usually use import/export option

Do access these setting via BIDs?
David Burrows
David Burrows
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17072 Visits: 10138
Yes or Report Builder via web browser (if you have permission)


Far away is close at hand in the images of elsewhere.

Anon.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search