Solved

SQL View Based on User Input

Posted on 2014-01-29
5
340 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 65

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 65

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

How to increase the row limit in Jasper Server.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

752 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