Solved

SQL View Based on User Input

Posted on 2014-01-29
5
335 Views
Last Modified: 2014-01-31
Hello,

I have a view request from a user to create a report that simply shows which of our techs are working or not based on input from the user which is a date range. This is an example of the expected output:

Column 1                       Date1         Date2            Date3              Date4             Date5

Employee Name               S                  S                   A                      A                      N


The S equals scheduled, the A equals available, and the N equals not available.

My problem is I am used to building sql views based on Static columns not on data that changes based on user input. Obviously i would be determining the value in each date column based on information in several tables which is tracked by the day with an hour value so that piece should be easy. Any ideas would be much appreciated.

Thanks
0
Comment
Question by:drizzt34953
  • 2
  • 2
5 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 150 total points
Comment Utility
>I have a view request .. based on input from the user
For starters, views by themselves do not allow for a user-defined parameter, so if the scope of your question is restricted to using a view, then this is not possible.   So, it sounds like what you really need is a Stored Procedure that accepts a parameter, and can return different sets populated differently based on that parameter.
0
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 350 total points
Comment Utility
To add to Jim point here...
you can either use a stored procedure or a SQL function which will return a table.

The experts from MS (microsoft) are branding function which returns a table as a parameterized views in their books (70-461 SQL Server development)


you can do some thing like this

CREATE FUNCTION dbo.<YourViewName>
(
--Parameter List
@Param1 VARCHAR(100)
....
@Paramn VARCHAR(100)
)
RETURNS TABLE
AS
RETURN
(
<Your View code using the @params for the function.
)

--Now you can use it in the your code similar to the view

select * FROM <yourViewName>(a,b...) 
--returns the results of what you are looking for 

Open in new window

0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
>I have a ..  request .. create a report .. on data that changes based on user input.
Also if 'data that changes' means the columns would change, keep in mind that SSRS (like SSIS) requires a 'contract' between a data set and it's columns such that those columns are static and do not change, and will often throw errors if those columns change based on a user-provided variable in a manner that SSRS can't handle.

If this is your situation, I'd recommend spelling out in more generic terms what you're trying to pull off here, and maybe we'll be able to provide a workaround.
0
 

Author Comment

by:drizzt34953
Comment Utility
I have reviewed what you both have contributed but i need to go back and think about it a bit. I want to make sure i am not making this more difficult than it has to be. Having two tables, one which tracks the time a person can work daily and the other that has the actual scheduled time i know that if i dragged this data out in rows instead of columns i could achieve the same thing without reinventing the wheel.

On the user end though the visual of having 70 employees one on each line for a certain date range would be very convenient for scheduling purposes. Not to mention if i then add in colors to indicate the status i think they would be very pleased.
0
 

Author Comment

by:drizzt34953
Comment Utility
Hello,

I ending up going with a static 14 day report which I was able to build in Crystal with a view i created. Its not exactly what i was going for but it will work for now. Thank you both for your time and knowledge.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now