Dynamic Query

I use IIF in the dynamic query to dynamically change the Select, Group By, and Order By statements.

In the table grouping properties, i also use IIF to change the grouping Field.

There are no errors, the report processes OK, but the report is not grouped or shows any Field values for which i have to use dynamic query. What could be the problem? Can anybody help please.

Thanks

Can you post the dynamic query you are using, as well as the group expression? Also if you enable tracing on the database side, is the query being executed correct?|||

Thank you for answering to my problem. I did get over it after much trying. The dynamic query looks like this:

="SELECT SUM(BASE_UNIT) AS BASE_UNIT "
& IIF(Parameters!Type.Value = "Location", ", Location", IIF(Parameters!Type.Value = "Admit_Source", ", Admit_Source", ", Provider_Name")) &
" as grouping FROM TBL_EOM
WHERE (MONTH(ENTRY_DATETIME) =@.RepMonth) AND (YEAR(ENTRY_DATETIME) = @.RepYear)" &
IIF(Parameters!Pract.Value = "*** ALL ***", " ", " AND (NAME = @.Prac) ") &
" GROUP BY Provider_Name, NAME" & IIF(Parameters!Type.Value = "Location", ", Location", IIF(Parameters!Type.Value = "Admit_Source", ", Admit_Source", " "))

In layout view i have this strig for field, grouping and sorting:

IIF(Parameters!Type.Value <> "Provider", Fields!grouping.Value, " ")

Thank you

Dynamic MDX Deployement in Reporting Services

I have created a Reporting Services report that uses MDX to retrieve data
from a cube. The MDX statements must use a parameter so they are placed into
text strings. The report will run with the Dynamic MDX in Visual Studio but
when I deploy it to the Web Server it locks up.
I believe that the dynamic MDX is causing the problem. The reports that
connect to the Cube that do not use parameters run fine on the web.
Any advice would be appreciated.
Thanks.I found this to be very helpful with parameterized MD
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp
"AndyT" wrote:
> I have created a Reporting Services report that uses MDX to retrieve data
> from a cube. The MDX statements must use a parameter so they are placed into
> text strings. The report will run with the Dynamic MDX in Visual Studio but
> when I deploy it to the Web Server it locks up.
> I believe that the dynamic MDX is causing the problem. The reports that
> connect to the Cube that do not use parameters run fine on the web.
> Any advice would be appreciated.
> Thanks.

Dynamic From Clause

Hi,
Q: Is there a way to make the table's name in the from clause dynamic?
I have a job (T-SQL statements) that makes copies of current month's
selected tables to our archive DB. All copies will have a time stamp like
TableName_yyyy_mm. What I always do is that I edit the T-SQL statements in
that job to reflect the current year and the current month for all statement
s
(find and replace of course).
This month will be,
Select * into archive.dbo.PEIF_2005_03 from data.dbo.PEIF
Next month will be,
Select * into archive.dbo.PEIF_2005_04 from data.dbo.PEIF
.. and so on
I have tried using variables for the time stamp, like:
Declare @.Year Int
Declare @.Month Int
Select @.Year=(
select
case
when month(getdate()) - 1 = 0 then year(getdate()) - 1
else year(getdate())
end
)
Select @.Month=(
select
case
when month(getdate()) - 1 = 0 then 12
else month(getdate()) - 1
end
)
Select * into archive.dbo.PEIF_@.yyyy_@.mm from data.dbo.PEIF (DOESN'T WORK)
Apparently @.variable only works for where clauses (at least in my case).
What I want (if possible) is that I don't need to update the job every month
and let the job does the work for me.
Please help,
Adrian T> Q: Is there a way to make the table's name in the from clause dynamic?
http://www.sommarskog.se/dynamic_sql.html|||you would want to look into the stored procedure sp_executesql in BOL.
Basically it allows you to say "exec sp_executesql @.mySQL" and whatever
is in the string of @.MySQL you've just built up it will generate an
execution plan for it, then run it.
Cheers
Will|||ur solution is almost there.
try this.. a small change.. hope this helps.
Declare @.Year Int
Declare @.Month Int
Select @.Year=(
select
case
when month(getdate()) - 1 = 0 then year(getdate()) - 1
else year(getdate())
end
)
Select @.Month=(
select
case
when month(getdate()) - 1 = 0 then 12
else month(getdate()) - 1
end
)
exec('Select * into archive.dbo.PEIF_' + @.yyyy + '_' + @.mm + ' from
data.dbo.PEIF')

Dynamic For Loop

My below code returns a union fine in northwind. However, I'd like add some
FOR LOOP's while building the SELECT statements to cut down on the redundant
code. As you can see, each @.SQLx statement has a variable from 1 to 3. I
created this exmple to get an idea of using a FOR LOOP to cut down on code.
CODE:
declare @.SQL varchar(4000), @.SQL1 varchar(4000), @.SQL2 varchar(4000), @.SQL3
varchar(4000)
declare @.dtYear1 int, @.dtYear2 int, @.dtYear3 int, @.dtStartDate1 datetime,
@.dtEndDate1 datetime
declare @.dtStartDate2 datetime, @.dtEndDate2 datetime, @.dtStartDate3
datetime, @.dtEndDate3 datetime
declare @.typeID int, @.debug int
set @.typeID = '1'
set @.dtStartDate1='19960101'
set @.dtEndDate1='19961231'
set @.dtStartDate2='19970101'
set @.dtEndDate2='19971231'
set @.dtStartDate3='19980101'
set @.dtEndDate3='19981231'
set @.dtYear1='1996'
set @.dtYear2='1997'
set @.dtYear3='1998'
SET @.SQL1 = ' SELECT ' + '''Year $$@.dtYear1$$''' + ' AS Date, '
SET @.SQL2 = ' SELECT ' + '''Year $$@.dtYear2$$''' + ' AS Date, '
SET @.SQL3 = ' SELECT ' + '''Year $$@.dtYear3$$''' + ' AS Date, '
set @.SQL1 = REPLACE(@.SQL1,'$$@.dtYear1$$',@.dtYear1)
set @.SQL2 = REPLACE(@.SQL2,'$$@.dtYear2$$',@.dtYear2)
set @.SQL3 = REPLACE(@.SQL3,'$$@.dtYear3$$',@.dtYear3)
set @.debug = 0
IF @.typeID = 1
BEGIN
SET @.SQL1 = @.SQL1 + 'MAX(Orders.Freight) AS MaxOfFreight '
SET @.SQL2 = @.SQL2 + 'MAX(Orders.Freight) AS MaxOfFreight '
SET @.SQL3 = @.SQL3 + 'MAX(Orders.Freight) AS MaxOfFreight '
END
IF @.typeID = 2
BEGIN
SET @.SQL1 = @.SQL1 + 'COUNT(*) AS SalesCount '
SET @.SQL2 = @.SQL2 + 'COUNT(*) AS SalesCount '
SET @.SQL3 = @.SQL3 + 'COUNT(*) AS SalesCount '
END
SET @.SQL1 = @.SQL1 + 'FROM Customers INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID WHERE '
SET @.SQL2 = @.SQL2 + 'FROM Customers INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID WHERE '
SET @.SQL3 = @.SQL3 + 'FROM Customers INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID WHERE '
SET @.SQL1 = @.SQL1 + 'Orders.OrderDate >= ''' + convert(char(8),
@.dtStartDate1, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
convert(char(8), @.dtEndDate1, 112) + ''''
SET @.SQL2 = @.SQL2 + 'Orders.OrderDate >= ''' + convert(char(8),
@.dtStartDate2, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
convert(char(8), @.dtEndDate2, 112) + ''''
SET @.SQL3 = @.SQL3 + 'Orders.OrderDate >= ''' + convert(char(8),
@.dtStartDate3, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
convert(char(8), @.dtEndDate3, 112) + ''''
SET @.SQL = @.SQL1 + ' UNION ALL ' + @.SQL2 + ' UNION ALL ' + @.SQL3
IF @.debug = 1
PRINT @.SQL
ELSE
EXEC(@.SQL)Scott
SQL Server does not have FOR LOOP , bit it does have WHILE LOOP
See soem examples
DECLARE @.i INT
SET @.i=1
WHILE @.i<100
BEGIN
INSERT INTO Table VALUES (@.i)
SET @.i=@.i+1
END
"Scott" <sbailey@.mileslumber.com> wrote in message
news:eR6CT9EBGHA.272@.TK2MSFTNGP09.phx.gbl...
> My below code returns a union fine in northwind. However, I'd like add
> some FOR LOOP's while building the SELECT statements to cut down on the
> redundant code. As you can see, each @.SQLx statement has a variable from 1
> to 3. I created this exmple to get an idea of using a FOR LOOP to cut down
> on code.
> CODE:
> declare @.SQL varchar(4000), @.SQL1 varchar(4000), @.SQL2 varchar(4000),
> @.SQL3 varchar(4000)
> declare @.dtYear1 int, @.dtYear2 int, @.dtYear3 int, @.dtStartDate1 datetime,
> @.dtEndDate1 datetime
> declare @.dtStartDate2 datetime, @.dtEndDate2 datetime, @.dtStartDate3
> datetime, @.dtEndDate3 datetime
> declare @.typeID int, @.debug int
> set @.typeID = '1'
> set @.dtStartDate1='19960101'
> set @.dtEndDate1='19961231'
> set @.dtStartDate2='19970101'
> set @.dtEndDate2='19971231'
> set @.dtStartDate3='19980101'
> set @.dtEndDate3='19981231'
> set @.dtYear1='1996'
> set @.dtYear2='1997'
> set @.dtYear3='1998'
> SET @.SQL1 = ' SELECT ' + '''Year $$@.dtYear1$$''' + ' AS Date, '
> SET @.SQL2 = ' SELECT ' + '''Year $$@.dtYear2$$''' + ' AS Date, '
> SET @.SQL3 = ' SELECT ' + '''Year $$@.dtYear3$$''' + ' AS Date, '
> set @.SQL1 = REPLACE(@.SQL1,'$$@.dtYear1$$',@.dtYear1)
> set @.SQL2 = REPLACE(@.SQL2,'$$@.dtYear2$$',@.dtYear2)
> set @.SQL3 = REPLACE(@.SQL3,'$$@.dtYear3$$',@.dtYear3)
> set @.debug = 0
> IF @.typeID = 1
> BEGIN
> SET @.SQL1 = @.SQL1 + 'MAX(Orders.Freight) AS MaxOfFreight '
> SET @.SQL2 = @.SQL2 + 'MAX(Orders.Freight) AS MaxOfFreight '
> SET @.SQL3 = @.SQL3 + 'MAX(Orders.Freight) AS MaxOfFreight '
> END
> IF @.typeID = 2
> BEGIN
> SET @.SQL1 = @.SQL1 + 'COUNT(*) AS SalesCount '
> SET @.SQL2 = @.SQL2 + 'COUNT(*) AS SalesCount '
> SET @.SQL3 = @.SQL3 + 'COUNT(*) AS SalesCount '
> END
> SET @.SQL1 = @.SQL1 + 'FROM Customers INNER JOIN Orders ON
> Customers.CustomerID = Orders.CustomerID WHERE '
> SET @.SQL2 = @.SQL2 + 'FROM Customers INNER JOIN Orders ON
> Customers.CustomerID = Orders.CustomerID WHERE '
> SET @.SQL3 = @.SQL3 + 'FROM Customers INNER JOIN Orders ON
> Customers.CustomerID = Orders.CustomerID WHERE '
> SET @.SQL1 = @.SQL1 + 'Orders.OrderDate >= ''' + convert(char(8),
> @.dtStartDate1, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
> convert(char(8), @.dtEndDate1, 112) + ''''
> SET @.SQL2 = @.SQL2 + 'Orders.OrderDate >= ''' + convert(char(8),
> @.dtStartDate2, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
> convert(char(8), @.dtEndDate2, 112) + ''''
> SET @.SQL3 = @.SQL3 + 'Orders.OrderDate >= ''' + convert(char(8),
> @.dtStartDate3, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
> convert(char(8), @.dtEndDate3, 112) + ''''
> SET @.SQL = @.SQL1 + ' UNION ALL ' + @.SQL2 + ' UNION ALL ' + @.SQL3
> IF @.debug = 1
> PRINT @.SQL
> ELSE
> EXEC(@.SQL)
>|||A better way would be something like this:
SELECT 'Year '+DATENAME(YEAR,MIN(orderdate)) AS [date],
MAX(O.Freight) AS maxoffreight
FROM Customers AS C
JOIN Orders AS O
ON C.customerid = O.customerid
WHERE O.orderdate BETWEEN @.startdate AND @.enddate
GROUP BY YEAR(O.orderdate) ;
David Portas
SQL Server MVP
--|||I guess what I'm asking is there a way in SQL to create dynamic variables?
So for example, my code would loop 3 times and each time my variables like
@.dtStartDate1 would increase from @.dtStartDate1 to @.dtStartDate2 and then
@.dtStartDate3.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23aBO7zFBGHA.2512@.TK2MSFTNGP09.phx.gbl...
> Scott
> SQL Server does not have FOR LOOP , bit it does have WHILE LOOP
> See soem examples
> DECLARE @.i INT
> SET @.i=1
> WHILE @.i<100
> BEGIN
> INSERT INTO Table VALUES (@.i)
> SET @.i=@.i+1
> END
>
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:eR6CT9EBGHA.272@.TK2MSFTNGP09.phx.gbl...
>|||I would do it like that except we have custom year begin and end dates and i
didn't want to take time to write a custom function.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1134997569.867513.55250@.f14g2000cwb.googlegroups.com...
>A better way would be something like this:
> SELECT 'Year '+DATENAME(YEAR,MIN(orderdate)) AS [date],
> MAX(O.Freight) AS maxoffreight
> FROM Customers AS C
> JOIN Orders AS O
> ON C.customerid = O.customerid
> WHERE O.orderdate BETWEEN @.startdate AND @.enddate
> GROUP BY YEAR(O.orderdate) ;
> --
> David Portas
> SQL Server MVP
> --
>|||scott wrote:
> I would do it like that except we have custom year begin and end dates and
i
> didn't want to take time to write a custom function.
>
Are your annual begin and end dates fixed in the Calendar year or are
they based on some specific non-Gregorian calendar rules? If it's a
Calendar year, just use DATEADD to offset the date by the required
number of days. If it's specific company calendar logic, create a
calendar table and populate it with the information about your
customer's calendar. Join the calendar table into the query. Either
way, you don't need dynamic SQL.
David Portas
SQL Server MVP
--