Report Omitting SQL Data

Question

Report Omitting SQL Data

john 22427

SSC Rookie

Points: 33
More actions
April 13, 2015 at 7:35 pm

#306969

I have a stored procedure that returns a simple table with the following columns:
Type char, Period date, Amount money
In my RDL report definition, I have two column groups. The first groups on 'Type' with filter set to "Type= 'I'". The second groups on 'Period' with filter set to "Type='H'".
Running the procedure in SSMS, the result set includes records as follows:
Type Period Amount
----- ------------- ---------
I 2014-12-31 $500
I 2014-11-30 $200
H 2014-12-31 $1500
H 2014-12-31 $300
H 2014-11-30 $2300
H 2014-10-31 $1400
H 2014-09-30 $700
and so on...
However, when I run the report, it only includes periods of November, October and September and omits all records from the month of December, even though the stored procedure returns 2 'H' records in December.
There are no filters defined for the report other than those specified for the column groups. I even tried re-creating the report with nothing but the two column groups as specified and I get the exact same result.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63428 More actions · Answer 1

Might sound like a dumb question, but what happens if you add a table to the report and drop the fields into a tablix but don't filter it. Just wondering if the data makes it to your report.

john 22427 SSC Rookie Points: 33 More actions · Answer 2

If I add a simple table to the report that includes the 3 fields, I see rows for all months including December.

If I then add a matrix with a single column group on Period, I see a column for every month EXCEPT December.

pietlinden SSC Guru Points: 63428 More actions · Answer 3

If you're using a matrix, you need at least one Row Group and one Column Group as well as one Aggregate...

So... this worked for me:

Row Groups: [PeriodEnd] (or the date column)

Column Groups: [Trans Type]

Value: SUM([Amount])

john 22427 SSC Rookie Points: 33 More actions · Answer 4

I am indeed using a Matrix, but I don't want to group the rows, only the columns. Following your suggestion, I added a simple table to a new report, and I'm seeing the same problem whether I use a Table or a Matrix.

The problem appears to be related to the filter.

The dataset returns some number of records in each period from January-December 2014.

ALL of the records in this sample have a 'Type' field value of 'H'.

Here is a snapshot from SSMS showing records from November and December.

The records are sorted in descending order by Period.

I add a simple table to the RDL with a single Row Group and a column of 'Period'.

I configure the Row Group to group by 'Period'.

I add a Filter to include records where 'Type' = 'H'.

The resulting table does not show any records from December.

If I remove the filter, the table shows all of the data rows.

pietlinden SSC Guru Points: 63428 More actions · Answer 5

John,

Any chance you can upload a copy of your report? The fun part is that you have to provide some data for it. So you have two options:

1. post CREATE TABLE and INSERT statements to populate a dummy table to use as the datasource.

2. create a union statement inside the report (embedded in the report).

I'm just not sure what's going on. If you have to show data for a month where there's no data, then the easiest way to do it is to use a Calendar table. Then you do something like this in your query:

SELECT Calendar.TheYear, Calendar.TheMonth, SUM(OtherTable.Amount)

FROM Calendar c LEFT JOIN OtherTable ot ON (c.TheYear = ot.TheYear AND c.TheMonth=ot.TheMonth)

GROUP BY c.TheYear, c.TheMonth

ORDER BY c.TheYear, c.TheMonth;