Solved

How do I use calculated fields in SSRS to generate automate reports without using parametrs.

Posted on 2010-09-15
19
562 Views
Last Modified: 2012-06-22
I have to convert crystal reports to ssrs manually. In crystal reports, I have a expression like
" date(max(table.col))".
I need to convert that to ssrs. and also I want to know how to use calculated fields to generate automated reports i.e. without using them in text boxes.
I have to create automated report for getting data for previous week without passing parameters.
Please suggest me and let me know if you have any questions.

Thanks,
Mounika
0
Comment
Question by:Mounikaa
  • 10
  • 8
19 Comments
 

Author Comment

by:Mounikaa
Comment Utility
Hi,

Please let me know the solution. Its urgetnt!!!
0
 
LVL 11

Expert Comment

by:aelliso3
Comment Utility
For the first part ... try this:

=CDate(Max(Fields!col.Value))
For the previous week part ... is your datasource a stored procedure, view, or embeded sql in the report?
0
 

Author Comment

by:Mounikaa
Comment Utility
thanks for your response.
i TRIED THAT.
But, I have to use this in calculated field. So. it is giving error saying that I am not supposed to use aggregate functions in calculated fields.
0
 

Author Comment

by:Mounikaa
Comment Utility
My data source is a stored procedure.
0
 
LVL 11

Expert Comment

by:aelliso3
Comment Utility
Since you are using a stored procedure, you can use the code below to set the Start and End times for the previous week
Let me try something on a test report and I'll get back to you on the aggregate calc part

DECLARE @CurrentDate DATE

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME



SET @CurrentDate = GETDATE()

--Iif datepart(dw, @CurrentDate)

SELECT @StartDate = DATEADD(d,(-(DATEPART(dw, @CurrentDate))+1)-7, @CurrentDate)

	, @EndDate = CONVERT(VARCHAR(10), (DATEADD(d,(-(DATEPART(dw, @CurrentDate))), @CurrentDate))) + ' 11:59:59 PM'

	

Open in new window

0
 
LVL 11

Expert Comment

by:aelliso3
Comment Utility
If you just try the expression below, what does it give you?  (w/o the CDATE)
=MAX(Fields!col.Value)
0
 

Author Comment

by:Mounikaa
Comment Utility


It is giving an error that I am should not use
Aggregate functions in Calculated fields.

0
 
LVL 11

Expert Comment

by:aelliso3
Comment Utility
Is it possible to get a screen shot of the design view (not the data itself)
 
0
 

Author Comment

by:Mounikaa
Comment Utility
One more thing.

I need to mention one more date other than start, end dates.
i.e create date.

Let me give clear explanation.

Start Date: Select changes with “Date/Time Change Begins” after Sunday at midnight (1 week prior)

o        E.g., if report is run on Monday 9/20 – changes implemented after Midnight on 9/12


 End Date:·         Select changes with “Date/Time Change Begins” before Sunday at midnight (1 day prior)

o        E.g., if report is run on Monday 9/20 – changes implemented before Midnight on 9/19


 Create Date:·         Select changes with “Change Created” after current date – 14 days at 5 PM

·         E.g., if report is run on Monday 9/20 – changes created after 5 PM on 9/6


Can you give me code in stored procedure for getting the automated report without using any parameters. I think, No need to use calculated fields if this procedure gives the report directly.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 11

Accepted Solution

by:
aelliso3 earned 500 total points
Comment Utility
Is this correct?  (this is the what the code below will produce

With 9/20/2010
       @StartDate      = 09/12/2010 12:00AM
       @EndDate       = 09/18/2010 11:59PM
       @CreateDate  = 09/06/2010 5:00PM

With Current Date  (9/15/2010)
       @StartDate      = 09/05/2010 12:00AM
       @EndDate       = 09/11/2010 11:59PM
       @CreateDate  = 09/1/2010 5:00PM

DECLARE @CurrentDate DATE

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

DECLARE @CreateDate DATETIME





SET @CurrentDate = GetDate()



SELECT @StartDate = DATEADD(d,(-(DATEPART(dw, @CurrentDate))+1)-7, @CurrentDate)

	, @EndDate = CONVERT(VARCHAR(10), (DATEADD(d,(-(DATEPART(dw, @CurrentDate))), @CurrentDate))) + ' 11:59:59 PM'

	, @CreateDate = CONVERT(VARCHAR(10), (DATEADD(d,(-14), @CurrentDate))) + ' 5:00:00 PM'

	

PRINT @StartDate

PRINT @EndDate

PRINT @CreateDate

Open in new window

0
 

Author Comment

by:Mounikaa
Comment Utility

I think, for current date i.e. With Current Date  (9/15/2010)

@StartDate      = 09/08/2010 12:00AM
       @EndDate       = 09/13/2010 11:59PM
       @CreateDate  = 09/1/2010 5:00PM
0
 
LVL 11

Expert Comment

by:aelliso3
Comment Utility
Oh ... so it just the last 7 days, and not the previous calendar week ... correct?
0
 

Author Comment

by:Mounikaa
Comment Utility
CREATE OR REPLACE PROCEDURE  PROC_NAME

IS
 DECLARE @CurrentDate DATE
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @CreateDate DATETIME
 
SET @CurrentDate = GetDate();
 
BEGIN

 SELECT @StartDate = DATEADD(d,(-(DATEPART(dw, @CurrentDate))+1)-7, @CurrentDate)
      , @EndDate = CONVERT(VARCHAR(10), (DATEADD(d,(-(DATEPART(dw, @CurrentDate))), @CurrentDate))) + ' 11:59:59 PM'
      , @CreateDate = CONVERT(VARCHAR(10), (DATEADD(d,(-14), @CurrentDate))) + ' 5:00:00 PM', OTHER COLS
FROM TABLE;
END PROC_NAME;


Is there any syntax error in this procedure.
I am getting the following error.

Error(4,2): PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following:     begin function package pragma procedure subtype type use    <an identifier> <a double-quoted delimited-identifier> form    current cursor external language

Please let me know the error.
0
 

Author Comment

by:Mounikaa
Comment Utility
yes.. it is just the last week
0
 
LVL 11

Expert Comment

by:aelliso3
Comment Utility
I've never heard of CREATE OR REPLACE in SQL ... I know it works in Oracle. You can use CREATE PROCEDURE, or if it's already there, then use ALTER PROCEDURE. There are a few changes below that may help.
 

CREATE PROCEDURE  PROC_NAME



AS



DECLARE @CurrentDate DATE

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

DECLARE @CreateDate DATETIME

  

SET @CurrentDate = GetDate()

 



 SELECT @StartDate = DATEADD(d,-7, @CurrentDate)

      , @EndDate = CONVERT(VARCHAR(10), (DATEADD(d,(-2), @CurrentDate))) + ' 11:59:59 PM'

      , @CreateDate = CONVERT(VARCHAR(10), (DATEADD(d,(-14), @CurrentDate))) + ' 5:00:00 PM'

      , OTHER COLS



FROM TABLE

END;

Open in new window

0
 

Author Comment

by:Mounikaa
Comment Utility
This is ORACLE PL/SQL.

I am getting the same error even though i tried the same syntax.

Error(4,2): PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following:     begin function package pragma procedure subtype type use    <an identifier> <a double-quoted delimited-identifier> form    current cursor external language
0
 
LVL 11

Expert Comment

by:aelliso3
Comment Utility
you may want to add this to the Oracle thread so that someone there can help out. I am the wrong person to help with Oracle syntax ... sry
0
 

Author Comment

by:Mounikaa
Comment Utility
k thanks for your help.
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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

771 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

11 Experts available now in Live!

Get 1:1 Help Now