0

im using the below function to get network days between the submission date and the day i run the report; at the same time i am using a date parameter in my SSRS to get the tickets between these dates , my problem is i need the end date of the network days to be the end date of my date parameter, i am not sure how to link them together somehow , specially that this function is on my SQL and the date parameter is on my SSRS

alter function [dbo].[days_diff](
                                 @date1 datetime
                                ,@date2 datetime
                                )
returns int
as
    begin
        declare @i int;
        declare @count int;
        declare @diff int;
        set @diff = datediff(d,@date1,@date2);
        set @i = 0;
        set @count = 0;
        while(@i < @diff)
        begin
            select @count = @count + 1
            where datename(dw,dateadd(d,@i,@date1)) not in('Saturday'
                                                          ,'Sunday'
                                                          );
            set @i = @i + 1;
        end;
        return @count;
    end;
iamdave
  • 12,023
  • 3
  • 24
  • 53
Mustafa El-Gamal
  • 31
  • 1
  • 4

1 Answers1

0

You can pass parameter values from your report into your dataset's SQL queries by referencing the parameter as a variable that you don't explicitly declare.

For example, to reference a report parameter called EndDate you can have a SQL script as follows:

declare @FilterValue nvarchar(50) = 'Group1'

select ProjectName
      ,SubmissionDate
      ,@ReportDate as ReportDate
      ,dbo.days_diff(SubmissionDate, @ReportDate) as NetworkDays
from ProjectSubmissions
where FilterValue = @FilterValue

that when used in an SSRS dataset will go and look for a report parameter called ReportDate to use in the query, which you can set to anything you require in your report design.

iamdave
  • 12,023
  • 3
  • 24
  • 53
  • well, my parameter is called enddate, so where should i mention this parameter in select you mentioned above , and do i have to change the variable you created on the top ? declare @FilterValue nvarchar(50) = 'Group1' – Mustafa El-Gamal Jan 27 '17 at 14:13
  • @MustafaEl-Gamal Please re-read my answer and if you don't understand what is being presented, do some reading on SQL and SSRS. Per my answer *"You can pass parameter values from your report into your dataset's SQL queries by referencing the parameter as a variable that you don't explicitly declare."* `@FilterValue` is explicitly declared so it won't take a value from your Report. `@ReportDate` on the other hand ***isn't*** explicitly declared... – iamdave Jan 27 '17 at 14:23
  • im sorry, still trying understand the whole thing , but thank you very much – Mustafa El-Gamal Jan 27 '17 at 14:26