Looking for suggestions to Combine Reports into one report

[GolfNFool]

Hello All,

I work for a business group of my company which has use of an SQL server that has some restrictions placed on us by our IT group. One restriction is that we are not able to use views. However, I don't want you to discount the use of a view if it is my best solution as I will fight the fight!!

OUR SQL Server's are R2 current.

I currently have 8 separate reports on our reporting server that we call Metric 1 -8 reports. Each of the reports have the exact same SQL statement as far as the fields being pulled and the tables being joined. The main difference between each report is an intricate WHERE clause for each.

I'd like to get the reports into one report where when there is a change to the report, like adding an additional field to the data I can make the change in one place as opposed to eight, and obviously want to have this report run as efficiently as possible. I think the report would be really slow trying to run SQL statements from eight different datasets. However, I am worried that some of the WHERE clauses are fairly intricate and not sure being able to use a Filter is going to work.

I was thinking of R2's concept of a shared dataset, but have not used this before, and not sure if it is the answer. So I am looking for suggestions to try to accomplish this condensing of reports.

Thanks, Troy

 

[xlbo]

If you can combine the data sets so that all conditions are met into 1 dataset then certainly a shared data set is for you. I would add an extra column when creating the shared data set that indicats hich one of th 8 metrics any particular line is for so that when you query the shared data set you can apply a very easy filter rather than having your complex where clauses in multiple places

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GolfNFool]

Geoff,

Thanks for your response. That is exactly the path I went down on Friday afternoon. Only exception is that I have eight extra columns as a record could be visible on more than one metric.

Troy