Passing HANA variables to Microsoft SSRS

[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?

[Former Member]

Hi,

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

Please let me know.

Thanks, Ganesh

[former_member211603]

Hi,

Did you find any solution to this. We are facing same problem unable to pass parameter from SSRS to HANA calculation view.

Please help if you have any pointers.

[Former Member]

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.

SELECT TOP 10 * FROM "_SYS_BIC"."XXX-DEV.MYWORKSPACE/MATERIAL_WITH_PARAM" (?)

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

Good luck

[Former Member]

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(?,?)

...to 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 😞

[luisdarui]

Hi Marina,

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

https://docs.microsoft.com/en-us/sql/reporting-services/report-data/data-sources-supported-by-report...

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?