Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 585
  • Last Modified:

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

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
Mounikaa
Asked:
Mounikaa
  • 10
  • 8
1 Solution
 
MounikaaAuthor Commented:
Hi,

Please let me know the solution. Its urgetnt!!!
0
 
aelliso3Commented:
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
 
MounikaaAuthor Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
MounikaaAuthor Commented:
My data source is a stored procedure.
0
 
aelliso3Commented:
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
 
aelliso3Commented:
If you just try the expression below, what does it give you?  (w/o the CDATE)
=MAX(Fields!col.Value)
0
 
MounikaaAuthor Commented:


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

0
 
aelliso3Commented:
Is it possible to get a screen shot of the design view (not the data itself)
 
0
 
MounikaaAuthor Commented:
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
 
aelliso3Commented:
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
 
MounikaaAuthor Commented:

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
 
aelliso3Commented:
Oh ... so it just the last 7 days, and not the previous calendar week ... correct?
0
 
MounikaaAuthor Commented:
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
 
MounikaaAuthor Commented:
yes.. it is just the last week
0
 
aelliso3Commented:
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
 
MounikaaAuthor Commented:
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
 
aelliso3Commented:
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
 
MounikaaAuthor Commented:
k thanks for your help.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now