May 14, 2007 at 10:16 am
I am new to SSAS and SSRS. I am trying to compare projects with estimated end date vs. actual end date. To do this I am creating a matrix report with projects as rows and quarters and years as columns with X's in the table where the estimated end date and actual end date are.
How do I get the columns to be generated for a specific date range? I want the user to have two parameters on the report: FromDate and ToDate (ex. 1/1/2007 - 12/31/2007). This should display 1/2007, 2/2007, 3/2007, 4/2007 as the columns. How would I drive the columns off of those two parameters?
May 15, 2007 at 12:30 am
I'm interested in how to do this as well. I'll try and help out, but I don't know a lot about this.
There's perhaps four parts to this:
1. Getting the parameter
2. Passing the parameter to the MDX query
3. Fishing out the dates you need
4. Presenting the info on the report
For parts 1 and 2, see this article: http://www.databasejournal.com/features/mssql/article.php/3644661
For part 3, you might use a named set. I don't know how to write the logic for this exactly, but you might use something along the lines of:
"WITH SET [Months] AS 'GENERATE({[Time].[Calendar].[Month].MEMBERS}, {[Time].[Calendar].CURRENTMEMBER.LAG('number of months ago of end date, starting at zero for last month')}, ALL) - GENERATE({[Time].[Calendar].[Month].MEMBERS}, {[Time].[Calendar].CURRENTMEMBER.LAG('number of months ago of start date, starting at zero for last month')}, ALL)'
You'd then use this named set as your COLUMNS clause in the MDX query (i.e. [Months] ON COLUMNS). Be aware that doing it this way will force selection of whole months only (since you're working at the month level, not the day level), which from your question may not be a bad thing.
For part 4, I'd have a level for years at the top of the columns in the matrix, then another level for months underneath.
Hope this helps, despite my uncertainty!
May 15, 2007 at 8:45 am
Remember you can also specify a set using <from> : <to> , e.g [20060601]:[20061231] and if using 2K5, i think you can use null on either end to have it flow to the natural end e.g. [20060601] : null would give you all dates between 20060601 and the last date in the dimension.
I really hope you're using RS2K5 - if so, simply drag the date dimension (or required level) to the Filters section of the datsset, change the Operator to be 'Range' (you can choose whether it's inclusive or exclusive on the range markers) and then check the two checkboxes indicating you want these range markers to be parameters. Then simply put the level you require from the time dimension in to the dataset. Use the results in a matrix as was previously sugested will take care of the dynamic column creation.
Steve.
May 16, 2007 at 9:51 am
Ok, I think I found the elegant solution...
I need to create a project event table: ProjectSK, DateSK, EventSK.
Events in this case would be limited to StartDate, ActualStartDate, EndDate, and ActualEndDate. This is a factless fact table or non-additive fact table depending on how I eventual implement the eventSK. Parameterizing the date becomes easy in SSRS.
Thank you for your help, it put me onto this solution allowing easy Project by Date queries.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply