using table name starting with @ character

Question

Post reply

using table name starting with @ character

quetzco

SSC Veteran

Points: 251
More actions
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

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

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

David-Leibowitz SSChasing Mays Points: 610 More actions · Answer 1

@ 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

quetzco SSC Veteran Points: 251 More actions · Answer 2

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

David-Leibowitz SSChasing Mays Points: 610 More actions · Answer 3

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

quetzco SSC Veteran Points: 251 More actions · Answer 4

Thank you for your help.

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

BR

Antonio

quetzco SSC Veteran Points: 251 More actions · Answer 5

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

David-Leibowitz SSChasing Mays Points: 610 More actions · Answer 6

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

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 7

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