SSRS skipping records

Question

SSRS skipping records

RG-801534

Mr or Mrs. 500

Points: 546
More actions
July 7, 2009 at 8:53 am

#211832

Hello all,
I have an issue with NULL values.
I am working with timesheets.
If a person do not have any entries for a week, ssrs is simply
skipping his name from the list.
But I want it to display his name and assign '-' for the days which he has not worked
this is what I could see in data tab:
employeename 6/216/226/236/246/256/266/27
amanda
brandon3.003.003.006.005.005.503.50
chris8.005.005.004.002.00
dennis3.504.002.001.503.00
elaine
francis5.503.758.509.50
This is how it's appearing in preview tab:
employeename6/216/226/236/246/256/266/27
brandon3.003.003.006.005.005.503.50
chris-8.005.005.004.002.00-
dennis3.504.002.001.503.00--
francis5.503.758.509.50---
Please let me know how I can acheive this.
Thanks,
RG

Viewing 15 posts - 1 through 15 (of 15 total)

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

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

Could you please provide a bit more detail? In which type of report object are you trying to display this information, Matrix, Table, List? Are you doing any grouping or aggregation?

I created a very simple report using your sample data which when I include it in a table object I am able to see the rows which contain NULL Values. I believe it's an issue with your grouping/aggregation that's removing the null values. Also check for any filters you have on your groups or other objects.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 2

you'll need to post your query and table structure for us to help you more.

i suspect your null values are being removed by aggregation, or if you have no entries in the table that list the hours worked for "amanda" then you will need to do something like

select name,"myexistingquery".* from employee left outer join "myexistingquery" on employee.id="myexistingquery.employeeid

obviously that's not valid syntax.......

MVDBA

RG-801534 Mr or Mrs. 500 Points: 546 More actions · Answer 3

Thanks for your response Luke,

I am displaying it in Matrix report.

There is grouping on employeename

But the issue is I was able to see the data correctly in data tab, but it is not appearing the same in preview

RG

RG-801534 Mr or Mrs. 500 Points: 546 More actions · Answer 4

Thanks for your response Michael

Actually, it is not the problem with code. I was able to see the data correctly in SSMS. Everything is fine. But when I am displaying it in Matrix report, it is skipping rows with NULL values

Thanks

RG

Luke L SSC-Dedicated Points: 35547 More actions · Answer 5

Since you are using the MAtrix I'm almost positive these records are being removed by your grouping. Not in the query mind you but in your Matrix.

Please post some sample data so that we might better help you. I have tried to infer some data from your question, but mine might not be in the same format as yours and may well be the difference between mine workings and yours not.

Please see the following on the best way to get your questions answered faster.

http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

To help us help you read this[/url]For better help with performance problems please read this[/url]

RG-801534 Mr or Mrs. 500 Points: 546 More actions · Answer 6

Luke,

Please find the attachement that has data and my code. I think this makes the idea clear.

Thanks,

RG

Luke L SSC-Dedicated Points: 35547 More actions · Answer 7

Is there any chance you could post your .RDL File?

I modified your test data a bit so that I could use it directly within SSRS (it didn't like all of the table variables...)

Here's how I modified your Code

CREATE Table #t1 ( employeename varchar(130), date datetime, hours numeric (10,2))

Insert INTO #t1

SELECT 'brandon', '2008-11-08','3.4' UNION ALL

SELECT 'brandon', '2008-11-09','4.0' UNION ALL

SELECT 'brandon', '2008-11-10','3.5' UNION ALL

SELECT 'brandon', '2008-11-11','4.5' UNION ALL

SELECT 'brandon', '2008-11-12','7.5' UNION ALL

SELECT 'chris', '2008-11-08','3.5' UNION ALL

SELECT 'chris', '2008-11-09','7.5' UNION ALL

SELECT 'chris', '2008-11-12','4.5' UNION ALL

SELECT 'dennis', '2008-11-08','5.5' UNION ALL

SELECT 'dennis', '2008-11-10','5.0' UNION ALL

SELECT 'dennis', '2008-11-11','5.0' UNION ALL

SELECT 'dennis', '2008-11-12','5.5' UNION ALL

SELECT 'francis', '2008-11-10','2.5' UNION ALL

SELECT 'francis', '2008-11-11','6.5' UNION ALL

SELECT 'francis', '2008-11-12','2.5'

Create table #t2 ( employeename varchar(130), supervisor_id varchar(130))

INSERT INTO #T2

SELECT 'amanda', 'crick' UNION ALL

SELECT 'brandon', 'crick' UNION ALL

SELECT 'chirs', 'crick' UNION ALL

SELECT 'dennis', 'crick' UNION ALL

SELECT 'elaine', 'crick' UNION ALL

SELECT 'douglas', 'klarup' UNION ALL

SELECT 'francis', 'crick' UNION ALL

SELECT 'murray', 'dan' UNION ALL

SELECT 'sarah', 'linda'

-------------------------------------------------------------

/*get the time details of people working under crick*/

-------------------------------------------------------------

/*My Code*/

-------------------------------------------------------------

declare @user table

(date datetime,

hours numeric(10,2),

employeename varchar(130))

insert into @user

select

b.date,

b.hours,

a.employeename

from

(

select employeename from #t2

where supervisor_id='crick'

)a

full join

(

select employeename, sum(hours) 'hours',convert(varchar(10), date, 126)'date'

from #t1

where date>='09-nov-2008'

and date<='15-nov-2008'

and employeename in

(select employeename from #t2

where supervisor_id='crick')

group by convert(varchar(10), date, 126), employeename

)b

on a.employeename=b.employeename

select * from @user

I created a blank report and dropped a Matrix onto it.

In the Row Group I added employeename in the column group I added Date.

I aggregated the hours in the data field using Sum(hours)

When I generate the report I get blank spaces where there were NULLS.

IS this the behavior you are looking for? Again, Make certain your are not Removing your NULLS by aggregation, filters or grouping.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

RG-801534 Mr or Mrs. 500 Points: 546 More actions · Answer 8

Luke,

Unfortunately I can not post the RDL file now. I have done the same as you did except for addding an expression for hours to format NULL value with '-'

IIF(IsNothing(Sum(Fields!hours.Value)),"-",Sum(Fields!hours.Value))

Please find the attached txt document to find the desired O/P format.

Thanks,

RG.

RG-801534 Mr or Mrs. 500 Points: 546 More actions · Answer 9

RG-801534

Mr or Mrs. 500

Points: 546

July 7, 2009 at 1:22 pm

#1021300

Please find the desired O/P format

Luke L SSC-Dedicated Points: 35547 More actions · Answer 10

Is the issue that you're not see the 11/13-11/15 columns at all?

You won't because they don't exist in your data. There is no way to display data that does not exist. What you'd have to do is join to a number/tally/calendar table to create some records that would be like the following

Null as name, 0 as hours, 2008-11-13 as date

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Luke L SSC-Dedicated Points: 35547 More actions · Answer 11

You'll need some sort of outter join to the tally table. Jeff talks a bit about this in the Dozens of Other Uses section of his article on Tally tables http://www.sqlservercentral.com/articles/T-SQL/62867/

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

RG-801534 Mr or Mrs. 500 Points: 546 More actions · Answer 12

getting dates is not my issue here.I have done that by outer joining to another temp table (it is populated by dates)

My issue here is:

The records for employees who do not have any entries are not appearing in the report. Say, Amanda, Chris, Elaine. They did not work that week and have NULL values (hours) for all the days. SSRS report did not display their names at all. I want their names to be displayed an put '-' for all the NULL values.

I hope I gave you a better picture.

Thanks,

RG.

Luke L SSC-Dedicated Points: 35547 More actions · Answer 13

Have a look at the attached XML. It's from the Body of the RDL I wrote that does exactly what you are requesting. check for differences between what I wrote and what you wrote. If those names are not appearing they are being filtered somewhere in your report.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

RG-801534 Mr or Mrs. 500 Points: 546 More actions · Answer 14

Sorry, I am unable to interpret. Please find the XML attached and suggest me where I went wrong

Thanks,

RG.