AXForum  
Вернуться   AXForum > Microsoft Dynamics AX > DAX: Программирование
DAX
Забыли пароль?
Зарегистрироваться Правила Справка Пользователи Сообщения за день Поиск Все разделы прочитаны

 
 
Опции темы Поиск в этой теме Опции просмотра
Старый 17.10.2019, 11:13   #1  
Ventainais is offline
Ventainais
Участник
 
13 / 10 (1) +
Регистрация: 31.08.2019
Unable to retrieve TypeId from DocuRef table for SSRS report
Good day.

My client needs a new report and for the report I have to check customer and vendor attachments.
When I test the table selection in a job, the DocuRef.TypeId has a value, but when I print the report the field is empty.
The report has no special design, it is just a simple table.

I need the TypeId for the if statement, but it looks as it never fetches the value.

I created a table "testTable" for testing to see what values are fetched and after I execute the report I see that TypeId is empty in all rows but the table DocuRef itself has no row with an empty TypeId field.

I am doing something wrong but I just can't see the error.
I hope you could help me.

Code below.
X++:
public void processReport()
{
    CustTable   custTable;
    VendTable   vendTable;
    DocuRef     docuRef;
    

    LogisticsPostalAddress          postalAddress;
    DirPartyLocation                    partyLocation;
    LogisticsAddressCountryRegion   addressCountryRegion;
    CustVendAC                      custVendAcc;

    utcDateTime                     startDateTime, endDateTime;

    TestTable_t    testTable;
    #define.SPP('URL')//TODO:Testing


    startDateTime   = DateTimeUtil::applyTimeZoneOffset(DateTimeUtil::getSystemDateTime(), DateTimeUtil::getCompanyTimeZone());
    endDateTime     = DateTimeUtil::addYears(startDateTime, -1);



   while select createdBy, createdDateTime, TypeId from docuRef
        where docuRef.RefTableId    == tableNum(CustTable)  &&
              docuRef.RefCompanyId  == curext()             &&
             (docuRef.createdDateTime >= startDateTime   &&
              docuRef.createdDateTime <= endDateTime     &&
              docuRef.TypeId == #SPP) || docuRef.TypeId != #SPP
        join AccountNum, Party from custTable
            where custTable.RecId       == docuRef.RefRecId     &&
                  custTable.Blocked     == CustVendorBlocked::No
        exists join partyLocation
            where partyLocation.IsPrimary   == true   &&
                  partyLocation.Party       == custTable.Party
        exists join postalAddress
            where postalAddress.Location == partyLocation.Location
        exists join addressCountryRegion
            where addressCountryRegion.CountryRegionId      == postalAddress.CountryRegionId    &&
                  addressCountryRegion.SanctionedCountry_mb == NoYes::Yes
    {
        tmpCustVendListTable.clear();
        testTable.clear();

        if (custVendAcc != custTable.AccountNum)
        {
            custVendAcc = custTable.AccountNum;
        }
        else
        {
            if (docuRef.TypeId != #SPP)
                continue;
        }

        tmpCustVendListTable.BPAccountNum       = custVendAcc;
        tmpCustVendListTable.BPName             = custTable.name();

        testTable.BPAccountNum       = custVendAcc;
        testTable.BPName             = custTable.name();

        testTable.TypeId             = docuRef.TypeId;


        if (docuRef.TypeId == #SPP)
        {
            tmpCustVendListTable.ResponsibleUser    = XUserInfo::find(false, docuRef.createdBy).name;
            tmpCustVendListTable.ScreeningDateTime  = docuRef.createdDateTime;

            testTable.ResponsibleUser    = XUserInfo::find(false, docuRef.createdBy).name;
            testTable.ScreeningDateTime  = docuRef.createdDateTime;
        }

        tmpCustVendListTable.insert();
        testTable.insert();
    }
}


Нажмите на изображение для увеличения
Название: CustVendSanctionList.gif
Просмотров: 239
Размер:	4.8 Кб
ID:	12397
Старый 17.10.2019, 18:49   #2  
dech is offline
dech
Участник
Аватар для dech
Самостоятельные клиенты AX
 
642 / 347 (13) ++++++
Регистрация: 25.06.2009
Адрес: Омск
Записей в блоге: 3
It seems that your startDateTime is greater than endDateTime.
try to find out what is inside:
X++:
info(strFmt("%1 %2", startDateTime, endDateTime));
Next step is to define conditions correctly. I guess you missed braces, because AND priority is higher than OR.
X++:
        where docuRef.RefTableId    == tableNum(CustTable)  &&
              docuRef.RefCompanyId  == curext()             &&
             (docuRef.createdDateTime >= startDateTime   &&
              docuRef.createdDateTime <= endDateTime     &&
              docuRef.TypeId == #SPP) || docuRef.TypeId != #SPP
__________________
// no comments
Старый 17.10.2019, 20:32   #3  
Ventainais is offline
Ventainais
Участник
 
13 / 10 (1) +
Регистрация: 31.08.2019
Thank You for the reply.

The date variables are correct.

NAME: endDateTime
VALUE:17.10.2019 20:18:39

NAME: startDateTime
VALUE: 17.10.2018 20:18:39

I use them to fetch DocuRef records which are a year old from current day and have TypeId == URL.
The "or" statement is used without the date period.

The complete requirement for data selection is as follows:

Report should show all active BP (CustTable.Blocked=“no“ and VendTable. Blocked =“no“) related records in DocuRef table
where DocuRef.Type= “URL” if primary address (DirPartyPostalAddressView IsPrimary=”YES”)
country has mark “YES” “Sanctioned country” and last screening date is older than one year from today
and all active debtors and vendors (CustTable.Blocked=“no“ and VendTable. Blocked =“no“) records
with primary address (DirPartyPostalAddressView IsPrimary=”YES”)
country has mark “YES” “Sanctioned country” and without related records in DocuRef table where DocuRef.Type= “URL”.

I think the while select statement looks correct.

I am just testing on CustTable.
Старый 18.10.2019, 10:45   #4  
dech is offline
dech
Участник
Аватар для dech
Самостоятельные клиенты AX
 
642 / 347 (13) ++++++
Регистрация: 25.06.2009
Адрес: Омск
Записей в блоге: 3
I see in your code if startDateTime contains current 2019 year then endDateTime should contain the date of 2018 year:
X++:
endDateTime     = DateTimeUtil::addYears(startDateTime, -1);
Here you substract the value of 1 year from startDateTime and assign result to endDateTime. Such conditions do not allow you to get something else than nothing.
Цитата:
Сообщение от Ventainais Посмотреть сообщение
I think the while select statement looks correct.
No, it doesn't look correct. Due to your current conditions in a case when docuRef.TypeId != #SPP you should pull the records for any company (RefCompanyId) and any table (RefTableId). I'm sure you pulled the records with empty TypeId field right for this case. Enclose following code in additional braces and you will see the difference.
X++:
             (docuRef.createdDateTime >= startDateTime   &&
              docuRef.createdDateTime <= endDateTime     &&
              docuRef.TypeId == #SPP) || docuRef.TypeId != #SPP
P.S. if endDateTime is current date and time you can omit this condition and wrinte only
X++:
docuRef.createdDateTime >= startDateTime
__________________
// no comments

Последний раз редактировалось dech; 18.10.2019 в 10:50.
Старый 18.10.2019, 19:00   #5  
Ventainais is offline
Ventainais
Участник
 
13 / 10 (1) +
Регистрация: 31.08.2019
Цитата:
I see in your code if startDateTime contains current 2019 year then endDateTime should contain the date of 2018 year:
Цитата:
Here you substract the value of 1 year from startDateTime and assign result to endDateTime. Such conditions do not allow you to get something else than nothing.
Not really sure what You mean by this. The two dateTime variables are used to fetch DocuRef records by field CreatedDateTime on one year period from current date.

Цитата:
No, it doesn't look correct. Due to your current conditions in a case when docuRef.TypeId != #SPP you should pull the records for any company (RefCompanyId) and any table (RefTableId). I'm sure you pulled the records with empty TypeId field right for this case. Enclose following code in additional braces and you will see the difference.
This got me thinking and I rewrote the while select statement as follows:

X++:
while select createdBy, createdDateTime, TypeId from docuRef
        where docuRef.RefTableId    == tableNum(CustTable)  &&
             ((docuRef.createdDateTime >= endDateTime   &&
              docuRef.createdDateTime <= startDateTime     &&
              docuRef.TypeId == #SPP) || docuRef.TypeId != #SPP)
        join AccountNum, Party from custTable
            where custTable.RecId       == docuRef.RefRecId     &&
                  custTable.dataAreaId  == docuRef.RefCompanyId &&
                  custTable.Blocked     == CustVendorBlocked::No
        exists join partyLocation
            where partyLocation.IsPrimary   == true   &&
                  partyLocation.Party       == custTable.Party
        exists join postalAddress
            where postalAddress.Location == partyLocation.Location
        exists join addressCountryRegion
            where addressCountryRegion.CountryRegionId      == postalAddress.CountryRegionId    &&
                  addressCountryRegion.SanctionedCountry_mb == NoYes::Yes
    {
        tmpCustVendListTable.clear();
        
        if (custVendAcc != custTable.AccountNum)
        {
            custVendAcc = custTable.AccountNum;
        }
        else
        {
            if (docuRef.TypeId != #SPP)
                continue;
        }

        tmpCustVendListTable.BPAccountNum       = custVendAcc;
        tmpCustVendListTable.BPName             = custTable.name();

        if (docuRef.TypeId == #SPP)
        {
            tmpCustVendListTable.ResponsibleUser    = XUserInfo::find(false, docuRef.createdBy).name;
            tmpCustVendListTable.ScreeningDateTime  = docuRef.createdDateTime;
        }

        tmpCustVendListTable.insert();
    }
Now when I execute the report I get the results I need.
Thank you dech.
Теги
axapta 2012, docuref, missing value, ssrs, typeid

 

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
stephenmann: Technical History of Dynamics AX - From Axapta 3.0 to AX2012 Blog bot DAX Blogs 5 03.03.2017 10:22
emeadaxsupport: Vendor invoice - A currency to convert from is required to retrieve exchange rate information when trying to access a Vendor Invoice that is linked to a Purchase Order Blog bot DAX Blogs 0 05.12.2014 19:11
sumitsaxfactor: Building a simple report – Using Report Data Provider Blog bot DAX Blogs 9 11.09.2012 10:17
emeadaxsupport: Opening a Report on AX 2012 raises infolog error: The default Report Server Configuration ID could not be found in the SRSServers table. Blog bot DAX Blogs 0 02.12.2011 00:13
dynamic-ax.co.uk: Import Emails from Outlook 2007 into Dynamics AX 2009 Blog bot DAX Blogs 1 03.07.2009 07:17
Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск
Опции просмотра

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.
Быстрый переход

Рейтинг@Mail.ru
Часовой пояс GMT +3, время: 21:18.
Powered by vBulletin® v3.8.5. Перевод: zCarot
Контактная информация, Реклама.