Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2010-09-15
19
Medium Priority
?
584 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

636 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