none
Default 'From' Date Parameter Question

    Question

  • I am writing a report that for now is using data from 2 tables. This report is going to be accessed via CRM and is based on a particular record chosen in CRM therefore my initial dataset is using the "CRMAF" prefixes.  This part is working fine

    I need to populate a 'From' date parameter value to the minimum date that exists on a related sub table.

    For example - assume we have an Orders and OrdersDetails tables

    My report has a dataset with a query something like this:

    SELECT CRMAF_Ord.OrderNumber,

    CRMAF_Ord.CustomerName,

    CRMAF_Dt.ProcessedDate

    FROM Orders as CRMAF_Ord

    Inner Join OrderDetails as CRMAF_Dt

    On CRMAF_Ord.OrderNumber = CRMAF_Dt.OrderNumber

    Each order could have many order detail records with different 'processeddate' values.  I want my 'From' datetime parameter value to display the earliest processeddate value for the given order record that has been selected.

    I created a 2nd dataset 'ProcessedFromDate' which is called from my 'From' parameter

    SELECT MIN(CRMAF_Dt.ProcessedDate) as 'Processed From'

    FROM OrderDetails

    This ultimately gives me back the earliest processeddate value that exists for all order detail records, not just the one I have selected.

    It seems I need to be able to somehow reference the main dataset or something but not sure how to limi the dates to only the record chosen.

    Any help would be appreciated.

    Thank you.


    Robert

    Friday, March 15, 2013 7:55 PM

Answers

All replies

  • Check this example. You can follow the steps adviced.

    http://msdn.microsoft.com/en-us/library/aa337498(v=sql.105).aspx


    Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

    Friday, March 15, 2013 10:16 PM
  • Hi Rdtruitt,

    Please change the query of the “ProcessedFromDate” to :
    SELECT MIN(CRMAF_Dt.ProcessedDate) as 'Processed From'
    FROM OrderDetails as CRMAF_Dt
    WHERE CRMAF_Dt.OrderNumber=@Ordernumber

    In this case, you will get the min date for the order you selected.

    If you have any questions, please feel free to ask.

    Regards,


    Charlie Liao
    TechNet Community Support

    Monday, March 18, 2013 10:04 AM
    Moderator
  • Hello - thanks for the assist but that doesn't quite work for it does not know what @Ordernumber is.

    I created a hidden 'Order Number' paramter that got the order number from a separate dataset that was essentially just a query (CRMAF prefix) to get the order number of the record chosen in CRM.  I then used that as a parameter value in another query to try and get the mininum date processed from the related details table for that record. Now sure why but causes the From parameter value to default to null and other such odd behavior. 


    Robert

    Saturday, March 23, 2013 3:10 AM
  • The odd thing that occurs  - I can have one dataset using the 'CRMAF' prefixes and display a table with data from the record that has been selected in CRM.

    I have another dataset that is exactly the same query but the data extracted is not the same for the record chosen.

    In the first dataset and table, for example, the order ID shows as '53' which matches the order I have selected and opened in CRM.

    Another table getting data from a second identical dataset will show some other order ID even though the dataset queries are the same.

    I am not sure why or how this is.

    Ultimately I am going back to the above scenario I am trying to default a 'From' date parameter to be the earliest date that exists in a field for a related table (ex orders details) associated with the parent 'Orders' record I have opened in CRM.  That earliest From date will be part of the range of data that should then return order details for the Order chosen .


    Robert

    Saturday, March 23, 2013 4:33 AM
  • Think I just read possibly something about limitations on doing what I want to do and possible use of 'explicit pre filtering'  . not sure until I look into that more. If anyone has a suggestion I"m all ears!


    Robert

    Saturday, March 23, 2013 4:46 AM