?
Solved

Reporting Services: Set a Time Dimension parameter to a default Max time?

Posted on 2009-04-01
4
Medium Priority
?
577 Views
Last Modified: 2013-11-27
Hi,

I am new to MDX and Analysis Services.  I have a simple report written with Visual Studio 2005's Reporting Services (v 8.0.50727.762)  that reads data off of an Analysis Services cube.
I have 1 parameter so that the report is filtered by a Valuation Time (from the Valuation Time Dimension).  Each day there is new data for the new Valuation Time.

I want to schedule this report to run automatically so that this parameter has either the latest Valuation Time from the Valuation Time Dimension selected, OR, just have the truncated system date show up (MM, DD, and YYYY).  I'd be happy with either solution.

This report is hosted on a Report Manager reporting server.  Also note that our Valuation Time Dimension is also a String and not set up as Date Time (don't ask, lol).

Anyone know how I can setup the Default Value of this parameter?  I have the parameter set up as Available Values: From Query, and Default Values, Non Queried.

Please Advise!  

Thanks,

Mike
When I hardcode the default parameter I get the result I want, but this needs to be more dynamic:
[DIM Valuation Time].[Valuation Time].&[2009-03-17T00:00:00]

Open in new window

0
Comment
Question by:mitai
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 16

Accepted Solution

by:
Auric1983 earned 750 total points
ID: 24040545

I think in order to set the parameter from a query you may need to do something similar to

Create a dataset with the following query (sorry for the pseudocode)

select top 1 Valuation Time from Dim Valuation time order by Valuationtime desc

Set your Parameter Default Value to "From Query" point it to the dataset created above and see what happens?
0
 

Author Comment

by:mitai
ID: 24044500
Thanks Auric,

This ALMOST works!  I created a separate Dataset that queries and returns the latest Valuation Time in the cube (perfect!).  But when I try to map my existing Valuation Time parameter to use this new Dataset under the "Default Values", "From Query" option, the report doesn't see this default value when I hit the Preview tab, I still have to select the date.

So, with this Valuation Time parameter, I have the "Available Values", "From Query", using the existing Dataset, and have only changed the "Default Values" part on the Report Parameters window to use my new Dataset.

Can anyone else shed some light as to why my report can't see this default value when I run the report?  I've attached the MDX for my new Dataset, and it returns the correct value under the ParameterCaption field.

Thanks,

Mike
WITH MEMBER [Measures].[ParameterCaption] AS '[DIM Valuation Time].[Valuation Time].CURRENTMEMBER.MEMBER_CAPTION' SELECT {[Measures].[ParameterCaption]} ON COLUMNS, [DIM Valuation Time].[Valuation Time].LastChild ON ROWS FROM [CR_CreditValuations]  

Open in new window

0
 

Author Closing Comment

by:mitai
ID: 31565366
This is the answer.  I'm new to MDX so I had to Google the proper syntax, but it worked.  Thanks! :)
0
 
LVL 16

Expert Comment

by:Auric1983
ID: 24048968
Glad to hear it mitai, I don't do a lot of MDX scripting, the majority of my SSRS experience is with straight SQL tables so i had an idea of what you needed to do just not the syntax...
0

Featured Post

Major Serverless Shift

Comparison of major players like AWS, Microsoft Azure, IBM Bluemix, and Google Cloud Platform

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question