Occasionally I find myself needing to generate a small table with a list of dates for various queries I may be running. To do this, I usually leverage the Date dimension since I do most of my work in BI environments with a traditional data warehouse. But if you don’t have access to a Date dimension table, you can quickly generate a date table using Continue reading Generate a Date Table via Common Table Expression (CTE)
Tag Archives: Date dimension
Create Date Dimension Table Script
On a recent project I created a Date dimension script that I thought, I’d share (mostly so I would have access to it from anywhere). I based this script on one written by Mike Davis, but I included a few changes such as a first and last day of month flag, open flag, relative time period attributes, and a few others. Enjoy! Continue reading Create Date Dimension Table Script
Use Date Picker Control with MDX Based Reports
If you’ve done a couple cube based reports, you’ve run into the issue of the dates being in string format. Because the dates are strings inside your cube, you can’t use the neat little date picker control reporting services gives you for datetime parameters. While you as a developer may understand this issue, your "tech savvy" end users may not. Fret no more. Here is a quick and easy way to use the built in date picker control for parameters in your MDX reports.
1) In the Report Data pane of BIDS, open the parameter properties for the parameter(s) you are using to specify a date or begin and end dates and change the data type to datetime. Change available values to None.
2) Go ahead and delete any hidden datasets that populate the drop down lists for your date parameters. You won’t need those with the date picker control.
3) In your data set that populates your report, open the properties, and in the parameters view, you want to use an expression on the parameter value field. So instead of the parameter value containing something like "=Parameters!DateDate.Value", you want to change it to something like:
="[Date].[Date].&[" + Format((Parameters!DateDate.Value), "MM-dd-yyyy") + "]"
Keep in mind that you’ll need to perform step 3 on any data sets that use your date parameters.
The key is to format the expression in such a way that it looks exactly like the members of your date dimension. Once you do that, click preview and you have the beautiful and end-user friendly date pickers in your cube based reports!
You can also go back to your parameters to specify default values using today and/or dateadd (ex. "=dateadd("M",-12,today)").
One of the downsides to using the date picker control with reports using your cube is a data source is that if the end user selects a date in the date picker that is not in your cube, your report is going to blow up, so just be aware of that.
If you have any questions, feel free to leave a comment!