Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Report Builder Expand / Collapse
Author
Message
Posted Friday, November 8, 2013 8:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 20, 2013 2:32 AM
Points: 7, Visits: 9
Hi,

Not sure where to put this...

Im working in SQL Report Builder (sorry dont know which version) and am having trouble with calculated fields.

Ive got 2 datasets - one with some tenancy information in it (DataSet1) and the other with address data in it (DataSet2)

Ive got a calculated field that ive called "DaysVoid" which is essentially one date subtracted by another.

I want to work out the total sum of all of the "DaysVoid" so have set up another calculated field which is called "TotalDaysVoid" //=sum(DaysVoid)\\ but when I run my query, I get the following error;

//The Value expression for the textrun ‘Textbox34.Paragraphs[0].TextRuns[0]’ contains an error: [BC30451] Name 'DaysVoid' is not declared.\\

Can anybody help?
Post #1512713
Posted Sunday, November 10, 2013 8:04 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:49 PM
Points: 3,024, Visits: 2,628
Try using the following expression in textbo34:

=sum(Fields!DaysVoid)



Post #1512995
Posted Monday, November 11, 2013 3:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 20, 2013 2:32 AM
Points: 7, Visits: 9
Thanks for your help - it worked! However....

It now brings back the value (in this case 111) as a weird format;

111.00:00:00

Ive formatted the number right, and the globalization settings are en-GB... is there something I have missed?

Thanks Again
Post #1513057
Posted Monday, November 11, 2013 5:13 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 13, 2014 5:54 AM
Points: 453, Visits: 583
is the text box property set to date time ?
Post #1513081
Posted Monday, November 18, 2013 7:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 20, 2013 2:32 AM
Points: 7, Visits: 9
Hi.

No, it is set to Number.

Also, the answer to my first question that I said it worked - actually didnt.
Post #1515239
Posted Monday, November 18, 2013 8:04 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 13, 2014 5:54 AM
Points: 453, Visits: 583
Possibly try CInt(sum(Fields!DaysVoid, "dataset_name") ) - assuming daysvoid is calculated as part of the query ?
Post #1515245
Posted Monday, November 18, 2013 8:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 20, 2013 2:32 AM
Points: 7, Visits: 9
No, it isnt - I think thats what the problem is.

In my dataset, i have "Start Date" and "End Date"

DaysVoid is a calculation of "End Date" subtract "Start Date"
Post #1515247
Posted Monday, November 18, 2013 8:29 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 13, 2014 5:54 AM
Points: 453, Visits: 583
try
DateDiff("dd",Fields!StartDate.Value,Fields!StartDate.Value) to get the days void
Post #1515260
Posted Monday, November 18, 2013 8:30 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 13, 2014 5:54 AM
Points: 453, Visits: 583
sorry that should read
DateDiff("dd",Fields!StartDate.Value,Fields!ENDDate.Value) to get the days void
Post #1515261
Posted Monday, November 18, 2013 8:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 20, 2013 2:32 AM
Points: 7, Visits: 9
Nope... afraid not. Just get #error
Post #1515262
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse