Union all in Reporting Services Dataset Referring to parameters. -- sql server reporting services

sql server reporting services : Union all in Reporting Services Dataset Referring to parameters.

Sen
4/26/2006 10:06:44 AM

I am using this below query which is with a Union all. The database I
am trying to connect is Oracle.
Once I go to preview I get an ORA error which mentions thatt the query
is not properly terminated.

Is there a way to create a Union all in a paraemeterized dataset with
connection to Oracle.
="SELECT GL_VALUES.PERIOD_ID, GL_VALUES.COMPANY_NUM, STORE." &
iif(mid(Parameters!Location.Value,1,1)="S","STORE_NUM",iif(mid(Parameters!Location.Value,1,1)="D","DISTRICT","REGION"))
& " AS BUSUNIT, RTRIM(VARIABLE_LEVELS.ACCT_UNIT) AS ACCT_UNIT,
GL_VALUES.ACCOUNT_ID, SUM(GL_VALUES.CURR_ACTUAL_AMT) CURR_ACTUAL_AMT,
SUM(GL_VALUES.CURR_PLAN_AMT) CURR_PLAN_AMT,
SUM(GL_VALUES.CURR_PLAN_AMT) LYR_ACTUAL_AMT,
SUM(GL_VALUES.YTD_ACTUAL_AMT) YTD_ACTUAL_AMT,
SUM(GL_VALUES.YTD_PLAN_AMT) YTD_PLAN_AMT,
SUM(GL_VALUES.LYTD_ACTUAL_AMT) LYTD_ACTUAL_AMT,
SUM(GL_VALUES.QTD_ACTUAL_AMT) QTD_ACTUAL_AMT,
SUM(GL_VALUES.QTD_PLAN_AMT) QTD_PLAN_AMT,
SUM(GL_VALUES.LY_QTD_ACTUAL_AMT) LY_QTD_ACTUAL_AMT, GL_VALUES.FYR,
ACCOUNT_SUB_ACCOUNT.ACCOUNT_NAME FROM LAWDSS.GL_VALUES GL_VALUES,
LAWDSS.ACCOUNT_SUB_ACCOUNT ACCOUNT_SUB_ACCOUNT, LAWDSS.VARIABLE_LEVELS
VARIABLE_LEVELS, LAWDSS.STORE WHERE GL_VALUES.ACCOUNT_ID =
ACCOUNT_SUB_ACCOUNT.ACCOUNT_ID (+) AND GL_VALUES.SUB_ACCOUNT_ID =
ACCOUNT_SUB_ACCOUNT.SUB_ACCOUNT_ID (+) AND GL_VALUES.STORE_NUM =
STORE.STORE_NUM (+) AND GL_VALUES.COMPANY_NUM = STORE.COMPANY_NUM (+)
AND GL_VALUES.COMPANY_NUM = VARIABLE_LEVELS.COMPANY (+) AND
GL_VALUES.VAR_LEVELS = VARIABLE_LEVELS.VAR_LEVELS (+) AND
(GL_VALUES.DEPT_ID " & Parameters!Department.Value & ") AND
(GL_VALUES.COMPANY_NUM = " & Parameters!Company.Value & ") AND
(GL_VALUES.PERIOD_ID = 1) AND (GL_VALUES.ACCOUNT_ID >= 5000) AND
(GL_VALUES.STORE_NUM IN (SELECT STORE_NUM FROM LAWDSS.STORE WHERE
STORE." &
iif(mid(Parameters!Location.Value,1,1)="S","STORE_NUM",iif(mid(Parameters!Location.Value,1,1)="D","DISTRICT","REGION"))
& " = " & cint(Mid(Parameters!Location.Value,2,4)) & " AND COMPANY_NUM
= " & Parameters!Company.Value & ")) GROUP BY GL_VALUES.PERIOD_ID,
GL_VALUES.COMPANY_NUM, STORE." &
iif(mid(Parameters!Location.Value,1,1)="S","STORE_NUM",iif(mid(Parameters!Location.Value,1,1)="D","DISTRICT","REGION"))
& ", VARIABLE_LEVELS.ACCT_UNIT, GL_VALUES.ACCOUNT_ID, GL_VALUES.FYR,
ACCOUNT_SUB_ACCOUNT.ACCOUNT_NAME ORDER BY GL_VALUES.FYR DESC,
RTRIM(VARIABLE_LEVELS.ACCT_UNIT), GL_VALUES.ACCOUNT_ID
union all
SELECT GL_VALUES.PERIOD_ID, GL_VALUES.COMPANY_NUM, STORE." &
iif(mid(Parameters!Location.Value,1,1)="S","STORE_NUM",iif(mid(Parameters!Location.Value,1,1)="D","DISTRICT","REGION"))
& " AS BUSUNIT, RTRIM(VARIABLE_LEVELS.ACCT_UNIT) AS ACCT_UNIT,
GL_VALUES.ACCOUNT_ID, SUM(GL_VALUES.CURR_ACTUAL_AMT) CURR_ACTUAL_AMT,
SUM(GL_VALUES.CURR_PLAN_AMT) CURR_PLAN_AMT,
SUM(GL_VALUES.CURR_PLAN_AMT) LYR_ACTUAL_AMT,
SUM(GL_VALUES.YTD_ACTUAL_AMT) YTD_ACTUAL_AMT,
SUM(GL_VALUES.YTD_PLAN_AMT) YTD_PLAN_AMT,
SUM(GL_VALUES.LYTD_ACTUAL_AMT) LYTD_ACTUAL_AMT,
SUM(GL_VALUES.QTD_ACTUAL_AMT) QTD_ACTUAL_AMT,
SUM(GL_VALUES.QTD_PLAN_AMT) QTD_PLAN_AMT,
SUM(GL_VALUES.LY_QTD_ACTUAL_AMT) LY_QTD_ACTUAL_AMT, GL_VALUES.FYR,
ACCOUNT_SUB_ACCOUNT.ACCOUNT_NAME FROM LAWDSS.GL_VALUES GL_VALUES,
LAWDSS.ACCOUNT_SUB_ACCOUNT ACCOUNT_SUB_ACCOUNT, LAWDSS.VARIABLE_LEVELS
VARIABLE_LEVELS, LAWDSS.STORE WHERE GL_VALUES.ACCOUNT_ID =
ACCOUNT_SUB_ACCOUNT.ACCOUNT_ID (+) AND GL_VALUES.SUB_ACCOUNT_ID =
ACCOUNT_SUB_ACCOUNT.SUB_ACCOUNT_ID (+) AND GL_VALUES.STORE_NUM =
STORE.STORE_NUM (+) AND GL_VALUES.COMPANY_NUM = STORE.COMPANY_NUM (+)
AND GL_VALUES.COMPANY_NUM = VARIABLE_LEVELS.COMPANY (+) AND
GL_VALUES.VAR_LEVELS = VARIABLE_LEVELS.VAR_LEVELS (+) AND
(GL_VALUES.DEPT_ID " & Parameters!Department.Value & ") AND
(GL_VALUES.COMPANY_NUM = " & Parameters!Company.Value & ") AND
(GL_VALUES.PERIOD_ID = 1) AND (GL_VALUES.ACCOUNT_ID >= 5000) AND
(GL_VALUES.STORE_NUM IN (SELECT STORE_NUM FROM LAWDSS.STORE WHERE
STORE." &
iif(mid(Parameters!Location.Value,1,1)="S","STORE_NUM",iif(mid(Parameters!Location.Value,1,1)="D","DISTRICT","REGION"))
& " = " & cint(Mid(Parameters!Location.Value,2,4)) & " AND COMPANY_NUM
= " & Parameters!Company.Value & ")) GROUP BY GL_VALUES.PERIOD_ID,
GL_VALUES.COMPANY_NUM, STORE." &
iif(mid(Parameters!Location.Value,1,1)="S","STORE_NUM",iif(mid(Parameters!Location.Value,1,1)="D","DISTRICT","REGION"))
& ", VARIABLE_LEVELS.ACCT_UNIT, GL_VALUES.ACCOUNT_ID, GL_VALUES.FYR,
ACCOUNT_SUB_ACCOUNT.ACCOUNT_NAME ORDER BY GL_VALUES.FYR DESC,
RTRIM(VARIABLE_LEVELS.ACCT_UNIT), GL_VALUES.ACCOUNT_ID"

+++

If I remove the union all I am OK . I need teh union all in this query.

Please help.

Sen
4/26/2006 12:54:33 PM

Thanks for your response , Could you please tell me if a Union all is
possible in a Dataset with parameters.I mean the double quotes shoudl
it be for each qhuery or shoudl it be for teh whole query. Thanks in
advance

Bruce L-C [MVP]
4/26/2006 1:35:42 PM

I suggest that you put this expression in a report which just consists of
the parameters and a single large textbox and assign this expression as the
source. No dataset. Run the report and look at the resulting query. You
should be able to copy and execute outside of RS. Most likely you are
missing something.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

[quoted text, click to view]

Bruce L-C [MVP]
4/26/2006 3:44:48 PM

You missed my point. When all is said and done, you are assembling a string
that consists of a valid SQL Statement. For instance, whene the expression
is evaluated you should end up with a valid SQL Statement, with all
appropriate single quotes around character data. By putting a textbox on an
empty report and assigning the expression to it then you can look and see
what is wrong with your expression.

The other thing to do is hard code a valid SQL statement that you tested
outside of RS. Then put it in the generic query designer, two panes instead
of 4 panes, you get to this by clicking on a button to the right of the ....
Then you can make sure that it works as you think it should, plus it will
give you your field list. Once you put in an expression no field list is
returned.

So for instance, it makes no difference if I do this:
="blah blah blah" & "somemore text"
However, look at the above, hmmm. No black space. That would have evaluated
to
blah blah blahsomemore text

Which you would see immediate if you assign the expression to a text box. So
I would rewrite it as:
="blah blah blah " & "somemore text"
Putting in the space so it evaluates correctly.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server reporting services

home · blog · groups

Questions? Comments? Contact the dn