Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DNF98
Starting Member
12 Posts |
 Posted - 2008-06-02 : 09:17:11
|
| I recently viewed an application which included customizable savable reports. In the first step, the user selects from a very large (probably over a hundred) set of variables to include in the report (these would be the columns displayed in the report). In a second step, the user selects (from the same set of variables) which variables they want to set as criteria indicators (these would then be dropdowns and text boxes on the report that would filter the query results).So, how do they do this? Lets take the AdventureWorks database as an example. Lets say the user can select the following variables to include in their report:Product.NameProduct.ProductNumberProduct.ColorProduct.SizeProduct.ListPriceWorkOrder.StartDateWorkOrder.EndDateWorkOrder.OrderQtyPurchaseOrderDetail.DueDatePurchaseOrderDetail.UnitPricePurchaseOrderDetail.OrderQtyVendor.NameEtc…How do you design this in the database? If you have a table that holds all of the possible variables, and a table that connects the user-selected variables to the user – how then do you construct the query that pulls it all together? For example, what if a user wants only these two variables in their report:Product.NameVendor.NameHere is the query:select p.name as ProductName, v.name as VendorName from Production.Product as pinner join Purchasing.ProductVendor as pv on pv.ProductID = p.ProductIdinner join Purchasing.Vendor as v on v.VendorId = pv.VendorIdHow do you build that query from just getting two variable names? |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-06-02 : 10:32:32
|
| They would probably be using dynamic SQL. The query would check flags via an if statement to see which parts of the query need to be added. |
 |
|
|
|
|
|
Saved Reports - How did they do that?