?
Solved

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

Posted on 2010-09-15
19
Medium Priority
?
582 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

765 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