Solved

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

Posted on 2010-09-15
19
576 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 8
19 Comments
 

Author Comment

by:Mounikaa
ID: 33683821
Hi,

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

Expert Comment

by:aelliso3
ID: 33683952
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
ID: 33684057
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:Mounikaa
ID: 33684063
My data source is a stored procedure.
0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33684144
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
ID: 33684189
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
ID: 33684222


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

0
 
LVL 11

Expert Comment

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

Author Comment

by:Mounikaa
ID: 33684277
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
 
LVL 11

Accepted Solution

by:
aelliso3 earned 500 total points
ID: 33684365
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
ID: 33684475

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
ID: 33684607
Oh ... so it just the last 7 days, and not the previous calendar week ... correct?
0
 

Author Comment

by:Mounikaa
ID: 33684637
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
ID: 33684642
yes.. it is just the last week
0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33684743
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
ID: 33684843
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
ID: 33684891
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
ID: 33684950
k thanks for your help.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

724 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