Solved

How TO Create Daily,Weekly, Monthly Report In SQL Server 2005 Reporting Services

Posted on 2006-06-19
32
1,344 Views
Last Modified: 2008-01-09
Greetings,

I am trying to create a report from SQL Server 2005 Reporting Services that shows the following:


Daily Report
Details                                                  

Date     Opened     Resolved                                        
1/3/2006     26      20                                        
1/4/2006     87      90                                        
1/5/2006     2       2                                        
1/6/2006     26     24                                        
1/7/2006     15     15                                        
1/8/2006     19     10                                        
1/9/2006     10     9                                        
1/10/2006     7     4    

Weekly Report
Week Ending     Opened     Resolved    
3/5/2006                  22     21    
3/12/2006                 64     50    
3/19/2006                27            26    
3/26/2006               50            30    
4/2/2006                 50            25    

Monthly
         
Month     Opened     Resolved    
February     150     145    
March         526     500    
April           89     88    
                         
All three of these reports are based on the entered(date) field and the closed(date) field in a table called calls.
0
Comment
Question by:Omega002
  • 18
  • 12
32 Comments
 
LVL 25

Expert Comment

by:jrb1
ID: 16936230
What is your table like?  You have a date and a status?

daily:

select yourdate
, sum(case yourstatus when = 'O' then 1 else 0) as opened
, sum(case yourstatus when = 'C' then 1 else 0) as closed
from yourtable
group by yourdate

weekly:

SELECT DATEADD(dd, 7-DATEPART(dwyourdate), yourdate) as DateEnding
, sum(case yourstatus when = 'O' then 1 else 0) as opened
, sum(case yourstatus when = 'C' then 1 else 0) as closed
from yourtable
group by DATEADD(dd, 7-DATEPART(dwyourdate), yourdate)

Monthly

select year(yourdate) + "/" + month(yourdate) as MonthDate
, sum(case yourstatus when = 'O' then 1 else 0) as opened
, sum(case yourstatus when = 'C' then 1 else 0) as closed
from yourtable
group by year(yourdate) + "/" + month(yourdate)
0
 

Author Comment

by:Omega002
ID: 16936291
the fields that determine the open tickets are entered(date the ticket was open and closed(date the ticket was closed).
0
 
LVL 25

Expert Comment

by:jrb1
ID: 16936362
OK, for daily how is:

select opendate, sum(opencnt) as opencnt, sum(closecnt) as closecnt from
(select opendate, 1 as opencnt, 0 as closecnt
from yourtable
union all
select closedate, 0, 1
from yourtable
where closedate is not null)
0
 
LVL 25

Expert Comment

by:jrb1
ID: 16936386
Sorry, to that daily we need a group by:

select opendate, sum(opencnt) as opencnt, sum(closecnt) as closecnt from
(select opendate, 1 as opencnt, 0 as closecnt
from yourtable
union all
select closedate, 0, 1
from yourtable
where closedate is not null)
group by opendate

And if that is OK, then the weekly and monthly could be:

select DATEADD(dd, 7-DATEPART(dw, opendate), opendate) as WeekEnding, sum(opencnt) as opencnt, sum(closecnt) as closecnt from
(select opendate, 1 as opencnt, 0 as closecnt
from yourtable
union all
select closedate, 0, 1
from yourtable
where closedate is not null)
group by DATEADD(dd, 7-DATEPART(dw, opendate), opendate)

Then monthly:

select Year(opendate) + '/' + Month(opendate) as MnthDate, sum(opencnt) as opencnt, sum(closecnt) as closecnt from
(select opendate, 1 as opencnt, 0 as closecnt
from yourtable
union all
select closedate, 0, 1
from yourtable
where closedate is not null)
group by Year(opendate) + '/' + Month(opendate)
0
 

Author Comment

by:Omega002
ID: 16942409
Listed below is a layout of the ticket table:
Column Name      Data Type      Length
ticketid                      int      4
entered                      datetime      8
closed                      datetime      8
problem                      text      16      

Based on this table alone I am trying to produce the following queries results:


Daily Report
Details                                                  

Date     Opened     Resolved                                        
1/3/2006     26      20                                        
1/4/2006     87      90                                        
1/5/2006     2       2                                        
1/6/2006     26     24                                        
1/7/2006     15     15                                        
1/8/2006     19     10                                        
1/9/2006     10     9                                        
1/10/2006     7     4    

Weekly Report
Week Ending     Opened     Resolved    
3/5/2006                  22     21    
3/12/2006                 64     50    
3/19/2006                27            26    
3/26/2006               50            30    
4/2/2006                 50            25    

Monthly
         
Month     Opened     Resolved    
February     150     145    
March         526     500    
April           89     88    

This is the query I have thus far:
select count(*),entered,closed from ticket
where ticket.closed IS NULL

So do the queries you replied with work with the information I provided you?
0
 

Author Comment

by:Omega002
ID: 16942475
I tried your daily query suggestion and it produced the following error:

Query:
select entered, sum(entered) as opencnt, sum(closed) as closecnt from
(select entered, 1 as opencnt, 0 as closecnt
from ticket
union all
select closed, 0, 1
from ticket
where closed is not null)
group by entered

Results:
Server: Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'group'.
0
 

Author Comment

by:Omega002
ID: 16943112
Hello Hello are you there?
0
 
LVL 25

Expert Comment

by:jrb1
ID: 16943222
Yep, just not that early in the morning.

Try this:

select entered, sum(opencnt) as opencnt, sum(closecnt) as closecnt from
(select entered, 1 as opencnt, 0 as closecnt
from ticket
union all
select closed, 0, 1
from ticket
where closed is not null)
group by entered
0
 

Author Comment

by:Omega002
ID: 16944026
I still got the same error listed below:
Results:
Server: Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'group'.
0
 
LVL 25

Expert Comment

by:jrb1
ID: 16948855
Does the inner query work?

select entered, 1 as opencnt, 0 as closecnt
from ticket
union all
select closed, 0, 1
from ticket
where closed is not null

I do this in SQL Server all the time...I'll see if something is different about reporting services.
0
 

Author Comment

by:Omega002
ID: 16950745
The query listed below works.

select entered, 1 as opencnt, 0 as closecnt
from ticket
union all
select closed, 0, 1
from ticket
where closed is not null

So this will be the syntax for the daily,weekly, and monthly reports? Can you do the daily portion so I will have an idea for the weekly and monthly?
0
 
LVL 25

Expert Comment

by:jrb1
ID: 16950976
Is entered a date only, or does it also have a time part?
0
 

Author Comment

by:Omega002
ID: 16951006
it has a time stamp as well but I can play with the date format in SQL Report Services.
0
 
LVL 25

Expert Comment

by:jrb1
ID: 16951273
Sure.  You should also be able to sum the opencnt and closecnt columns and group by the date.  Can you try that?
0
 

Author Comment

by:Omega002
ID: 16951322
JRB1,

Here's a query that was developed by a vendor to capture percentages using the daily, weekly, and monthly reports:
=IIf(Parameters!period.Value="Monthly",
"SELECT DATENAME(mm,entered) AS period, DATEPART(mm,entered), " &
"SUM(CASE source_code WHEN 1 THEN 1 ELSE 0 END)AS Phone, " &
"SUM(CASE source_code WHEN 2 THEN 1 ELSE 0 END)AS Emails, " &
"SUM(CASE source_code WHEN 3 THEN 1 ELSE 0 END)AS Web, " &
"SUM(CASE source_code WHEN 4 THEN 1 ELSE 0 END)AS Letters, " &
"SUM(CASE source_code WHEN 5 THEN 1 ELSE 0 END)AS Other, " &
"SUM(CASE source_code WHEN 6 THEN 1 ELSE 0 END)AS Alarms, " &
"SUM(CASE source_code WHEN 7 THEN 1 ELSE 0 END) AS Monitoring, " &
"COUNT(*) AS Total FROM ticket  WHERE entered between '" & Parameters!startdate.Value & "' and '" & Parameters!enddate.Value  &
"' GROUP by DATENAME(mm,entered),DATEPART(mm,entered) ORDER BY DATEPART(mm,entered)",
IIf(Parameters!period.Value="Weekly",
"SET DATEFIRST 7; SELECT CONVERT(VARCHAR,(CASE DATEPART(dw,entered) WHEN 2 THEN DATEADD(DAY,6,entered) " &
                          "WHEN 3 THEN DATEADD(DAY,5,entered) " &
                "WHEN 4 THEN DATEADD(DAY,4,entered) " &
                          "WHEN 5 THEN DATEADD(DAY,3,entered) " &
                          "WHEN 6 THEN DATEADD(DAY,2,entered) " &
                          "WHEN 7 THEN DATEADD(DAY,1,entered) " &
                          "ELSE entered END),101) AS period, " &
"SUM(CASE source_code WHEN 1 THEN 1 ELSE 0 END)AS Phone, " &
"SUM(CASE source_code WHEN 2 THEN 1 ELSE 0 END)AS Emails, " &
"SUM(CASE source_code WHEN 3 THEN 1 ELSE 0 END)AS Web, " &
"SUM(CASE source_code WHEN 4 THEN 1 ELSE 0 END)AS Letters, " &
"SUM(CASE source_code WHEN 5 THEN 1 ELSE 0 END)AS Other, " &
"SUM(CASE source_code WHEN 6 THEN 1 ELSE 0 END)AS Alarms, " &
"SUM(CASE source_code WHEN 7 THEN 1 ELSE 0 END) AS Monitoring, " &
"COUNT(*) AS Total FROM ticket  WHERE entered between '" & Parameters!startdate.Value & "' and '" & Parameters!enddate.Value  &
"' GROUP by CONVERT(VARCHAR,(CASE DATEPART(dw,entered) WHEN 2 THEN DATEADD(DAY,6,entered) " &
                          "WHEN 3 THEN DATEADD(DAY,5,entered) " &
                "WHEN 4 THEN DATEADD(DAY,4,entered) " &
                          "WHEN 5 THEN DATEADD(DAY,3,entered) " &
                          "WHEN 6 THEN DATEADD(DAY,2,entered) " &
                          "WHEN 7 THEN DATEADD(DAY,1,entered) " &
                           "ELSE entered END),101)" &
" ORDER BY  CONVERT(VARCHAR,(CASE DATEPART(dw,entered) WHEN 2 THEN DATEADD(DAY,6,entered) " &
                          "WHEN 3 THEN DATEADD(DAY,5,entered) " &
                "WHEN 4 THEN DATEADD(DAY,4,entered) " &
                          "WHEN 5 THEN DATEADD(DAY,3,entered) " &
                          "WHEN 6 THEN DATEADD(DAY,2,entered) " &
                          "WHEN 7 THEN DATEADD(DAY,1,entered) " &
                           "ELSE entered END),101)",
"SELECT CONVERT(varchar,entered,101) AS period, " &
"SUM(CASE source_code WHEN 1 THEN 1 ELSE 0 END)AS Phone, " &
"SUM(CASE source_code WHEN 2 THEN 1 ELSE 0 END)AS Emails, " &
"SUM(CASE source_code WHEN 3 THEN 1 ELSE 0 END)AS Web, " &
"SUM(CASE source_code WHEN 4 THEN 1 ELSE 0 END)AS Letters, " &
"SUM(CASE source_code WHEN 5 THEN 1 ELSE 0 END)AS Other, " &
"SUM(CASE source_code WHEN 6 THEN 1 ELSE 0 END)AS Alarms, " &
"SUM(CASE source_code WHEN 7 THEN 1 ELSE 0 END) AS Monitoring, " &
"COUNT(*) AS Total FROM ticket  WHERE entered between '" & Parameters!startdate.Value & "' and '" & Parameters!enddate.Value  &
"' GROUP by convert(varchar,entered,101) ORDER BY convert(varchar,entered,101)"                  
))

Now how would you apply this syntax to what I am trying to capture? This is also working with the ticket table.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:Omega002
ID: 16954352
Also the query you suggested is not giving a true count of the number of tickets that are open and the number of tickets that are closed using the entered date as the number of tickets that are open and the closed date fieild as the number of tickets that are closed.
0
 

Author Comment

by:Omega002
ID: 16963468
Hello hello are you there?
0
 
LVL 25

Expert Comment

by:jrb1
ID: 16964279
=IIf(Parameters!period.Value="Monthly",
"SELECT DATENAME(mm,entered) AS period, DATEPART(mm,entered), " &
"COUNT(*) AS Opened, " &
"SUM(CASE closedate WHEN null THEN 0 ELSE 1 END) AS Closed " &
"WHERE entered between '" & Parameters!startdate.Value & "' and '" & Parameters!enddate.Value  &
"' GROUP by DATENAME(mm,entered),DATEPART(mm,entered) ORDER BY DATEPART(mm,entered)",
IIf(Parameters!period.Value="Weekly",
"SET DATEFIRST 7; SELECT CONVERT(VARCHAR,(CASE DATEPART(dw,entered) WHEN 2 THEN DATEADD(DAY,6,entered) " &
                          "WHEN 3 THEN DATEADD(DAY,5,entered) " &
               "WHEN 4 THEN DATEADD(DAY,4,entered) " &
                          "WHEN 5 THEN DATEADD(DAY,3,entered) " &
                          "WHEN 6 THEN DATEADD(DAY,2,entered) " &
                          "WHEN 7 THEN DATEADD(DAY,1,entered) " &
                          "ELSE entered END),101) AS period, " &
"COUNT(*) AS Opened, " &
"SUM(CASE closedate WHEN null THEN 0 ELSE 1 END) AS Closed " &
"FROM ticket  WHERE entered between '" & Parameters!startdate.Value & "' and '" & Parameters!enddate.Value  &
"' GROUP by CONVERT(VARCHAR,(CASE DATEPART(dw,entered) WHEN 2 THEN DATEADD(DAY,6,entered) " &
                          "WHEN 3 THEN DATEADD(DAY,5,entered) " &
               "WHEN 4 THEN DATEADD(DAY,4,entered) " &
                          "WHEN 5 THEN DATEADD(DAY,3,entered) " &
                          "WHEN 6 THEN DATEADD(DAY,2,entered) " &
                          "WHEN 7 THEN DATEADD(DAY,1,entered) " &
                           "ELSE entered END),101)" &
" ORDER BY  CONVERT(VARCHAR,(CASE DATEPART(dw,entered) WHEN 2 THEN DATEADD(DAY,6,entered) " &
                          "WHEN 3 THEN DATEADD(DAY,5,entered) " &
               "WHEN 4 THEN DATEADD(DAY,4,entered) " &
                          "WHEN 5 THEN DATEADD(DAY,3,entered) " &
                          "WHEN 6 THEN DATEADD(DAY,2,entered) " &
                          "WHEN 7 THEN DATEADD(DAY,1,entered) " &
                           "ELSE entered END),101)",
"SELECT CONVERT(varchar,entered,101) AS period, " &
"COUNT(*) AS Opened, " &
"SUM(CASE closedate WHEN null THEN 0 ELSE 1 END) AS Closed " &
"FROM ticket  WHERE entered between '" & Parameters!startdate.Value & "' and '" & Parameters!enddate.Value  &
"' GROUP by convert(varchar,entered,101) ORDER BY convert(varchar,entered,101)"                  
))
0
 

Author Comment

by:Omega002
ID: 16965804
This query generated some syntax errors.
0
 
LVL 25

Expert Comment

by:jrb1
ID: 16970019
OK, I found a few.  If there are more syntax errors, please post the error message;

=IIf(Parameters!period.Value="Monthly",
"SELECT DATENAME(mm,entered) AS period, DATEPART(mm,entered), " &
"COUNT(*) AS Opened, " &
"SUM(CASE closedate WHEN null THEN 0 ELSE 1 END) AS Closed " &
"from ticket WHERE entered between '" & Parameters!startdate.Value & "' and '" & Parameters!enddate.Value  &
"' GROUP by DATENAME(mm,entered),DATEPART(mm,entered) ORDER BY DATEPART(mm,entered)",
IIf(Parameters!period.Value="Weekly",
"SET DATEFIRST 7; SELECT CONVERT(VARCHAR,(CASE DATEPART(dw,entered) WHEN 2 THEN DATEADD(DAY,6,entered) " &
                          "WHEN 3 THEN DATEADD(DAY,5,entered) " &
               "WHEN 4 THEN DATEADD(DAY,4,entered) " &
                          "WHEN 5 THEN DATEADD(DAY,3,entered) " &
                          "WHEN 6 THEN DATEADD(DAY,2,entered) " &
                          "WHEN 7 THEN DATEADD(DAY,1,entered) " &
                          "ELSE entered END),101) AS period, " &
"COUNT(*) AS Opened, " &
"SUM(CASE closedate WHEN null THEN 0 ELSE 1 END) AS Closed " &
"FROM ticket  WHERE entered between '" & Parameters!startdate.Value & "' and '" & Parameters!enddate.Value  &
"' GROUP by CONVERT(VARCHAR,(CASE DATEPART(dw,entered) WHEN 2 THEN DATEADD(DAY,6,entered) " &
                          "WHEN 3 THEN DATEADD(DAY,5,entered) " &
               "WHEN 4 THEN DATEADD(DAY,4,entered) " &
                          "WHEN 5 THEN DATEADD(DAY,3,entered) " &
                          "WHEN 6 THEN DATEADD(DAY,2,entered) " &
                          "WHEN 7 THEN DATEADD(DAY,1,entered) " &
                           "ELSE entered END),101)" &
" ORDER BY  CONVERT(VARCHAR,(CASE DATEPART(dw,entered) WHEN 2 THEN DATEADD(DAY,6,entered) " &
                          "WHEN 3 THEN DATEADD(DAY,5,entered) " &
               "WHEN 4 THEN DATEADD(DAY,4,entered) " &
                          "WHEN 5 THEN DATEADD(DAY,3,entered) " &
                          "WHEN 6 THEN DATEADD(DAY,2,entered) " &
                          "WHEN 7 THEN DATEADD(DAY,1,entered) " &
                           "ELSE entered END),101)",
"SELECT CONVERT(varchar,entered,101) AS period, " &
"COUNT(*) AS Opened, " &
"SUM(CASE closedate WHEN null THEN 0 ELSE 1 END) AS Closed " &
"FROM ticket  WHERE entered between '" & Parameters!startdate.Value & "' and '" & Parameters!enddate.Value  &
"' GROUP by convert(varchar,entered,101) ORDER BY convert(varchar,entered,101)"))
0
 

Author Comment

by:Omega002
ID: 16970739
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.
0
 
LVL 25

Expert Comment

by:jrb1
ID: 16971512
Everything appears balanced.  Can you try this piece:

="SELECT DATENAME(mm,entered) AS period, DATEPART(mm,entered), " &
"COUNT(*) AS Opened, " &
"SUM(CASE closedate WHEN null THEN 0 ELSE 1 END) AS Closed " &
"from ticket WHERE entered between '" & Parameters!startdate.Value & "' and '" & Parameters!enddate.Value  &
"' GROUP by DATENAME(mm,entered),DATEPART(mm,entered) ORDER BY DATEPART(mm,entered)"
0
 

Author Comment

by:Omega002
ID: 16973607
JBR1 the piece of query still produced this error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.

Create a table called ticket on your end with these two date fields and see if you get the same error:

CREATE TABLE [dbo].[ticket] (
      [ticketid] [int] NOT NULL ,
      [entered] [datetime] NOT NULL ,
      [enteredby] [int] NOT NULL ,
      [modified] [datetime] NOT NULL ,
      [modifiedby] [int] NOT NULL ,
      [closed] [datetime] NULL ,
      [pri_code] [int] NULL ,
      [resolution_date] [datetime] NULL ,
      [cause] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [link_contract_id] [int] NULL ,
      [link_asset_id] [int] NULL ,
      [product_id] [int] NULL ,
      [customer_product_id] [int] NULL ,
      [expectation] [int] NULL ,
      [key_count] [int] NULL ,
      [est_resolution_date_timezone] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [assigned_date_timezone] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [resolution_date_timezone] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [status_id] [int] NULL ,
      [trashed_date] [datetime] NULL ,
      [user_group_id] [int] NULL ,
      [cause_id] [int] NULL ,
      [resolution_id] [int] NULL ,
      [defect_id] [int] NULL ,
      [escalation_level] [int] NULL ,
      [resolvable] [bit] NOT NULL ,
      [resolvedby] [int] NULL ,
      [resolvedby_department_code] [int] NULL ,
      [state_id] [int] NULL ,
      [site_id] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
0
 

Author Comment

by:Omega002
ID: 16980921
Are you there?
0
 

Author Comment

by:Omega002
ID: 16983992
Have  you tried the suggested query using SQL Server 2005 Reportings Services?
0
 

Author Comment

by:Omega002
ID: 16992259
Have you given up on this topic?
0
 
LVL 25

Expert Comment

by:jrb1
ID: 16993476
no, i've been busy, and i don't have an environment I can test in 2005 reporting services at the moment.  I'll see if I can come up with another idea.
0
 
LVL 25

Expert Comment

by:jrb1
ID: 16993485
where do you put the query developed by the vendor?
0
 

Author Comment

by:Omega002
ID: 16993536
This is within Visul Studio which is integrated within SQL Server 2005 Reporting Services.
0
 

Author Comment

by:Omega002
ID: 17000950
JRB1 are you there?
0
 
LVL 16

Accepted Solution

by:
CWS (haripriya) earned 250 total points
ID: 17009024
Hi Omega002,

You have posted your Qn. under "Database" topic area. Since you are using MS SQL, if you give a pointer under the Topic "Microsoft SQL" you will get more Experts for answering your question.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Query combine all charges 6 58
spx for moving values to new table 5 60
vcenter 6 u2 install question 1 85
SQL Update Query 23 82
Introduction: Often, when running a query with joins, the results show up "duplicates", and often, those duplicates can be "eliminated" in the results using DISTINCT, for example. Using DISTINCT is simple: just add it after the SELECT keyword, an…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now