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
Subscribe to:
Posts (Atom)