Prologika Forums

Teo,

Nice book!  I just picked it up and I'm working through it.  I have a question concerning how parameters work with the new RS and AS tools.  In the 200 versions I had an MDX query:

=
"select
   {[Measures].[Cust Ret Last Week], [Measures].[Pct Cust Ret Last Week Overview],
     [Measures].[Cust Ret Prev 4 Weeks], [Measures].[Pct Cust Ret Prev 4 Weeks Overview], [Measures].[Avg Cust Ret Prev 4 Weeks],
     [Measures].[Cust Ret Prev 8 Weeks], [Measures].[Pct Cust Ret Prev 8 Weeks Overview], [Measures].[Avg Cust Ret Prev 8 Weeks]} on columns,
   NON EMPTY {order({[Attributed Class].children}, ([Measures].[Cust Ret Last Week]), DESC)} on rows
 from
   [Onion Peel]
 where
   (" & Parameters!product_family.Value & ",
   [Drive Diagnosis Code].[All Drive Diagnosis Code].[valid],
   [VMI].[All VMI].[valid],
   [Source Code].[All Source Code].[valid])"

that did what I needed.  Now, I am trying to port this to 2005 and finding parameters a bit cumbersome.  First a ported the dims et all to match the new schema in the UDM and changed the parameter to be specfied by the @:

select
   {[Measures].[Cust Ret Last Week], [Measures].[Pct Cust Ret Last Week Overview],
     [Measures].[Cust Ret Prev 4 Weeks], [Measures].[Pct Cust Ret Prev 4 Weeks Overview], [Measures].[Avg Cust Ret Prev 4 Weeks],
     [Measures].[Cust Ret Prev 8 Weeks], [Measures].[Pct Cust Ret Prev 8 Weeks Overview], [Measures].[Avg Cust Ret Prev 8 Weeks]} on columns,
   NON EMPTY {order({[Attributed Class].[Attributed Class].children}, ([Measures].[Cust Ret Last Week]), DESC)} on rows
 from
   [Onion Peel]
 where
   (strtoset(@product_family),
   [Drive Diagnosis Code].[Drive Diagnosis Code].[OP Filter].&[valid],
   [VMI].[VMI].[OP Filter].&[valid],
   [Source Code].[Source Code].[OP Filter].&[valid])

First, why do I need to strtoset() this.  I don't want a set, this is just a single value.  Second, it appears that I have to define my parameter in Report -> Parameters and by right clicking in the data pane.  How do I leave a null value as the default then as leaving default blank in the right clicked section greys out the "OK" button?  While the string method from 2000 was a pain, this seems to be worse.

Just a personal methodology questions, how do you go about naming/using your dim, attribute, hierarchies so you don't end up having all this repetition?

Anyway, just wondering what your thoughts were on parameters.

Keehan

First, why do I need to strtoset() this.  I don't want a set, this is just a single value. 

By default, the MDX Query Designer configures the parameter as a multi-value parameter. It needs to StrToSet function to convert the string array of parameter values to set. Of course, you can overwrite the MDX statement with your own if this is not appropriate. Just make sure that you don't switch back to the Design mode because your changes will be overwritten since revere-engineering custom changes is not currently supported.

Second, it appears that I have to define my parameter in Report -> Parameters and by right clicking in the data pane.  How do I leave a null value as the default then as leaving default blank in the right clicked section greys out the "OK" button?  While the string method from 2000 was a pain, this seems to be worse.

Not sure which section you are referring to but if you go to the Report Parameters dialog (Report->Parameters menu), you can change the default to NULL.

Teo,

I am a long time user of Reporting Services, so I am familiar with the Report -> Report Parameters dialog.  My MDX query looks like the following right now:

When I try to run the query from the Data tab, I get:

Query preparation failed.  Additional information: Parser: The query contains the product_family parameter, which is not declared. (msmgdsrv)

When I switch to the Layout tab, I get:

The query cannot be retrieved from the query builder.  Check the query for syntax errors.  Reporting Services will continue to use the most recent valid query.  Additional information:   Query preparation failed. (Microsoft.AnalysisServices.Controls)

However, product_family is defined in Report -> Report Parameters and the report works from the Preview tab.  Looking around, I find another parameter dialog called Query Parameters.  It can be found by clicking the @ icon in the Data tab or by right-clicking in the results area of the Data tab.  If I don't fill this in then I hit the 2 errors above.  If I do fill this in, the errors go away, but now my default Report Parameter is no longer NULL it defaults to the value set in the Query Parameter.  It is not possible to set Query Parameter to NULL as it is assigned by browsing the dimension and can not be left blank as this greys out the OK button.

What is the purpose of the Query Parameter dialog?  Why can't they just pop up a window to ask for values like they did in RS 2k with standard named parameters?

Thanks again,

Keehan

Teo,

I have it working better now.  I did have to fillout the Query Parameter dialog to not get the errors and it doesn't prompt for parameter values, but I guess I can live with it.

Cheers,

Keehan

Hi,

I had the same error message :
the parameter was configured in Report Parameters and executing the query displayed it was not declared.
Copying the Report Parameter window's content into the Query Parameter's window corrected the problem.
Thanks,

a+, =)
-=Clément=-

Configuration :
Windows XP SP3 Pro
BIDS 2005