none
Query Perparation Failed. Report Designer. Trying to order by Time dimension

    Question

  • SSRS automatically created this dataset when I wanted to add my time dimension as a parameter:

    WITH MEMBER [Measures].[ParameterCaption] AS [Time].[Calendar Time].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue] AS [Time].[Calendar Time].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [Time].[Calendar Time].CURRENTMEMBER.LEVEL.ORDINAL SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Time].[Calendar Time].ALLMEMBERS ON ROWS FROM ( SELECT ( -{ [Dim Customer].[Zip Code].&[] } ) ON COLUMNS FROM ( SELECT ( STRTOSET(@StoresStoreName, CONSTRAINED) ) ON COLUMNS FROM [DWPOC]))

    Now I want to edit it, but it gives me a query preparation failed error...even before I put my changes in.  All I do is right-click on the dataset, select Query, Click OK, and I get the error.  The statement itself will execute just fine when I click the execute button....but Report Designer apparently sees some kind of error so it won't even save the code it automatically generated.  

    Assuming I get this figured out why that is occuring, all I want to do is put the dates in descending order...which I hope is as simple as adding the ORDER clause:

     WITH MEMBER [Measures].[ParameterCaption] AS [Time].[Calendar Time].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue] AS [Time].[Calendar Time].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [Time].[Calendar Time].CURRENTMEMBER.LEVEL.ORDINAL SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , ORDER([Time].[Calendar Time].ALLMEMBERS,DESC) ON ROWS FROM ( SELECT ( -{ [Dim Customer].[Zip Code].&[] } ) ON COLUMNS FROM ( SELECT ( STRTOSET(@StoresStoreName, CONSTRAINED) ) ON COLUMNS FROM [DWPOC]))

    Thanks!

    Thursday, June 10, 2010 8:39 PM

Answers

  • SSRS automatically created this dataset when I wanted to add my time dimension as a parameter:

    WITH MEMBER [Measures].[ParameterCaption] AS [Time].[Calendar Time].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue] AS [Time].[Calendar Time].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [Time].[Calendar Time].CURRENTMEMBER.LEVEL.ORDINAL SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Time].[Calendar Time].ALLMEMBERS ON ROWS FROM ( SELECT ( -{ [Dim Customer].[Zip Code].&[] } ) ON COLUMNS FROM ( SELECT ( STRTOSET(@StoresStoreName, CONSTRAINED) ) ON COLUMNS FROM [DWPOC]))


    This is not the default query SSRS automatically generated. Someone changed it before, because the parameter dataset shouldn’t include any parameter (except cascading parameter). It should be like this:

    WITH MEMBER [Measures].[ParameterCaption] AS [Time].[Calendar Time].CURRENTMEMBER.MEMBER_CAPTION

    MEMBER [Measures].[ParameterValue] AS [Time].[Calendar Time].CURRENTMEMBER.UNIQUENAME

    MEMBER [Measures].[ParameterLevel] AS [Time].[Calendar Time].CURRENTMEMBER.LEVEL.ORDINAL

    SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,

    [Time].[Calendar Time].ALLMEMBERS ON ROWS

    FROM [DWPOC]

     

    So could you try above query? And personally, I would suggest you delete all datasets and recreate them again, because I’m not sure if there is any other issue.  

     

    Back to the error message, the issue is caused by the ‘from’ statement in MDX query. By default no subquery should be there. But there is parameter @ StoresStoreName try to filter the members. However, since the report has not run yet, so no value pass to the query, then it will return blank. Just like below query:

    SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Date].[Calendar Year].ALLMEMBERS ON ROWS FROM (select  STRTOSET("",CONSTRAINED)  on 0 from [Adventure Works])

     

    It will return error message “An MDX expression was expected. An empty expression was specified.”

    This dataset (SSRS automatically generated) is used for supplying available value to let user choose.

     

    Hope this helps,

    Raymond


    Raymond Li - MSFT
    Monday, June 14, 2010 6:07 AM
    Moderator

All replies

  • When I click on details for the error, I get this:

    An MDX expression was expected. An empty expression was specified.
    ----------------------------
    Query preparation failed.

    I do not have the same problem for other datasets it automatically created.

     

    Thursday, June 10, 2010 8:41 PM
  • SSRS automatically created this dataset when I wanted to add my time dimension as a parameter:

    WITH MEMBER [Measures].[ParameterCaption] AS [Time].[Calendar Time].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue] AS [Time].[Calendar Time].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [Time].[Calendar Time].CURRENTMEMBER.LEVEL.ORDINAL SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Time].[Calendar Time].ALLMEMBERS ON ROWS FROM ( SELECT ( -{ [Dim Customer].[Zip Code].&[] } ) ON COLUMNS FROM ( SELECT ( STRTOSET(@StoresStoreName, CONSTRAINED) ) ON COLUMNS FROM [DWPOC]))


    This is not the default query SSRS automatically generated. Someone changed it before, because the parameter dataset shouldn’t include any parameter (except cascading parameter). It should be like this:

    WITH MEMBER [Measures].[ParameterCaption] AS [Time].[Calendar Time].CURRENTMEMBER.MEMBER_CAPTION

    MEMBER [Measures].[ParameterValue] AS [Time].[Calendar Time].CURRENTMEMBER.UNIQUENAME

    MEMBER [Measures].[ParameterLevel] AS [Time].[Calendar Time].CURRENTMEMBER.LEVEL.ORDINAL

    SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,

    [Time].[Calendar Time].ALLMEMBERS ON ROWS

    FROM [DWPOC]

     

    So could you try above query? And personally, I would suggest you delete all datasets and recreate them again, because I’m not sure if there is any other issue.  

     

    Back to the error message, the issue is caused by the ‘from’ statement in MDX query. By default no subquery should be there. But there is parameter @ StoresStoreName try to filter the members. However, since the report has not run yet, so no value pass to the query, then it will return blank. Just like below query:

    SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Date].[Calendar Year].ALLMEMBERS ON ROWS FROM (select  STRTOSET("",CONSTRAINED)  on 0 from [Adventure Works])

     

    It will return error message “An MDX expression was expected. An empty expression was specified.”

    This dataset (SSRS automatically generated) is used for supplying available value to let user choose.

     

    Hope this helps,

    Raymond


    Raymond Li - MSFT
    Monday, June 14, 2010 6:07 AM
    Moderator
  • Awesome!!!  Things work for me now.

    But yes, as I see it, the MDX I initially posted above is automatically created by the Query Designer when the first dataset is created.  When creating my first dataset, DataSet1, I drag in the columns I want to see, then at the top I dragged in Store Name and clicked the Parameter checkbox, then dragged in Zip Code and put a filter on that (Not Equal to blank), and finally dragged in my Time dimension and clicked the Parameter checkbox.  After I click OK, it generates the MDX/datasets for dataset1, the store parameter, and the TimeCalendar parameter, and it automatically appended this to the TimeCalendarTime dataset:

    FROM ( SELECT ( -{ [Dim Customer].[Zip Code].&[] } ) ON COLUMNS FROM ( SELECT ( STRTOSET(@StoresStoreName, CONSTRAINED) ) ON COLUMNS

    It turns out I bascially had to remove the extra stuff it put in.  So again, I just find it odd that it automically creates the MDX, but when I go in to edit it..it won't allow me to save it unless I remove the part that causes the error first.  Thanks for the explaination of the error, I kind of see why it errors out I suppose, but I'm quite new at this. 

    Thursday, June 24, 2010 7:27 PM