Using Union with nulls in Sql Reporting 2005

[gtjr921]

I am new to Sql Reporting so I will try to explain this as best i can.

I have a sql query that has two unions with the same table
When i run the query I get the expected results.
When i put the fields on the report not all of the data shows up.

1. The data in my first query only displays one row for my results even though there are 5 rows.
The data in my third query for the total calls does not display, I have the field set in the layout. I wanted to just use the reporting to give me the sum of all the calls for each caller, but it would only give me the sum for each month not the year.
SUM(CASE WHEN Direction = '2' and LEN(Targetnum) = 7 THEN 1 ELSE 0 END) AS [TotYRLocalCallCount],
SUM(CASE WHEN Direction = '2' and LEN(Targetnum) >= 10 THEN 1 ELSE 0 END) AS [TotYRLDCalls]

Here's My Query

--query 1 Top 5 Area Codes
select top (5) (substring(Targetnum,1,3)) As [TopAreaCodes],
datepart(Month,convert(datetime,convert (varchar(8), localday))) as [Month],
datepart(Year,convert(datetime,convert (varchar(8), localday))) As [Year],
null as CallerName,null As TalkTimeTot,
null As AvgCallLengthDay,null AS TimeMonthTest,
null AS LocCallCount,null as TotLDCallsPerMonth,
null as AvgLDCallsPerDay,null as [411Ccount],null As AvgTalk,null as [TotYRLocalCallCount], null as [TotYRLDCalls]  from cdrmain 
where Targetnum like '[2-9]%' and  Targetnum not like '800%' or
Targetnum not like '[0-1]%' 
and Direction ='2'
And LEN(Targetnum) >= 10

GROUP BY datepart(Month,convert(datetime,convert (varchar(8), localday))),
datepart(Year,convert(datetime,convert (varchar(8), localday))),targetnum

--Query 2 Long Distance Call Details

Union All

SELECT null as [TopAreaCodes],
datepart(Month,convert(datetime,convert (varchar(8), localday))) As [Month],
datepart(Year,convert(datetime,convert (varchar(8), localday))) As [Year],
CallerName,
Sum (TalkDuration) /60 As TalkTimeTot,
 sum (TalkDuration) /60 /21.5 As AvgCallLengthDay,
sum (TalkDuration) /60  As TimeMonthTest,
 SUM(CASE WHEN Direction = '2' and  LEN(Targetnum) = 7  THEN 1 ELSE 0 END) AS [LocalCallCount],
  SUM(CASE WHEN Direction = '2' and  LEN(Targetnum) >= 10  THEN 1 ELSE 0 END) AS [TotLDCallsPerMonth],
SUM(CASE WHEN Direction = '2' and  LEN(Targetnum) >= 10  THEN 1 ELSE 0 END) /21.5 AS [AvgLDCallsPerDay],
SUM(CASE WHEN TargetNum = '411' THEN 1 ELSE 0 END) AS [411Ccount],
Avg (TalkDuration)/60 As AvgTalk,null as [TotYRLocalCallCount], null as [TotYRLDCalls] 
FROM  CDRMAIN
Where Len (TargetNum) >= 10 or TargetNum ='411' or  Len (TargetNum) >= 7
GROUP BY datepart(Month,convert(datetime,convert (varchar(8), localday))),
datepart(Year,convert(datetime,convert (varchar(8), localday))), CallerName
HAVING  HAVING  
 (CallerName = 'CallerName1')Or
 (CallerName = 'CallerName2')Or
 (CallerName = 'CallerName2')

--year to date query 3 get Total calls 
Union All

SELECT null as [TopAreaCodes],
null as [Month],
null As [Year],
CallerName,null As TalkTimeTot,
null As AvgCallLengthDay,null AS TimeMonthTest,
null AS LocCallCount,null as TotLDCallsPerMonth,
null as AvgLDCallsPerDay,null as [411Ccount],null As AvgTalk, SUM(CASE WHEN Direction = '2' and  LEN(Targetnum) = 7  THEN 1 ELSE 0 END) AS [TotYRLocalCallCount],
  SUM(CASE WHEN Direction = '2' and  LEN(Targetnum) >= 10  THEN 1 ELSE 0 END) AS [TotYRLDCalls] 
FROM  CDRMAIN
Where Len (TargetNum) >= 10 or TargetNum ='411' or  Len (TargetNum) >= 7
GROUP BY CallerName
HAVING  
 (CallerName = 'CallerName1')Or
 (CallerName = 'CallerName2')Or
 (CallerName = 'CallerName2')

order by datepart(Month,convert(datetime,convert (varchar(8), localday))) Desc,
datepart(Year,convert(datetime,convert (varchar(8), localday))) Desc

 

[Catadmin]

If you're looking for the data in the Data tab of Reporting Services (after executing the query to make sure it returns), it won't display all rows. This is normal.

HOWEVER, if you've cut-n-pasted your query into a new data set, SRS might be cutting it off after a certain line. It might be that it only has X amount of space available for a command.

My suggestion is to throw your query into a stored procedure on the database then run EXEC <MyProc> as your DataSet instead of trying to run the whole query. See if that resolves your problem.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[gtjr921]

Maybe you can help me figure out the best way for me to do this report.
I am thinking it may be better if I seperated each of my queries to different datasets.

I am not sure how to display all of the data into one table or at least so it looks like it is in one table.
For instance
MY first query gets the top 5 area codes
dialed
second query gets call details by caller grouped by month
the last query gets the total calls for the year.
(There may be a better way to do that query.
Of course i took out the null colums I select from when i seperated my queries.
so right now my results look something like

Month	Caller	Avg LD Day   AvgLeDay	
						
Sept06						
	Caller1	  0.27	0.55	12		
						
	Caller2	  0	0.32	7		
Oct 06						
						
	Caller 1	0.046	0			
						
	Caller2	  0	0.52	10	
then the next table would be like
Caller Loc YtoD ld y to D
		
Caller1	  1	  6
Caller2	  2	  4

then my area code table just show the top 5 area codes
like
Top 5 A. Codes
603
937
212
859
206

Is there a way to do this so it will look more uniform?
Or maybe a better query so the results are easier to manipulate with sql reporting?

 

[Catadmin]

The UNION part of your query does make it difficult since you're getting different result sets with each part of the query, so yes, breaking it up into separate queries would make sense.

What I do in instances like this is nest tables on the report. Or nest sub-reports within a "master" table. You put the most detailed information in the inner-most table/sub-report, then the middle info as the second layer, then the highest level detail is the outer-most table.

If you use sub-reports, you'll have to create report parameters to pass between the subs. If you use nested tables, you shouldn't have to worry about parameters.

Does that help? Wish I could draw a picture of the nested tables, but this forum isn't exactly picture friendly. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[gtjr921]

I'll google nested tables and see what i can do thanks for the info.

 

[xlbo]

I have found this group to be very useful - couple of MS developers in there who were on the RS build team....

http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question