Skip to Content

Passing HANA variables to Microsoft SSRS

Aug 16, 2017 at 12:41 AM


avatar image
Former Member

Hello, I am trying to use SSRS with HANA. I created a calculated view in HANA with one variable. I actually tried both variable and parameter. Then connected to HANA via ODBC in my SSRS report. DataSet query looks like that

select top 10 * from "_SYS_BIC"."XXX-DEV.MYWORKSPACE/MATERIAL_WITH_PARAM" WHERE "Material" = @MaterialNumber. It gives me an error that syntax is incorrect near @, I assume HANA doesn't like @. If I use HANA syntax

select top 10 * from "_SYS_BIC"."XXX-DEV.MYWORKSPACE/MATERIAL_WITH_PARAM" ('PLACEHOLDER' = ('$$Material_Number$$', '[$Material_Number]' )) then SSRS doesn't understand it. What is the solution? How to pass a parameter from HANA to SSRS or create a parameter in SSRS? Did anyone successfully use SSRS with HANA?

10 |10000 characters needed characters left characters exceeded
Former Member


I am also having same issue. have you got any solution yet?

Please let me know.

Thanks, Ganesh

* Please Login or Register to Answer, Follow or Comment.

2 Answers

Luis Darui
Aug 17, 2017 at 04:40 PM

Hi Marina,

Microsoft doesn't support SAP HANA as a data source for SSRS:

You can still use ODBC to connect to HANA, but I don't think this is either T-SQL syntax (where you're trying to use parameters like @MaterialNumber) neither '[$Material_Number]').

Did you try to check the SSRS documentation to find out how to use this with ODBC (generic) connections?

10 |10000 characters needed characters left characters exceeded
Andrew Davies Apr 05 at 06:29 AM

I'm fighting with SSRS connecting to SAP HANA DB at the moment.

FYI I've got a DataSet to connect via ODBC, but the syntax for a stored proc appears to be...

CALL MySchema.MyProc(?)

...entered as TEXT! The go the the DataSet Parameters and map the ? in order to your report params.

But I've found a few caveats - I had to use the 32-bit HDBODBC driver for Report Builder, and the ODBC driver doesn't appear to like 2 or more parameters (1 is fine).

I'd image its similar for views & functions i.e.


...but I haven't tried it and you may hit the multi-param issue as well.

Good luck

Show 1 Share
10 |10000 characters needed characters left characters exceeded

FYI I have ostensibly got the multi-params to work by building the entire DataSet TEXT as a function.

So first use Query Designer to execute e.g.

CALL MySchema.MyProc(?,?) get the fields...then change the text to e.g...

=String.Format("CALL MySchema.MyProc({0},'{1}')", Parameters!Value1.Value, Parameters!Value2.Value)

Not impressed :(