Post reply

using table name starting with @ character

  • April 18, 2010 at 12:58 pm

    #233987

    Hi all,

    I'm new to Reporting Service.

    I'm trying to build a report where the main table used has a name starting with the @ character.

    RS suppose it is a parameter and when the report starts, I'm asked for a value.

    The table name is included in brackets [].

    Obviously I cannot change the name.

    Is there a way to use it without the parameter request?

    Thanks

    Antonio

  • April 19, 2010 at 5:32 am

    #1153709

    @ is reserved in ssrs as well as SQL server. What's the datasource?

    can you alias the table in your select/view/whatever?

    Like "select cola from @table table1"?

    Or are you trying to access a temp table?

    SQLScrubs: SSRS Audit, Optimization & Management reports

  • April 19, 2010 at 6:18 am

    #1153744

    Thanks for your reply.

    The query I use is this:

    SELECT RILEVA.U_Emp AS Emp,

    b.lastName + ' ' + b.firstName AS Nominativo,

    RILEVA.U_Data AS Data,

    RILEVA.U_Catt AS CodAtt,

    RILEVA.U_Tipo AS Tipo,

    RILEVA.U_Totale*60 as Minuti,

    RILEVA.U_CodComm,

    CASE WHEN isnull(COMMESSA.U_Name,'') = '' THEN d.PrjName ELSE COMMESSA.U_Name END AS Commessa,

    RILEVA.U_CodPos AS Posizione,

    RILEVA.U_Note AS Note,

    RILEVA.U_Costo AS Costo

    FROM [@BTPRJ_RILEVA] as RILEVA

    INNER JOIN OHEM b ON b.empID = RILEVA.U_emp

    LEFT OUTER JOIN [@BTPRJ_COMMESSA] AS COMMESSA ON COMMESSA.U_Code = RILEVA.U_CodComm

    LEFT OUTER JOIN OPRJ d ON d.PrjCode = RILEVA.U_CodComm

    WHERE isnull(RILEVA.U_Origine,'T') = 'T' AND isnull(COMMESSA.U_VerComm,'00') = '00' AND isnull(RILEVA.U_VerComm,'00') = '00'

    As you can see there are two tables starting with @ and with an alias (RILEVA and COMMESSA).

    The name of the tables are established by the application software (SAP Business One) when creating user tables, so I can't modify this behaviour.

    As a result, designing a report I must define two parameters in the "Report parameters" menu, otherwise I get an error.

    BR

    Antonio

  • April 19, 2010 at 7:00 am

    #1153780

    hmm, i think there is a netweaver ds for SSRS, not sure about business one...i know nothing about SAP.

    you could try an expression, though offhand I'm not sure that will work.

    that would be ="Select cola from @table as tableA"

    Give it a shot, or shout out to SAP and ask them how others do it...assuming they don't press hard on the "everyone uses business objects for reporting"

    SQLScrubs: SSRS Audit, Optimization & Management reports

  • April 19, 2010 at 7:24 am

    #1153805

    Thank you for your help.

    I tink I'm loosing the war ....

    BR

    Antonio

  • April 19, 2010 at 8:38 am

    #1153881

    Don't ask me how or why, but I've found a trick to avoid the RS request for a parameter related to a table name:

    1. From the "Data" folder, press the button with the three dots at the right of the "dataset" name (as say: Edit selected dataset)

    2. From the popup window, go to the "Parameters" folder

    3. You will find all the parameters RS has extracted from the SQL Statements

    4. Remove all the references to a table

    5. Press OK and voilà, everything works without asking nonsense parameters.

    Antonio

  • April 19, 2010 at 8:41 am

    #1153883

    Ahh, that makes sense..when you first entered the SQL...SSRS was trying to be "smart" and automatically created the parameters for you.

    Good deal!

    SQLScrubs: SSRS Audit, Optimization & Management reports

  • April 19, 2010 at 8:55 am

    #1153895

    HI,

    Try the following syntax:

    CREATE TABLE "@SELECT"

    (

    COLUMNNAME VARCHAR(20)

    )

    select * from "@select"

    Also remenber to enable the Set Quoted identifier property....

    Raunak J

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply