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!
Thursday, March 29, 2012
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
sqlFormulas (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.
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
>
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
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?
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
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)
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.
|
|
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
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~~~~
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?
>
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?sqlFormula 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
> >
> >
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
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
Subscribe to:
Posts (Atom)