SQL Reporting: how to declare and assign a variable?

Hi,

This is a tricky topic.
First as you are moving from Crystal (as I did 3 years ago) you will get very frustrated with RS at times.
It works completely differently to CR and until you can change your way of thinking over to the way RS works instead of trying to find equivalent's from CR in RS, it won't be easy.

RS doesn't have the different "passes" that CR has. The trick to RS is to make the database do the hard work and generate ALL the values you need.

Having said that there are some other tricks;

1) You can add calculated fields to a dataset via RS itself. In the dataset explorer where it lists the fields returned by the query, you can right click aand "Add" your own field defined by a VB.Net expression which can refer to any field in that dataset. This is evaluated record by record so can slow the query down.

2) Within in a data control you can refer to values in other cells within the same scope, i.e. the same line. Again, this is evaluated record by record and is likely to be quicker done in the original query itself.

3) VB.Net code: You can write code by going to the Report menu then Report Properties, then Code. You can declare functions and variables in here. But you have to remember these are rendered as web pages and as such they are stateless, what that means is the page will reset variables between page throws and you will lose any variables set.

4) Hidden parameters are a good way to hold individual values for the life of a report. They have to be populated either from a dataset or a VB.Net expression - you cannot alter this half way through a report.

If you could explain the scenario, I can be more specific about which route you should take.

Cheers
Chris

PS: I've never regretted switching from CR to RS. To me it's a better SME reporting tool. There are scenario's where CR is better but in the main I think RS wins.