locked
business data list webpart with date filter RRS feed

  • Question

  •  

    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

  • 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