9

I understand Crystal Reports has fallen out of favor with some in the development community and perhaps rightly so: I haven't used it in about 15 years. But when I did, I could make it sing.

Perhaps that is why I find SSRS so disappointing. The software is clumsy, non-intuitive and one-dimensional. I have been fighting it for the last week and I am posting here as a last resort.

I have been forced to create my report entirely in a SQL Server table and I shouldn't have to do that.

For example: why can't I use the expression in a textbox;

 Fields!Foo1.Value/Fields.Foo2.Value

where Foo1 is in Dataset1 and Foo2 is in Dataset2.

If this is beyond the capability of the software, that's OK. I beginning to think it is.

So maybe someone can answer this question: Does anyone know of any other reporting software besides Crystal Reports and Reporting Services that is more versatile?

Hello World
  • 93
  • 1
  • 1
  • 4

3 Answers3

9

I think your would have been resolved by now, however if u r facing same issue then you can use lookup in expression.
It enable you to browse through values of other dataset while there is a common field in both the datasets

=Lookup(value in dataset1,value in dataset2, common field in both datasets, dataset name from which we need to get data)
Mayur Birari
  • 5,837
  • 8
  • 34
  • 61
vaibhav asthana
  • 91
  • 1
6

Neither Crystal nor SSRS will allow you to directly compare values in two different datasets on a row-by-row basis.

In both tools:

  • If both datasets come from the same relational database, then the simplest way to compare values is to combine the two datasets into a single query.
  • If both datasets come from different relational databases that can be linked together (for example, by using Linked Servers in SQLServer, or Database Links in Oracle), then the simplest way to compare values is to combine the two datasets into a single query that accesses both linked databases.
  • If both datasets come from datasources that cannot be directly linked together, then the two datasets can be linked together in Crystal/SSRS by means of a subreport. (More details on subreports in SSRS here.)

There is a reporting tool called BIRT that has a similar reporting paradigm to SSRS (ie. a web-style layout, instead of Crystal's banded reporting approach), includes much the same functionality as SSRS, is Open Source and enables you to link datasets together directly to create joint datasets. However, it is less intuitive to use than either Crystal or SSRS.

3

Stick with it - Reporting Services is an amazingly flexible reporting platform.

You can do what you want by specifying the dataset after the field name but you have to specify which Foo2 you want or how you want to deal with Foo2.

The way you do this is through aggregate functions. For example, lets say Dataset2 only has one row of data, then you get the first one:

=Fields!Foo1.Value / First(Fields!Foo2.Value, "Dataset2")

If there are many rows and you want to divide by the sum of Foo2 then you can do this:

=Fields!Foo1.Value / Sum(Fields!Foo2.Value, "Dataset2")

The point is, you are currently stepping through Dataset1 - you need to let Reporting Services know how to deal with Dataset2 in order to return the Foo2 you need.

Chris Latta
  • 20,316
  • 4
  • 62
  • 70
  • Thanks for the reply and the encouragement. However, that is just the problem. I need to process line by line in the detail, not with aggregates. More to the point, I am computing a percentage column with row value from Dataset1 and Dataset2. – Hello World Apr 27 '12 at 00:23
  • 4
    Why not just join the tables using SQL in the Datasource and have all the data you need in one datasource? – Chris Latta Apr 27 '12 at 01:33