Problem Using Calendar Periods as parameter comparison
Don’t know if this is possible or if there is a better way to do this, but any help would be appreciated.
I have a where clause that is used to select sales information for a period based on a parameter that’s passed from a drop down list. The parameter being passed("& parameters!GetPeriod.Value &) represents any level of the calendar hierarchy selected from a drop down list. It could be year, qtr, month, week, or day. The problem is, when you compare the parameter to the calendar, you have to specify the level of the hierarchy the comparison is for([Fiscal Month Name]). Is there any other way to handle this? If not, it means I have to write a separate report for each level of the hierarchy.
WHERE {ClosingPeriod([Calendar].[Fiscal Calendar].[Fiscal Month Name], [Calendar].[Fiscal Calendar].["& parameters!GetPeriod.Value &"])}
I'm Assuming Reporting Services ...
I'm assuming you are refering to using Reporting Services with an Analysis Services data source. I do just what you are attemtping in many articles of my MSSQL Server Reporting Services Series.
See if you can resolve your questions via one of my examples, and get back with any further specific questions / issues.
Good Luck!
Bill
Pay Particular Attention to the Picklist Support Datasets ...
Pay particular attention to the picklist support datasets ... in your case, specifically for those that deal with Date ... the picklists are simply constructed and generate the MDX qualified name for all hierarchical levels.
If this gives you trouble, feel free to get back to to me here...
HTH,
Bill
