locked
business data list webpart with date filter RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

     

    Hello,

    need to handle below scenario in business data list webpart with date filter criteria.

    user should get data if the eneterd date is within the range of current date to past 90 days.

    and i m using below query for this purpose.

    SELECT * From EBBReport WHERE IsCurrent=0 AND

    (DATEPART(y,EffDate) = DATEPART(y,@Effddate))

    AND EffDate > DATEADD(dd, -90, getdate())

    I'm not sure how to validate this criteria.

    1) user should not see data if the entered date is not within the above range
    2)user should not see data if the entered date is future date
    Monday, December 19, 2011 11:47 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi, 

    I would suggest you to use a stored procedure. Please take a look at this example.

    Stored Procedure 

    CREATE PROCEDURE [dbo].[GetDataByDate]
	@d date
AS
BEGIN
SELECT *
FROM SexTable
WHERE @d <= GETDATE() AND @d >= GETDATE() - 90
END


    Application Definition File

    <?xml version="1.0"?>
<LobSystem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:schemaLocation="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog BDCMetadata.XSD" Type="Database" Version="1.0.0.0" Name="TestDBLOBSystem" xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">
  <Properties>
    <Property Name="WildcardCharacter" Type="System.String">%</Property>
  </Properties>
  <LobSystemInstances>
    <LobSystemInstance Name="TestDBInstance">
      <Properties>
        <Property Name="DatabaseAccessProvider" Type="System.String">SqlServer</Property>
        <Property Name="AuthenticationMode" Type="System.String">PassThrough</Property>
        <Property Name="RdbConnection Data Source" Type="System.String">auriga\sqlexpress</Property>
        <Property Name="RdbConnection Initial Catalog" Type="System.String">TestDB</Property>
        <Property Name="RdbConnection User ID" Type="System.String">hr</Property>
        <Property Name="RdbConnection Password" Type="System.String">12345</Property>
        <Property Name="RdbConnection Integrated Security" Type="System.String" />
        <Property Name="RdbConnection Pooling" Type="System.String">false</Property>
      </Properties>
    </LobSystemInstance>
  </LobSystemInstances>
  <Entities>
    <Entity EstimatedInstanceCount="0" Name="TestEntity">
      <Identifiers>
        <Identifier TypeName="System.String" Name="[SexID]" />
      </Identifiers>
      <Methods>
        <Method Name="TestFinder">
          <Properties>
            <Property Name="RdbCommandText" Type="System.String">dbo.GetDataByDate</Property>
            <Property Name="RdbCommandType" Type="System.String">StoredProcedure</Property>
          </Properties>
          <FilterDescriptors>
            <FilterDescriptor Type="Comparison" Name="d" />
          </FilterDescriptors>
          <Parameters>
            <Parameter Direction="In" Name="@d">
              <TypeDescriptor TypeName="System.String" Name="d" AssociatedFilter="d">
                <DefaultValues>
                  <DefaultValue MethodInstanceName="TestFinderFinder" Type="System.String">01.01.2012</DefaultValue>
                </DefaultValues>
              </TypeDescriptor>
            </Parameter>
            <Parameter Direction="Return" Name="TestFinder">
              <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="TestFinderDataReader">
                <TypeDescriptors>
                  <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="TestFinderDataRecord">
                    <TypeDescriptors>
                      <TypeDescriptor TypeName="System.String" Name="SexID" IdentifierName="[SexID]"/>
                      <TypeDescriptor TypeName="System.String" Name="Description" />
                    </TypeDescriptors>
                  </TypeDescriptor>
                </TypeDescriptors>
              </TypeDescriptor>
            </Parameter>
          </Parameters>
          <MethodInstances>
            <MethodInstance Name="TestFinderFinder" Type="Finder" ReturnParameterName="TestFinder" ReturnTypeDescriptorName="TestFinderDataReader" ReturnTypeDescriptorLevel="0" />
          </MethodInstances>
        </Method>
      </Methods>
    </Entity>
  </Entities>
</LobSystem>


    The result on the SharePoint page

    Dmitry

    Lightning Tools LogoLightning Tools Check out our SharePoint tools and web parts
    Tuesday, December 20, 2011 12:03 PM