Problem with an IIF Statement

In my group footer I build a string based on the number of rows in that
group plus the field the group was based on.
But if the group was based on a an empty string I get an #error in the
textbox.
So I wrapped the code with an IIF statement. If the field is empty do
nothing otherwise build the string.
= IIF(Fields!AdjustmentCode.Value = "","No Adjustment
Code",countrows("grpAdjustmentDesc") & " Records for Adjustment Code
Description: " & Fields!AdjustmentCode.Value.trim & " - " &
Fields!AdjustmentDescription.Value.tolower)FYI this is the error: The value expression for the textbox
'textbox35' contains an error: Object variable or With block
variable not set.|||VB.Net evaluates all parts of the IIF which is why you get the error. It
does not just evaluate the true portion. You could use code behind report to
do this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PAPutzback" <phillip_putzback@.insightbb.com> wrote in message
news:1128709777.373098.129000@.g49g2000cwa.googlegroups.com...
> In my group footer I build a string based on the number of rows in that
> group plus the field the group was based on.
> But if the group was based on a an empty string I get an #error in the
> textbox.
> So I wrapped the code with an IIF statement. If the field is empty do
> nothing otherwise build the string.
> = IIF(Fields!AdjustmentCode.Value = "","No Adjustment
> Code",countrows("grpAdjustmentDesc") & " Records for Adjustment Code
> Description: " & Fields!AdjustmentCode.Value.trim & " - " &
> Fields!AdjustmentDescription.Value.tolower)
>|||Can you point me in some direction for a Code Behind example. I can't
find anything yet.
Thanks.|||Look in books on line for the phrase code block
They have an example with that. I tend to develop my code in vb.net, test it
and then copy it over.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PAPutzback" <phillip_putzback@.insightbb.com> wrote in message
news:1128712212.284978.143180@.z14g2000cwz.googlegroups.com...
> Can you point me in some direction for a Code Behind example. I can't
> find anything yet.
> Thanks.
>|||All i see is XML. This is going to be a lot of work to just handle the
fact the the Rpt can't handle a blank value.
So your saying I can put vb code in this.
<Textbox Name="textbox35">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>25</ZIndex>
<rd:DefaultName>textbox35</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>= countrows("grpAdjustmentDesc") &
" Records for Adjustment Code Description: " &
Fields!AdjustmentCode.Value.trim & " - " &
Fields!AdjustmentDescription.Value.tolower</Value>
</Textbox>|||Can you post the hyperlink from the RSBook online address bar to where
the sample is.|||I put this in the code window
<Code>
Public Function test() As String
Dim strTest As String
strTest = "BLAH BLAH BLAH"
Return strTest
End Function
</Code>
I put this in a textbox
= code.test()
d:\phfxclaims\Pending Claims Detail.rdl There is an error on line 0 of
custom code: [BC32035] Attribute specifier is not a complete statement.
Use a line continuation to apply the attribute to the following
statement.
And this is my error|||You should not be having to modify the rdl at all (which is what you are
doing).
Search Books Online for the phrase: writing custom code
Also these two links:
ms-help://MS.RSBOL80.1033/RSCREATE/htm/rcr_creating_expressions_v1_84f9.htm
ms-help://MS.RSBOL80.1033/RShowto/htm/hrs_designer_v1_1nfp.htm
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PAPutzback" <phillip_putzback@.insightbb.com> wrote in message
news:1128719715.084263.60890@.g43g2000cwa.googlegroups.com...
>I put this in the code window
> <Code>
> Public Function test() As String
> Dim strTest As String
> strTest = "BLAH BLAH BLAH"
> Return strTest
> End Function
> </Code>
>
> I put this in a textbox
> = code.test()
>
> d:\phfxclaims\Pending Claims Detail.rdl There is an error on line 0 of
> custom code: [BC32035] Attribute specifier is not a complete statement.
> Use a line continuation to apply the attribute to the following
> statement.
> And this is my error
>|||Thanks for the links. I finally got it to work
I had cut and pasted the code from this article
http://www.15seconds.com/issue/041110.htm and I had the tags in there
which were getting placed in the XML definition
Thanks for the quick responses

Problem with a sql statement

I have a table with the monthly internet usage in minutes for each account.

I wish to create a new table based of the previously mentioned that limits the number of records per account to 9, so only include records based on each account for their past 9 months worth of internet usage.

Can anyone help me do this?Take a look at the Transact-SQL forum for help in this regard.

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1|||I think TSQl forum is a better place for this question...moved from SSIS forum

Problem with a date function

Hello All!

I have a table with a date column. I would like to be able to DELETE the rows based on the date column. The condition is 30 days from todays date. So anything older than 30 days from todays date, it will delete those rows.

Any suggesttion the best way to do this. I was thinking of a simple select statement, but can't figure it out.

TIA!!

Rudy

Hi there,

Is your date column of data type DateTime? If so, try something like:

DELETE FROM [Table Name]
WHERE DATEADD(d, -30, GETDATE()) > [Date Column]

What happens is DATEADD(d, -30, GETDATE()) is used to obtain a date that is 30 days from todays date. Then, anything in the table where the entry in the Date Column (which I assume is of data type DateTime) is older than DATEADD(d, -30, GETDATE()), i.e. older than 30 days from today's date, gets deleted.

Hope that helps a bit, but sorry if it doesn't.
|||Thank you! Just what I needed!

Problem viewing report based on analysis services

I am using using some code which Implements IReportServerCredentials (see
the object below) to pass credentials to a non local report server. This
works fine and my reportviewer can get it's report from a non local server.
When the report uses Analysis Services it displays the parameters part of
the report viewer so I know it is getting as far as the report server but
when I press view report I doesn't display anything, not even an error. I
have tried adding the user I am logging as to the roles within the analysis
Services project it is based on but with no luck. I have included the code
in case anyone finds it helpful. Can anyone help?
Dim cred As New ReportServerCredentials("username", "password", "domain")
ReportViewer1.ServerReport.ReportServerCredentials = cred
Imports Microsoft.VisualBasic
Imports Microsoft.Reporting.WebForms
Imports System.Net
Public Class reportingservices
End Class
<Serializable()> _
Public Class ReportServerCredentials
Implements IReportServerCredentials
Private _userName As String
Private _password As String
Private _domain As String
Public Sub New(ByVal userName As String, ByVal password As String, ByVal
domain As String)
_userName = userName
_password = password
_domain = domain
End Sub
Public ReadOnly Property ImpersonationUser() As
System.Security.Principal.WindowsIdentity Implements
Microsoft.Reporting.WebForms.IReportServerCredentials.ImpersonationUser
Get
Return Nothing
End Get
End Property
Public ReadOnly Property NetworkCredentials() As ICredentials Implements
Microsoft.Reporting.WebForms.IReportServerCredentials.NetworkCredentials
Get
Return New NetworkCredential(_userName, _password, _domain)
End Get
End Property
Public Function GetFormsCredentials(ByRef authCookie As System.Net.Cookie,
ByRef userName As String, ByRef password As String, ByRef authority As
String) As Boolean Implements
Microsoft.Reporting.WebForms.IReportServerCredentials.GetFormsCredentials
userName = _userName
password = _password
authority = _domain
Return Nothing
End Function
End ClassIn this instance are you trying to access the report in the following
situation:
client accessing report (machine1) -> Report Server (machine 2) -> Analysis
Services (machine 3)?
If so then it is most likely a kerberos authentication issue due to the
"double hop" you are experiencing.
For more information on enabling this check the following article:
http://support.microsoft.com/kb/917409/en-us
SQL Server Developer Support Engineer
"Fresno Bob" wrote:
> I am using using some code which Implements IReportServerCredentials (see
> the object below) to pass credentials to a non local report server. This
> works fine and my reportviewer can get it's report from a non local server.
> When the report uses Analysis Services it displays the parameters part of
> the report viewer so I know it is getting as far as the report server but
> when I press view report I doesn't display anything, not even an error. I
> have tried adding the user I am logging as to the roles within the analysis
> Services project it is based on but with no luck. I have included the code
> in case anyone finds it helpful. Can anyone help?
> Dim cred As New ReportServerCredentials("username", "password", "domain")
> ReportViewer1.ServerReport.ReportServerCredentials = cred
>
> Imports Microsoft.VisualBasic
> Imports Microsoft.Reporting.WebForms
> Imports System.Net
>
>
> Public Class reportingservices
> End Class
>
> <Serializable()> _
> Public Class ReportServerCredentials
> Implements IReportServerCredentials
> Private _userName As String
> Private _password As String
> Private _domain As String
> Public Sub New(ByVal userName As String, ByVal password As String, ByVal
> domain As String)
> _userName = userName
> _password = password
> _domain = domain
> End Sub
> Public ReadOnly Property ImpersonationUser() As
> System.Security.Principal.WindowsIdentity Implements
> Microsoft.Reporting.WebForms.IReportServerCredentials.ImpersonationUser
> Get
> Return Nothing
> End Get
> End Property
> Public ReadOnly Property NetworkCredentials() As ICredentials Implements
> Microsoft.Reporting.WebForms.IReportServerCredentials.NetworkCredentials
> Get
> Return New NetworkCredential(_userName, _password, _domain)
> End Get
> End Property
> Public Function GetFormsCredentials(ByRef authCookie As System.Net.Cookie,
> ByRef userName As String, ByRef password As String, ByRef authority As
> String) As Boolean Implements
> Microsoft.Reporting.WebForms.IReportServerCredentials.GetFormsCredentials
> userName = _userName
> password = _password
> authority = _domain
> Return Nothing
> End Function
> End Class
>
>

Problem using EXEC() to run DBCC DBREINDEX

I am trying to run DBCC DBREINDEX using EXEC(), code is below.
Based upon the error message at the bottom, the @.currenttable variable
receives the value 1 but when @.currenttable is referenece in the DBCC
statement, the value isn't there. Can anyone tell me what I'm doing wrong?
declare @.sqltest varchar(40), @.currenttable int
set @.currenttable = (select table_id from Table_Space where table_id = 1)
set @.sqltest = 'DBCC DBREINDEX(''@.currenttable'','''',75)'
print @.currenttable
print @.sqltest
EXEC(@.sqltest)
Below is the message I get:
1
DBCC DBREINDEX('@.currenttable','',75)
Server: Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named '@.currenttable'. Check sysobjects.nosurfdj,
DBCC DBREINDEX expects a table name and there is not table named
'@.currenttable'.
declare @.sqltest varchar(40), @.currenttable int
declare @.tn sysname
set @.tn = (select table_name from Table_Space where table_id = 1)
set @.sqltest = 'DBCC DBREINDEX(''' + @.tn + ''','''',75)'
print @.currenttable
print @.sqltest
EXEC(@.sqltest)
go
AMB
"nosurfdj" wrote:

> I am trying to run DBCC DBREINDEX using EXEC(), code is below.
> Based upon the error message at the bottom, the @.currenttable variable
> receives the value 1 but when @.currenttable is referenece in the DBCC
> statement, the value isn't there. Can anyone tell me what I'm doing wrong
?
> declare @.sqltest varchar(40), @.currenttable int
> set @.currenttable = (select table_id from Table_Space where table_id = 1)
> set @.sqltest = 'DBCC DBREINDEX(''@.currenttable'','''',75)'
> print @.currenttable
> print @.sqltest
> EXEC(@.sqltest)
> Below is the message I get:
> 1
> DBCC DBREINDEX('@.currenttable','',75)
> Server: Msg 2501, Level 16, State 1, Line 1
> Could not find a table or object named '@.currenttable'. Check sysobjects.
>|||Quote problems around ''@.currenttable''.
Try:
'DBCC DBREINDEX(' + @.currenttable + ','''',75)'
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"nosurfdj" <nosurfdj@.discussions.microsoft.com> wrote in message news:2406FBD3-FD2A-4F69-8A
8E-F446DC1473BF@.microsoft.com...
>I am trying to run DBCC DBREINDEX using EXEC(), code is below.
> Based upon the error message at the bottom, the @.currenttable variable
> receives the value 1 but when @.currenttable is referenece in the DBCC
> statement, the value isn't there. Can anyone tell me what I'm doing wrong
?
>
> declare @.sqltest varchar(40), @.currenttable int
> set @.currenttable = (select table_id from Table_Space where table_id = 1)
> set @.sqltest = 'DBCC DBREINDEX(''@.currenttable'','''',75)'
> print @.currenttable
> print @.sqltest
> EXEC(@.sqltest)
>
> Below is the message I get:
> 1
> DBCC DBREINDEX('@.currenttable','',75)
> Server: Msg 2501, Level 16, State 1, Line 1
> Could not find a table or object named '@.currenttable'. Check sysobjects.
>|||I knew it was going to be something simple.
Thanks for your help-that did it.
"Alejandro Mesa" wrote:
> nosurfdj,
> DBCC DBREINDEX expects a table name and there is not table named
> '@.currenttable'.
> declare @.sqltest varchar(40), @.currenttable int
> declare @.tn sysname
> set @.tn = (select table_name from Table_Space where table_id = 1)
> set @.sqltest = 'DBCC DBREINDEX(''' + @.tn + ''','''',75)'
> print @.currenttable
> print @.sqltest
> EXEC(@.sqltest)
> go
>
> AMB
> "nosurfdj" wrote:
>

Problem Using Alias

I need a select that sums the same column, but with different criteria.
I also need to "roll up" the sums into a "higher level" sum based on
some other tables. This is an accounting database, where subaccounts
roll up into a single account (i.e. subaccounts 101 and 102 roll up
into account 100).
I'm using an alias, but not getting correct results. I believe I'm
ending up with a cartesian product, but not sure of the "fix". Note: I
cannot change the table design, this is an existing accounting database
created by a different application.
Here's a simplified version of the query with tables following that:
SELECT Account.Name, Sum(A.Amount) as Actual, Sum(B.Amount) as Budget
FROM Account, SubAccount, Trans A, Trans B
WHERE
Account.AccountNum = SubAccount.AccountNum and
Trans.SubAccountNum = SubAccount.SubAccountNum and
A.Trans.Type = 'A' and B.Trans.Type = 'B'
GROUP BY Account.Name
What's wrong with my SQL above?
Here are my tables with sample data:
Table: Account
AccountNum Name
--
100 Fuel
200 Tires
Table: SubAccount
AccountNum SubAccountNum Name
---
100 101 Diesel
100 102 Gasoline
200 200 Winter Tire
200 201 All Season Tire
Table: Trans (transactions)
SubAccountNum Amount Type (A-Actual, B-Budget)
---
101 10 A
102 20 A
200 30 A
201 40 A
101 50 B
102 60 B
200 70 B
201 80 B

>From the data above, I need to end up with output from my Select as
follows:
Name Actual Budget
--
Fuel 30 110
Tires 70 150> FROM Account, SubAccount, Trans A, Trans B
Please use ANSI inner joins. It will make your query easier to read and
will allow you to

> Trans.SubAccountNum = SubAccount.SubAccountNum and
> A.Trans.Type = 'A' and B.Trans.Type = 'B'
You're saying, instead of calling Trans "Trans", let's call it "A". What do
you expect A.Trans to mean? Didn't you mean
A.SubAccountNum = SubAccount.SubAccountNum
AND
A.Type = 'A'
AND B.Type = 'B'
I have a better way to formulate the query however, because of my first
point, I have no idea whether two copies of the Trans table are required,
how they each relate to Account and SubAccount, and how the sums should be
generated. If you provide more details I'd be more than happy to write you
a more readable query. Please see: http://www.aspfaq.com/5006|||As you said, I should be using joins. The code below did the trick.
Thanks for your input.
SELECT Account.Name,
Sum(Case Trans.Type When 'A' Then Trans.Amount Else 0 End) as Actual,
Sum(Case Trans.Type When 'B' Then Trans.Amount Else 0 End) as Budget
FROM Account
INNER JOIN SubAccount
ON Account.AccountNum = SubAccount.AccountNum
INNER JOIN Trans
ON Trans.SubAccountNum = SubAccount.SubAccountNum|||> Please use ANSI inner joins. It will make your query easier to read and
> will allow you to
finish sentences! What I was going to elaborate on about is that you can
separate join and filter criteria. Looks like you've already got a handle
on it in this case.|||CREATE TABLE Accounts (AccountNum int NOT NULL, [name] varchar(25), PRIMARY
KEY (AccountNum))
GO
CREATE TABLE SubAccounts (AccountNum int REFERENCES Accounts(AccountNum),
SubAccountNum int, [name] varchar(25))
GO
INSERT INTO Accounts VALUES (100, 'Fuel')
INSERT INTO Accounts VALUES (200, 'Tires')
INSERT INTO SubAccounts VALUES (100,101,'Diesel')
INSERT INTO SubAccounts VALUES (100,102,'Gasoline')
INSERT INTO SubAccounts VALUES (200,200,'Winter Tire')
INSERT INTO SubAccounts VALUES (200,201,'All Season Tire')
CREATE TABLE Trans
(SubAccountNum int, Amount int, Type char(1))
GO
INSERT INTO test4 VALUES (101,10,'A')
INSERT INTO test4 VALUES (102,20,'A')
INSERT INTO test4 VALUES (200,30,'A')
INSERT INTO test4 VALUES (201,40,'A')
INSERT INTO test4 VALUES (101,50,'B')
INSERT INTO test4 VALUES (102,60,'B')
INSERT INTO test4 VALUES (200,70,'B')
INSERT INTO test4 VALUES (201,80,'B')
SELECT a.[name], SUM(t1.TotalActual) AS "Total Actual",
SUM(t2.TotalBudgeted) AS "Total Budgeted"
FROM Accounts a INNER JOIN SubAccounts sa ON a.AccountNum=sa.AccountNum
INNER JOIN (SELECT SubAccountNum, SUM(Amount) AS "TotalActual" FROM Trans
WHERE Type='A'
GROUP BY SubAccountNum) t1 ON t1.SubAccountNum=sa.SubAccountNum
INNER JOIN (SELECT SubAccountNum, SUM(Amount) AS "TotalBudgeted" FROM Trans
WHERE Type='B'
GROUP BY SubAccountNum) t2 ON t2.SubAccountNum=sa.SubAccountNum
GROUP BY a.[name]
"joeacunzo@.yahoo.com" wrote:

> I need a select that sums the same column, but with different criteria.
> I also need to "roll up" the sums into a "higher level" sum based on
> some other tables. This is an accounting database, where subaccounts
> roll up into a single account (i.e. subaccounts 101 and 102 roll up
> into account 100).
> I'm using an alias, but not getting correct results. I believe I'm
> ending up with a cartesian product, but not sure of the "fix". Note: I
> cannot change the table design, this is an existing accounting database
> created by a different application.
> Here's a simplified version of the query with tables following that:
> SELECT Account.Name, Sum(A.Amount) as Actual, Sum(B.Amount) as Budget
> FROM Account, SubAccount, Trans A, Trans B
> WHERE
> Account.AccountNum = SubAccount.AccountNum and
> Trans.SubAccountNum = SubAccount.SubAccountNum and
> A.Trans.Type = 'A' and B.Trans.Type = 'B'
> GROUP BY Account.Name
> What's wrong with my SQL above?
> Here are my tables with sample data:
> Table: Account
> AccountNum Name
> --
> 100 Fuel
> 200 Tires
> Table: SubAccount
> AccountNum SubAccountNum Name
> ---
> 100 101 Diesel
> 100 102 Gasoline
> 200 200 Winter Tire
> 200 201 All Season Tire
> Table: Trans (transactions)
> SubAccountNum Amount Type (A-Actual, B-Budget)
> ---
> 101 10 A
> 102 20 A
> 200 30 A
> 201 40 A
> 101 50 B
> 102 60 B
> 200 70 B
> 201 80 B
>
> follows:
> Name Actual Budget
> --
> Fuel 30 110
> Tires 70 150
>