Hi all,
Does anybody know how to set the parameter's default value dynamically?
I'm working on a report with some parameters against datacube, and I hope the default value of one of the parameters could be set dynamically based on the user's login.
Thanks,
Jone
Jone,
In your Report Parameter - Default Values - Non-Queries you can place an expression like so:
=IIf(User!UserID = "Bob", "USA","Canada")
I hope this is what you were looking for.
Ham
Hi
I have a report of 8.5"(W) by 11"(H).The header and footer size are fixed.
The header size is 4.75 in and footer size is 2.375in.
In the report body I have a table where the data is dynamic.
The problem is when the data is small i mean like 2 or 3 columns it doesnt touch the footer .
If we have more than 10 columns or so it goes to the next page and even then it doesnt touch the footer beacuse the data stops there.
If we have like 4 columns or so in the table then it touches the footer.
I tried to replace the table with the list but I got the same problem there also.
I tried to place the table in a rectangle and it has the same problem.
I want the table to touch the footer no matter how much the data is .
Is there any work around for this problem?
I really appreciate your help and time
Thanks
Hobbs
Hello,
The table will always grow based on its content. We do not currentlly support "Fill To Page" functionallity. Do you want the table style (borders and background color) to fill the page? Did you try settting the style on the report body?
Thank you,
Nico
|||Hi Nico
Thanks for the reply.I tried the boderstyle of body and the table but it stops wherever the data ends.
I can send you the rdl file or the exported pdf if u want a take look at what I am talking about.And also I have a the bordstyle of the table column set to solid.even that one stops with the last data.
I was thinking if we have something like calulcating the line height or may be fill the gap after the table somehow dynamically then only fix it.
Thanks
Hobbs
|||Did you solve it? I've got the same problem. I've got a report that needs border around it. If i use body's border then i can see the report correctly in report manager or after rendering it to pdf. But when i try to print from report manager right hand side border is missing. Printing of the report from rendered pdf file is fine. I tried few things like, created bland report with body only with border style to solid. I could see the border and print the border correctly. Then i added page header/footer and suddenly the right hand side border was missing. I think its bug. check this link http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=115351. I checked all the margin and layout setup everything is correct. Don't know how to get it working?
Regards,
Vivek
Hi
I have a report of 8.5"(W) by 11"(H).The header and footer size are fixed.
The header size is 4.75 in and footer size is 2.375in.
In the report body I have a table where the data is dynamic.
The problem is when the data is small i mean like 2 or 3 columns it doesnt touch the footer .
If we have more than 10 columns or so it goes to the next page and even then it doesnt touch the footer beacuse the data stops there.
If we have like 4 columns or so in the table then it touches the footer.
I tried to replace the table with the list but I got the same problem there also.
I tried to place the table in a rectangle and it has the same problem.
I want the table to touch the footer no matter how much the data is .
Is there any work around for this problem?
I really appreciate your help and time
Thanks
Hobbs
Hello,
The table will always grow based on its content. We do not currentlly support "Fill To Page" functionallity. Do you want the table style (borders and background color) to fill the page? Did you try settting the style on the report body?
Thank you,
Nico
|||Hi Nico
Thanks for the reply.I tried the boderstyle of body and the table but it stops wherever the data ends.
I can send you the rdl file or the exported pdf if u want a take look at what I am talking about.And also I have a the bordstyle of the table column set to solid.even that one stops with the last data.
I was thinking if we have something like calulcating the line height or may be fill the gap after the table somehow dynamically then only fix it.
Thanks
Hobbs
|||Did you solve it? I've got the same problem. I've got a report that needs border around it. If i use body's border then i can see the report correctly in report manager or after rendering it to pdf. But when i try to print from report manager right hand side border is missing. Printing of the report from rendered pdf file is fine. I tried few things like, created bland report with body only with border style to solid. I could see the border and print the border correctly. Then i added page header/footer and suddenly the right hand side border was missing. I think its bug. check this link http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=115351. I checked all the margin and layout setup everything is correct. Don't know how to get it working?
Regards,
Vivek
HI chaps
my scenario is that, i have a table in which i have column such as col01,col02,col03....col31
i want to retrieve the records related to that column by specifying the name dynamically (by using loop) as fist three character are same ('col?') and i have to do integer increment in last two character (?01...?31). Is it possible that I can use variable for column name?, if yes how? or is there any other way to achieve this task ?
waiting for your reply
regards
Something like the following will work for you:
create table DynCols
(col01 int,
col02 int,
col11 int,
col12 int) -- and so on, fo all 30 columns
declare @.query varchar(255)
set @.query = 'select '
declare @.counter int
set @.counter = 1
while @.counter <= 31
begin
if @.counter < 10
set @.query = @.query + 'col' + '0' + convert(char(1), @.counter) + ','
else
set @.query = @.query + 'col' + convert(char(2), @.counter) + ','
set @.counter = @.counter + 1
end
-- remove the last comma and append the rest:
set @.query = substring(@.query, 0, len(@.query) - 1) + ' from DynCols'
print @.query
The result is:
select col01,col02,col03,col04,col05,col06,col07,col08,col09,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23,col24,col25,col26,col27,col28,col29,col30,col3 from DynCols
|||fafnir wrote:
Something like the following will work for you:
create table DynCols
(col01 int,
col02 int,
col11 int,
col12 int) -- and so on, fo all 30 columnsdeclare @.query varchar(255)
set @.query = 'select 'declare @.counter int
set @.counter = 1while @.counter <= 31
begin
if @.counter < 10
set @.query = @.query + 'col' + '0' + convert(char(1), @.counter) + ','
else
set @.query = @.query + 'col' + convert(char(2), @.counter) + ','
set @.counter = @.counter + 1
end-- remove the last comma and append the rest:
set @.query = substring(@.query, 0, len(@.query) - 1) + ' from DynCols'print @.query
The result is:
select col01,col02,col03,col04,col05,col06,col07,col08,col09,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23,col24,col25,col26,col27,col28,col29,col30,col3 from DynCols
thanx for your reply....
yes i can do it that way but still it will be string (varchar) can you tell me how to execute that statement? i can print the statement but i dont know how will I able to execute a query which is stored in the varaible
|||sp_executesql @.query
|||i have written the follwoing script
DECLARE @.CURRENTDATE DATETIME
DECLARE @.STARTDATE DATETIME
DECLARE @.LOOPDATE DATETIME
DECLARE @.NOOFMONTHS INT
DECLARE @.NOOFDAYS INT
DECLARE @.DAYCOUNTER TINYINT
DECLARE @.I INT
DECLARE @.J TINYINT
DECLARE @.DAYNUMBER VARCHAR(6)
/*INSERT INTO PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR(BPCODE)
SELECT DISTINCT LTRIM(RTRIM(CZMMCU)) FROM STAGING.DBO.F0007*/
SET @.CURRENTDATE = (SELECT CONVERT(DATETIME,CAST(DATE AS VARCHAR(10))) FROM PRESENTATIONEUROPE.DBO.CALENDAR
WHERE CURRENTDAY = 0 )
--Check for first time execution and set start date accordingly
IF (SELECT MAX(DATE) FROM PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR) IS NOT NULL
SET @.STARTDATE = CONVERT(DATETIME,(SELECT CONVERT(VARCHAR(8),MAX(DATE)) FROM
PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR))
ELSE
SET @.STARTDATE = (SELECT CONVERT(DATETIME, CONVERT(VARCHAR(4),(CAST(MIN(CZYR)AS INT) + 2000))+'-'+
CONVERT(VARCHAR(2),MIN(CZMT))+'-01') FROM STAGING.DBO.F0007 WHERE CZYR = (SELECT MIN(CZYR) FROM STAGING.DBO.F0007))
--GET THE NO OF MONTH DIFFERENCE
SET @.NOOFMONTHS = DATEDIFF(MM,@.STARTDATE,@.CURRENTDATE)
SET @.I = 1
SET @.LOOPDATE = @.STARTDATE
DECLARE @.LOOPDATE2 INT
DECLARE @.QUERY VARCHAR(500)
WHILE (@.I<=@.NOOFMONTHS)
BEGIN
SET @.LOOPDATE2 = (CONVERT(VARCHAR(8),CONVERT(DATETIME, CONVERT(VARCHAR(4),YEAR(@.LOOPDATE))
+'-'+ CONVERT(VARCHAR(2),MONTH(@.LOOPDATE))+'-01'),112))
SET @.J = 1
WHILE (@.J<=31)
BEGIN
IF (@.J<10)
BEGIN
SET @.QUERY = 'INSERT INTO PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR (BPCODE, DATE, DATEE1, WORKINGDAY ) SELECT LTRIM(RTRIM(CZMMCU)), @.LOOPDATE2, (SELECT DATEE1 FROM PRESENTATIONEUROPE.DBO.CALENDAR WHERE DATE ='+CONVERT(VARCHAR(8),@.LOOPDATE,112)+'),
CZTD0'+CONVERT(CHAR(1),@.J)+' FROM STAGING.DBO.F0007 T1 LEFT OUTER JOIN PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR T2
ON LTRIM (RTRIM(T1.CZMMCU)) = T2.BPCODE AND T2.DATE =' +CONVERT(VARCHAR(8),@.LOOPDATE2)+
'WHERE T2.BPCODE IS NULL AND CZYR='+ SUBSTRING(CONVERT(VARCHAR(4),YEAR(@.LOOPDATE)),3,4)+
'AND CZMT ='+CONVERT(CHAR(2),MONTH(@.LOOPDATE))
END
ELSE
BEGIN
SET @.QUERY = 'INSERT INTO PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR (BPCODE, DATE, DATEE1, WORKINGDAY ) SELECT LTRIM(RTRIM(CZMMCU)), @.LOOPDATE2, (SELECT DATEE1 FROM PRESENTATIONEUROPE.DBO.CALENDAR WHERE DATE ='+ CONVERT(VARCHAR(8),@.LOOPDATE,112) +'),
CZTD'+CONVERT(CHAR(1),@.J)+' FROM STAGING.DBO.F0007 T1 LEFT OUTER JOIN PRESENTATIONEUROPE.DBO.WORKDAYCALENDAR T2
ON LTRIM (RTRIM(T1.CZMMCU)) = T2.BPCODE AND T2.DATE =' +CONVERT(VARCHAR(8),@.LOOPDATE2)+
'WHERE T2.BPCODE IS NULL AND CZYR='+ SUBSTRING(CONVERT(VARCHAR(4),YEAR(@.LOOPDATE)),3,4)+
'AND CZMT ='+CONVERT(CHAR(2),MONTH(@.LOOPDATE))
END
SP_EXECUTESQL @.QUERY
SET @.J = @.J+1
END
PRINT @.LOOPDATE
SET @.I = @.I + 1
END
and getting follwing error
Msg 102, Level 15, State 1, Line 62
Incorrect syntax near 'SP_EXECUTESQL'.
|||use:
exec sp_executesql @.query
