November 12, 2007 at 11:01 am
I am trying to import Reports from MS ACCESS DB to Reporting Services.
I followed online instructions, and imported the reports and can see them in layout
but have had no luck in previewing my reports in Reporting services.
The error: An Error has occurred during report processing. Query execution failed for 'DataSet1'.Undefined function 'IsMonthOK' in expression.
Is there something I am doing wrong.
Thanks in advanced.
November 14, 2007 at 1:18 pm
Can you run the query from the data tab?
November 14, 2007 at 1:28 pm
I can not run the query from the datatab.
It gives me an error pop up: Undefined function 'IsMonthOK' in expression.
but the data tab is not displaying anything except a simple code:
Select *
From [MS access Query]
Thats it....
I would assume that it is missing all the other queries it took to come up with the [MS AccessQuery] - you know how MS Access is query on query...
but I thought the import would do.just that - grab all the queries and related functions..
any suggestion?
November 14, 2007 at 1:39 pm
I would move the data and saved queries (views) to SQL Server - not easy but would solve the issue.
If you need to leave the data in Access - then have you tested your data source connection. Click the ... next to the dataset in the data tab, and then under the data source click the ... again, and make sure all that info is correct, and click test connection. let me know if it is successful.
November 15, 2007 at 2:28 am
Has anybody written a VBA function called IsMonthOK() in the original Access mdb?
November 15, 2007 at 4:34 am
My tip for converting Access reports into Reporting Services: rewrite them from scratch. Unless your reports are really simple, you'll spend so much time rejigging parameters and Access functions and subqueries that it just isn't worth it.
November 15, 2007 at 6:41 am
Alex Cooper (11/12/2007)
I am trying to import Reports from MS ACCESS DB to Reporting Services.I followed online instructions, and imported the reports and can see them in layout
but have had no luck in previewing my reports in Reporting services.
The error: An Error has occurred during report processing. Query execution failed for 'DataSet1'.Undefined function 'IsMonthOK' in expression.
Is there something I am doing wrong.
Thanks in advanced.
Alex,
You cannot transfer any custom functions from Access into RS, and many built-in Access functions like Nz() don't work either. About the only thing you can use is IIf so you could either 1) re-write the IsMonthOK function and embed that into your query or 2) carry the raw data forward into the report and then use T-SQL to accomplish the month validation.
January 8, 2008 at 10:19 am
You also cannot use any Access specific function such as DSum. I ran into this problem. What I did to get around it is convert the Access query to a stored procedure, then put it in SSRS. Also if you need to keep the report in Access, just have your report call your stored procedure using a Pass-Through query. There were 2 advantages to this: 1) performance. Usually you don't even see the "Running query" box in the lower left corner of Access because the stored procedure is now running on SQL Server and not the client machine 2) security. Our Access databases don't have much security and anyone could run/see the report. Assign rights accordingly to the stored procedure.
You can also just link the SSRS report to a button in Access. Just put the url of the report into the "Hyperlink Address" property of the button. Hope this helps.
July 4, 2008 at 3:28 am
many built-in Access functions like Nz() don't work either
I have an Access query (called ReportQuery for the sake of this example) that has NZ in it, and even though the SSRS report is just SELECT * FROM ReportQuery, it fails because it can't understand the NZ command.
I don't understand why that should be the case, given that Access will be processing the query, not SQL Server?
Thanks,
Wayne
July 4, 2008 at 8:39 am
I don't know the specifics of the relationship between Access reports and SSRS. I just usually put my SELECT statement into a stored procedure and then call the stored procedure for the report on SSRS; thereby circumventing Access altogether.
Oh, I almost forgot, you can use ISNULL() in your stored proc to represent the NZ function in Access. Also, I was going back thru your original post and you may not need the IsMonthOK function. You can use the date picker in an SSRS report to pick a date and you won't have to worry if the string of the month is OK. Or you can put your month-ends in a table and have your users choose those from a drop-down and use a parameter. I hope I'm not being too vague.
July 5, 2008 at 3:15 am
SQLWannabe (7/4/2008)
I don't know the specifics of the relationship between Access reports and SSRS. I just usually put my SELECT statement into a stored procedure and then call the stored procedure for the report on SSRS; thereby circumventing Access altogether.
SSRS is reporting on data from an Access database, where the report query is, so I can't circumvent Access. I'm not sure why SSRS doesn't just ask Access for the query results. It shouldn't care how the query works and hence the NZ should be fine. But yes, I can replace NZs with IIFs but there are lots so I was trying to avoid doing that if possible.
July 24, 2009 at 2:20 pm
We found a similar issue related to the query string - Access is expecting different wildcard characters than T-SQL (i.e., a ? instead of a % for "any number of characters"). Is there a way someone has found to substitute wildcard characters?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply