Functions and formulas in RS - new

[tkaz]

I am new to RS...moving from CR...and I am having problems with a very simple report.

I am using BIDS 2005 and an SQL DB.

I have a simple table report showing Plant,Plant Name, Product, Product Description, Price Code, Price, and Price effective date. I have groups on Plant, Product and Price Code.

In CR, I wrote a formula on the effective date to return True or False for Max(effective date.) Threw it on the report and used in the select where "True." Worked beautifully!

I can't duplicate that in RS. Maybe it is not possible, but I was hoping someone could help guide me in the right direction. Any help would be appreciated.

 

[RiverGuy]

I'm not sure if you're having issues writing the expression/formula, or figuring where to write it.

SSRS is kind of like Excel. Each TextBox--or what could be described as a cell--in your report table/grid/list can have it's expression set. Right-click on a box and choose "Expression." It will bring up an editor to write your expression. You can either put a field value in there, or use more logic. You can also add calculated fields to your dataset, using expressions to create the output. You can also write VB.Net code inside of the report for more complex formulas, when can then be referenced in the expressions.

So, in your case, to write an expression to meet your needs, the pseudocode would be something like the following

=Iif(Fields!YourField.Value = Something, true, false)

 

[tkaz]

Thanks RiverGuy...it is going to take me some time to get used to this! I can do pretty much all I want in CR and this seems much more complicated; although all new and unfamiliar does

I had actually tried that expression earlier but could not get it to work. It needs to be evaluated on the price code group level. I get an error message about the scope parameter not being valid for an aggregate function.

We moved to this because it was so much "easier!" I feel really dumb. Maybe you could direct me to some good resources to learn the ins and outs quickly.

Thanks again for your help!!

 

[RiverGuy]

We moved to this because it was so much "easier!" I feel really dumb. Maybe you could direct me to some good resources to learn the ins and outs quickly.

Overall, I would say the ease of creating logic in the reports is similar between Crystal and SSRS. Some things may be easier in one and harder in the other, but overall, neither lacks anything significant to impede report development.

SSRS is also easy to deploy to a server. I'm not sure if Crystal Enterprise (or whatever their server version is called) is as easy to deploy and maintain, but in my experience, I've created and seen a lot of client side reporting with Crystal, whereas SSRS is usually on the server side.

 

[tkaz]

I've always deployed CR through an ASP app. But, our company has decided to move to this new tool and deploy on Sharepoint so I have to make the leap...easy or not.

This was my first stab at it. To be fair and honest, I'm just an old end user with moderate SQL skills and a lot of business experience behind me that helps me create reports that mean something. I can "think" the logic a lot easier than I can write it.

So, can you recommend any good resources for me?

 

[RiverGuy]

So, can you recommend any good resources for me?

I actually meant to answer that question, but I forgot. I pretty much learned SSRS on my own, so I'm not sure about any books on the subject. However, databasejournal.com has great original content for SSRS and SSAS.

Check out this link

http://www.databasejournal.com/features/article.php/3593466/MS-SQL-Series.htm

and scroll down to the author William Pearson. He has a fairly large library of SSRS articles he has written. Many of his articles are about working with SSRS in conjunction with SSAS, but you can probably still get some good information out of them.

 

[tkaz]

Thanks! I'll give that a whirl.

 

[tkaz]

RiverGuy...

Just wanted to let you know that I got it!! I had to put the expression in the product code group,copy all the detail lines to the group and hide details. I have exactly what I need. Thank you so much for your support.

 

[RiverGuy]

Way to go, I'm sure you'll be up to full speed in SSRS before you know it.