| Author |
Topic |
|
rajani
Constraint Violating Yak Guru
367 Posts |
 Posted - 2005-11-30 : 17:20:49
|
| Hi friendsam writing a tool for our application that allows user write their own queries ofcourse they dont know sql but we need provide some sql server type "query designer" type functionality where users can select fields/tables and as u know sql server builds query as we go.i heard that sql server that functionaly thru some objects (i think its SMO) and my question is does any one has some examples or links and any advise ?Thanks for your helpCheers |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-12-01 : 03:10:01
|
| If you develope your own Query Analyser thru application, then there are chances Delete and Drop statements can be executed. Give the option to select the values and display the records accordinglyMadhivananFailing to plan is Planning to fail |
 |
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2005-12-01 : 19:34:33
|
| I agree that Madhivanan. at the moment we r not worried abt DML commands but we need to write query engine to let our users develop their own queries .any ideas how can i do that plz. I just need a start where to begin.ThanksCheers |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-12-02 : 00:42:07
|
| Are you trying to simulate the same logic used in Query analyser?MadhivananFailing to plan is Planning to fail |
|
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2005-12-04 : 14:44:23
|
| not same as that one as we want to expose only few tables and column .ThanksCheers |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-12-04 : 14:49:52
|
| Do you get SQL Server Magazine? If so, read this months issue. They have an article about reporting services with a custom "report builder". You might want to check it out.FYI:DMO is the programming language for SQL Server 2000.SMO is the programming language for SQL Server 2005. If you're planning on going down this route, you have a fun learning curve ahead. :) Look at Cognos ReportBuilder and the UDM capabilities of SQL Server 2005 before you commit yourself to this project.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2005-12-04 : 15:04:41
|
| Thank you very much Derrick.You gave me very good suggestions before many times.BTW i dont get sql server magazine so probably i cant read that.i'll look at other suggestions you made (ie. UDM capabilities).Kindly point me ,if you have any sample scripts with regard to this.Thanks again.Cheers |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-12-04 : 15:27:15
|
| http://www.microsoft.com/technet/prodtechnol/sql/virtuallab/default.mspxThere's a virtual lab on SMO here. The SQL Server 2005 downloads have Books Online which includes a programming reference for DMO and SMO.http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/default.mspxYou can also download trial copies OR SQL Server Express at this site. They've done a good job putting together resources for 2005, so lookup UDM and Reporting services on there and dig away.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2005-12-04 : 15:32:51
|
| Thank you very much for quick reply Derrick.I'll get on to them :)Cheers |
|
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2005-12-04 : 19:49:39
|
| Hi derricki found abv articles very useful especially sql server 2005 report builder utility.we had a quick look and very impressed with what it offers.have you worked on this report builder ,derrick ? am just wondering how can we display descriptive names for the fields in report builder for end users? at the moment it just displays field names from table.Cheers |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-12-04 : 21:18:27
|
| You can make "friendly names" when defining the UDM the report builder uses. It's in the Properties.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2005-12-04 : 21:23:24
|
| Thanks again derrick.finally figured out.Cheers |
|
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2005-12-04 : 22:25:56
|
| hi derricki need your expert advise here. i resolved naming issue with a View with lengthy field names instead of using Description property.the issue is ,i'll start with an example ,i've 2 views (ParentView and ChildView) and the tables used by these views have foreign key relation ship .when i use these 2 entities in report builder as soon as i add a column from one view (say ParentView) the other view disappears from list !! I assume Report builder looks FKey to link 2 entities and since am using views which will not have any fkeys on them it removes other entities from list.is there any way to link these 2 views so that users can add few columns from ParentView and few more from ChildView.Yes, If i use tables directly everything works fine .Thanks for your help.Cheers |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-12-04 : 23:13:05
|
| If you use a UDM and build the report off of that, you can define a relationship between the child and parent views I believe. I don't have the tool up right now, so I would have to get back with you later. Try it out though.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2005-12-04 : 23:37:52
|
| >>I don't have the tool up right now, so I would have to get back with you laterIf you can do that ,that is much appreciated.meantime i'll try to figure out what UDM is !!Thank you very muchCheers |
|
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2005-12-05 : 15:49:24
|
| Hi derricki read documentation on UDM and seems i need to use Hierarchical model for my requirement.but document does not say anything how to create a UDM model ? or there any built in tools to achieve that? or am i missing something here .ThanksCheers |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-12-05 : 23:42:17
|
| You can select Report Model as a project type in the BI Development Studio. That will allow you to create a very detailed model. There's a tutorial included free with 2005. It has a good walkthrough for you on creating a UDM in the tool. Look up the Analysis Services tutorials in Books Online.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2005-12-06 : 14:56:42
|
| Thank you very much derrick.I finally figured out how to do that,if anyone interested ,when creating Report model when we double click on data source view we created it gives graphical representation of the object just like database diagram in enterprise manager and we can drag&drop references.am just curious ,if you have any ideas,since Report service is server based is it possible to use Report builder on a client machine to create new reports ? i know that client workstations can easily run reports that're already created on server.Thanks you for all your help derrick,you got me on to a very good start.if anyone wants URL,derrick mentioned abv ,here it is (its BI URL)ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/sqltut9/html/95c9c883-12e5-47af-8da1-b77c6ef7484c.htmCheers |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
writing a query builder