Solved

SSRS Dynamic Date Parameter

Posted on 2016-09-12
25
150 Views
Last Modified: 2016-09-14
Hello,

I am developing a SSRS Report in which i have date parameters based on different selections. It has the date range like
1) Last week
2)Last month
3) YTD
4) Last quarter
5)Custom date
 So when user selects the last week, last month, last year my parameter works fine because i have given the pre-determined functions for date in my SQL code. And for custom date i have given null values. Please see the attached for code.  But when the user selects custom date  and picks date in start date and end date text boxes don't work and they throw out a error saying select a parameter, even after selecting the date range.  Because i have given null in the sql code for custom date. In run time when i am passing the date string it is unable to understand.
For this reason i decided to create two extra text boxes for custom date and use them. When the user select the custom date  the custom start date and end date should pop up dynamically and give the user access to select the date range from the calendar picker.
But even now the custom date text boxes are unable to understand the date passed as it had null in the back end for period selected.

Can anyone suggest any solution for this issue. And i am also looking to show the custom start date and custom end date text boxes only when user selects the custom date under Period drop down.  Is it possible??

Please see the attachments for my sql code, Period screen shot, date text boxes, and error message.

Any help is appreciated. Thank you..
Date-text-boxes.png
Error.png
Report-Period.png
Date-Paremeters_Scriptv2.sql
0
Comment
Question by:Vira
  • 9
  • 8
  • 7
  • +1
25 Comments
 

Expert Comment

by:David Naulls
ID: 41795072
Hi Raj,

The error message points to the "startdate" parameter being null, which it is in your picture.  Although you've filled in the "Custom" dates the problem is with your original parameter.  Open the properties for your startdate and enddate parameters and in the general tab you can set the Allow null value checkbox as True.  This should get you passed this problem.
0
 

Author Comment

by:Vira
ID: 41795083
Hi David,

When i am setting up the null value and selecting the date its running and giving me the Customstartdate and enddate as blank without any result. Basically the dates are getting disappeared when i hit preview.
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41795301
What are the data types of the date parameters ?
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41795303
Also have you given any default value for start and end date.

I suggest you should e.g. IF you want to show data for last 3 months then

Start date = todaydate - 3 months

End date = today date

Enjoy!!
0
 

Author Comment

by:Vira
ID: 41795319
Hello Pawan,

Data type for the Startdate and Enddate is the Date/time.. Yes, for all the periods i have given the default date functions..But for the custom date i cannot give any default date because i need it to be selected based on the end user selection.   I
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41795327
Start date and end date are null from the SQL Query for Custom Date Period ? Is this expected ?
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41795333
Try and put some value there  and then check whether error is coming or not. If not then we will know that error is there..
0
 

Author Comment

by:Vira
ID: 41795347
Yes, are right the start date and end date corresponds to null values in the sql code. I cant think of anything for the custom date. If i put 1/1/1900 to till date the start date is getting hard coded with 1/1/1900.
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41795361
Okies, Why the values are not getting set for Start and End Date for Custom Period? Can you check that code?
0
 

Author Comment

by:Vira
ID: 41795381
Because in my code i am saying it has null for start and end date as we don't have any functions for free flowing date range.. And because i am doing union all for all the statements my column values should be same across. So when there is a null in the determined and i am passing a date string from the front end. Its getting confused and its not understanding the date passed as it has null values in the backend.

So is there any solution you can think about for this issue??
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41795388
Yes now i got. When you pass null in date it is giving blank. When you are setting value in the parameters check for Null with ISNULL.... and then set the value.
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41795390
Try this =IIF(Fields!DatumBSE.Value is nothing, nothing, FormatDateTime(Fields!DatumBSE.Value, 2))
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 13

Expert Comment

by:Megan Brooks
ID: 41795426
It seems to me that you can do this with three parameters, a Period parameter that obtains available period names and IDs from a dataset query, a Custom Start parameter, and a Custom End parameter. The latter two should NOT allow NULL values. For each one you can create a default value dataset that calculates a start or end date (depending on which dataset & parameter pair it is) that depends on the period ID selected by the Period parameter (using a dataset parameter which is the Period report parameter). If a custom period is selected then the calculated default dates will both be NULL.

By connecting the datasets of the two date parameters to the value of the Period parameter, you create a parameter dependency that is observed by the report. When the report runs initially, the date parameters are disabled and you must select a period. Once you do that, the two date boxes populate with the start and end dates for the period selected. If you select Custom then the date boxes default to NULL and you must give them valid date values before you can run the report.

I can provide you with a sample RDL file that will be worth a thousand words, but I am not sure if I will have it ready tonight.
0
 
LVL 13

Expert Comment

by:Megan Brooks
ID: 41795436
Here is the RDL. I'm assuming SSRS 2008 or above; I don't know if I can generate 2005 (I am using SSRS 2016).

One hitch I ran into is that the custom date parameters have to allow NULL values in order for NULLs to be passed when a custom period is selected by the Period parameter. This is pretty clunky to work with, but the built-in parameters have a lot of usability issues. Ideally, you should write your own UI for accepting the parameters, and suppress the parameter pane of the report viewer.

There might be a workaround for that, but I am not going to be able to get to it tonight. If you eliminate the "Custom" period option altogether, once you select any other period you can customize it in the start/end date boxes.

My datasets use your CTE, unmodified, but only query it for the value(s) needed by each parameter dataset. It would be cleaner to include just the code that is actually required within each dataset query.
DynamicPeriod.rdl
0
 
LVL 13

Expert Comment

by:Megan Brooks
ID: 41796539
OK, here are some of the thousand words spelled out, although I am going to change some of the code from what was in the first RDL, above, and post a new one at the end. I may do this in several installments.

First of all, parameters can take their available value lists and defaults from static data or from a query, or not, but what we are interested in here is a Period parameter that takes its available value list from static data and two date parameters (start and end) that take their default values from queries. The Period parameter is the simplest:

  <ReportParameter Name="Period">
      <DataType>Integer</DataType>
      <Prompt>Period</Prompt>
      <ValidValues>
        <ParameterValues>
          <ParameterValue>
            <Value>1</Value>
            <Label>Prior Week</Label>
          </ParameterValue>
          <ParameterValue>
            <Value>2</Value>
            <Label>Prior Month</Label>
          </ParameterValue>
          <ParameterValue>
            <Value>3</Value>
            <Label>Prior Quarter</Label>
          </ParameterValue>
          <ParameterValue>
            <Value>4</Value>
            <Label>Prior Year</Label>
          </ParameterValue>
          <ParameterValue>
            <Value>5</Value>
            <Label>Year To Date Prior Month</Label>
          </ParameterValue>
          <ParameterValue>
            <Value>6</Value>
            <Label>Month To Date</Label>
          </ParameterValue>
          <ParameterValue>
            <Value>7</Value>
            <Label>Quarter to Date</Label>
          </ParameterValue>
          <ParameterValue>
            <Value>8</Value>
            <Label>Year To Date</Label>
          </ParameterValue>
          <ParameterValue>
            <Value>9</Value>
            <Label>Yesterday</Label>
          </ParameterValue>
          <ParameterValue>
            <Value>0</Value>
            <Label>Custom Date</Label>
          </ParameterValue>
        </ParameterValues>
      </ValidValues>
    </ReportParameter>
  

Open in new window


This looks quite different from what you see for a parameter definition in Report Designer, and yet it is strangely the same. I have simply copied the XML out of the RDL instead of trying to do multiple screen shots to show the the information. Everything is there; just in a different format. You can copy from this into the designer, or you can go to the Solution Explorer, right click the RDL file, and select "View Code." Once you are in the code window you can copy the above XML sample and paste it on top of the existing Period parameter, being VERY careful to replace everything from the first <ReportParameter> element through its matching </ReportParameter>, changing nothing else (especially not the remaining <ReportParameter> elements that follow).

Note: the code (XML) editor is not a very user-friendly environment. If you introduce a significant error while using it, your report is likely to fail to load. It does validate and report errors, and you can undo as long as the window is still open and you haven't made too many other changes. Still, frequent backups might be a good idea. If you are using source control (doesn't everyone?), you can stash or shelve your changes.

(There is a bug in Report Designer that causes parts of the designer to quit working after you make changes in the code window. The workaround is to close the designer and reopen it. You don't have to close the code window. I don't know which versions have this bug, but I have been dealing with it for at least 2 years, in VS 2012 and 2013. I haven't determined if it is still in VS 2015.)

For comparison, I have attached a screen shot of the Available Values (<ValidValues>) pane of the parameter properties.

Note that I changed the ID for "Custom Dates" from 10 to zero. The available values list lets you order the items independently of ID, and it made sense to me to make the "special" value zero and leave room at the end for other predefined ranges.

Continued...
ValidValuesPane.png
0
 
LVL 13

Expert Comment

by:Megan Brooks
ID: 41796653
Once you have defined the Period parameter, you can create a Dataset that generates the start and end dates according to the period the user has selected. I changed the original query somewhat for my example:

WITH DateRange_CTE AS 
(
	SELECT        CAST(1 AS int) AS PeriodID, CONVERT(DATETIME, DATEADD(wk, DATEDIFF(wk, 0, 
                                GETDATE()), - 7)) AS Startdate, CONVERT(DATETIME, DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), - 1)) AS Enddate
        UNION ALL
        SELECT        CAST(2 AS int) AS PeriodID, CONVERT(DATETIME, DATEADD(mm, - 1, DATEADD(mm, 
                                DATEDIFF(mm, 0, GETDATE()), 0))) AS Startdate, CONVERT(DATETIME, CONVERT(DATE, DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(mm, 0, 
                                GETDATE()), 0)))) AS Enddate
        UNION ALL
        SELECT        CAST(3 AS int) AS PeriodID, DATEADD(qq, DATEDIFF(q, 2, GETDATE()) - 1, 0) AS Startdate, 
                                DATEADD(qq, DATEDIFF(q, 2, GETDATE()), 0) - 1 AS Enddate
        UNION ALL
        SELECT        CAST(4 AS int) AS PeriodID, CONVERT(DATETIME, '1/1/' + CAST(YEAR(GETDATE()) 
                                - 1 AS VARCHAR(4))) AS Startdate, CONVERT(DATETIME, '1/1/' + CAST(YEAR(GETDATE()) AS VARCHAR(4))) - 1 AS Enddate
        UNION ALL
        SELECT        CAST(5 AS int) AS PeriodID, CONVERT(DATETIME, 
                                '1/1/' + CAST(YEAR(GETDATE()) AS VARCHAR(4))) AS Startdate, CONVERT(DATETIME, CONVERT(DATE, DATEADD(ms, - 3, DATEADD(mm, 
                                DATEDIFF(mm, 0, GETDATE()), 0)))) AS Enddate
        UNION ALL
        SELECT        CAST(6 AS int) AS PeriodID, CONVERT(DATETIME, DATEADD(mm, DATEDIFF(mm, 0, 
                                GETDATE()), 0)) AS Startdate, CONVERT(DATETIME, CONVERT(DATE, GETDATE())) AS Enddate
        UNION ALL
        SELECT        CAST(7 AS int) AS PeriodID, DATEADD(qq, DATEDIFF(q, 0, GETDATE()), 0) AS Startdate, 
                                CONVERT(DATETIME, CONVERT(DATE, GETDATE())) AS Enddate
        UNION ALL
        SELECT        CAST(8 AS int) AS PeriodID, CONVERT(DATETIME, '1/1/' + CAST(YEAR(GETDATE()) 
                                AS VARCHAR(4))) AS Startdate, CONVERT(DATETIME, CONVERT(DATE, GETDATE())) AS Enddate
        UNION ALL
        SELECT        CAST(9 AS int) AS PeriodID, CONVERT(DATETIME, CONVERT(DATE, GETDATE())) 
                                - 1 AS Startdate, CONVERT(DATETIME, CONVERT(DATE, GETDATE())) - 1 AS Enddate
        UNION ALL
        SELECT        CAST(10 AS int) AS PeriodID, NULL AS Startdate, NULL AS Enddate
)
    SELECT        CAST([StartDate] AS DATE) AS StartDate, CAST([EndDate] AS DATE) AS EndDate
     FROM            DateRange_CTE AS DateRange_CTE_1
  WHERE (PeriodID = @Period)

Open in new window


Note this is a different dataset from the one used to populate the report data region with data. This dataset exists only to supply the start and end date parameters with default dates, and to link the date parameters to the period parameter so that the period parameter must be set before dates can be entered or changed.

Also note that the query ends with the clause "WHERE (PeriodID = @Period)". This causes the output to be filtered according to the current setting of the @Period parameter. The symbol @Period, however, does not represent the Period report parameter. Instead, it represents a dataset parameter that is local to the dataset containing the query. The local parameter, in turn, references the report parameter of the same name. Confused? Well, it is confusing but it is also very flexible. The dataset parameter can be defined with an expression, and it need not be named the same as a report parameter.

If you create a new dataset, set the data source (it isn't used by the query but it still must be defined), set the dataset name (I called it DateRangeDS), paste the above query into the query text box, click OK, and save the project. Report Designer should create the @Period dataset parameter and automatically link it to the @Period report parameter. In fact, if the @Period report parameter doesn't exist, it will add it too. This can cause trouble in some situations, since you may end up with report parameters you didn't mean to create, so watch out. In this situation, however, it is OK as long as the @Period report parameter has already been created as described earlier. Otherwise you would have to go back and modify it to do the right things, including presenting the available values list.

I have attached screen shots of the Query and Parameters panes of the completed DateRangeDS  dataset properties dialog.
DateRangeDSQueryPane.png
DateRangeDSParametersPane.png
0
 
LVL 13

Expert Comment

by:Megan Brooks
ID: 41796893
Well, I ran into problems and had to modify the solution somewhat. I noticed the issue after further testing, and it makes perfect sense. We can set the default values for Start and End from a query (dataset), but we can't set their current values, and the current values "lock in" once they are set from the defaults. This is actually a good thing, however, because it means that there will always be values in the two date boxes when a pre-calculated period is selected. They might not be the same dates as those for the period start and end - they won't be if the period is changed to a different one - but they will at least be valid dates that will not prevent the report from executing. It's a compromise, but probably one of the better ones available.

The solution, then, is to use DateRangeDS to supply default values to the Start and End parameters, but to calculate the final values rather than taking them directly from the Start and End parameters. They will come directly from the Start and End parameters only if the value of Period is zero, and from DateRangeDS otherwise. The calculation for the start date, for example, looks like
IIf(Parameters!Period.Value = 0, Parameters!Start.Value, First(Fields!StartDate.Value, "DateRangeDS"))
But I am getting ahead of myself.

Continued...
0
 

Author Comment

by:Vira
ID: 41796929
Hello Megan,

I have created the pre-determined values as you suggested in the afternoon. I am moving forward with your recent suggestions. I will keep you posted once i am done with it.

Thanks for your time and help.
0
 
LVL 13

Expert Comment

by:Megan Brooks
ID: 41797186
Oops, I stalled again, but there is a solution. I have used a similar trick in production systems and it has been working for years without a hitch, but it is a little more complex to implement than I would prefer.

We need to be able to select either the calculated date range for the selected period or, if the period is "Custom Date," the custom date range specified by the Start and End parameters. That range must then be made available to the main report dataset query as a pair of parameters. That's where the other problem was. The values from DateRangeDS cannot be passed directly to another dataset as parameters. They must be buffered through a pair of internal parameters in order to satisfy all of SSRS's internal quirks.

Microsoft doesn't say much about internal parameters. They are not available to change in the UI, or through URL parameters, or even the web service as I recall. They are used in weird situations like this to cause a dataset to execute and return a value. Interestingly, the default value of an internal parameter does not seem to "lock in" the way it does with a normal parameter. In this case that means that when the Period parameter changes and DateRangeDS updates to new values, the internal parameter values update too. That's exactly what we need.

First, however, DateRangeDS needs to be extended with two calculated fields that select between the calculated date ranges and the custom date range. See the attached screen shot to see how this is done. The two selection expressions mentioned earlier are, in modified form, incorporated right into the dataset so that the result of the calculation can be presented to the parameter default. These expressions are

=IIf(Parameters!Period.Value = 0, Parameters!Start.Value, Fields!StartDate.Value)
=IIf(Parameters!Period.Value = 0, Parameters!End.Value, Fields!EndDate.Value)
The screen shot shows one of them being entered on the Fields pane. I have called the fields RangeStart and RangeEnd to distinguish them as the actual, final range, not the calculated range or the custom range.

Continued...
DateRangeDSCalcFields.png
0
 
LVL 13

Accepted Solution

by:
Megan Brooks earned 500 total points
ID: 41797212
Now create the Start and End parameters and configure them to take their default values from DateRangeDS. This implicitly links the Start and End parameters to the Period parameter, because DateRangeDS depends on Period, and Start and End depend on DateRangeDS.

Create the Start and End report parameters using the DateTime data type, and not allowing NULL, blank, or multiple values. I said this didn't work last night, but I was wrong. On the Default Values pane of each parameter properties dialog, select "Get values from a query", select DateRangeDS as the dataset, and select the appropriate field, StartDate or EndDate, according to which parameter it is. I have attached screen shots for the Start parameter properties.

Next create the RangeStart and RangeEnd internal parameters. These two parameters are nearly identical to Start and End, but you must mark them as "Internal" on the General pane, and the "Get values from a query" options, while they still use the DateRangeDS dataset, will specify RangeStart or RangeEnd for the field, instead of StartDate or EndDate.

For my example, I created a simple report body consisting of two text boxes that show the StartDate and EndDate values, so that you can see the effects of selecting different Period values and entering different Start and End values. I also created a dummy dataset called ReportDS that accepts two parameters, RangeStart and RangeEnd, and passes them through to the output, which otherwise consists of rows numbered 1, 2, 3, and 4. RangeStart and RangeEnd repeat on each row. It's trivial, but it serves to demonstrate that the parameters work. A table data region below the two text boxes displays the results from ReportDS.

The StartDate and EndDate text boxes display the values of the RangeStart and RangeEnd internal parameters.

When you first run the report you must select a value for Period. If you select Custom Date initially, the Start and End dates will not be filled in and you can enter them. If you change to any other Period, Start and End will populate with the dates corresponding to that period, as long as they are still empty. Once filled in, they will not change unless you change them or restart the report. The custom dates will only be used, however, if "Custom Date" is selected for the period.

Rather than supply all the details for building my example report, I have attached the updated RDL file (SSRS 2008). You should be able to add it to your SSRS report project and run it after a tweak or two. You will need to download it and copy it to your report project directory, and then add it to the project using the "Add existing" option. After that you should be able to open it in the designer. Change the Data Source connection string to point to your own database. The example does not actually use this database, but SSRS requires that you point to a valid one anyway. It can be empty. Both queries used in the example contain only static data.

If this were a real report, the RangeStart and RangeEnd parameters for the ReportDS dataset would determine the date range of the actual report data query.

That's it! Or, as my former boss would say, "That was easy."
DynamicPeriod.rdl
0
 

Author Comment

by:Vira
ID: 41797993
Hi Megan,

Thanks for your solution, it looks awesome. But i just want to ask, when i initially select the prior week it would populate corresponding dates in the text boxes and then i change the selection to custom date then the dates in the date boxes should get refreshed to date selected..But  its not happening the dates are being static as per previous selection only. And this issue is not only with custom date, even if you select different pre-determined date range its not getting changed.

If this can be addressed its really helpful.. Reason to bring this issue is, it may create a confusion to end user if the dates don't get changed as per custom selection.
0
 
LVL 13

Expert Comment

by:Megan Brooks
ID: 41798167
I used the SSRS ReportViewer UI on and off over a span of eight years, and never was it fully satisfactory for the users of the various tools I developed. It can do basic things, but it is inflexible and there are many things that it does not do well. Some features are just plain broken. It's clunky and slow. The "View Report" button is positioned so that it disappears off the screen if the report is wide. The pagination controls are only at the top of the page, forcing the user to navigate back to the top before moving to the next page. And on it goes.

I can't make the built-in UI controls do exactly what you are looking for. What I came up with is a functional equivalent that lets you select pre-calculated periods or enter custom date ranges.

There is no API for changing the value of a parameter directly; all you can do is set a default value. I once went in with a debugger and located where the actual parameter values were stored, and it is in private storage not accessible to developer code. I also did not find any undocumented API method that would allow the values to be changed. The controls do what they do and you and the users must adjust to that. The Report Viewer UI controls always seem to involve usability compromises.

Over the last several years I worked for a company that has its own reporting UI written in C# that feeds the selected values directly to the renderer, using the report execution web service end point. That is the way to go about it. That approach, however, requires a substantial investment in web development.
0
 

Author Comment

by:Vira
ID: 41798370
Thanks for your solution Megan. It really helps.

I appreciate your time and work...
0
 

Author Closing Comment

by:Vira
ID: 41798373
Your knowledge and experience  is enormous...Thanks
0
 
LVL 13

Expert Comment

by:Megan Brooks
ID: 41798494
Your are welcome!

This has been interesting for me. First of all, I realized early on that I hadn't used some of these features for a number of years and I couldn't quite remember how they worked. It was a good refresh. Thanks.

Second, I came to appreciate how much work can be involved in explaining something relatively simple but non-trivial about SSRS. Most people work with the Report Designer UI, and I assume that that is what is familiar to them. I do almost all my layout work in it myself. When it comes to working with the detailed properties of each component, however, I frequently switch over to the XML editor because the Designer is just too slow and awkward, and there are any number of things it can't do (try changing the order of datasets just using the Designer). Sometimes data type attributes are auto-assigned for you and the results are only visible in the XML. When you try to do a complex cut/paste, or a copy/paste from another report, sometimes the designer just says "NO" and it has to be done in XML. Then there are the "auto-corrupt" bugs that cause the designer to quit working altogether...

But I digress. The great thing about XML in this situation is that it is just text, and can be pasted into a forum post. Creating and posting screen shots is not so great. Posting RDL files and leaving it to the reader to download and go exploring is a compromise at best.

I'm thinking now that videos might be a better way to present Designer features. I don't have much practice at creating editing them, and I'm not wild about viewing them either -- most of the time I would rather just read about it -- but when it comes to graphic interfaces with their right clicks and property sheets and all those visual things that take so many words to describe, videos start to make more sense even to me. It's something I will look into for future posts here.

That said, I'll be glad to continue to clarify anything I've posted above. SSRS is a flexible and powerful tool (the Report Viewer UI parameters, maybe not so much), but it has a long learning curve. I have been using it for around 12 years, since it was an add-on for SQL Server 2000, and there are still a bunch of properties and a number of features that I have yet to learn.
1

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Steps to solve SSRS SQL 2008 R2 User Access Control (UAC) Permission Error With the introduction of SQL Server 2008 R2 and Vista (Windows 7 as well) came new enhanced security features. One of the features included was User Access Control (UAC) t…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now