none
Change hard-code to parameter RRS feed

  • Question

  • Hi all,

    I have code to retrieve the amount of Disbursements made in a specific month:

    with member [Measures].[Last Month Disbursements] as
    sum( openingperiod( [Date - Common].[Last Day of Month Date].[Last Day of Month Date],
       [Date - Common].[Last Day of Month Date].&[2011-10-31T00:00:00] ), [Measures].[Disbursements] )
    select { [Measures].[Disbursements], [Measures].[Last Month Disbursements] } ON COLUMNS,
    nonempty( [Proj].[Proj number].children ON ROWS
          There is more code but this is where I need help

    This code does get to total Disbursements and the Last Month Disbursements correctly - for the month ending 2011-10-31.

    But I want the user to be able to enter a parameter for the date and substitute the parameter in place of the bold-italicized entry in the code above.  I have a parameter named 'MonthEnding' which is populated by the user selecting from a drop-down list of the [Date - Common].[Last Day of Month Date] entries.

    How do I get the @MonthEnding  or  Parameter!MonthEnding.value into the code?

    Thanks for any help you can give me,

    Tina

    

    • Moved by lucy-liuModerator Thursday, March 29, 2012 2:45 AM it is a ado.net issue (From:Visual Studio Editor)
    Tuesday, March 27, 2012 2:24 PM

Answers

  • Also in case you are wondering, yes the expressions can be anyplace in the string.

        Public Sub EmbeddedExpressionDemo()
            Dim FieldsForSelect As String = "FirstName, LastName"
            Dim SomeDate As Date = #3/4/2012#
            Dim MyQuery As String =
                <SQL>
                    SELECT <%= FieldsForSelect %> 
                    FROM Customer 
                    WHERE JoinDate &gt;= '<%= SomeDate.ToShortDateString %>'
                </SQL>.Value
            Console.WriteLine("[{0}]", MyQuery)
        End Sub
    SELECT FirstName, LastName FROM Customer WHERE JoinDate >= '3/4/2012'



    KSG

    Sunday, April 1, 2012 7:45 AM

All replies

  • Hi UsingSP,

    This is a ADO.NET issue, I will move it to ADO.NET Managed Provider forum for a better support.

    Thank you for your understanding!


    Lucy Liu [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, March 29, 2012 2:44 AM
    Moderator
  • Could you post your code?


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, March 29, 2012 1:06 PM
  • Hi Paul,

    The code in question is in the original entry.  Since I entered the question, I have altered the code (Bold Italic below) to be:

    with member [Measures].[Last Month Disbursements] as
    sum( openingperiod( [Date - Common].[Last Day of Month Date].[Last day of Month Date],
    STRTOMEMBER( @MonthEnding, CONSTRAINED) ), [Measures].[Disbursements] )
    SELECT [ [Measures].[Disbursements], [Measures].[Last Month Disbursements] } ON COLUMNS
    nonempty( [Proj].[Proj Number].children ON ROWS

    This code gets the Disbursements for the month-ending date BUT it puts the amount on ALL the rows not just the row for the month-ending that matches the parameter.  This is what I am trying to solve now.

    Any help or hints on what to try next would be appreciated.

    Tina

    Thursday, March 29, 2012 6:21 PM
  • Hello,

    I am not about your SQL statement yet the following should work if the intent is too place a value into the statement which I am guessing is a string that gets evaluated by the data provider you are using.  The <%= and %> denote the beginning and ending of an embedded expression.

    VS2008 or higher VB.NET

        Public Sub EmbeddedExpressionDemo()
            Dim SomeDate As Date = #3/4/2012#
            Dim MyQuery As String = <SQL>SELECT * FROM Customer WHERE JoinDate &gt;= '<%= SomeDate.ToShortDateString %>' </SQL>.Value
            Console.WriteLine("[{0}]", MyQuery)
            MyQuery = <SQL>SELECT * FROM Customer WHERE JoinDate &lt;= '<%= Now.ToShortDateString %>' </SQL>.Value
            Console.WriteLine("[{0}]", MyQuery)
            MyQuery = <SQL>SELECT * FROM Customer WHERE JoinDate &lt; '<%= DateSerial(Today.Year, Today.Month, 0).ToShortDateString %>' </SQL>.Value
            Console.WriteLine("[{0}]", MyQuery)
        End Sub

    Results from the Console.WriteLine above

    SELECT * FROM Customer WHERE JoinDate >= '3/4/2012' 
    SELECT * FROM Customer WHERE JoinDate <= '4/1/2012' 
    SELECT * FROM Customer WHERE JoinDate < '3/31/2012' 


    KSG

    Sunday, April 1, 2012 7:41 AM
  • Also in case you are wondering, yes the expressions can be anyplace in the string.

        Public Sub EmbeddedExpressionDemo()
            Dim FieldsForSelect As String = "FirstName, LastName"
            Dim SomeDate As Date = #3/4/2012#
            Dim MyQuery As String =
                <SQL>
                    SELECT <%= FieldsForSelect %> 
                    FROM Customer 
                    WHERE JoinDate &gt;= '<%= SomeDate.ToShortDateString %>'
                </SQL>.Value
            Console.WriteLine("[{0}]", MyQuery)
        End Sub
    SELECT FirstName, LastName FROM Customer WHERE JoinDate >= '3/4/2012'



    KSG

    Sunday, April 1, 2012 7:45 AM