Change hard-code to parameter

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



Answer 1

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

Answer 2

Could you post your code?

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 3

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

Answer 4

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

Answer 5

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