[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2875
  • Last Modified:

SQL reporting services OLAP cube how to query time dimension using date range

I'm trying to build a report in SSRS that runs against an OLAP cube in an Analysis Services database.  This cube has a 'Time' dimension with a year-quarter-month-day hierarchy.  Using the MDX query builder in reporting services I'd like to figure out a way to filter on a date range based on the Time dimension and ideally have this as a report parameter.  I'd like for the user to pick a start and end date from two date picker controls and have that fed into the filter.  This is trivial with SQL but proving difficult with MDX and the cube.  Any ideas?  
0
javaught
Asked:
javaught
  • 2
1 Solution
 
PedroCGDCommented:
Dear friend,
The SSRS wizard do all that for you... and you can check the code genereted by it.
Follow the attached images.
Helped?
Regards,
Pedro
www.pedrocgd.blogspot.com
image1.JPG
image2.JPG
image3.JPG
image4.JPG
image5.JPG
0
 
javaughtAuthor Commented:
Pedro

Thank you for the reply.  I do not seem to have the dimension available that is in your example.  I have a time dimension with several members I can query individually, such as year, month, day, etc, but nothing that combines them.  I attached a screen capture showing this.  This is why I am having trouble specifying a date range.  Is there a way I can add a dimension like the one shown in your example?
time-query.JPG
0
 
PedroCGDCommented:
Dear javaught,
Yes, you are right... my time dimension is different. Maybe is your problem... but tell me wich is the error...

Also check this link and website:
http://www.ssas-info.com/analysis-services-articles/59-time-dimension/587-how-to-create-a-time-dimension-in-sql-server-2005

helped?
regards!
Pedro

TimeDimension.JPG
TimeDimension-AS.JPG
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now