Formulas in a Graph?? Wont work!

Hi all, Happy New yrs!

I have a problem.

I am trying to make a graph in Crystal 8.5. The values I want to use to populate this graph are from formulas.

When I am in the graph expert thing, those formulas dont show up... and i have no clue why...

Is there something special I have to do to accomplish this?

Thanks in advance!
-ThadSo should I assume this isnt possible?

Thanks!

Formulas for Column Properties

I have a date column that has 1/1/1900 entries and I want to write a formula..(i think)...in the column formula section that when this date is encountered, the column should show NULL. I don't know the syntax to use in the formula section of the column properties. Can anyone give some syntax examples.

Thanks

Hi,

if you want to store 1/1/1900 but just display NULL you should consider using a view or a query to decide on the value. If you want to change the data upon insert in the table, you should consider a trigger to change the data on the events like update / Insert to change if its a 1/1/1900 to NULL.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

sql

Formulas (that are correct) don't validate in EM

I have the following table in SQL server 2000 SP3:
CREATE TABLE [dbo].[Dates] ([ID] [int] NOT NULL ,
[EOMDate] [smalldatetime] NOT NULL ,
[BOMDate] AS (dateadd(month,(-1),(convert(smalldatetime,[EOMDate]) +
1))) ,
[BOM3MonthsAgo] AS (dateadd(month,(-3),(convert(smalldatetime,[EOMDa
te])
+ 1))) ,
[EOMPrev] AS (dateadd(month,(-1),(convert(smalldatetime,[EOMDate]) +
1))
- 1)
) ON [PRIMARY]
The idea here is simple; we enter the end-of-month date for the most
recent month where we have all the data, (when that happens is a little
unpredictable). The other dates are commonly used beginning-of-month
and the ending date of the previous month, etc. (Those convert
functions appeared when I scripted the table -- I didn't enter them.)
This create statement works fine, and I can fill in the EOMDate, and
everything else works perfectly.
UNLESS I edit any of the table definition in Enterprise Manager. Then I
get these messages from EM:
'Dates' table
- Error validating the formula for column 'BOMDate'.
- Error validating the formula for column 'EOMPrev'.
- Error validating the formula for column 'BOM3MonthsAgo'.
Why does EM not like these formulas? In EM, the BOMDate formula looks
like this:
(dateadd(month,(-1),(convert(smalldatetime,[EOMDate]) + 1)))
Aside from the abundance of parentheses, what is EM complaining about?
Since it *works*, I think EM is confused.
Thanks for any insight on this.
David WalkerEM is not as sophisticated. That's one of the reasons why you should use
Query Analyzer and DDL statements for altering your table structures. You
will also see similar problems with view definitions in EM.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"DW" <None> wrote in message news:eO85pTTDEHA.3888@.TK2MSFTNGP10.phx.gbl...
I have the following table in SQL server 2000 SP3:
CREATE TABLE [dbo].[Dates] ([ID] [int] NOT NULL ,
[EOMDate] [smalldatetime] NOT NULL ,
[BOMDate] AS (dateadd(month,(-1),(convert(smalldatetime,[EOMDate]) +
1))) ,
[BOM3MonthsAgo] AS (dateadd(month,(-3),(convert(smalldatetime,[EOMDa
te])
+ 1))) ,
[EOMPrev] AS (dateadd(month,(-1),(convert(smalldatetime,[EOMDate]) +
1))
- 1)
) ON [PRIMARY]
The idea here is simple; we enter the end-of-month date for the most
recent month where we have all the data, (when that happens is a little
unpredictable). The other dates are commonly used beginning-of-month
and the ending date of the previous month, etc. (Those convert
functions appeared when I scripted the table -- I didn't enter them.)
This create statement works fine, and I can fill in the EOMDate, and
everything else works perfectly.
UNLESS I edit any of the table definition in Enterprise Manager. Then I
get these messages from EM:
'Dates' table
- Error validating the formula for column 'BOMDate'.
- Error validating the formula for column 'EOMPrev'.
- Error validating the formula for column 'BOM3MonthsAgo'.
Why does EM not like these formulas? In EM, the BOMDate formula looks
like this:
(dateadd(month,(-1),(convert(smalldatetime,[EOMDate]) + 1)))
Aside from the abundance of parentheses, what is EM complaining about?
Since it *works*, I think EM is confused.
Thanks for any insight on this.
David Walker|||So it's a bug in EM, or maybe a lack of interest on the developers' part
in making it work correctly? That's good to know, so I won't rely on EM
or believe what it says. It might throw some newbies off, though.
Doesn't EM just send the definition through as Alter Table statements?
Those shouldn't fail. Strange...
Thanks.
David Walker
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in
news:Oh1KLxTDEHA.3584@.TK2MSFTNGP11.phx.gbl:

> EM is not as sophisticated. That's one of the reasons why you should
> use Query Analyzer and DDL statements for altering your table
> structures. You will also see similar problems with view definitions
> in EM. --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
>
> "DW" <None> wrote in message
> news:eO85pTTDEHA.3888@.TK2MSFTNGP10.phx.gbl... I have the following
> table in SQL server 2000 SP3:
> CREATE TABLE [dbo].[Dates] ([ID] [int] NOT NULL ,
> [EOMDate] [smalldatetime] NOT NULL ,
> [BOMDate] AS (dateadd(month,(-1),(convert(smalldatetime,[EOMDate])
+
> 1))) ,
> [BOM3MonthsAgo] AS
> (dateadd(month,(-3),(convert(smalldatetime,[EOMDate]) + 1))) ,
> [EOMPrev] AS (dateadd(month,(-1),(convert(smalldatetime,[EOMDate])
+
> 1)) - 1)
> ) ON [PRIMARY]
> The idea here is simple; we enter the end-of-month date for the most
> recent month where we have all the data, (when that happens is a
> little unpredictable). The other dates are commonly used
> beginning-of-month and the ending date of the previous month, etc.
> (Those convert functions appeared when I scripted the table -- I
> didn't enter them.)
> This create statement works fine, and I can fill in the EOMDate, and
> everything else works perfectly.
> UNLESS I edit any of the table definition in Enterprise Manager. Then
> I get these messages from EM:
> 'Dates' table
> - Error validating the formula for column 'BOMDate'.
> - Error validating the formula for column 'EOMPrev'.
> - Error validating the formula for column 'BOM3MonthsAgo'.
> Why does EM not like these formulas? In EM, the BOMDate formula looks
> like this:
> (dateadd(month,(-1),(convert(smalldatetime,[EOMDate]) + 1)))
> Aside from the abundance of parentheses, what is EM complaining about?
> Since it *works*, I think EM is confused.
> Thanks for any insight on this.
> David Walker
>
>|||Hi David,
Yes, when you press YES when the prompt titled ' Validation Warnings', the
Enterprise Manager will send the Alter statement to change the definition
of the table. However, before that there is some pre-validation process for
the table with formulas. It have to check all the constraints and use the
formulas to re-acount the related columns. This process is complecated and
this is during this process that the warning message prompt. It is just a
warning and would not prevent you from alter the table. As Vyas said, you
could use the Query Analyzer to alter the table without get any warning
messages so as your client application.
Hope this helps.
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Formulas

I have a table with 1 columns in it
CREATE TABLE [dbo].[date] (
[Date_updated] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
[Day_of_w] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
[Day_of_month] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
[Day_of_year] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
I want to aaply the following formulas to the last 3 columns, but when I
enter the formula in the on the design table screen, it doesn't like the
syntax. I am entering the following for each column
datepart(DW,Date_occured_from)
datepart(DD,Date_occured_from)
datepart(DY,Date_occured_from)
Can someone help with the correct syntax for doing this please. ThanksI do not see column [Date_occured_from] in the table definition.
Example:
use northwind
go
create table t (
colA datetime default (getdate()),
colB as cast(right('0' + ltrim(datepart(dw, colA)), 2) as char(2))
)
go
insert into t default values
go
select * from t
go
drop table t
go
AMB
"Munch" wrote:

> I have a table with 1 columns in it
> CREATE TABLE [dbo].[date] (
> [Date_updated] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> [Day_of_w] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> [Day_of_month] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> [Day_of_year] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> I want to aaply the following formulas to the last 3 columns, but when I
> enter the formula in the on the design table screen, it doesn't like the
> syntax. I am entering the following for each column
> datepart(DW,Date_occured_from)
> datepart(DD,Date_occured_from)
> datepart(DY,Date_occured_from)
> Can someone help with the correct syntax for doing this please. Thanks
>|||"Enter the formula" ' Are you trying to create computed columns?
If so, the column [Date_occured_from] must exists in the table, and it must
be a datetime, or smalldatetime datatype.
The only column in your table that appears to be a date is Date_Updated, and
it is typed as a Char(8)... If you want to use that, you will have to cat it
to a datetime first, and use the correct column name
datepart(DW, Cast(Date_Updated As DateTime))
datepart(DD, Cast(Date_Updated As DateTime))
datepart(DY, Cast(Date_Updated As DateTime))
"Munch" wrote:

> I have a table with 1 columns in it
> CREATE TABLE [dbo].[date] (
> [Date_updated] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> [Day_of_w] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> [Day_of_month] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> [Day_of_year] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> I want to aaply the following formulas to the last 3 columns, but when I
> enter the formula in the on the design table screen, it doesn't like the
> syntax. I am entering the following for each column
> datepart(DW,Date_occured_from)
> datepart(DD,Date_occured_from)
> datepart(DY,Date_occured_from)
> Can someone help with the correct syntax for doing this please. Thanks
>|||But, lest I forget, You would be much much better off using DateTime or
SmallDateTime as the datatype of that column (Date_Updated) in the first
place...
"Munch" wrote:

> I have a table with 1 columns in it
> CREATE TABLE [dbo].[date] (
> [Date_updated] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> [Day_of_w] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> [Day_of_month] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> [Day_of_year] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> I want to aaply the following formulas to the last 3 columns, but when I
> enter the formula in the on the design table screen, it doesn't like the
> syntax. I am entering the following for each column
> datepart(DW,Date_occured_from)
> datepart(DD,Date_occured_from)
> datepart(DY,Date_occured_from)
> Can someone help with the correct syntax for doing this please. Thanks
>

Formulae for SQL Server table cells

Hi,

I know that we can have User Defined Functions (UDFs) for columns but is it possible to have UDFs for each cell (like we can define functions for each cell in excel). Heres the situation:

I have a database table that will have multiple fields. Some of the columns will be functions of other columns. One of the columns has variable functions.

Heres an example:

TABLE(A, B, C, D)

Total Records = 9;

A

BCDA1B1C1 = A1+ B1D1 = A1A2B2C2 = A2+ B2D2 = A2A3B3C3 = A3+ B3D3 = A3A4B4C4 = A4+ B4D4 = A4A5B5C5 = A5+ B5D5 = A5 + B5A6B6C6 = A6+ B6D6 = A6 + B6A7B7C7 = A7+ B7D7 = A7 + B7A8B8C8 = A8+ B8D8 = A8 + B8A9B9C9 = A9+ B9D9 = A9 + B9

If you notice, for Column D, some of the cells have a different formula than others. Is this doable?

Thanks...

One possible way would be there was an Id column with value 1-9, once the table was populated to make an update stored procedure calculate column unconditionally and column D according to a formula.

|||

Please let me know if I am going off-track here but this is what I understood from what you wrote.

Have an ID column (say autoincrement integers), then when all the values are being populated, I run an UPDATE stored procedure which whould check if ID is from 1-4, then assign the same value otherwise if ID = 5 to 9, perform calculation.

|||

Instead of an Identity column you could just populate it manually.

Incidentally as an alternative you could drop columns C and D from the table, sum A + B to get column C and use a function reading Id, A, B to get column D in the select.

|||

TATWORTH:

drop columns C and D from the table, sum A + B to get column C and use a function reading Id, A, B to get column D in the select

Does that mean I won't have columns C and D in the schema but will be generated dynamically when SELECTed (they won't ever be in the schema).

|||

>Does that mean I won't have columns C and D in the schema but will begenerated dynamically when SELECTed (they won't ever be in the schema).
Yes!

Formula to view records

Hi,
I am having a report which takes a parameter to view records based on the parameter value passed to it.

I need to check the length of the parameter, if the length of the parameter value is "4", it should compare the parameter value with one field value, if the length is more than 4, then it should compare the value with another field and shoe me the records.

I have made the formula, but it doesn't show records, where as there are records in the table.

how could i do that ? Is anybody there, who could help me in that?

Thanks in advance.

Regards

DeepakIf len(parameter)=4 then
--do some work
else
--do some other work

Do you want to do this from Front end application?

Formula to Summarizie records

Hi, I have a report which will obtain following set of data, for example:

NumberPacking CasePackingDesc Amount
2 Bottle 32kg
1 Bottle 10kg
2 Case 10kg
-----------
TOTAL 3 Bottle 52kg
2 Case

I have problem generating the TOTAL of 3 bottle and 2 case. I would like to write a formula field to return the result, but I don't know how to loop though all the records in the record sets.

Here is the formula field I have done so far:

Local stringVar temp := "";
Local numberVar i;
for i:= 1 to DistinctCount({Table.CasePackingDesc}) step 1 do
(
temp := temp + CStr (Sum ({@.NumberPacking})) + {Table.CasePackingDesc} + Chr(13);
);
temp

It basically return the same CasePackingDesc for 2 times.

Any idea on this will be greatly appreciated, thanks!have you tried the
WhilePrintingRecords
Statement to loop through the Datasets.

You dont need the for loop in the formulafield.
Write the Result of your account in a global variable.sql

Formula to Calculate weekdays in a month

I am having trouble coming up with a formula that will tell me how many weekdays there are in a month. I would need this formula to give me the number of weekdays in every month. Does anyone know how I would go about doing this? I have been struggling with this for 4 days now and ANY help would be GREATLY appreciated.

Thanks in advance.Hi,

Try this download.

Click here (http://support.businessobjects.com/communityCS/FilesAndUpdates/cr9_business_days_samples.zip.asp)

Formula Syntax

Is there a sood reference for the syntax that is used in formulas?
Here's the thing...
If .NET there is no IIF Function...so I think, its SQL Syntax.
Then there's the Bang notation (!) so I think its Access's syntax
But I can say:
=IIF(Fields!LAST_NAME.Value.indexOf("A") = 0, "Starts With 'A'", "Does not
start with 'A'")
...which is leveraging String.indexOf which is part of the .NET Model (C#?).
...but its not case sensitive (VB.NET?)
so if anyone out there had a really strong understanding of what I can do
syntactically, or exactly WHAT is parsing these expressions, I'd appreciate
it. It would help us all know what we can and can not do.Thank you for the comments. My observations are very similar to yours. I
believe the expression handler parallels the functions found here, which
you will notice are VB.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/
vaoriVBRuntimeLibraryKeywords.asp
| Thread-Topic: Formula Syntax
| thread-index: AcTFoYa5vwda7O/DQ22hld4dBt+/1w==| X-WBNR-Posting-Host: 216.88.246.15
| From: =?Utf-8?B?RGF2aWQgSmVzc2Vl?= <DavidJessee@.discussions.microsoft.com>
| Subject: Formula Syntax
| Date: Mon, 8 Nov 2004 06:45:01 -0800
| Lines: 15
| Message-ID: <E890CF96-C782-440B-A69B-14597AD8E89D@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:34345
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Is there a sood reference for the syntax that is used in formulas?
| Here's the thing...
|
| If .NET there is no IIF Function...so I think, its SQL Syntax.
| Then there's the Bang notation (!) so I think its Access's syntax
| But I can say:
| =IIF(Fields!LAST_NAME.Value.indexOf("A") = 0, "Starts With 'A'", "Does
not
| start with 'A'")
| ...which is leveraging String.indexOf which is part of the .NET Model
(C#?).
| ...but its not case sensitive (VB.NET?)
|
| so if anyone out there had a really strong understanding of what I can do
| syntactically, or exactly WHAT is parsing these expressions, I'd
appreciate
| it. It would help us all know what we can and can not do.
|
|

formula question

I have this crystal formula

How can this be converted to a reporting services Expression

if {ITEMCODE} in ["A","B"]

then "TypeA"

else if

{ITEMCODE} in ["C","D"]

then "TypeB"

else

"TypeC"

Use this:

=iif((Fields!ITEMCODE.Value = "A" OR Fields!ITEMCODE.Value = "B") ,"TypeA",IIF((Fields!ITEMCODE.Value = "C" OR Fields!ITEMCODE.Value = "D"),"TypeB","TypeC"))

Hope this will work.

Regards,

Manoj Verma

|||

Hi Manoj

Is this the only way.

one of my formulas may have to have upto 100 or's?

Thanks for your help

|||

I don't think you may have any other option or if it is there then i'm not able to figure out.

Sorry....

Regards

Manoj Verma

|||

Thanks for your help Manoj

I will have to group them another way using another table!

Formula Question

I have a field "Accept" that can be Null or contain either "Y" or "N". In my
test data I have a total of 4 rows, 2 with "Y", 1 with "N" and 1 Null. I
want a count of each case in the footer along with some descriptive text. I
tried the formula
=Count(Fields!Accept.Value="Y")
But it returns "4"? The same thing happens if I use
=Count(Fields!Accept.Value="N")
What is wrong in this formula?
Also, I really want the report to display something like: "Number of
Accepts: 2" but if I try to code the field as:
"Number of Accepts: " & =Count(Fields!Accept.Value="Y")
the formula displays as text?
TIA
Waynetry to write:
= "Number of Accepts: " & Sum(IIf(Fields!Accept.Value="Y",1,0))
Good Luck
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:uiB9sAU3FHA.3400@.tk2msftngp13.phx.gbl...
> I have a field "Accept" that can be Null or contain either "Y" or "N". In
my
> test data I have a total of 4 rows, 2 with "Y", 1 with "N" and 1 Null. I
> want a count of each case in the footer along with some descriptive text.
I
> tried the formula
> =Count(Fields!Accept.Value="Y")
> But it returns "4"? The same thing happens if I use
> =Count(Fields!Accept.Value="N")
> What is wrong in this formula?
> Also, I really want the report to display something like: "Number of
> Accepts: 2" but if I try to code the field as:
> "Number of Accepts: " & =Count(Fields!Accept.Value="Y")
> the formula displays as text?
>
> TIA
> Wayne
>
>|||Liz;
Thank you very much. I never would have figured that one out. Seems like
making a point the hard way. I've ordered a couple of books that I hope will
explain the logic of creating expressions.
Wayne
"Liz Matyas" <lizmts@.mail.com> wrote in message
news:uK3U8FZ3FHA.1276@.TK2MSFTNGP09.phx.gbl...
> try to write:
> = "Number of Accepts: " & Sum(IIf(Fields!Accept.Value="Y",1,0))
> Good Luck
>
> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
> news:uiB9sAU3FHA.3400@.tk2msftngp13.phx.gbl...
>> I have a field "Accept" that can be Null or contain either "Y" or "N". In
> my
>> test data I have a total of 4 rows, 2 with "Y", 1 with "N" and 1 Null. I
>> want a count of each case in the footer along with some descriptive text.
> I
>> tried the formula
>> =Count(Fields!Accept.Value="Y")
>> But it returns "4"? The same thing happens if I use
>> =Count(Fields!Accept.Value="N")
>> What is wrong in this formula?
>> Also, I really want the report to display something like: "Number of
>> Accepts: 2" but if I try to code the field as:
>> "Number of Accepts: " & =Count(Fields!Accept.Value="Y")
>> the formula displays as text?
>>
>> TIA
>> Wayne
>>
>>
>|||The Count RDL aggregate function works similar as the Count aggregate in
SQL: it will count all rows that are NOT NULL. The expresson
Fields!Accept.Value="Y" will return a boolean value (true/false) for all
rows, that's why the count results in the value 4.
Besides using =Sum(iif(...)) as shown by Liz, you could also use
=Count(iif(Fields!Accept.Value="Y", 1, Nothing))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:u8BwZda3FHA.128@.tk2msftngp13.phx.gbl...
> Liz;
> Thank you very much. I never would have figured that one out. Seems like
> making a point the hard way. I've ordered a couple of books that I hope
> will explain the logic of creating expressions.
> Wayne
> "Liz Matyas" <lizmts@.mail.com> wrote in message
> news:uK3U8FZ3FHA.1276@.TK2MSFTNGP09.phx.gbl...
>> try to write:
>> = "Number of Accepts: " & Sum(IIf(Fields!Accept.Value="Y",1,0))
>> Good Luck
>>
>> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
>> news:uiB9sAU3FHA.3400@.tk2msftngp13.phx.gbl...
>> I have a field "Accept" that can be Null or contain either "Y" or "N".
>> In
>> my
>> test data I have a total of 4 rows, 2 with "Y", 1 with "N" and 1 Null. I
>> want a count of each case in the footer along with some descriptive
>> text.
>> I
>> tried the formula
>> =Count(Fields!Accept.Value="Y")
>> But it returns "4"? The same thing happens if I use
>> =Count(Fields!Accept.Value="N")
>> What is wrong in this formula?
>> Also, I really want the report to display something like: "Number of
>> Accepts: 2" but if I try to code the field as:
>> "Number of Accepts: " & =Count(Fields!Accept.Value="Y")
>> the formula displays as text?
>>
>> TIA
>> Wayne
>>
>>
>>
>|||Robert;
Thanks for that additional information. It helps me understand the process.
Wayne
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:eZK0yma3FHA.3036@.TK2MSFTNGP10.phx.gbl...
> The Count RDL aggregate function works similar as the Count aggregate in
> SQL: it will count all rows that are NOT NULL. The expresson
> Fields!Accept.Value="Y" will return a boolean value (true/false) for all
> rows, that's why the count results in the value 4.
> Besides using =Sum(iif(...)) as shown by Liz, you could also use
> =Count(iif(Fields!Accept.Value="Y", 1, Nothing))
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
> news:u8BwZda3FHA.128@.tk2msftngp13.phx.gbl...
>> Liz;
>> Thank you very much. I never would have figured that one out. Seems like
>> making a point the hard way. I've ordered a couple of books that I hope
>> will explain the logic of creating expressions.
>> Wayne
>> "Liz Matyas" <lizmts@.mail.com> wrote in message
>> news:uK3U8FZ3FHA.1276@.TK2MSFTNGP09.phx.gbl...
>> try to write:
>> = "Number of Accepts: " & Sum(IIf(Fields!Accept.Value="Y",1,0))
>> Good Luck
>>
>> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
>> news:uiB9sAU3FHA.3400@.tk2msftngp13.phx.gbl...
>> I have a field "Accept" that can be Null or contain either "Y" or "N".
>> In
>> my
>> test data I have a total of 4 rows, 2 with "Y", 1 with "N" and 1 Null.
>> I
>> want a count of each case in the footer along with some descriptive
>> text.
>> I
>> tried the formula
>> =Count(Fields!Accept.Value="Y")
>> But it returns "4"? The same thing happens if I use
>> =Count(Fields!Accept.Value="N")
>> What is wrong in this formula?
>> Also, I really want the report to display something like: "Number of
>> Accepts: 2" but if I try to code the field as:
>> "Number of Accepts: " & =Count(Fields!Accept.Value="Y")
>> the formula displays as text?
>>
>> TIA
>> Wayne
>>
>>
>>
>>
>sql

Formula Problem

HI All,

I have made a formula in the formula field.
How can I make use of that formula to the report?
And other question,
is the syntax of the sql expression same as the syntax of sql
and what will it return and how to apply on the report?

Thanks for answer my stupid questionhi, :wave:
To use the formula in the report..drag it and place it whereever u want to display it...if i am right?

Yes the syntax of the sql expresstion is same as sql..but in where condition if u want to use any fields in the report..dont type it urself..just double click the field from the report fields in the formula editor..

Regards,
Rohini :thumb:|||Thanks for your reply

But I still don't know how to use the sql expression field
Can you show me a step by step example for me?

Thanks~~~~

Formula Problem

I get the following error in Report Manager when running a report. The
report runs perfect in visual studio when I preview it.
Reporting Services Error
_____
* An error has occurred during report processing. (rsProcessingAborted)
Get Online Help
* The sort expression for the grouping 'table1_Group1' uses a numeric
aggregate function on data that is not numeric. Numeric aggregate
functions (Sum, Avg, StDev, Var, StDevP, and VarP) can only aggregate
numeric data. (rsProcessingError) Get Online Help
_____
Microsoft Reporting Services
I pass two parameters, qty and sortdate. As the formulas show below,
when they select qty and ascending, it uses sum(fields). When I pick
date, it does not use sum.
This will sort by the value in the sortby parameter. Used for sortdate
field in data.
=iif(Parameters!Direction.Value="Descending" AND
Parameters!SortBy.Value<>"quantity",Fields(Parameters!SortBy.Value).Value,0)
When I need to sort by the sum of the qty since it is grouped, I do
this...
=iif(Parameters!Direction.Value="Descending" AND
Parameters!SortBy.Value="quantity",sum(Fields(Parameters!SortBy.Value).Value),0)
As I said above, this works perfect in visual studio when I preview it.
Any Ideas?What is the datatype of Fields!quantity.Value? Most likely it is not a
System.Int32 at runtime.
You may want to try this (assuming you want to sum up integers):
=iif(Parameters!Direction.Value="Descending" AND
Parameters!SortBy.Value="quantity",
sum(CInt(Fields(Parameters!SortBy.Value).Value)), 0)
Or use this if you want to actually sum double:
=iif(Parameters!Direction.Value="Descending" AND
Parameters!SortBy.Value="quantity",
sum(CDbl(Fields(Parameters!SortBy.Value).Value)), 0.0)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Geddes" <john_g@.alamode.com> wrote in message
news:eTl79Cj4EHA.3504@.TK2MSFTNGP12.phx.gbl...
> I get the following error in Report Manager when running a report. The
> report runs perfect in visual studio when I preview it.
> Reporting Services Error
> _____
>
> * An error has occurred during report processing. (rsProcessingAborted)
> Get Online Help
> * The sort expression for the grouping 'table1_Group1' uses a numeric
> aggregate function on data that is not numeric. Numeric aggregate
> functions (Sum, Avg, StDev, Var, StDevP, and VarP) can only aggregate
> numeric data. (rsProcessingError) Get Online Help
> _____
> Microsoft Reporting Services
>
> I pass two parameters, qty and sortdate. As the formulas show below,
> when they select qty and ascending, it uses sum(fields). When I pick
> date, it does not use sum.
> This will sort by the value in the sortby parameter. Used for sortdate
> field in data.
> =iif(Parameters!Direction.Value="Descending" AND
>
Parameters!SortBy.Value<>"quantity",Fields(Parameters!SortBy.Value).Value,0)
> When I need to sort by the sum of the qty since it is grouped, I do
> this...
> =iif(Parameters!Direction.Value="Descending" AND
>
Parameters!SortBy.Value="quantity",sum(Fields(Parameters!SortBy.Value).Value
),0)
>
> As I said above, this works perfect in visual studio when I preview it.
> Any Ideas?
>

Formula problem

env: sql server 2000

objective:
add a formula for an INT column to the FORMULA field or the DEFAULT
VALUE field in DESIGN VIEW.

all of the following attempts failed
IIF (columnName = 0, "1", columnName + 1)
IIF (columnName = 0, 1, columnName + 1)
IIF (0, "1", columnName + 1)

caveat: cannot use IDENTITY
REASON: I'd like have sets of repeatable values for this row, e.g.
set a
1 -- row 1
2 -- row 2
3
4
5
set b
1 -- row 6
2
3
set c
1 -- row 9
2
3
4

Underlying rationale is to support OO design I understand probably it's
going to be huge headache for lots of people down the road but ...

TIA.NickName (dadada@.rock.com) writes:
> env: sql server 2000
> objective:
> add a formula for an INT column to the FORMULA field or the DEFAULT
> VALUE field in DESIGN VIEW.
> all of the following attempts failed
> IIF (columnName = 0, "1", columnName + 1)
> IIF (columnName = 0, 1, columnName + 1)
> IIF (0, "1", columnName + 1)

I will have to admit that I don't understand much of your post.
IIF is Access/VB, but I guess you know that.

In case not, the syntax in SQL server is

CASE WHEN columnName = 0 THEN 1 ELSE columnName + 1 END

Then again, this can be simiplied to:

columnName + 1

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Oops, haven't touched db for a couple of months, now it seems that I
totally forgot everything or mix everything. Ok, joke aside, case stmt
does not seem to work, strange.|||Ahe, because of the default NULL value.

Formula Parsing

Hi,

I have three tables in the following structure (simplified):

Table 1: Containing the customers
---------------
create table Customers
(
[cusID] int identity(1, 1) not null,
[cusName] varchar(25) not null
)

Table 2: Containing the customer data fields
-------------------
create table Data
(
[datID] int identity(1, 1) not null,
[datName] varchar(25) not null,
[datFormula] varchar(1500)
)

Table 3: Containing the customer data values
--------------------
create table Values
(
[cusID] int not null,
[datID] int not null,
[valValue] sql_variant
)

In this structure the user can add as many data fields to a customer as
he wants (e.g. Country, City, Email, Phone, ...). I have added triggers
which create a view similar to a pivot (I am working in SQL 2000) and
add triggers to the view so it is insertable, deletable and updateable.

What I would like to do, is allow the user to create new fields where
the values are based upon a calculation. This calculation would be done
through a formula similar to what he would do e.g. in excel (this
formula is stored in the dimFormula field then).

An example might help. Let's assume the user created a field 'Sales'
(containing last year's sales) and 'Invoices' (containing the number of
invoices that were created for him last year). Now, he wants to create
a field 'AvgSales' with the formula '[Sales]/[Invoices]'.

(Note that through adding these data fields, the above view was created
(let's assume it is called vw_Customers and contains the columns [ID],
[Name], [Sales], [Invoices], [AvgSales]).

What I am looking for is a function which can parse this formula into a
t_sql query which runs the calculation. So, the formula
'[Sales]/[Invoices]' would be translated into (let's assume there are
no records with NULL or zero invoices):

update vw_Customers
set [AvgSales] = [Sales]/[Invoices]
from vw_Customers

I am able to do the above with simple calculations (where you can even
use sql functions e.g. year, len, ...). Now I would like to take this
one step forward into the possibility of using functions with more
variables.

For example. Let's assume, the user wants to add a rating (field called
'Rating') to his customers based upon the result of 'AvgSales. He
enters the formula 'if([AvgSales] > 2500, 'A', 'B')'.

If anyone could help me on this, I would be very grateful. Thanks.

MMike wrote:
> Hi,
> I have three tables in the following structure (simplified):
> Table 1: Containing the customers
> ---------------
> create table Customers
> (
> [cusID] int identity(1, 1) not null,
> [cusName] varchar(25) not null
> )
> Table 2: Containing the customer data fields
> -------------------
> create table Data
> (
> [datID] int identity(1, 1) not null,
> [datName] varchar(25) not null,
> [datFormula] varchar(1500)
> )
> Table 3: Containing the customer data values
> --------------------
> create table Values
> (
> [cusID] int not null,
> [datID] int not null,
> [valValue] sql_variant
> )
> In this structure the user can add as many data fields to a customer as
> he wants (e.g. Country, City, Email, Phone, ...). I have added triggers
> which create a view similar to a pivot (I am working in SQL 2000) and
> add triggers to the view so it is insertable, deletable and updateable.
> What I would like to do, is allow the user to create new fields where
> the values are based upon a calculation. This calculation would be done
> through a formula similar to what he would do e.g. in excel (this
> formula is stored in the dimFormula field then).
> An example might help. Let's assume the user created a field 'Sales'
> (containing last year's sales) and 'Invoices' (containing the number of
> invoices that were created for him last year). Now, he wants to create
> a field 'AvgSales' with the formula '[Sales]/[Invoices]'.
> (Note that through adding these data fields, the above view was created
> (let's assume it is called vw_Customers and contains the columns [ID],
> [Name], [Sales], [Invoices], [AvgSales]).
> What I am looking for is a function which can parse this formula into a
> t_sql query which runs the calculation. So, the formula
> '[Sales]/[Invoices]' would be translated into (let's assume there are
> no records with NULL or zero invoices):
> update vw_Customers
> set [AvgSales] = [Sales]/[Invoices]
> from vw_Customers
> I am able to do the above with simple calculations (where you can even
> use sql functions e.g. year, len, ...). Now I would like to take this
> one step forward into the possibility of using functions with more
> variables.
> For example. Let's assume, the user wants to add a rating (field called
> 'Rating') to his customers based upon the result of 'AvgSales. He
> enters the formula 'if([AvgSales] > 2500, 'A', 'B')'.
> If anyone could help me on this, I would be very grateful. Thanks.
> M

The best advice I can give you is to not try doing this with pure SQL.
You'll save yourself a lot of headache if you take some data that's a
little more "raw" and manipulate it in some other programming language
to get the desired result.|||Mike (michael.matthys@.hotmail.com) writes:
> In this structure the user can add as many data fields to a customer as
> he wants (e.g. Country, City, Email, Phone, ...). I have added triggers
> which create a view similar to a pivot (I am working in SQL 2000) and
> add triggers to the view so it is insertable, deletable and updateable.
> What I would like to do, is allow the user to create new fields where
> the values are based upon a calculation. This calculation would be done
> through a formula similar to what he would do e.g. in excel (this
> formula is stored in the dimFormula field then).
>...
> For example. Let's assume, the user wants to add a rating (field called
> 'Rating') to his customers based upon the result of 'AvgSales. He
> enters the formula 'if([AvgSales] > 2500, 'A', 'B')'.

I can only echo "ZeldorBlat" don't do this in SQL. If you had been on
SQL 2005, you could possibly have used CLR modules for the task.

But I wonder if you are not barking up the wrong tree entirely. Have
you looked at Analysis Services? I'm completely ignorant about Analysis
Services myself, but I would not be surprised if it has some support
for what you are trying to do.

If you are dead set on doing this in SQL 2000, you have to choices:
1) require that the user uses T-SQL syntax, for instance
CASE WHEN [AvgSales] THEN 'A' ELSE 'B' END
2) Define you own forumla language, and parse it in client code and
define the columns in the views as the users defines his formulas.

Beside AS, you could also investigate what 3rd party products out
there that may address your needs.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Look up the EAV design flaw you have re-discovered and stop writing SQL
like this. SQL is not a computational language; it is a database
language.

formula not working

This does nothing:
([rush24] + [rush6] + [addLocationsTotal] + 50)

This gives me my total correctly:
([rush24] + [rush6] + 50)

All column are numeric(9) except the "total" column which is numeric(13).

Why is this happening?

I did not quite understand your question, could you describe your issue in more detail?sql

Formula Issue

Thanks in advance for any help.
I am spoofing rows to force an X # of rows in a table, which is working
well. However, the spoofed rows are causing an error with the formula
below.
=cstr(iif(Fields!ServiceEnd.Value.ToString = "", " ",
cstr(DatePart("m", Fields!ServiceEnd.Value))))
Without the formula, the rows have no data and no error. Any
suggestions?
Thanks,
MorganNot quite sure what yo're trying to do but might one of these formulas work:
=Iif(IsDate(Fields!ServiceEnd), cstr(DatePart("m",
Fields!ServiceEnd.Value)), "")
OR
=Iif(IsNothing(Fields!ServiceEnd), "", cstr(DatePart("m",
Fields!ServiceEnd.Value)))
HTH
--
Magendo_man
Freelance SQL Reporting Services developer
Stirling, Scotland
"Morgan" wrote:
> Thanks in advance for any help.
> I am spoofing rows to force an X # of rows in a table, which is working
> well. However, the spoofed rows are causing an error with the formula
> below.
> =cstr(iif(Fields!ServiceEnd.Value.ToString = "", " ",
> cstr(DatePart("m", Fields!ServiceEnd.Value))))
> Without the formula, the rows have no data and no error. Any
> suggestions?
>
> Thanks,
> Morgan
>|||Thank you.
A slightly modified version of your suggestions seemed to do the trick.
=iif(IsDate(Fields!ServiceBegin.Value), Day(Fields!ServiceBegin.Value),
"")
magendo_man (donotspam) wrote:
> Not quite sure what yo're trying to do but might one of these formulas work:
> =Iif(IsDate(Fields!ServiceEnd), cstr(DatePart("m",
> Fields!ServiceEnd.Value)), "")
> OR
> =Iif(IsNothing(Fields!ServiceEnd), "", cstr(DatePart("m",
> Fields!ServiceEnd.Value)))
> HTH
> --
> Magendo_man
> Freelance SQL Reporting Services developer
> Stirling, Scotland
>
> "Morgan" wrote:
> > Thanks in advance for any help.
> >
> > I am spoofing rows to force an X # of rows in a table, which is working
> > well. However, the spoofed rows are causing an error with the formula
> > below.
> >
> > =cstr(iif(Fields!ServiceEnd.Value.ToString = "", " ",
> > cstr(DatePart("m", Fields!ServiceEnd.Value))))
> >
> > Without the formula, the rows have no data and no error. Any
> > suggestions?
> >
> >
> > Thanks,
> >
> > Morgan
> >
> >

formula in sql server

hi,
my users can make posts in my web application, i mean they fill a form and the information they filled will be saved in sql server 2000 and can be shown in web application,now i want to give each post an Id and save it in the database, how can i do that? does sql server have the abilities or i should do sth in my c# application

thanx

This is something that is best to let the database handle for you.

Set your ID column as an Identity column in your sql table.

Sql server will then set its value to a unique auto incrementing value automatically every time a new row is inserted.

Formula in SQL Enterprise manager

I have a table with 2 columns and I want to have a 3rd column that combines the 1st and 2nd column automatically and populates the 3rd column.

i know this can be done with the Formula option in the 3rd column but I dont know how to combine columns. One is of datatype int and the other is of datatype varchar.

Here are my column names etc.

name var
-------
step int
action varchar

thankscast the int as a varchar and concatenate them both together in an update statement

Formula in flaot field

I have a float field and need the data to present itself as a datetime. I have been unsuccessful at converting the entire field to datetime so I thought I would try a formula but have had no success. Can this be done?

I have data like so:

100599
10699
120597
etc...

and need to hit them as though they were datetime for order by and such...

or an 'in query' conversion...

ThanksI came up with a solution, thanks anyway.sql

Formula in Excel Sheet

Hello,

I have one simple Report in SQL Reporting Services.

there are only Three Columns GroupName, Description, Amount.

In this report there is Grouping on GroupName Field.

In Group Footer in want Group Total. i'm writting here like =Sum(Fields!Amount.Value)

Now, while preview of this report I'm exporting the same to Excel File.

While opening that Excel file in Group Footer there is no Formula which i written in .rdl file.

If any one can help me out on this Formula field it will be great.

Thank You.

Unfortunately the Excel export doesn't support this.

Formulas are only translated to Excel formulas when they use references to report items instead of fields. For example, instead of "=Fields!Amount.Value" you would use "=ReportItems!AmountTextBox.Value". You can use these to get add two values together on the same row, for instance.

However, you can only use aggregate functions such as SUM on report items when the expression is in the page header or footer. So you won't be able to use, say "=SUM(ReportItems!AmountTextBox.Value" in the table footer.

We are aware that this severely limits the utility of formulas in Excel, and are considering ways to improve it. We do not have a timeframe for this at this time.

Formula in a View

I'm trying to perform the following within a view. I'm not getting the
values which I would expect. I'm assuming I have a data type problem where
some precision is being dropped. Could someone give me a heads-up on what
I'm doing wrong? I need full precision.
SUM(((MarketPrice+MarketPriceUp100-(2*MarketPriceUp50))/(MarketPriceUp50*((1
00/10000)^2))/100)
* MarketValue)/ SUM(MarketValue) as [Convexity],
Thanks,
Tom Woods
The Baker Group, LP"Tom Woods" <twoods@.jamesbaker.com> wrote in message
news:OgZCcxTvFHA.2396@.TK2MSFTNGP14.phx.gbl...
> I'm trying to perform the following within a view. I'm not getting the
> values which I would expect. I'm assuming I have a data type problem
> where some precision is being dropped. Could someone give me a heads-up
> on what I'm doing wrong? I need full precision.
>
> SUM(((MarketPrice+MarketPriceUp100-(2*MarketPriceUp50))/(MarketPriceUp50*(
(100/10000)^2))/100)
> * MarketValue)/ SUM(MarketValue) as [Convexity],
>
What are the types of those columns?
Integral types use integer division.
David|||All columns in the formula are 9(12,8).
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uyQL80TvFHA.1560@.TK2MSFTNGP09.phx.gbl...
> "Tom Woods" <twoods@.jamesbaker.com> wrote in message
> news:OgZCcxTvFHA.2396@.TK2MSFTNGP14.phx.gbl...
> What are the types of those columns?
> Integral types use integer division.
> David
>|||Hi Tom
This piece of the expression (100/10000) will be carried out as integer
division, and yield 0. If you want decimal results, make at least one of the
operands a decimal (100.0/10000)
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tom Woods" <twoods@.jamesbaker.com> wrote in message
news:OgZCcxTvFHA.2396@.TK2MSFTNGP14.phx.gbl...
> I'm trying to perform the following within a view. I'm not getting the
> values which I would expect. I'm assuming I have a data type problem
> where some precision is being dropped. Could someone give me a heads-up
> on what I'm doing wrong? I need full precision.
>
> SUM(((MarketPrice+MarketPriceUp100-(2*MarketPriceUp50))/(MarketPriceUp50*(
(100/10000)^2))/100)
> * MarketValue)/ SUM(MarketValue) as [Convexity],
> Thanks,
> Tom Woods
> The Baker Group, LP
>|||When I change it to the following I get an error. Could the problem be with
the exponent? Is there a different way of performing the exponential?
((100.0/10000)^2)
BTW, I get the following error when changing 100 to 100.0
--
Microsoft SQL-DMO (ODBC SQLState: 42000)
--
Error 403: Invalid operator for data type. Operator equals boolean XOR, type
equals numeric.
--
OK
--
Thanks,
Tom
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:usIhD4TvFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi Tom
> This piece of the expression (100/10000) will be carried out as integer
> division, and yield 0. If you want decimal results, make at least one of
> the operands a decimal (100.0/10000)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Tom Woods" <twoods@.jamesbaker.com> wrote in message
> news:OgZCcxTvFHA.2396@.TK2MSFTNGP14.phx.gbl...
>
>|||^ operator is bitwise exclusive OR operation for integers. For getting
exponential values as results you might want to try POWER function like:
SELECT POWER( 100.0/10000, 2 )
Anith|||Can you use (1.0/10000) instead of ((100.0/10000)^2)?
Perayu
"Tom Woods" <twoods@.jamesbaker.com> wrote in message
news:uVvmS$TvFHA.596@.TK2MSFTNGP12.phx.gbl...
> When I change it to the following I get an error. Could the problem be
> with the exponent? Is there a different way of performing the
> exponential?
> ((100.0/10000)^2)
>
> BTW, I get the following error when changing 100 to 100.0
> --
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> --
> Error 403: Invalid operator for data type. Operator equals boolean XOR,
> type equals numeric.
> --
> OK
> --
> Thanks,
> Tom
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:usIhD4TvFHA.1032@.TK2MSFTNGP12.phx.gbl...
>|||I was able to get it to work by using the POWER function and also changing
the integer values to have precision.
Thanks,
Tom
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:uPKrC0UvFHA.908@.tk2msftngp13.phx.gbl...
> Can you use (1.0/10000) instead of ((100.0/10000)^2)?
> Perayu
> "Tom Woods" <twoods@.jamesbaker.com> wrote in message
> news:uVvmS$TvFHA.596@.TK2MSFTNGP12.phx.gbl...
>

Formula Help for a new user

I am brand new to CR... i have version 10. My work jsut threw this on me and i am about clueless. I have a variable that is giving me multiple occurances of the same data. I am pulling in account numbers in (with other fields too) in a certain date range. What would be a good formula to tell it to ONLY show me each account # once?If your variable is in a field, you can set the field's 'SuppressIfDuplicate' to True. Or in a formula, you can compare the field in the current record to the previous or next record by doing:

If {FieldName} = Next ({FieldName} Then
//Code for duplicate values
Else
//Code for non-duplicate values

Just replace 'Next' with 'Previous' if you want to look at the previous record.

(I'm running CR 8.5, so the syntax may be a little different than in 10)|||Thanks for your help... i'll try the suppress first... but i'll hold ont to that Formula for future reference also.

_________________________________________________________________

If your having a bad day dont worry it'll change; if your having a good day dont worry it'll change too.

Formula Help :)

I have a matrix table & have a picture next to table which should display
when the percentage of the 2 colums is less than 5%.
My guess on this formula is which doesnt work.
=IIF sum((count(iif( Fields!SwitchDispId.Value, "QueueCallDetail") = 16),
"b") )/ ((count(iif( Fields!SwitchDispId.Value, "QueueCallDetail") = 1), "b")
IS < .05
Firstly i just want to total the 2 columns (disposition 16 & 1) then get a
percentage.
I would also like to get a percentage om each row as well.
Please help.
Thanks
toddI started trying to write your formula for you this morning, but discovered
I don't really understand what you are trying to do..
If you post more details ( what is the 16 and 1 for? what is "b" for. etc),
someone will be able to help you...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tango" <Tango@.discussions.microsoft.com> wrote in message
news:528A7104-0F8A-45A7-A38A-F0CA7C776C8D@.microsoft.com...
>I have a matrix table & have a picture next to table which should display
> when the percentage of the 2 colums is less than 5%.
> My guess on this formula is which doesnt work.
> =IIF sum((count(iif( Fields!SwitchDispId.Value, "QueueCallDetail") = 16),
> "b") )/ ((count(iif( Fields!SwitchDispId.Value, "QueueCallDetail") = 1),
> "b")
> IS < .05
> Firstly i just want to total the 2 columns (disposition 16 & 1) then get a
> percentage.
> I would also like to get a percentage om each row as well.
> Please help.
> Thanks
> todd|||Sorry wayne. I have a matrix table that has 2 colum groups. Those that have a
number 16 & the second column counts those records with a number 1. the b was
just something that i coped from a different formula.
so in summery i have various groups down the left hand side. at the top of
have a count of the number of 16's in the first column & & a count of the
number 1's in the second column.
These matrix tables are hard to understand..
Thanks
Todd
"Wayne Snyder" wrote:
> I started trying to write your formula for you this morning, but discovered
> I don't really understand what you are trying to do..
> If you post more details ( what is the 16 and 1 for? what is "b" for. etc),
> someone will be able to help you...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Tango" <Tango@.discussions.microsoft.com> wrote in message
> news:528A7104-0F8A-45A7-A38A-F0CA7C776C8D@.microsoft.com...
> >I have a matrix table & have a picture next to table which should display
> > when the percentage of the 2 colums is less than 5%.
> >
> > My guess on this formula is which doesnt work.
> >
> > =IIF sum((count(iif( Fields!SwitchDispId.Value, "QueueCallDetail") = 16),
> > "b") )/ ((count(iif( Fields!SwitchDispId.Value, "QueueCallDetail") = 1),
> > "b")
> > IS < .05
> >
> > Firstly i just want to total the 2 columns (disposition 16 & 1) then get a
> > percentage.
> >
> > I would also like to get a percentage om each row as well.
> >
> > Please help.
> > Thanks
> > todd
>
>

Formula help ... With Dates

I am pulling in the 2 digit month 01,02,03 etc.... and the four digit year(2005)
I want a formula to only pull the previous 12 months...
Any help... Ideas? I am fairly new to CR... not good w/ the formulas yet.Create a formula and put your date field in there and subtract 365 (the number of days in a year. Also, there is a Year(X) function, this should also prove to be useful.sql

Formula Help

I have a report that I need to take the average of a total(s)...
For example
I have a field that is a sum of units(number) and then I have a field that is a count of payments(number). I need to average these two fields,so that I have the average # of payments?How can I do this?I would try creating a new formula and expand the "REPORT FIELDS" in the tree within the formula editor (2nd one from left) and pull the applicable summary fields in. I'm not positive that would work as I haven't tried it before.|||I've tried that but it gives me an error of

The Summary/Running Total field cannot be created.|||I just tested it in a report I have and it worked just fine. Where are you trying to use it? You will not be able to use it in the details, but it would have to fall in the same group footer as the summary.|||These calculations are in the group footer. I need to average the grand totals.

What formula are you using? These are the two fields that I need to use

(Sum ({CLCHARGE.CUNITS}, {CLPAYMNT.COMPANY}) and
Count({CLPAYMNT.CHGID},{CLPAYMNT.COMPANY})|||Sorry I didn't ask this in the previous post, but what version of CR are you using? I am on 10, so if you are earlier, maybe it doesn't support those calculations in the same way it does for me. If you are 10 or XI, then I would need to ask - did you create formulas for the summaries or did you use the INSERT / SUMMARY from the menu bar? If you created the summaries by letting CR do it via the INSERT / SUMMARY, then you create a separate formula that drags those summaries in to the formula editor (typing a "/" between them), it should work.|||I am using 8.5|||It may be that you can't do it from 8.5. I'm sorry that I couldn't help you.|||I was afraid of that...Thanks for you help anyway.|||Hi there I have an average formula setup in one of my reports using CR 8.5.

whileprintingrecords;

numbervar TotalTsec;
local numbervar AverageFix:=TotalTsec/DistinctCount ({SW_CASE.swCaseId});
numbervar LenOfDay;

Hope this points you in the right direction !

Many Thanks

Formula help

Report Field (LastApptDate)
I want to take the CurrentDate - LastApptDate and if its Less than 1500 days, to produce an "X" or else nothing
The second part is CurrentDate - LastApptDate - if it greater than 1500 days to produce an "X" else nothingI just started a new job and don't have access to anything yet, but if I had access to some data to test it, I think it would go something like this:

if DateDiff("d",LastApptDate,CurrentDate) >= 1500 then

"x"

else

""

or maybe like this:

if LastApptDate >= DateAdd("d", -1500, CurrentDate) then

"X"

else

""

Formula for running total

Hi,
Probably very simple but I've just started learning this but how would i do a running total in CR11 I have done the basic field but what I need is something like...
If Qty_Field = Price_field then * to give me final price
Can any help me?
ThanksI think you can use this in the Use formula option

Formula for Record Calculation

I have created two commands in the database expert. And I want to do the calulation of these records.

My situation is:

In table A, I have fields "Country","Amount" and "Currency".
In table B, I have fields "Currency" and "Exchange".

I want get the sum of "Amount"*"Exchange" if table A "Currency" = table B "Currency" that group by "Country"

How to write this formula?

Thank you fro helping me.Have u linked these commands by currency?|||Please tell me how to linkand what I should do after linking?
Thanks very much|||I have linked the currency field of two commands.

My situation is tableB store the currency of all countries in the world. And some of the countries using same currency. If I use a sql statement to join two table, then the records will be duplicated and cannot get the correct exchange rate of each currency.
So that I want to write a formula that caluclate the tableA amount with correct currency. But I don't know how to get the exchange rate of tableB if tableA.currency=tableB.currency.

Do you have any idea to do that?
thanks alot~~~|||I am using mysql 4.0 which is not supported subquery.

If you have any idea other than my method. Please tell me. This problem spent me a week ago~

Really thanks alot~

Formula for Graph Size

I have a report control in my report. But I want to expand graph depending on the selected number of months (Parameter) by users.

But there is no formula option for graph control. Is there any workaround?

You might be able to do something with padding. But that would not change the space your graph takes up in the page, you would just have more (or less) white space, depending on your current data.

>L<

sql

Formula for disk space

I am trying to work out a formula that works out the change in disk size on our servers and whether they have increased/decreased in size. We have created a table on our database server, that looks at the disk space used, and total disk size. This was created on the 23 March 2007. Effectively the report needs to show the disk history from this date. I have added a parameter on my Crystal report to select the date to view the disk space history, but when I look at the database, the figures I get on my report do not correspond with what it should read. Any help with getting this formula to work would be appreciatedis the 23 March 2007 data remaining static ? if not, then you will have issues.

You should be able to write a simple formula

{field.march23} - {currentvalue}

Your result should be the march23 size, - the current size

without an example, it is difficult to know

Formula for calculated column

Hi,
I'm struggling to get a calculated column to work in sql, the fields to be calculated are:
[AdRevenue_a] money
[Admissions_a] int
[DoorPrice_a] smallmoney
[DoorSplit_a] money
And the calculation I require is:
(AdRevenue_a / ( (Admissions_a * DoorPrice_a) - DoorSplit_a )) * 100
This is what I think it should be but it doesn't work...
convert(decimal(6,2), ((AdRevenue_a / ((Admissions_a * DoorPrice_a) - DoorSplit_a))*100) ))

Any suggestions??

(AdRevenue_a / ( (Admissions_a * DoorPrice_a) - DoorSplit_a )) * 100
should work.
In this:
convert(decimal(6,2), ((AdRevenue_a / ((Admissions_a * DoorPrice_a) - DoorSplit_a))*100) ))
you have an extra bracket at the end and it will work if you remove it.|||That didn't fix the issue.
The problematic field seems to be DoorSplit_a, if this is removed the rest of the calculation works??
|||Do you have any sample data that you can provide. Also are any of the columns nullable? IF so you might need to use ISNULL() appropriately.
If I supplied all the values properly it seemed to work:

DECLARE
@.AdRevenue_amoney,
@.Admissions_aint
,@.DoorPrice_asmallmoney
,@.DoorSplit_amoney

select
@.AdRevenue_a= 100
,@.Admissions_a= 50
,@.DoorPrice_a= 2
,@.DoorSplit_a= 25

select(@.AdRevenue_a/((@.Admissions_a* @.DoorPrice_a)- @.DoorSplit_a))* 100

selectconvert(decimal(6,2),((@.AdRevenue_a/((@.Admissions_a* @.DoorPrice_a)- @.DoorSplit_a))*100))

|||All aggregate functions in SQL Server ignore NULL values except COUNT (*), and the ISNULL function will replace NULL with 0 which could give you wrong numbers if [DoorSplit_a] allows NULL. Try the link below for more info. Hope this helps
http://www.akadia.com/services/dealing_with_null_values.html|||Thanks for the replies.
I'm not even getting as far as the data.
The error is thrown by sql when I try to enter the calculation into the formula box.|||Please post the exact formula you are now trying to enter. As Dinakar pointed out, yourfirst example had a mistmatched number of opening and closingparentheses.
Also, for future questions, please use a more specific term than "doesnot work". An exact description of the error you are encounteringwill go a long way towards pinpointing and correcting your problem.

Formula for adding two column amts together

Can anyone tell me what formula I need to do to add the amounts from summarized data in one column to the same in another column to get a total for both columns?

I have tried {file.name} + {file.name2} and variations on this (using Sum), but this doesn't add them together into one amount.

Thanks!

Julesuse

summary fields1+summary field2 in formula

ex:
Sum ({@.aa}, {Command.GRPNO})+Sum ({Command.ACCID}, {Command.GRPNO})

Formula field vs. no formula field

Dear all,
Sounds like a simple question.
I have three fields in my table
FieldA
FieldB
FieldC
FieldD
FieldC = FieldA * FieldB
FieldD = 0.01 * FieldA * FieldB
(actually the scenario is slightly complex that this)
I have two options
1. Declare FieldC and FieldD as formula fields and relax myself by
bothering only about FieldA and FieldB from my asp.net application
2. Worry about updating FieldC and FieldD as "there could be a serious
performance difference"
Updates happen from different screens, and it would make a deal of
difference to me if i can allow sql to take care of the several formula
fields i want to keep. At the same time, i don't want my customers to
come crying saying the system is very slow.
Any advice?
Thanks
Hi
If your program unnecessarily updates column A and B then you will probably
be better off with a view, although you would really want to fix the
programming! It is not so much where your updates occur, but the number and
performance of inserts/updates/selects which decide which way is better!
It also sounds like you are accessing the tables directly, therefore I would
also recommend using stored procedures.
John
"mich_stone@.yahoo.com" wrote:

> Dear all,
> Sounds like a simple question.
> I have three fields in my table
> FieldA
> FieldB
> FieldC
> FieldD
> FieldC = FieldA * FieldB
> FieldD = 0.01 * FieldA * FieldB
> (actually the scenario is slightly complex that this)
> I have two options
> 1. Declare FieldC and FieldD as formula fields and relax myself by
> bothering only about FieldA and FieldB from my asp.net application
> 2. Worry about updating FieldC and FieldD as "there could be a serious
> performance difference"
> Updates happen from different screens, and it would make a deal of
> difference to me if i can allow sql to take care of the several formula
> fields i want to keep. At the same time, i don't want my customers to
> come crying saying the system is very slow.
> Any advice?
> Thanks
>
|||Defining a formula (computed column) such as:
CREATE TABLE t(c1 int, c2 AS c1 * 100)
doesn't store the calculated value. I.e. no penalty for modifications. However, for below search
argument, SQL Server can (probably) not use an index on c1:
WHERE c2 = 10000
So, you can create an index on a computed column where the value is actually stored. And in this
case, you do pay for modifications.
An alternative to computed columns is to create a view with the calculated values and have your
users SELECT from the view.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
<mich_stone@.yahoo.com> wrote in message news:1107240862.212907.54340@.c13g2000cwb.googlegro ups.com...
> Dear all,
> Sounds like a simple question.
> I have three fields in my table
> FieldA
> FieldB
> FieldC
> FieldD
> FieldC = FieldA * FieldB
> FieldD = 0.01 * FieldA * FieldB
> (actually the scenario is slightly complex that this)
> I have two options
> 1. Declare FieldC and FieldD as formula fields and relax myself by
> bothering only about FieldA and FieldB from my asp.net application
> 2. Worry about updating FieldC and FieldD as "there could be a serious
> performance difference"
> Updates happen from different screens, and it would make a deal of
> difference to me if i can allow sql to take care of the several formula
> fields i want to keep. At the same time, i don't want my customers to
> come crying saying the system is very slow.
> Any advice?
> Thanks
>

Formula field vs. no formula field

Dear all,
Sounds like a simple question.
I have three fields in my table
FieldA
FieldB
FieldC
FieldD
FieldC = FieldA * FieldB
FieldD = 0.01 * FieldA * FieldB
(actually the scenario is slightly complex that this)
I have two options
1. Declare FieldC and FieldD as formula fields and relax myself by
bothering only about FieldA and FieldB from my asp.net application
2. Worry about updating FieldC and FieldD as "there could be a serious
performance difference"
Updates happen from different screens, and it would make a deal of
difference to me if i can allow sql to take care of the several formula
fields i want to keep. At the same time, i don't want my customers to
come crying saying the system is very slow.
Any advice?
ThanksHi
If your program unnecessarily updates column A and B then you will probably
be better off with a view, although you would really want to fix the
programming! It is not so much where your updates occur, but the number and
performance of inserts/updates/selects which decide which way is better!
It also sounds like you are accessing the tables directly, therefore I would
also recommend using stored procedures.
John
"mich_stone@.yahoo.com" wrote:

> Dear all,
> Sounds like a simple question.
> I have three fields in my table
> FieldA
> FieldB
> FieldC
> FieldD
> FieldC = FieldA * FieldB
> FieldD = 0.01 * FieldA * FieldB
> (actually the scenario is slightly complex that this)
> I have two options
> 1. Declare FieldC and FieldD as formula fields and relax myself by
> bothering only about FieldA and FieldB from my asp.net application
> 2. Worry about updating FieldC and FieldD as "there could be a serious
> performance difference"
> Updates happen from different screens, and it would make a deal of
> difference to me if i can allow sql to take care of the several formula
> fields i want to keep. At the same time, i don't want my customers to
> come crying saying the system is very slow.
> Any advice?
> Thanks
>|||Defining a formula (computed column) such as:
CREATE TABLE t(c1 int, c2 AS c1 * 100)
doesn't store the calculated value. I.e. no penalty for modifications. Howev
er, for below search
argument, SQL Server can (probably) not use an index on c1:
WHERE c2 = 10000
So, you can create an index on a computed column where the value is actually
stored. And in this
case, you do pay for modifications.
An alternative to computed columns is to create a view with the calculated v
alues and have your
users SELECT from the view.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
<mich_stone@.yahoo.com> wrote in message news:1107240862.212907.54340@.c13g2000cwb.googlegroup
s.com...
> Dear all,
> Sounds like a simple question.
> I have three fields in my table
> FieldA
> FieldB
> FieldC
> FieldD
> FieldC = FieldA * FieldB
> FieldD = 0.01 * FieldA * FieldB
> (actually the scenario is slightly complex that this)
> I have two options
> 1. Declare FieldC and FieldD as formula fields and relax myself by
> bothering only about FieldA and FieldB from my asp.net application
> 2. Worry about updating FieldC and FieldD as "there could be a serious
> performance difference"
> Updates happen from different screens, and it would make a deal of
> difference to me if i can allow sql to take care of the several formula
> fields i want to keep. At the same time, i don't want my customers to
> come crying saying the system is very slow.
> Any advice?
> Thanks
>sql

Formula field vs. no formula field

Dear all,
Sounds like a simple question.
I have three fields in my table
FieldA
FieldB
FieldC
FieldD
FieldC = FieldA * FieldB
FieldD = 0.01 * FieldA * FieldB
(actually the scenario is slightly complex that this)
I have two options
1. Declare FieldC and FieldD as formula fields and relax myself by
bothering only about FieldA and FieldB from my asp.net application
2. Worry about updating FieldC and FieldD as "there could be a serious
performance difference"
Updates happen from different screens, and it would make a deal of
difference to me if i can allow sql to take care of the several formula
fields i want to keep. At the same time, i don't want my customers to
come crying saying the system is very slow.
Any advice?
ThanksHi
If your program unnecessarily updates column A and B then you will probably
be better off with a view, although you would really want to fix the
programming! It is not so much where your updates occur, but the number and
performance of inserts/updates/selects which decide which way is better!
It also sounds like you are accessing the tables directly, therefore I would
also recommend using stored procedures.
John
"mich_stone@.yahoo.com" wrote:
> Dear all,
> Sounds like a simple question.
> I have three fields in my table
> FieldA
> FieldB
> FieldC
> FieldD
> FieldC = FieldA * FieldB
> FieldD = 0.01 * FieldA * FieldB
> (actually the scenario is slightly complex that this)
> I have two options
> 1. Declare FieldC and FieldD as formula fields and relax myself by
> bothering only about FieldA and FieldB from my asp.net application
> 2. Worry about updating FieldC and FieldD as "there could be a serious
> performance difference"
> Updates happen from different screens, and it would make a deal of
> difference to me if i can allow sql to take care of the several formula
> fields i want to keep. At the same time, i don't want my customers to
> come crying saying the system is very slow.
> Any advice?
> Thanks
>|||Defining a formula (computed column) such as:
CREATE TABLE t(c1 int, c2 AS c1 * 100)
doesn't store the calculated value. I.e. no penalty for modifications. However, for below search
argument, SQL Server can (probably) not use an index on c1:
WHERE c2 = 10000
So, you can create an index on a computed column where the value is actually stored. And in this
case, you do pay for modifications.
An alternative to computed columns is to create a view with the calculated values and have your
users SELECT from the view.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
<mich_stone@.yahoo.com> wrote in message news:1107240862.212907.54340@.c13g2000cwb.googlegroups.com...
> Dear all,
> Sounds like a simple question.
> I have three fields in my table
> FieldA
> FieldB
> FieldC
> FieldD
> FieldC = FieldA * FieldB
> FieldD = 0.01 * FieldA * FieldB
> (actually the scenario is slightly complex that this)
> I have two options
> 1. Declare FieldC and FieldD as formula fields and relax myself by
> bothering only about FieldA and FieldB from my asp.net application
> 2. Worry about updating FieldC and FieldD as "there could be a serious
> performance difference"
> Updates happen from different screens, and it would make a deal of
> difference to me if i can allow sql to take care of the several formula
> fields i want to keep. At the same time, i don't want my customers to
> come crying saying the system is very slow.
> Any advice?
> Thanks
>

Formula Field in SQL Server 2000

Does anyone know how to use Formula Field in SQL Server 2000. I have googled a lot, but haven't been able to find out how to use it..

Thxyou can add/subtract or write a formula to compute from values in other columns..
lets say you have colA, colB and colC. In colC, you can set the formula as colA+ColB.

you can look for "computed columns" in BOL.

hth|||Whats BOL - Book Online??

Formula Field

I'm using the crystal report of VB.Net 2005 I've tried to write
ReportInstance.DataDefinition.FormulaFields(index).Text = "{DataTable.ColumnName}"
but it didn't work ,I've found that FormulaFields is read only,is there a way to do my point,I mean I need to assign column names of a datatble to the FormulaField(index).Try this, the following code has always worked for me:

For Each crxFormulaField In crxReport.FormulaFields
Select Case crxFormulaField.Name
Case "{@.YourFormulaName}"
crxFormulaField.Text = "{DataTable.ColumnName}"
End Select
Next crxFormulaField

Good luck!
-- Heather

Formula field

Hi,

My table looks like..
accountno datecreated balance ecode
100 09/04/07 50 a
101 09/04/07 60 b
102 09/04/07 70 c
103 09/04/07 90 d

I am using the formula..
IF {TEST_TEMP.ECODE}="b" THEN
{TEST_TEMP.ACCOUNT}
ELSE
0

I have placed the formula field in the "Details" section. But I am seeing 4 rows in the output out of which 1 row showing 101(accountno) and the remaining 0.
what should I do in order to display just the accountno 101?.
I have also tried placing the formula field in "PageHeader" section. But no use.

Thanks.can u try removing the else part?
IF {TEST_TEMP.ECODE}="b" THEN
{TEST_TEMP.ACCOUNT}|||But I am seeing 4 rows in the output out of which 1 row showing 101(accountno) and the remaining 0

You got what u asked for!
use IF {TEST_TEMP.ECODE}="b" THEN
{TEST_TEMP.ACCOUNT}
ELSE
"";|||I tried both..that is removing the ELSE part and putting "". but no use..|||Put the formula in : Menu / Report / Edit Selection Formula / Record
with the next syntax :
{TEST_TEMP.ECODE}="b"|||To show only the rows which are 'b" just go to the section expert, select details, and conditionally suppress it using a formula like:

If {table.field) <> "b" then true

Formula Error - Cannot find dimension member

When I run a MDX query for a specific date, where I donot have any data in the fact table, I get the error - "Formula Error - Cannot find dimension member "--" - in a name binding function.
Query 1:
select {[Measures].[BlockOrders]} ON COLUMNS
from USAGEDETAILS
WHERE [Trdate].[All Trdate].[2002].[July].[30]
This works fine, but if I run the following query, I get the above error message, because there is no data for July 1st, in the fact table.

select {[Measures].[BlockOrders]} ON COLUMNS
from USAGEDETAILS
WHERE [Trdate].[All Trdate].[2002].[July].[1]

Can some one help me?
Thanks,
Sophia.We have two tables Loans (Parent) & LoanIndicators (Child).

LoanIndicators stores the Loan status (ex Outstanding Amount) date wise. This table is updated with a new record only when the Outstanding Amount Changes , until that time the last record holds the status.

Ex :
Loan Table
Id LoanId LoanAmount
1 L1 1000
2 L2 2000

LoanIndicator Table

Id LoanId IndicatorDate Outstanding Amount
1 L1 1/1/2005 100
2 L1 1/13/2005 90

So in the above case if i want to know the Outstanding amount on 1/10/2005 of L1 i.e the record dated 1/1/2005 gives me the outstanding amount of 100.

To Create a Cube on this I create a View linking Loan & LoanIndicators i.e the view holds all the Parent + Child Records of each of the Loans.
Also the cube has a dimension on IndicatorDate.

Now in the Cube the Total AMount of Loan disbursed is 4000 but it shouold be 3000.
It is 4000 because it is summing L1 twice as the view has two records for L1.
1) How do we prevent this summing of L1 twice ?

2) If I want to know the Outstanding Amount on 1/10/2005 for L1 , how do i get it (It should be 100 as the position only changes on 1/13/2005).

TIA

Shuchi Agarwalsql