All of my reports use time in some way. I have used the SSAS out of the box system generated time dimension. This generated table is joined to all of the tables I use in building cubes. Everything works well - fiscal year, month info, etc. when I use parameters that are generated from MDX queries.
This however can be much too cumbersome when using finite sets of data. For instance, I have a fact table that it populated monthly (monthly snapshots) to show trends in the data. I display this trend using a graph, showing the data across Fiscal months -- no problem. I would like to display the data associated with each snapshot. You would think I could retrieve the specific dates associated with each monthly snapshot and pass the results of that query to the parameter's available values. Indeed I can, but it is not usable.. .I get a variety of errors, that essentially show that you just can't mix parameter values obtained outside an MDX query with MDX values.
Certainly I could accomplish what i need to do by filtering based on what I know is in the table I'm querying, but that is static and requires regular maintenance as time passes -- not an elegant solution.
Any ideas
Unable to mix non-MDX parameters with MDX data
GoranB
I don't know if that will help you, but this is my method to mix non-MDX parameters with MDX data.
Let's take an example
Objects in my MDX query -> DATE / SALES_AMOUNT / SALES AMOUNT_MTD
If I want to build a report showing my last opened day results, in the filter section I mark the DATE object as "Parameter". In another dataset I build an SQL query returning the MAX(DATE) of my facts table, using convert(varchar,DATE,126).
As DATE is a parameter, RS2005 creates a parameter named "DIMENSIONOBJECT", in my example it would be TIMEDATE. In the report parameter window, I create a new parameter before the TIMEDATE parameter, called DATA_DATE typed string. I mark it "Hidden", in available values "Non queried", and in default values I choose "From query" and the SQL dataset with the field returned. Same options for the TIMEDATE parameter, except that in default vaules I choose "Non Queried" and set this expression :
="[TIME].[DATE].&[" & Parameters!DATE_DATA.Value & "]"
And that's it
Does that answer your question
Regards
Ayzan
kevin_estcst
Could you give us an example.
Regards
Ayzan
micxba