Skip to Content
avatar image
Former Member

Passing HANA variables to Microsoft SSRS

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?

Add comment
10|10000 characters needed characters exceeded

  • Former Member

    Hi,

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

    Please let me know.

    Thanks, Ganesh

  • Get RSS Feed

2 Answers

  • Aug 17, 2017 at 04:40 PM

    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-reporting-services-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?

    Add comment
    10|10000 characters needed characters exceeded

  • 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.

    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

    Add comment
    10|10000 characters needed 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(?,?)

      ...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 :(