?
Solved

Today's Date in Report Services 2008?

Posted on 2013-02-01
7
Medium Priority
?
1,597 Views
Last Modified: 2013-02-06
I have Report Services 2008 running against an Oracle database.  The problem occurs when I want to use SYSDATE in my SQL code - SSRS errors out with ORA-00904 "SYDATE": invalid identifier.

Last I checked, SYSDATE worked just fine, valid and all.

To prove this, I run the SQL code through *any* other interface (like Toad, for instance) and it works just fine.  So SYSDATE is valid.  Except for when you're using SSRS 2008, apparently.

So I look across the 'net, and get told "you have to build a parameter" for that.  Fine.  I do, and it's called THISDAY, and it's definition is (Fx) '=Today' <---literally that simple.  Well, now it puts a text block at the top of the report form BEFORE the report runs, looking for a value (from the user, of course) for THISDAY, which has already been defined as being '=Today'.

Aaargh!  Something this simple shouldn't be so flamin' complicated!

How do I use the equivalent of today's date within a SQL query in Microsoft's (Silly) Report Services 2008?

Thanks!  o/
0
Comment
Question by:LongFist
[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
  • 5
  • 2
7 Comments
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 38844527
As per your error ORA-00904 "SYDATE": invalid identifier.

You are not ussing SYSDATE, but SYDATE.
0
 
LVL 1

Author Comment

by:LongFist
ID: 38844536
Typo on my part: rest assured it is rejecting the keyword SYSDATE.   I'll edit that, if possible...
0
 
LVL 1

Author Comment

by:LongFist
ID: 38844615
Okay, I can't edit my initial post.  Pity.  But the problem remains.  SYSDATE doesn't appear to work if called from within a Report Services Query, and for reasons I cannot fathom, I cannot set up a simple "stand in" parameter to provide the value "Now" or "Today".  Seems rather strange.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 38844746
SYSDATE is oracle internal variable and therefore ORA-00904 should not throw this error.
If we want to reproduce this error, perform the following:-

select "SYSDATE" from dual;
       *
ERROR at line 1:
ORA-00904: "SYSDATE": invalid identifier




select 'SYSDATE' FROM DUAL;

'SYSDATE'
--------------------------------
SYSDATE


select SYSDATE  FROM DUAL;
SYSDATE
---------
01-FEB-13


Therefore check for single and double quotes: -
0
 
LVL 1

Author Comment

by:LongFist
ID: 38844867
Okay, this is what the code looks like:

SELECT DISTINCT v.VIN_Number
FROM veh_master v
INNER JOIN veh_bld d ON v.VIN_Number = d.VIN_Number
WHERE d.bdate > sysdate
AND v.vehType in ('F','S')
AND v.constPlan is not null
AND v.VIN_Number not in (select VIN_Number from veh_opt where subtype ='SINGLEAXLE')

Open in new window


When I run it from Toad, it takes a few seconds, then returns about eighty rows, all VINs.  So I know the SQL code works: it's already been tested.

Until I drag it into Report Services.  I create the report, set up the DataSource, then paste in the SQL code (above, which works elsewhere, tested).  At that point  I start catching errors about SYSDATE.  Which I need, because that's Today's Date, which is the beginning of the data in which I am interested.

So: I need to somehow get Report Services to somehow regurgitate Today's Date in a manner that allows me to use it in the SQL report code, as it is necessary to select only VIN's under planning from today forward.  I'm no longer concerned that SYSDATE is throwing an error: I just need to figure out what Report Services needs in that place, or otherwise something generated within Report Services to provide that same info, so that my SQL report can - upon scheduled trigger for eMail subscriptions - provide certain engineers with a heads-up of possible issues for the day, upcoming week.

...and I believe it should really be simple, a lot simpler than it currently appears to be.
0
 
LVL 1

Accepted Solution

by:
LongFist earned 0 total points
ID: 38845318
SOLUTION:  At the top left corner of the Query Designer there is a button labelled "Edit as Text".  This throws you into a notepad-like form with a grid at the bottom.  Paste your (tried-and-true) SQL code there.  Click the "!" button (also at the top) and watch it work, just like it does elsewhere.

Sometimes, to make Microsoft products work, you have to get their clever contraptions out of the way.

Fortunately, once a query has been set up in "Edit as Text" mode, it stays there, so that snazzy "knows better than you" Query Designer won't pop back up (and thus edit your SQL code) and ruin your day later on.

So: It is possible to accomplish so simple a thing as "SELECT SYSDATE FROM DUAL" in Microsoft Report Services - you simply have to get past their automated Query Designer to accomplish it.
0
 
LVL 1

Author Closing Comment

by:LongFist
ID: 38858738
I should have experimented within the bounds of the SQL Query Designer before getting flustered and making noise over here on EE.  Perhaps, however, this will help some other silent victim, perhaps forced to undertake a previous genius' reporting projects, also with little to no documentation.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

762 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