Expression Syntax Help

Does anyone know of a good reference for Expression Syntax? I have
years of experience in SQL using all kinds of tools, but this thing
has got me really flustered. No If..Else, just endless nested Iif's
in Expressions? No Case..When? No Count Distinct? Can I fake a
Running Total?
This thing is killing me!
JodyPlease refer to the following resources for more information:
Expressions:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_6fhv.asp
Functions:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
In particular SQL Server 2000 Reporting Services does have a CountDistinct
and RunningValue functions.
SQL Server 2000 Reporting Services uses VB .NET expression syntax which
gives access to a large number of functions that can be used in reports.
Switch syntax is support in places of nested ifs.
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JodyT" <datagal@.msn.com> wrote in message
news:f9d864c3.0408120855.1266d521@.posting.google.com...
> Does anyone know of a good reference for Expression Syntax? I have
> years of experience in SQL using all kinds of tools, but this thing
> has got me really flustered. No If..Else, just endless nested Iif's
> in Expressions? No Case..When? No Count Distinct? Can I fake a
> Running Total?
> This thing is killing me!
> Jody|||BTW: a reference of available expression functions in the VB.NET runtime
library is available on MSDN:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vaoriVBRuntimeLibraryKeywords.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message
news:%23BTA8jJgEHA.3016@.tk2msftngp13.phx.gbl...
> Please refer to the following resources for more information:
> Expressions:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_6fhv.asp
> Functions:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
> In particular SQL Server 2000 Reporting Services does have a CountDistinct
> and RunningValue functions.
>
> SQL Server 2000 Reporting Services uses VB .NET expression syntax which
> gives access to a large number of functions that can be used in reports.
> Switch syntax is support in places of nested ifs.
>
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "JodyT" <datagal@.msn.com> wrote in message
> news:f9d864c3.0408120855.1266d521@.posting.google.com...
> > Does anyone know of a good reference for Expression Syntax? I have
> > years of experience in SQL using all kinds of tools, but this thing
> > has got me really flustered. No If..Else, just endless nested Iif's
> > in Expressions? No Case..When? No Count Distinct? Can I fake a
> > Running Total?
> >
> > This thing is killing me!
> >
> > Jody
>|||Thanks Bruce...
That gets me some basic functions, but I have a ton of complex Crystal
formulas that need to be converted and I can't seem to get the 'iif'
syntax to act the way I want it to. I don't find any good
references/examples on MSDN.
Here's a Crystal example:
if {Orders.ShippedDate} = {?Report Period} then
if {Orders.RecordSource} = 'Siebel' then
if ({OrderItems.ProductTypeCode} = 'Delivery' and
Not({OrderItems.ProductCode} startswith 'HC2-D-RUSH')) or
{OrderItems.ProductCode} startswith 'HC2-D-QS' or
{OrderItems.ProductCode} = 'HC2-PR093-PC005' then
0
else {OrderItems.BaseAmt}+{OrderItems.ItemAdj}-{@.MTD
UpCharges}
else if {Orders.RecordSource} = 'NFOS' then
if {OrderItems.ProductCode} = 'QS' or
{OrderItems.ProductCode} like '*LOGO' then
0
else {OrderItems.BaseAmt}+{OrderItems.ItemAdj}-{@.MTD
UpCharges}
else if {Orders.RecordSource} = 'EPIC' then
if {OrderItems.ProductCode} = 'QS' or
{OrderItems.ProductCode} like '*LOGO' then
0
else {OrderItems.BaseAmt}+{OrderItems.ItemAdj}
else 0
else 0
How would you handle something like this? (And no cracks about the
data inconsistancy, it was that way when I got here and ain't gonna
change any time soon enough for this project)
My first several tries were pretty pathetic. They would run, but no
data came back.
Thanks,
Jody
"Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message news:<#BTA8jJgEHA.3016@.tk2msftngp13.phx.gbl>...
> Please refer to the following resources for more information:
> Expressions:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_6fhv.asp
> Functions:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
> In particular SQL Server 2000 Reporting Services does have a CountDistinct
> and RunningValue functions.
>
> SQL Server 2000 Reporting Services uses VB .NET expression syntax which
> gives access to a large number of functions that can be used in reports.
> Switch syntax is support in places of nested ifs.
>
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "JodyT" <datagal@.msn.com> wrote in message
> news:f9d864c3.0408120855.1266d521@.posting.google.com...
> > Does anyone know of a good reference for Expression Syntax? I have
> > years of experience in SQL using all kinds of tools, but this thing
> > has got me really flustered. No If..Else, just endless nested Iif's
> > in Expressions? No Case..When? No Count Distinct? Can I fake a
> > Running Total?
> >
> > This thing is killing me!
> >
> > Jody|||Three more options:
1. Transfer some of this logic to a stored procedure.
2. Consider using Switch():
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctswitch.asp?frame=true
3. Combination of the above two.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JodyT" <datagal@.msn.com> wrote in message
news:f9d864c3.0408121449.2109e4d0@.posting.google.com...
> Thanks Bruce...
> That gets me some basic functions, but I have a ton of complex Crystal
> formulas that need to be converted and I can't seem to get the 'iif'
> syntax to act the way I want it to. I don't find any good
> references/examples on MSDN.
> Here's a Crystal example:
> if {Orders.ShippedDate} = {?Report Period} then
> if {Orders.RecordSource} = 'Siebel' then
> if ({OrderItems.ProductTypeCode} = 'Delivery' and
> Not({OrderItems.ProductCode} startswith 'HC2-D-RUSH')) or
> {OrderItems.ProductCode} startswith 'HC2-D-QS' or
> {OrderItems.ProductCode} = 'HC2-PR093-PC005' then
> 0
> else {OrderItems.BaseAmt}+{OrderItems.ItemAdj}-{@.MTD
> UpCharges}
> else if {Orders.RecordSource} = 'NFOS' then
> if {OrderItems.ProductCode} = 'QS' or
> {OrderItems.ProductCode} like '*LOGO' then
> 0
> else {OrderItems.BaseAmt}+{OrderItems.ItemAdj}-{@.MTD
> UpCharges}
> else if {Orders.RecordSource} = 'EPIC' then
> if {OrderItems.ProductCode} = 'QS' or
> {OrderItems.ProductCode} like '*LOGO' then
> 0
> else {OrderItems.BaseAmt}+{OrderItems.ItemAdj}
> else 0
> else 0
> How would you handle something like this? (And no cracks about the
> data inconsistancy, it was that way when I got here and ain't gonna
> change any time soon enough for this project)
> My first several tries were pretty pathetic. They would run, but no
> data came back.
> Thanks,
> Jody
> "Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message
news:<#BTA8jJgEHA.3016@.tk2msftngp13.phx.gbl>...
> > Please refer to the following resources for more information:
> >
> > Expressions:
> >
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_6fhv.asp
> >
> > Functions:
> >
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
> >
> > In particular SQL Server 2000 Reporting Services does have a
CountDistinct
> > and RunningValue functions.
> >
> >
> > SQL Server 2000 Reporting Services uses VB .NET expression syntax which
> > gives access to a large number of functions that can be used in reports.
> > Switch syntax is support in places of nested ifs.
> >
> >
> > --
> > Bruce Johnson [MSFT]
> > Microsoft SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "JodyT" <datagal@.msn.com> wrote in message
> > news:f9d864c3.0408120855.1266d521@.posting.google.com...
> > > Does anyone know of a good reference for Expression Syntax? I have
> > > years of experience in SQL using all kinds of tools, but this thing
> > > has got me really flustered. No If..Else, just endless nested Iif's
> > > in Expressions? No Case..When? No Count Distinct? Can I fake a
> > > Running Total?
> > >
> > > This thing is killing me!
> > >
> > > Jody|||Forgive me for being such a bonehead, but the .net stuff is all greek
to me. I've used many report tools in my day, but never seen anything
as unintuitive as this.
I created an SP for the code below, brought it in as a dataset, but I
can't see a way to combine the new SP dataset with my larger text type
query dataset in the same table object. So what good is it to me?
Are you saying I should create one huge SP for the whole dataset?
That sort of defeats my goal which was to use a standardized query and
report template, and vary the expressions by client (and a couple
other variables across the business) when necessary as I did in
Crystal. But the expression editor in RS doesn't seem to be robust
enough for the complexity of the original formulas.
Am I silly to try to make things this simple?
What are the books I have not teaching me? So much of the stuff in
the MSDN Library is so .net based, I find it very hard to decipher as
a newcomer.
Jody
"Ravi Mumulla \(Microsoft\)" <ravimu@.online.microsoft.com> wrote in message news:<ett6PeMgEHA.1276@.TK2MSFTNGP09.phx.gbl>...
> Three more options:
> 1. Transfer some of this logic to a stored procedure.
> 2. Consider using Switch():
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctswitch.asp?frame=true
> 3. Combination of the above two.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "JodyT" <datagal@.msn.com> wrote in message
> news:f9d864c3.0408121449.2109e4d0@.posting.google.com...
> > Thanks Bruce...
> >
> > That gets me some basic functions, but I have a ton of complex Crystal
> > formulas that need to be converted and I can't seem to get the 'iif'
> > syntax to act the way I want it to. I don't find any good
> > references/examples on MSDN.
> >
> > Here's a Crystal example:
> >
> > if {Orders.ShippedDate} = {?Report Period} then
> > if {Orders.RecordSource} = 'Siebel' then
> > if ({OrderItems.ProductTypeCode} = 'Delivery' and
> > Not({OrderItems.ProductCode} startswith 'HC2-D-RUSH')) or
> > {OrderItems.ProductCode} startswith 'HC2-D-QS' or
> > {OrderItems.ProductCode} = 'HC2-PR093-PC005' then
> > 0
> > else {OrderItems.BaseAmt}+{OrderItems.ItemAdj}-{@.MTD
> > UpCharges}
> > else if {Orders.RecordSource} = 'NFOS' then
> > if {OrderItems.ProductCode} = 'QS' or
> > {OrderItems.ProductCode} like '*LOGO' then
> > 0
> > else {OrderItems.BaseAmt}+{OrderItems.ItemAdj}-{@.MTD
> > UpCharges}
> > else if {Orders.RecordSource} = 'EPIC' then
> > if {OrderItems.ProductCode} = 'QS' or
> > {OrderItems.ProductCode} like '*LOGO' then
> > 0
> > else {OrderItems.BaseAmt}+{OrderItems.ItemAdj}
> > else 0
> > else 0
> >
> > How would you handle something like this? (And no cracks about the
> > data inconsistancy, it was that way when I got here and ain't gonna
> > change any time soon enough for this project)
> >
> > My first several tries were pretty pathetic. They would run, but no
> > data came back.
> >
> > Thanks,
> > Jody
> >
[SNIP]
> > > >
> > > > This thing is killing me!
> > > >
> > > > Jody