| Author |
Topic |
|
sql_dan
Starting Member
43 Posts |
 Posted - 2010-06-04 : 07:59:52
|
Hi Guys,I am using the Business Intelligence Development Suite (BIDS) to design and deploy all of my reports to SSRS.The company I work for has 3 sites and therefore I connect to three different databases with the same architecture. For a big project I developed I have created a new database to store information from all three site databases to store all the big stuff for quick access and have a few things that without creating new tables for them are just looked up as and when's needed.The reports architecture is one where you start off with a site selection page, then an agents selection page, then a customer selection page finally resulting in a customer detail page. Each of these are separate reports in the project and I only mention it as the parameters used to run the final customer detail report are inherited from the selections made in the initial 3 selection reports.My current method (which works great by the way!) is to separate into 3 rectangles complete replicas of the data I would like to show for each of the three sites and then using the 'Visible' option hide each rectangle based on the site parameter!What I would like to achieve and this may or may not be possible is to only have a single rectangle and using the site parameter select the data from the corresponding database. This will have two bonuses:1. It will tidy the report up and ensure that any changes are only made once, not 3 times!2. Tidy the datasets up as I have had to create datasets for each tablix in the rectangles for each site tripling the setup work!In essence I guess the question is... quote:
Can I in BIDS create data queries where the 'FROM' clause is looking at a variable?
Thanks for looking, I appreciate any suggestions and I apologise for the length of the post!let me know if anything is unclear!  CheersDanIf you cant sleep at night, its not the coffee its the bunk! |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-06-04 : 08:16:03
|
I don't believe it is possible but I don't really know.As an approach:My idea would be to have one or more views which are combining the data from the 3 sources giving them an extra column inside the select to have the possibility to get the records of the wanted database.In your "FROM" you need only to specify that view(s).Maybe another mate here has a better idea.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
savior faire
Posting Yak Master
194 Posts |
Posted - 2010-06-05 : 09:28:30
|
| Seems like you can do this with report parameters and have each parameter be a query where you pass the value from the first selection to the second, the finally to the third one.______________________________________________Talk sense to a fool and he calls you foolish. |
|
|
|
sql_dan
Starting Member
43 Posts |
Posted - 2010-06-05 : 10:32:41
|
quote:
Seems like you can do this with report parameters and have each parameter be a query where you pass the value from the first selection to the second, the finally to the third one.
How would I do this? I am already using the parameters to specify which information to use for each of the reports its goes to. How could I then utilise the parameters in the query fields selecting the data?I haven't had a play with the views yet as suggested by webfred but I can see how that works.If you could elaborate I would be grateful.ThanksDanIf you cant sleep at night, its not the coffee its the bunk! |
|
|
|
savior faire
Posting Yak Master
194 Posts |
Posted - 2010-06-06 : 12:58:45
|
| The first parameter will retrieve some data from a table based on a selection from a user.Take the selection and input that to the second parameters query to retrieve the data based on the first query.On so on.______________________________________________Talk sense to a fool and he calls you foolish. |
|
|
|
sql_dan
Starting Member
43 Posts |
Posted - 2010-06-08 : 06:09:18
|
| Thank you everyone for your ideas on this one. After a day of playing i have managed to increase the speed and reduce the expense of the queries through the use of webfred's view idea!Thanks you very much as always!!Dan__________________________________________________If you cant sleep at night, its not the coffee its the bunk! |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-06-08 : 06:21:13
|
welcome 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
savior faire
Posting Yak Master
194 Posts |
Posted - 2010-06-08 : 06:38:38
|
| I am curious as to how webfred's solution is working and why you want to create additional database structure.Seems like "we" are duplicating data in the view.I guess I just do not understand his idea. I have read it several times and don't see what it is doing.______________________________________________Talk sense to a fool and he calls you foolish. |
|
|
|
sql_dan
Starting Member
43 Posts |
Posted - 2010-06-08 : 07:05:51
|
| The view is consolidating the data into a single data source for all three sites. So when I am querying I have all of the data in the view to query rather than trying to jump on 3 seperate data sources!It has made the query run much faster and works better.Dan__________________________________________________If you cant sleep at night, its not the coffee its the bunk! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-06-08 : 07:11:43
|
Will this not be enough?SELECT {whatever columns you want}FROM ( SELECT {whatever columns you want} FROM SiteDatebase1 WHERE @param1 = 1 UNION ALL SELECT {whatever columns you want} FROM SiteDatebase2 WHERE @param2 = 1 UNION ALL SELECT {whatever columns you want} FROM SiteDatebase3 WHERE @param3 = 1 ) AS dNow you can combine any source you want with three simple checkboxes.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
savior faire
Posting Yak Master
194 Posts |
Posted - 2010-06-08 : 11:09:28
|
| Okay, I see what you are doing Dan. I would not do it that way, but I am always up for seeing what others are doing and their reasoning and logic behind it. The only way to learn new techniques.______________________________________________Talk sense to a fool and he calls you foolish. |
|
|
|
|
Same query different sources?? [SOLVED]