Dynamics Community Forum Thread Details

Hi All,

I'm having a problem with report which I'm putting together.

Basically it should be item movement summary within specific time, everything works fine if just 1 filter is specified but I'm getting above error when adding 3 filters (happens only sometimes with 2).

From whole report I narrowed it down to 3 fields ... which never goes trough.

Strange thing is, that by looking at SQL Profiler the query is not even passed to server, it happens at HQ level.

Can anyone help .....

Thanks in advance

Arthur

//--- Report Summary --- //

Begin ReportSummary
   ReportType = reporttypeMisc
   ReportTitle = "Summary Product Sales Report"
   PageOrientation = pageorientationLandscape
   WordWrap = False
   ShowDateTimePicker = False
   OutLineMode = True
   Groups = 2
   GroupDescription = ""
   DisplayLogo = True
   LogoFileName = "MyLogo.bmp"
   ProcedureCall = ""
   PreQuery1 = "IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'ItemMovementView_Detailed') DROP VIEW ItemMovementView_Detailed"
   PreQuery2 = <BEGIN>
        Create VIEW ItemMovementView_Detailed AS
        SELECT
            CAST(DATEDIFF(dd, 0, [Transaction].Time) as DateTime) as [Date]
            ,[TransactionEntry].ItemID as [ItemID]
            ,MAX([Item].Description) as [ItemDescription]
            ,MAX([Category].Name) as [CategoryName]
            ,MAX(Supplier.SupplierName) as [SuppName]

        FROM [TransactionEntry] WITH (NOLOCK)
            LEFT JOIN [Transaction] WITH (NOLOCK) ON [Transaction].TransactionNumber = TransactionEntry.TransactionNumber AND [Transaction].StoreID = TransactionEntry.StoreID
            LEFT JOIN Item WITH (NOLOCK) ON [Item].ID = [TransactionEntry].ItemID
            LEFT JOIN Supplier WITH (NOLOCK) ON [Item].SupplierID = [Supplier].ID
            LEFT JOIN [Category] WITH (NOLOCK) ON [Category].ID = [Item].CategoryID
        GROUP BY CAST(DATEDIFF(dd, 0, [Transaction].Time) as DateTime), [TransactionEntry].ItemID
<END>
   TablesQueried = "FROM ItemMovementView_Detailed as [I]"
   SelCriteria = ""
   GroupBy = "[I].ItemID"
   SortOrder = ""
End ReportSummary

//--- Title Rows ---//

Begin TitleRow
   Text = "<Store Name>"
   Font = "Arial"
   FontBold = True
   FontSize = 16
   Color = "Blue"
End TitleRow

Begin TitleRow
   Text = "<Report Title>"
   Font = "Arial"
   FontBold = True
   FontSize = 12
   Color = "Black"
End TitleRow

Begin TitleRow
   Text = "Generated On <Report Date>"
   Font = "Arial"
   FontBold = True
   FontSize = 10
   Color = "Black"
End TitleRow

//--- Filters ---//
Begin Filter
   FieldName = "[I].Date"
   FilterOp = reportfilteropBetween
   FilterLoLim = "19/04/2011"
   FilterHilim = "05/05/2011"
   FilterNegated = False
   FilterConnector = reportfilterbooleanconAND
End Filter

Begin Filter
   FieldName = "[I].ItemDescription"
   FilterOp = reportfilteropLike
   FilterLoLim = "Waves"
   FilterHilim = "Waves"
   FilterNegated = False
   FilterConnector = reportfilterbooleanconAND
End Filter

Begin Filter
   FieldName = "[I].CategoryName"
   FilterOp = reportfilteropLike
   FilterLoLim = "HH BATHROOM"
   FilterHilim = "HH BATHROOM"
   FilterNegated = False
   FilterConnector = reportfilterbooleanconAND
End Filter

//--- Columns ---//

Begin Column
   FieldName = "[I].Date"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Last Rcv"
   VBDataType = vbDate
   Formula = "MAX([I].Date)"
   ColHidden = True
   ColNotDisplayable = True
   FilterDisabled = False
   ColWidth = 900
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "[I].ItemDescription"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Description"
   VBDataType = vbString
   Formula = "MAX([I].ItemDescription)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 2115
   GroupMethod = groupmethodNone
   ColFormat = ""
   ColAlignment = flexAlignLeftCenter
End Column

Begin Column
   FieldName = "[I].CategoryName"
   DrillDownFieldName = "Category.Name"
   DrillDownReportName = ""
   Title = "Category"
   VBDataType = vbString
   Formula = "MAX([I].CategoryName)"
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1515
   GroupMethod = groupmethodNone
   ColFormat = ""
   ColAlignment = flexAlignLeftCenter
End Column

Hi Guys,

Just to let you know that I got it sorted ...2 days on something that simple ...

Problem : NULL values !

Some item with no Category or Supplier where throwing out error.

Changing View statement fixed the issue ...

PreQuery2 = <BEGIN>

Create VIEW ItemMovementView_Detailed AS

SELECT

CAST(DATEDIFF(dd, 0, [Transaction].Time) as DateTime) as [Date]

,[TransactionEntry].ItemID as [ItemID]

,MAX([Item].Description) as [ItemDescription]

,MAX(CASE WHEN [Category].Name IS NULL THEN '' ELSE [Category].Name END) as [CategoryName]

,MAX(CASE WHEN Supplier.SupplierName IS NULL THEN '' ELSE Supplier.SupplierName END) as [SuppName]

FROM [TransactionEntry] WITH (NOLOCK)

LEFT JOIN [Transaction] WITH (NOLOCK) ON [Transaction].TransactionNumber = TransactionEntry.TransactionNumber AND [Transaction].StoreID = TransactionEntry.StoreID

LEFT JOIN Item WITH (NOLOCK) ON [Item].ID = [TransactionEntry].ItemID

LEFT JOIN Supplier WITH (NOLOCK) ON [Item].SupplierID = [Supplier].ID

LEFT JOIN [Category] WITH (NOLOCK) ON [Category].ID = [Item].CategoryID

GROUP BY CAST(DATEDIFF(dd, 0, [Transaction].Time) as DateTime), [TransactionEntry].ItemID

<END>

Community site session details

Err: 3704 "Operation is not allowed when the object is closed"

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics RMS (Archived)

Featured topics

Product updates