locked
SSAS - Problem with query parameter RRS feed

  • Question

  • Hi,

    I realize, there are many posts already created on different forums ( including this one)  concerning the topic I`m gonna mention, but they either remained unanswered or answers given haven`t worked in my case. Maybe by the time I created this post someone solved this problem :)

    I have a SSAS report based on cube. Dataset query looks like this :

    SELECT NON EMPTY { [Measures].[Hours In Track], [Measures].[Hours Arve Sick Leave] } ON COLUMNS, NON EMPTY { ([Dim Date].[Hierarchy].[Date ID].ALLMEMBERS * [Dim Division].[Hierarchy].[Division ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@FromDimDateHierarchy) : STRTOMEMBER(@ToDimDateHierarchy) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DimDivisionHierarchy) ) ON COLUMNS FROM [BicepsArveCube])) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

    Parameter @DimDivisionHierarchy, has default value (given while designing query) for which the report generates expected data. When I hardcode it`s value, it works as well. When as "available values" I use auto-generated dataset (auto-generated when designing query and adding parameter) it works as well. My goal is to use custom dataset as "available values", but when I try to pass value to query from parameter, I get error:

    "the STRTOSET functions expects a tuple set expression for the 1 argument. A string or numeric expression was used."

    When I add CONSTRAINED clause, the error changes to one saying, that rules made by CONSTRAINED expression were voilated.

    PLEASE guys, help me if you can, I`ve got really no clue what`s going on, and why the hell it doesn`t work. I`ve spend 3 days already digging uncle google searching for solution, but found none.

    Thursday, August 22, 2013 12:39 PM

Answers

  • problerm solved - just used CInt(Format(parameter_value, "yyyyMMdd")) instead of Replace and it works perfect
    Monday, September 2, 2013 7:02 AM

All replies

  • There are two values associated with a SSRS parameter.  One for the "Label" and the other for the "Value". 

    Make sure you are mapping the MDX Unique Name such as "[Date].[Fiscal Year].&[2007]" to the "Value" field and  passing the same "Value" field as a  parameter to your main data set ("=Parameters!DateParam.Value")

    Thursday, August 22, 2013 1:17 PM
  • Hi Artur,

    I can guess that you have checked the data set itself : if 'DimDivisionHierarchy' is set to the relevant parameter (@DimDivisionHierarchy) at the parameter section and the @DimDivisionHierarchy parameter is set to Text (so it does not need to have multiple values !) /

    Before we'll dig in, try please to change the default value (Specified values) from '[Dim].[Dim H].&[0]' to '="[Dim].[Dim H].&[0]" so now you'll se the text <<Exp>> in the text box . Sounds stupid but I've seen some differences between the two texts ..

    Let me know if it helps :) 


    Regards, David .

    Thursday, August 22, 2013 1:22 PM
  • Hi Sha Kanjoor Anandan,


    available values for @DimDivisionHierarchy are created by simple query :

    SELECT DISTINCT divisionID, divisionName
    FROM            Dim_Division

    then it`s mapped like this: value -> divisionID (smallint), label -> divisionName

    Paratemer @DimDivisionHierarchy is of integer type with multiple values option, (user must be able to run report for several different divisions at the same time) so everything should work correctly...


    Friday, August 23, 2013 6:52 AM
  • Hi David,

    I love the part "before we dig in" ;) - it`s great to see someone motivated to help me :).

    Yep, parameter section is fine - it was auto-generated when I designed query (Visual Studio 2012) and checked the "parameter" checkbox in the filter section.

    As to the default value - I have this disabled. I have only changed available values from auto-generated dataset (based on hierarchy from division dimension), to my custom one consisting of divisionID (smallint) as value, and DivisionName as label.

    Parameter value in dataset->properties->parameters is set to =Parameters!DimDivisionHierarchy.Value, since parameter type is integer and divisionID is smallint, so no conversion was needed, unless I`m wrong.

    Just to clarify, I`m MDX beginner so please have patience ;)

    Friday, August 23, 2013 7:40 AM
  • available values for @DimDivisionHierarchy are created by simple query :

    SELECT DISTINCT divisionID, divisionName
    FROM            Dim_Division

    then it`s mapped like this: value -> divisionID (smallint), label -> divisionName

    Paratemer @DimDivisionHierarchy is of integer type with multiple values option, (user must be able to run report for several different divisions at the same time) so everything should work correctly...


    The problem here is you are trying to pass a simple integer value to the STRTOSet function. Let us say , if two values of division choosen , the parameter will be having values like '1,2' and in the query it will be come STRTOSET(1,2). But what is expected is STRTOSET('DimDivision.Division.&[1],DimDivision.Division.&[2]').

    There are two way you could achieve this

    1. While forming the MDX and using the filer , you can see a checl box called parameter. Once you check that , a parameter will be created and a dataset will be associated to it with the selected dimension attribute. The dataset will nt visible , you may need to right click on datasets and click on "show hidden datasets'.

    2. You need to parse your parameter value '1,2' to 'DimDivision.Division.&[1],DimDivision.Division.&[2]' and then pass it to the MDX.

    Hope this helps.


    Best Regards Sorna

    Friday, August 23, 2013 8:28 AM
  • Hi Sorna,

    The thing is, when I follow your advice, I get CONSTRAINED clause voilation. When I delete CONSTRAINED, report doesn`t return any data ( when I hardcode parameter values as defaults it returns data, when I pass the exactly same values as dynamic parameters` values it returns no data... I think something weird happens when I input parameters at runtime...

    EDIT:

    problem with Division solved - I created 1 element hierarchy, used it as filter and checked " parameter " checkbox. Next problem is with dates. In date dimension, public key is smallint with date key like "20130823". When I used it as filter in the same way, the report is ultra slow, probably because it takes all values of dateID to the available values, which in this case is over 5000 entries. I`d like the Parameter field on report to be date/time ( to have calendar as input help), but when I try to convert date to integer using code:

    CInt(Replace(Replace(Parameters!FromDimDateHierarchy.Value, "-", ""), "00:00:00", ""))

    I get either CONSTRAINED voilation error, or when I try to make the value as tuple with code:

    ="[Dim Date].[Hierarchy].[Date ID].&["& CInt(Replace(Replace(Parameters!FromDimDateHierarchy.Value, "-", ""), "00:00:00", ""))& "]"

    I get no data on report.

    Friday, August 23, 2013 10:04 AM
  • Hi Artur ,

    Since the query looks ok (you can get data if you enter a quote as a default value to your parameter..), can you please share : 1)  the quote as the default value when it works . 2) what the parameter gets as default from ds (simple way will be to create a text box and then throw the parameter in) .

    Please choose just one member .


    Regards, David .

    Sunday, August 25, 2013 6:57 AM
  • Hi David,

    As in above post I wrote, division problem is solved. The problem remaining are the dates. Date dimension has public key defined as Int, where example value is 20130826. When designing dataset query, I add to filter parameters the hierarchy it`s (public key, fieldname -> dateID ) in. I choose "range inclusive" option, define default values by choosing 2 values from hierarchy tree. Then, I check the "parameter" checkboxes, so I get those 2 range variables as parameters in my report. After pressing OK button, all is auto-generated.

    The default values are some 2 random dates in integer format like 20130801 and 20130805. When in dataset query designer I press "click to execute query" I get expected data. I used the text fields you mentioned to get exact values of date parameters and these are as follows:

    [Dim Date].[Hierarchy].[Date ID].&[20130801]

    and

    [Dim Date].[Hierarchy].[Date ID].&[20130805]

    What I need to do is to use Date/Time type of parameter field so the user has a calendar to specify dates interval for data retrieval. To do that, I changed parameter field type to Date/Time, disabled default and avialable values ( these were set to auto-generated values taken from dataset query designer mentioned in beginning of this post). Problem is, that when I try to convert Date/Time to Int, using Replace and Cint functions:

    CInt(Replace(Replace(Parameters!FromDimDateHierarchy.Value, "-", ""), "00:00:00", ""))

    I get Constrained violation error or, when I delete "constrained" keyword from query and try to use above code to create a tuple :

    [Dim Date].[Hierarchy].[Date ID].&[CInt(Replace(Replace(Parameters!FromDimDateHierarchy.Value, "-", ""), "00:00:00", ""))]

    I get no data on the report, after choosing from calendar input help the same values as I set as default ( so i`m sure data should appear on report )


    Monday, August 26, 2013 9:01 AM
  • Hi again ,

    I'm sure it is something simple that we cannot put our finger on ..
    Please have a look at peter's suggestion (2nd post from the bottom) :
    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/4292e607-8fa7-4d71-af8f-3a2e9978f76e/how-to-use-an-mdx-datetime-value-as-default-value-in-a-report-parameter
    It aint clever but it must work since you'll build the date from the year/month/day raw values .
    Hope to hear some good news soon ;)


    Regards, David .

    Monday, August 26, 2013 2:43 PM
  • Hi again :) ,

    Peter`s solution is for creating a dataset for values. My problem is a bit different. I think I came to the core of the problem. In my report, the input help MUST be a calendar, so Date/Time type of parameter is essential. With method of small steps, I came to sth like that:

    parameter type: date/time

    - I used your advice about text field, and it showed me, that parameter value when date/time type is as follows: 2013-08-05 00:00:00, while my DateID is Integer type, so I need to do conversion:

    -when in dataset parameters tab I specify the parameter value as expression:

    ="[Dim Date].[Date ID].&["
     & Replace(Replace("2013-08-05 00:00:00", "-", ""), " 00:00:00", "")
     & "]"

    I get the data as expected, everything works fine. At this point I was like "f*** yeah !!!!! finally !!!!". But when I changed the hardcoded value to parameter pointer:

    ="[Dim Date].[Date ID].&["
     & Replace(Replace(Parameters!FromDimDateDateID.Value, "-", ""), " 00:00:00", "")
     & "]"

    I get constrained voilation error... don`t know why, because as written above, the Parameters!FromDimDateDateID.Value looks exactly the same as the hardcoded value I used.

    I`m starting to get really frustrated :P


    UPDATE: After removing CONSTRAINED keyword from mdx query, the report doesn`t take into account the date value I`ve given. The "From" date is set to 2013-08-10,  but on the report I have all older records as well. The "To" date is at the moment chosen from auto-generated Integer field ( DateID, PK, Integer) input help based on all existing values of that field in Date dimension. The "To" date parameter works fine, and the report data is narrowed by it.
    Tuesday, August 27, 2013 9:19 AM
  • Hi Artur ,

    Are you still struggling with the report ? does your new post 'SSAS - Access Denied Problem' refer to the same issue ?


    Regards, David .

    Wednesday, August 28, 2013 11:42 AM
  • Hi there :)

    Yea, still struggling, and the access denied problem refers to same issue. I created a new topic, because I think the reason for this access denial may be of other nature than that Parameter stuff.

    Wednesday, August 28, 2013 11:57 AM
  • problerm solved - just used CInt(Format(parameter_value, "yyyyMMdd")) instead of Replace and it works perfect
    Monday, September 2, 2013 7:02 AM