Solved

SQL View Based on User Input

Posted on 2014-01-29
5
341 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 150 total points
ID: 39819252
>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
ID: 39819280
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 66

Expert Comment

by:Jim Horn
ID: 39819655
>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
ID: 39820782
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
ID: 39824672
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

615 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