CODEWITHSUNDEEP
reporting-servicesexpressionmicrosoft-dynamicsdynamics-gp
Pracha Yang
Pracha Yang

Reputation: 11

SSRS sum if series expression error

I'm working with Microsoft dynamics GP, trying to get some data to display in a column chart in SSRS. Below is the query I used to get started

SELECT RTRIM(S.CUSTNMBR) 'Customer ID', RTRIM(C.CUSTNAME) 'Customer Name', S.SOPTYPE, 
CONVERT(varchar(4), YEAR(S.DOCDATE)) +' Q'+ convert(varchar(1), DATEPART("QUARTER",S.DOCDATE)) AS 'YEAR QUARTER',
S.DOCDATE, S.DOCID,SL.QUANTITY
FROM SOP30200 S INNER JOIN SOP30300 SL ON S.SOPNUMBE = SL.SOPNUMBE
INNER JOIN IV00101 IV ON SL.ITEMNMBR = IV.ITEMNMBR
INNER JOIN RM00101 C ON S.CUSTNMBR = C.CUSTNMBR
WHERE S.VOIDSTTS = 0 AND IV.ITMCLSCD NOT IN ('NON INV','TRAINING','LABOR')

In the series properties in SSRS I've added the following to the value field.

=SUM(iif(Fields!SOPTYPE.Value = 3 AND Fields!DOCID.Value = "INV" 
OR Fields!DOCID.Value = "RMAINV", Fields!QUANTITY.Value, 0))

When I preview the report there's no data in the chart. For the chart I'm just using a column chart. Any ideas?

Additional Information: To clarify my above statement. The expression used is for the values in the chart data. When I right click on the columns in design mode, I get the option to select series properties.

Link to picture for the data points I'm working with on the chart. Chart Image

Query I used to confirm the expression: 

SELECT RTRIM(S.CUSTNMBR) 'Customer ID', RTRIM(C.CUSTNAME) 'Customer Name', CONVERT(varchar(4), YEAR(S.DOCDATE)) +' Q'+ convert(varchar(1), DATEPART("QUARTER",S.DOCDATE)) AS 'YEAR QUARTER',
SUM(iif(S.SOPTYPE = 3 AND S.DOCID = 'INV' OR S.DOCID = 'RMAINV', SL.QUANTITY, 0)) 'QUANTITY'
FROM SOP30200 S INNER JOIN SOP30300 SL ON S.SOPNUMBE = SL.SOPNUMBE
INNER JOIN IV00101 IV ON SL.ITEMNMBR = IV.ITEMNMBR
INNER JOIN RM00101 C ON S.CUSTNMBR = C.CUSTNMBR
WHERE S.VOIDSTTS = 0 AND IV.ITMCLSCD NOT IN ('NON INV','TRAINING','LABOR')
GROUP BY S.CUSTNMBR, C.CUSTNAME, CONVERT(varchar(4),YEAR(S.DOCDATE)) +' Q'+ convert(varchar(1), DATEPART("QUARTER",S.DOCDATE))

Edit : format code

Upvotes: 0

Views: 913

Answers (1)

Pracha Yang
Pracha Yang

Reputation: 11

After StevenWhite asked some questions, it started to get me thinking differently about my problem.
So instead on just relying on the chart I added a table to display the data. And it was returning data, but a data value of 0. Therefore I wasn't getting any values returned on my chart. I had made the mistake of assuming that there was not value, but there was a value of zero.

After that, I broke down the sum(if(...)) expression. it turns out that when I used 

=SUM(iif(Fields!SOPTYPE.Value = 3, Fields!QUANTITY.Value, 0))

I received data, but when I used 

=SUM(iif(Fields!DOCID.Value = "INV", Fields!QUANTITY.Value, 0))

I got a warning message: 

Severity    Code    Description Project File    Line    Suppression State    
Warning     [rsAggregateOfMixedDataTypes] The Y expression for the chart ‘Chart7’ 
uses an aggregate function on data of varying data types.  Aggregate functions other than 
First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type.

After doing some internet searches, it was getting an error due to the return data type in Quantity. So I just updated the sum(if(...)) expression to return a CInt. And this fixed the problem.

Updated expression: 

=SUM(iif(Fields!SOPTYPE.Value = 3 AND Fields!DOCID.Value = "INV" 
OR Fields!DOCID.Value = "RMAINV", CInt(Fields!QUANTITY.Value), 0))

But I'm still not sure how one field is returning varying data types, unless the 0 I used for the else portion of the iif is a different data type from the value field.

Upvotes: 1

Related Questions