SSRS: How to use a date/time parameter as an input for an MDX query (instead of a list of text values)

I guess we’ve all seen it before, VS/SSDT/BIDS automatically generates datasets for input parameters (which is fine to start with).

But for Date/Time parameters it always (by default) selects the ‘Text’-datatype (drop-down list), instead of the ‘Date/Time’-datatype (nice calendar).


What we want to achieve
So this is what we want to achieve (in red):

SSRS datetime input parameter instead of text for mdx





Start a clean slate
First step is to drop the generated date/time parameter(s) we’re not going to use and create a new one:

SSRS datetime input parameter instead of text for mdx - properties






Make sure to select the ‘Date/Time’ as the datatype, which enables the nice calender-type input. Leave the ‘Available Values’-section unchanged/empty.


Hussle the Date-value in the correct format (YYYY-MM-DD)
A difference between both datatypes is that the ‘Date/Time’-datetype will return values formatted based on your local/regional settings (e.g. YYYY-M-D or in my case D-M-YYYY).
Where I need it to be YYYY-MM-DD (including 0’s), so something like:
2015-12-01

To achieve this, we need to cast/convert a little bit and include a ‘0’ when we need it:

=IIF(LEN(CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value)))) = 2,
CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value))),
"0"+CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value)))
)



Turn it into a MDX value
The final step is to get it into the desired value for MDX:
[Dim Creation Date].[Date].&[2015-12-05T00:00:00]

Here’s the full code expression which you can use as a parameter value (expression):

="[Dim Creation Date].[Date].&["
+CSTR(DATEPART(DateInterval.Year,CDate(Parameters!DateFrom.Value)))+"-"+
IIF(LEN(CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value)))) = 2,
CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value))),
"0"+CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value)))
)+"-"+
IIF(LEN(CSTR(DATEPART(DateInterval.Day,CDate(Parameters!DateFrom.Value)))) = 2,
CSTR(DATEPART(DateInterval.Day,CDate(Parameters!DateFrom.Value))),
"0"+CSTR(DATEPART(DateInterval.Day,CDate(Parameters!DateFrom.Value)))
)
+"T00:00:00]"



Use the expression as an input value for your dataset
The final step is to use the expression:

SSRS datetime input parameter instead of text for mdx - dataset properties









Done!
That’s it! Bye bye ugly drop-down list 🙂

SSRS datetime input parameter instead of text for mdx - final result











Advertisements

SOLVED: “The size necessary to buffer the XML content exceeded the buffer quota” (SSRS)

ssrs-xml-buffer-overflow-error





OMG…I hate this one.
Most of the times when you’re using textbox references (like: ‘Reportitems!Textbox33.Value‘) and when I think you press ‘Delete’ on certain cells, VS automatically renews the textbox-numbers. So things get messed up.

ssrs-xml-buffer-overflow-error-non-existing-textbox-items





So…check if any of your report items are referencing non-existing textbox items, non-existing paramters or fields that are not in existing dataset scope.

Funny thing is that when you start deleting other report items (of which you think they work properly), VS somehow makes up his mind by giving you a decent error message:

ssrs-xml-buffer-overflow-error-which-explains











Data driven subscriptions cannot be created because the credentials used to run the report are not stored,the report is using user-defined parameters values, or if a linked report, the link is no longer valid.

Well SSRS is stil bugging you huh?

ssrs-data-driven-subscription-cannot-be-created-user-defined-parameter-values

There are a few things you may need to check:

1) Make sure that you’ve stored the credentials of the data source IN the report

2) Since the data-driven subscription is running without an actual user-context, the use of User!UserID is not allowed.

Start by adding this custom code to your report (under ‘Report Properties‘ -> ‘Code‘):

Public Function UserName()
Try
Return Report.User!UserID
Catch
Return "System"
End Try
End Function

Now you need the find: User!UserID

And replace it by: code.UserName()

in the Code-view of the report.








SSRS: Hide a Tablix is case no rows are displayed (row count after applying a filter)

Here’s the thing. In case you don’t use filters in your Tablix, you can easily run a count on the dataset. Like for example:
=IIF(CountRows("Dataset1") > 0, False, True)


But, in my case my Tablix has a couple of filters and this changes things.
In order to count the number of rows which the Tablix is actually using/displaying, you may want to use this expression for the Hide-property:
=IIF(CountRows() > 0, False, True)

ssrs-hide-tablix-when-no-records-returned








SSRS Parameter Expression: Previous Workday

Depends on whether your company works on a Saturday, but one of these SSRS Expressions will do the trick:

Basically WEEKDAY() will return the day number of the week, which will return:
– Sunday = 1
– Monday = 2
– etc.
(Doesn’t matter which Language (e.g. en-GB, etc.) you’ve set in the report)

So if you would like to select the previous workday (including Saturdays):

=IIF(WEEKDAY(TODAY())=2,
DATEADD("d",-CINT(WEEKDAY(TODAY())),TODAY()),
DATEADD("d",-1,TODAY())
)

‘In case it’s Monday, select Today’s date minus 2 days. Otherwise return yesterday’
If you would like to exclude the entire weekend:

=IIF(WEEKDAY(TODAY())=1 OR WEEKDAY(TODAY())=2,
DATEADD("d",-CINT(WEEKDAY(TODAY()))-1,TODAY()),
DATEADD("d",-1,TODAY())
)









SSRS: SQL Agent Job Status report

I’ve created a report that shows a little more information than the SQL Agent Activity Monitor and it can be scheduled via SSRS:

sql-agent-jobs-report

Download:
Click here to download the files:
SSRSSQLAgentJobsReport.zip



Here’s another report of mine which you might find interesting:
SSRS: SQL Agent Job Schedule Timeline report

sql-agent-job-schedule-timeline-report








SSRS: Easily calculate the difference between two fields/textboxes with an expression

This might seem to be a simple approach, but for some it takes a while to figure out that such tricks are even possible in SSRS.
For example to calculate the difference/variance between Actual and Budget values in a report:

=ReportItems!Textbox50.Value - ReportItems!Textbox171.Value

SSRS-difference-between-two-textboxes-fields-values-variances-expression
Simply lookup the textbox’ name in the Properties-window:

SSRS-difference-between-two-textboxes-fields-values-variances-textbox-properties-name
This approach also works for ReportItems outside of the table/matrix (across the report even).
In some cases you’ll need to combine two KPI values from different matrixes/tables in the report and for example run another formula to come up with a definite rating/value.

Instead of ‘Fields!‘ use ‘ReportItems!‘ to refer to actual items on the report canvas (this can be on the header, footer or body of the report).
In my experience you may need to use a temporary hidden textbox to store the value temporary in order to use it in the header or footer. So in case you run into some sort of constraint, try this one out first.