June 2, 2008 at 2:36 pm
Hi
I have to create a dataset for the Reporting service.
I currently have 3 views my report needs to show one column from each view. what should i need to do?
I am guessing that i need to create another view which gets the required column from all 3 views.
is this approach is correct?
any suggestion or help is greatly appreciated.
Thanks
June 2, 2008 at 2:56 pm
Are there any relations between the views? What about creating a single view that access the data directly rather than accessing other views?
June 2, 2008 at 3:02 pm
I cant do that. those views are really big & i just need only one column from that view.
Ex: view1 -
Date columnView1
----------------------
1/1/08 50
view2 -
Date columnView2
-------------------------
1/7/08 45
view3 -
Date columnView3
--------------------
1/1/08 20
1/7/08 25
My new view or temp table should have -
Date ColumnView1 ColumnView2 columnView3
----------------------------------------------
1/1/08 50 null 20
1/7/08 null 45 25
even if I create Store procedure with above outputs thats fine. How can i do this?
June 2, 2008 at 8:41 pm
Do any of the views have all of the dates you will need?
Do you need all of the rows or are you looking for a particular subset of the data? It might not be a bad idea to parameterize a stored procedure if the views are quite large.
June 3, 2008 at 12:08 am
Even if the data is large, a subquery might do the trick for you
Select view1.date, view1.column1, view2.column2, view3.column3
from
(select date, column1 from table1) as view1
left join
(select date, column2 from table2) as view2
on view1.date = view2.date
left join
(select date, column3 from table3) as view3
on view1.date = view3.date
Dont forget to look at your execution plans to avoid those scans
~PD
June 3, 2008 at 7:45 am
Thank you, this is what I was looking for.
June 3, 2008 at 7:53 am
Dont forget to look at your execution plans to avoid those scans
Did you check?
June 3, 2008 at 12:32 pm
Yes I Checked & I did little manipulation accordingly. but the main approach was as you guided to join the views
June 3, 2008 at 12:46 pm
The posted solution may not return all data. If the date doesn't exist in the first view, it will not be used in the output.
June 4, 2008 at 8:07 am
You can use a calendar or tally table to provide all relevant dates then left join each view to that.
June 4, 2008 at 12:28 pm
matt.mallen (6/3/2008)
The posted solution may not return all data. If the date doesn't exist in the first view, it will not be used in the output.
Change them from left joins to full outer joins. That should do what you need for this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply