1

So what I need is to create a fake/imitation table that is embedded in code. It will have a list of dates in a VALUE/LABEL style set up. With dynamic dates on the Value and the description (Today's Date, Yesterdays Date, Last Monday, First of Month, etc) as Label.

This is how I want it to appear

Value      Label
5/25/2017  Today
5/24/2017  Yesterday
5/15/2017  Prev Monday
5/01/2017  First of Month

This is how it currently appears (which I don't want)

Today   Yesterday   2_Days_ago
2017-05-25 00:00:00.000 2017-05-24 00:00:00.000 2017-05-23 00:00:00.000

The purpose is to create a dynamic set of dates to choose from in Subscription reporting in SSRS. I will use this mock table as a stored dataset on the reporting server to link to a set of To dates and From dates. IE Date From [Yesterday] to Date To [Today]. etc.

I found this reference site https://www.mssqltips.com/sqlservertip/3421/add-a-date-range-dataset-in-sql-server-reporting-services/ Which is a great start, but I want to take it one step further by making this a table like dataset. Many reports require subscription dates that vary. IE certain people want it daily with the Date Range being [Yesterday] to [Today]. Some people want it Weekly, [Prev Monday] to [Prev Saturday] and others want it once a Month [Prev BOM] to [Prev EOM] Etc.
Having a Date dataset that will dynamically change for subscription reporting will help a lot in not creating multiple versions of the report with difference default dates.

This is as far as I got. I have no idea how to get the Labels to appear next to the dates in the correct order.

SELECT convert(datetime,convert(varchar(8),getdate(),112)) as [Today] UNION
SELECT convert(datetime,convert(varchar(8),DATEADD(d,-1,GETDATE()),112)) as [Yesterday] UNION
SELECT convert(datetime,convert(varchar(8),DATEADD(d,-2,GETDATE()),112)) AS [2_Days_ago] UNION
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) AS [ThisWeek_Mon] UNION
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),4) AS [ThisWeek_Fri] UNION
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),5) AS [ThisWeek_Sat] UNION
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) AS [ThisWeek_Sun] UNION
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) AS [PreviousWeek_Mon] UNION
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),4) AS [PreviousWeek_Fri] UNION
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) AS [PreviousWeek_Sat] UNION
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) AS [PreviousWeek_Sun] UNION
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) AS [BOM FirstDayOfThisMonth] UNION
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) AS [EOM_LastDayOfThisMonth] UNION
SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) AS [Prev_BOM_FirstDayOfLastMonth] UNION
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) AS [Prev_EOM_LastDayOfLastMonth] UNION
SELECT DATEADD(q,DATEDIFF(q,0,GETDATE())-1,0) AS [BoPQ FirstDayOfLastFullQuarter] UNION
SELECT DATEADD(D, 0, DATEDIFF(D, 0, DATEADD(s,-1,DATEADD(q,1,DATEADD(q,DATEDIFF(q,0,GETDATE())-1,0))))) AS [EoPQ_LastDayOfLastFullQuarter] UNION
SELECT DATEADD(q,DATEDIFF(q,0,GETDATE()),0) AS [BoCQ_FirstDayOfThisQuarter] UNION
SELECT DATEADD(D, 0, DATEDIFF(D, 0, DATEADD(s,-1,DATEADD(q,2,DATEADD(q,DATEDIFF(q,0,GETDATE())-1,0))))) AS [BoCQ_LastDayOfThisQuarter] UNION
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) AS [BoY_FirstDayOfThisYear] UNION
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))) AS [EoY_LastDayOfThisYear] UNION
SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) AS [Prev_BoY_FirstDayOfLastYear] UNION
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))) AS [Prev_EoY_LastDayOfLastYear]`
dplres81715
  • 11
  • 2
  • Have a look at this as an alternative to what you are attempting: [**Answer**](https://stackoverflow.com/questions/43978337/subscribing-to-a-report-with-different-default-parameters/43979295#43979295) – R. Richards May 25 '17 at 16:57
  • It's a good alternative just from what I can see. I'd like to see if there is a better way though. – dplres81715 May 25 '17 at 17:32
  • ok, so I was able to figure it out with some ingenious help. SELECT DateTable.Value ,DateTable.Label FROM ( SELECT convert(datetime,convert(varchar(8),getdate(),112)) AS [Value], 'Today' AS [Label], 10 AS [SortOrder] UNION SELECT convert(datetime,convert(varchar(8),DATEADD(d,-1,GETDATE()),112)) AS [Value], 'Yesterday' AS [Label], 20 AS [SortOrder] UNION SELECT convert(datetime,convert(varchar(8),DATEADD(d,-2,GETDATE()),112)) AS [Value], '2 Days ago' AS [Label], 30 AS [SortOrder] ) AS [DateTable] ORDER BY [SortOrder] – dplres81715 May 25 '17 at 18:30

2 Answers2

0

Thanks for the code that you provided.

Based on that I just made some modifications to create this dataset. Hope this is useful for you.

SELECT convert(datetime,convert(varchar(8),getdate(),112)) as Date, 'Today' 

as Label, 0 AS Sort UNION
SELECT convert(datetime,convert(varchar(8),DATEADD(d,-1,GETDATE()),112)) , 'Yesterday', 1 UNION
SELECT convert(datetime,convert(varchar(8),DATEADD(d,-2,GETDATE()),112)) , '2_Days_ago', 2 UNION
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0), 'ThisWeek_Mon', 3 UNION
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),4) , 'ThisWeek_Fri', 4 UNION
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),5) , 'ThisWeek_Sat', 5 UNION
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) , 'ThisWeek_Sun', 6 UNION
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) , 'PreviousWeek_Mon', 7 UNION
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),4) , 'PreviousWeek_Fri', 8 UNION
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) , 'PreviousWeek_Sat', 9 UNION
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) , 'PreviousWeek_Sun', 10 UNION
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) , 'BOM FirstDayOfThisMonth', 11 UNION
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))), 'EOM_LastDayOfThisMonth', 12 UNION
SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)), 'Prev_BOM_FirstDayOfLastMonth', 13 UNION
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))), 'Prev_EOM_LastDayOfLastMonth', 14 UNION
SELECT DATEADD(q,DATEDIFF(q,0,GETDATE())-1,0), 'BoPQ FirstDayOfLastFullQuarter', 15 UNION
SELECT DATEADD(D, 0, DATEDIFF(D, 0, DATEADD(s,-1,DATEADD(q,1,DATEADD(q,DATEDIFF(q,0,GETDATE())-1,0))))), 'EoPQ_LastDayOfLastFullQuarter' , 16 UNION
SELECT DATEADD(q,DATEDIFF(q,0,GETDATE()),0) , 'BoCQ_FirstDayOfThisQuarter' , 17 UNION
SELECT DATEADD(D, 0, DATEDIFF(D, 0, DATEADD(s,-1,DATEADD(q,2,DATEADD(q,DATEDIFF(q,0,GETDATE())-1,0))))), 'BoCQ_LastDayOfThisQuarter', 18 UNION
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) , 'BoY_FirstDayOfThisYear', 19 UNION
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))) , 'EoY_LastDayOfThisYear', 20 UNION
SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)), 'Prev_BoY_FirstDayOfLastYear', 22 UNION
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))), 'Prev_EoY_LastDayOfLastYear', 23

ORDER BY Sort

Let me know if you need any further clarification/information.

MycrowSoft
  • 153
  • 2
  • 17
0

EDIT! FYI, the below fix isn't going to work for my needs after all. SSRS stores the "subscription email dates" like [Yesterday] not as a LABEL but as a VALUE. So Yesterday isn't the dynamic date like I thought but a set value like 5/30/2017. In theory, it would work great but this is definitely not the fix I was looking for. Back to the drawing board.

I'm keeping the code as a Trial and Error log of my failures. hah

Ok, I got some advice on other forum post that led me combine the unions into 3 columns. This solved the problem for my purposes. I placed the following code in a Shared Dataset.

So there's Value, Label and SortOrder 

SELECT
    DateTable.Value
    ,DateTable.Label
FROM
(
SELECT convert(datetime,convert(varchar(8),getdate(),112)) AS [Value], 'Today' AS [Label], 10 AS [SortOrder] UNION
SELECT convert(datetime,convert(varchar(8),DATEADD(d,-1,GETDATE()),112)) AS [Value], 'Yesterday' AS [Label], 20 AS [SortOrder] UNION
SELECT convert(datetime,convert(varchar(8),DATEADD(d,-2,GETDATE()),112)) AS [Value], '2 Days ago' AS [Label], 30 AS [SortOrder] UNION
SELECT convert(datetime,convert(varchar(8),DATEADD(d,-3,GETDATE()),112)) AS [Value], '3 Days ago' AS [Label], 40 AS [SortOrder] UNION
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) AS [Value], 'ThisWeek Mon' AS [Label], 50 AS [SortOrder] UNION
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),1) AS [Value], 'ThisWeek Tue' AS [Label], 51 AS [SortOrder] UNION
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),2) AS [Value], 'ThisWeek Wed' AS [Label], 52 AS [SortOrder] UNION
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),3) AS [Value], 'ThisWeek Thur' AS [Label], 53 AS [SortOrder] UNION
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),4) AS [Value], 'ThisWeek Fri' AS [Label], 54 AS [SortOrder] UNION
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),5) AS [Value], 'ThisWeek Sat' AS [Label], 55 AS [SortOrder] UNION
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) AS [Value], 'ThisWeek Sun' AS [Label], 56 AS [SortOrder] UNION
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) AS [Value], 'PreviousWeek Mon' AS [Label], 60 AS [SortOrder] UNION
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),1) AS [Value], 'PreviousWeek Tue' AS [Label], 61 AS [SortOrder] UNION
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),2) AS [Value], 'PreviousWeek Wed' AS [Label], 62 AS [SortOrder] UNION
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),3) AS [Value], 'PreviousWeek Thur' AS [Label], 63 AS [SortOrder] UNION
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),4) AS [Value], 'PreviousWeek Fri' AS [Label], 64 AS [SortOrder] UNION
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),5) AS [Value], 'PreviousWeek Sat' AS [Label], 65 AS [SortOrder] UNION
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) AS [Value], 'PreviousWeek Sun' AS [Label], 66 AS [SortOrder] UNION
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) AS [Value], 'Month First Day Of This Month' AS [Label], 70 AS [SortOrder] UNION
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) AS [Value], 'Month Last Day Of This Month' AS [Label], 80 AS [SortOrder] UNION
SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) AS [Value], 'Month-Prev First Day Of Last Month' AS [Label], 90 AS [SortOrder] UNION
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) AS [Value], 'Month-Prev Last Day Of Last Month' AS [Label], 100 AS [SortOrder] UNION
SELECT DATEADD(q,DATEDIFF(q,0,GETDATE()),0) AS [Value], 'Quarter FirstDayOfThisQuarter' AS [Label], 110 AS [SortOrder] UNION
SELECT DATEADD(D, 0, DATEDIFF(D, 0, DATEADD(s,-1,DATEADD(q,2,DATEADD(q,DATEDIFF(q,0,GETDATE())-1,0))))) AS [Value], 'Quarter LastDayOfThisQuarter' AS [Label], 120 AS [SortOrder] UNION
SELECT DATEADD(q,DATEDIFF(q,0,GETDATE())-1,0) AS [Value], 'Quarter-Prev FirstDayOfLastFullQuarter' AS [Label], 130 AS [SortOrder] UNION
SELECT DATEADD(D, 0, DATEDIFF(D, 0, DATEADD(s,-1,DATEADD(q,1,DATEADD(q,DATEDIFF(q,0,GETDATE())-1,0))))) AS [Value], 'Quarter-Prev LastDayOfLastFullQuarter' AS [Label], 140 AS [SortOrder] UNION
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) AS [Value], 'Year FirstDayOfThisYear' AS [Label], 150 AS [SortOrder] UNION
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))) AS [Value], 'Year LastDayOfThisYear' AS [Label], 160 AS [SortOrder] UNION
SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) AS [Value], 'Year-Prev FirstDayOfLastYear' AS [Label], 170 AS [SortOrder] UNION
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))) AS [Value], 'Year-Prev LastDayOfLastYear' AS [Label], 180 AS [SortOrder]
) AS [DateTable]
ORDER BY
[SortOrder]`

I left the SortOrder value in 10 digit increments so that I can insert additional date parameters at later times. I ran my first subscription report with it and it worked like a charm. 1 report can now have multiple date parameter subscriptions.

Wodin
  • 3,243
  • 1
  • 26
  • 55
dplres81715
  • 11
  • 2