Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select statement - Effective running Expand / Collapse
Author
Message
Posted Monday, April 28, 2014 10:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:13 AM
Points: 16, Visits: 27
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...
Post #1565650
Posted Monday, April 28, 2014 10:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:52 PM
Points: 13,069, Visits: 11,907
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 Moden's 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)
Post #1565651
Posted Monday, April 28, 2014 11:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:13 AM
Points: 16, Visits: 27
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
Post #1565658
Posted Monday, April 28, 2014 11:04 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:29 AM
Points: 1,885, Visits: 18,486
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 !
__________________________________________________________________
Post #1565662
Posted Monday, April 28, 2014 3:19 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:52 PM
Points: 13,069, Visits: 11,907
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 Moden's 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)
Post #1565717
Posted Tuesday, April 29, 2014 5:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:45 AM
Points: 7,048, Visits: 6,797
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.

Post #1565865
Posted Wednesday, April 30, 2014 5:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:13 AM
Points: 16, Visits: 27
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?
Post #1566311
Posted Wednesday, April 30, 2014 5:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:45 AM
Points: 7,048, Visits: 6,797
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.

Post #1566317
Posted Wednesday, April 30, 2014 6:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:13 AM
Points: 16, Visits: 27
ok thanks, new to SSRS usually use import/export option

Do access these setting via BIDs?
Post #1566349
Posted Wednesday, April 30, 2014 6:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:45 AM
Points: 7,048, Visits: 6,797
Yes or Report Builder via web browser (if you have permission)


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

Anon.

Post #1566351
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse