CODEWITHSUNDEEP
sql-server
Robert E. McIntosh
Robert E. McIntosh

Reputation: 6135

Using Parameters to generate date range

So I have multiple reports I am trying to merge into one report. The big issue is that one report is run every two weeks and the other is run once a month.

The date range for the report was created using this sql

SELECT
    CASE 
        WHEN DAY(GETDATE()) <= 15 THEN
            DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) + 15
        ELSE
            DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) , 0)
    END AS LO_DATE

So I tried adding a parameter that I could use that would basically say, hey if this is checked, then always run it for the whole month not the last two weeks. That sql looks like this.

IF (@RUN_FOR_MONTH = 'true')
    SELECT
        CASE 
            WHEN DAY(GETDATE()) <= 15 THEN
                DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) + 15
            ELSE
                DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) , 0)
        END AS LO_DATE
ELSE
    SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) , 0) AS LO_DATE

However I keep getting this error:

The report parameter ‘LO_DATE’ has a DefaultValue or a ValidValue that depends on the report parameter “RUN_FOR_MONTH”. Forward dependencies are not valid.

I am new to using SQL Server Report Builder, so if you need more information, please ask and I'll provide it.

REQUESTED CHANGE - I still get the same error

SELECT
    CASE
        WHEN @RUN_FOR_MONTH = 'true' THEN
            DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) , 0)
        WHEN DAY(GETDATE()) <= 15 THEN
            DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) + 15
        ELSE
            DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) , 0)
    END AS LO_DATE

Upvotes: 0

Views: 76

Answers (1)

Thom A
Thom A

Reputation: 95554

Parameters in SSRS are derived sequentially. If you have a parameter that is based off another's value, you must list those parameters in in the correct sequential order. Take the below

enter image description here

In this example, @DateFrom would be evaluated first, and then @DateTo; thus @DateFrom cannot depend on @DateTo (however the reverse is fine).

If, for example, the value of @DateFrom was DateAdd("d", -2, @DateTo) you would receive the error you have above. You would need to select @DateTo and click the Up Arrow icon (Or select @DateFrom and the down arrow)

Upvotes: 1

Related Questions