How to declare variable in SSRS 2008 in Dataset
Hi All,
I have to write query in which i have to declare variable ,i dont want to use SP ,i tried to write in DATASET only.
The query which runs fine in Query window in SQL as below
declare @a int
Set @a=(select count(*) from roles where rolename='browser')
select @a
but when i run the same query in SSRS Dataset in throws error
"An error occured while the query design method was being saved. ORA-06550; line 1,column 9:PLS-00103: Encountered the symbol "@A" when expecting one of the following:
Being function package pragma procedure subtype type use <an identifier><a double -quoted delimited-identifier>form current cursor
Kindly Suggest how to declare variable in SSRS dataset
Amit
September 29th, 2010 3:47pm
I'm guessing you are querying an Oracle data source given the ORA error return? The syntax as it is will work for a SQL Server data source. This is more than likely a conversion problem to PL/SQL. Are you using the same provider in SSMS
to SSRS data source?Ted Krueger
Blog on lessthandot.com @onpnt on twitter
Free Windows Admin Tool Kit Click here and download it now
September 29th, 2010 3:58pm
Thanks onpnt,
can u please tell me how to define variable in PL/SQL
Amit
September 29th, 2010 4:30pm
Hi Amit,
I guess this site should help you out.
http://it.toolbox.com/blogs/oracle-guide/learn-plsql-declaring-variables-named-vs-anonymous-blocks-10508
HTH,
Suman
Free Windows Admin Tool Kit Click here and download it now
September 29th, 2010 5:01pm
HI,
I working on SSAS 2008,
I here by pasted the query below ,what i want to store result of both queries in Different variable say A,B ,and want to populate
only those variable.
i.e Variable A=Result set of query 1
Variable B =Result set of Query 2
then want to display variable A,B value
Query 1
SELECT NON EMPTY { [Measures].[ACW Goal] } ON COLUMNS FROM ( SELECT ( { [Client].[CLIENT NAME].&[135] } ) ON COLUMNS FROM ( SELECT ( { [Employee].[EMPLOYEE NAME].&[REBECCA ELLENBECKER] } ) ON COLUMNS FROM ( SELECT ( [Date].[Date].&[2010-09-29T00:00:00]
: [Date].[Date].&[2010-09-29T00:00:00] ) ON COLUMNS FROM [Team Lead]))) WHERE ( [Employee].[EMPLOYEE NAME].&[REBECCA ELLENBECKER], [Client].[CLIENT NAME].&[135] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME,
FONT_SIZE, FONT_FLAGS
Query 2
SELECT NON EMPTY { [Measures].[NNNNCW Goal] } ON COLUMNS FROM ( SELECT ( { [Client].[CLIENT NAME].&[135] } ) ON COLUMNS FROM ( SELECT ( { [Employee].[EMPLOYEE NAME].&[REBECCA ELLENBECKER] } ) ON COLUMNS FROM ( SELECT ( [Date].[Date].&[2010-09-29T00:00:00]
: [Date].[Date].&[2010-09-29T00:00:00] ) ON COLUMNS FROM [Team Lead]))) WHERE ( [Employee].[EMPLOYEE NAME].&[REBECCA ELLENBECKER], [Client].[CLIENT NAME].&[135] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME,
FONT_SIZE, FONT_FLAGS
Please tell how to alot values to these variable under MDX and then how to populate the same.
Amit
October 1st, 2010 1:12pm