Custom report via Reporting Services

[Narizz28]

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?

 

[johnjose]

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

jj

 

[Narizz28]

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.

 

[PascalHL]

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

 

[Narizz28]

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?

 

[Narizz28]

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.