Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Custom report via Reporting Services

Status
Not open for further replies.

Narizz28

MIS
Mar 8, 2001
161
I am trying to get an abandoned call report so I can graph the intervals of abandonments. I am using MS Reporting services and all is well until I try to limit it down to a date using the TIMESTAMP in the WHERE clause. I either get an error that the syntax is wrong or that the column TIMESTAMP doesn't exist.

I am running this against the dApplicationStat view in the Blue database.

Any ideas?
 
Don't you try to change time format 00:00:00 for day ?

jj
 
I've tried YYYY/MM/DD 00:00:00:00, and just the YYYY/MM/DD by itself. Both result in a "Could not parse query" error.

If I change the format to a MS SQL datetime format, i.e. YYYY-MM-DD, I get an error that TIMESTAMP is not not a valid column, however doing a SELECT * clearly show the column name as TIMESTAMP.

I'm a bit at a loss. I figured out a work around by using the filter option in the graph and filtering the result set to Day(Timestamp) = Day(Now()) AND Month(Timestamp) = Month(Now()) AND Year(Timesatmp) = Year(Now()), but I am still interested in figuring out how to pasre a query using the TIMESTAMP in the WHERE column.

Thanks for your help.
 
Hi,

don't know if it will do with yuor requester, but with Access, i did always use the #.
Example :
SELECT *
FROM dbo_iAgentBySkillsetStat
where timestamp = #12/12/2005#
returns to you all the lines where month/day/year = 12/12/2005 (without problems around hour and format)

Hope i did understand well your question, and that it may help.

Regards,
PG
 
Thanks for your response Pascal. I tried your example against dApplicationStat in Access and it worked fine. I then copied it over to Reporting services and chnaged it slightly to conform with TSQL syntax. Here's what my query looks like:

SELECT *
FROM dbo.dApplicationStat
WHERE timestamp = 12/14/2005 AND ApplicationID = 10002

And I get the infamous "Invalid column name 'timestamp'".

Any other ideas or insights? What would the query look like in Crystal Reports?
 
My last post made me think "Duh, why not write the report in Crystal and copy the query?" I did and this is what I found. In order to get the Timestamp format to match in Reporting Services, it has to be structured as such:

SELECT *
FROM dApplicationStat
WHERE Timestamp={ts '2005-12-14 00:00:00:00'}

This worked like a champ. Any deviation from this format generates an error. I also tested against the iApplicationStat view succesfully like such

SELECT *
FROM dApplicationStat
WHERE (Timestamp<={ts '2005-12-15 18:15:00:00'}
AND Timestamp>={ts '2005-12-15 18:30:00:00'})

Hope this helps! Happy reporting.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top