Newbie Questions

[deepsheep]

Sorry if these have been answered, I'm having problems searching for them.

I have a fair bit of experience with Crystal Reports 8.5 and am just starting on Reporting Services with MSSQL 2005. I felt a good place to start would be to convert some existing reports over. Here are some of the questions I've found:

1) I have several fields in a group with a =count(iif((expression), 1,0). I would like to sum them up without retyping the entire expression for each field I want included in the sum. I've tried fieldname.value + fieldname2.value ... without success. Any ideas?

2) In CR 8.5 I could have variables to share from one formula to the next. Can I still do that? How?

3) I have a few formulas that are very extensive. They crunch the data, put it into variables and the variables are displayed else where. How can I do this?

4) I'm having trouble accessing the web-based report server at

http://localhost/reports.

I'm pretty sure I just missed a step, but I can't find it documented anywhere. Any suggestions or good how to manuals?

5) I'm going to need to create a graph in a very specific format. Neither CR nor Excel could do it. I can create it in VB .NET as an image. Any suggestions on how to smush it together?

I'm not stuck on any one way of doing things and don't mind changing if it makes life better.

Thanks!

 

[DaveyCrockett]

DeepSheep,

1. Not too sure!
2. Not too sure!

3. Have you tried to put the "crunching" into a stored proc and having that value returned from the sp?

4. Try

http://localhost/reportserver/

if you are developing locally. Otherwise use

http://yourservername/ReportServer/

5. You do have limited capability of graphing by default, but if you want to create images on the fly and have a nice visual interface... try something like Dunda's Graphs for Reporting Services.

Best of luck,

Brian

 

[SQLBI]

Hi DeepSheep,

Crystal and Reporting Services are as you know very different. There are apps out there that claim to convert CR to RDL, but they aren't that good so any conversion must be done manually by recreating your reports in BIDS. Unlike Crystal, my advise for ANY RS report would be to perform ALL calculations on your database using Stored Procedures. Not only ill you get the performance gains SPs offer, but you'll be able to backup and reuse your code throughout the business (trust me, i learnt the hard way).

In answer to your questions...

1. The ReportItems Collection llows you to reference specific 'cells' or objects within your report. Use the Properties window to give the cells in question meaningfull names (they'll probably default to textbox123 or similar) and reference them i your expression like this

=ReportItems!Cell1.Value + ReportItems!Cell2.Value + ReportItems... etc

2. Reporting Services doesn't use Formulas and Variables like CR. Instead, you probably want to look at using Parameters or place a textbox on your page and hide it either behind another object, or by setting its Hidden property to true. Place your expression in this textbox and refer to it using the ReportItems collection. My advice would be to do ALL of your calculations at the database using Stored Procedures and use the report to simply disply the values.

3. As DaveyCrocket suggested, use a stored procedre to do all your calculations.

4.

http://localhost/reports

is the web service that directs you to the Report Manager interface. If you can't access it, you may have a problem with IIS, check with your network/web guys.

http://localhost/reportserver

is where you deploy your reports to (Right click your solution/project in the solution explorer and click properties and set the TargetServerURL property.) Again, if you can't access it, you may have IIS issues.

5. RS2005 graphing is much better than 2000, however you may still be limited by your complex requirements. Trial and error is all i can suggest at this stage.

Hope this helps.

Cheers,
Leigh

"If you had one shot, one opportunity, to seize everything you ever wanted, in one moment, would you capture it, or let it slip?" - Eminem

 

[xlbo]

2. Reporting Services doesn't use Formulas and Variables like CR

uuum - yes it does - you can create report variables that are calculations based on returned fields. You can also use formulae typed directly into the expression editor. The negative side is that there is not an extensive amount of help as to what functions are available - generally though, most .NET expressions and functions can be used in the expression editor

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SQLBI]

Sorry, i forgot about Custom/Calcualted fields which would do the same as Formulas/Varables in CR You could also write your own .net assemblies and use them in your report but that may be a bit of over kill.

From a best practice point of view though i'd repeat my suggestion to do everything poosible at the database using SPs and not have reports littered with calculated fields, expressions and aggregations. SQL Server (and other RDBMS products) are optimised for querying and calculation, Reporting Services is essentially a rendering layer. I've seen reports where people have brought 100000's of rows accross the network and filtered it or performed additional aggregation/calculations in RS... Why? whats the point when the database can do it all for you and deliver the minimal amount of data for RS to display.

If you've used SPs to generate your CR repots in the past then you're off to a good start. If you're used to pointing CR directly at a table or view then you need to get out of that mindset if your're to get the most from RS. CR is afterall IMO largely an end user tool, RS/BIDS is a developer tool.


Cheers,
Leigh

"If you had one shot, one opportunity, to seize everything you ever wanted, in one moment, would you capture it, or let it slip?" - Eminem

 

[xlbo]

Sorry - was just correcting that statement - not the rest of the advice. I totally agree - SP aggregation is better optimised than report aggregation. Most of the texts that I have read suggest doing the minimum calculation possible in the report itself (sometimes there is no other way however)

btw - are you the "ex" LeighMoore Mr SQLBI ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SQLBI]

Sure am Geoff... had a paranoid attack about using my full name in public forums (identity theft?) so decided on a more suitable handle.

As i'm working as a SQL BI consultant, thought it apt...

Cheers,
Leigh

"If you had one shot, one opportunity, to seize everything you ever wanted, in one moment, would you capture it, or let it slip?" - Eminem

 

[xlbo]

lol - Fair play. Only problem with that is when you learn new skills - I should really be xlbosqlrsbi by now - bit of a mouthfull though so thought I'd stay with xlbo !

Congrats on the job upgrade

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[deepsheep]

Thanks for your help guys!
Your posts confirmed that some of the stuff that was barely doable in CR may not be in RS, or at least not to the same extent.
I've tried the stored procedure approch in the past with limited success for the data I'm trying to evaluate. I was pre-crunching data into a table for each day for reporting. The biggest problem I had was to be able to filter suspicious data in an efficeint fashion.
If I have to do that for reporting services, I may as well just display that on the webpage instead of going through reporting services.

Thanks!

 

[eo]

Hi,

I might be missing the point here, but I cannot see how a SQL SP can be used for all calculations. Might just be my ignarance so bear with me please.
Suppose the report shows 4 Columns: Code1, Code2, Date and Value1. This is done by a SP. But the report needs to show this data, grouped by Code2 (lets call this group1), showing a sum value of Value1 by Group1, a count of the number of entries by Group1, and an average of Value1 by Group1 (this is calculated by Sum of Value1 by Group1/ Count of entries in Group1).
As the SP returns the data in a certain granular level, how can the SP ALSO return totals at a group level?
Am I missing a major trick here . Your comments would dearly help.



EO
Hertfordshire, England

 

[xlbo]

as per my post:

Most of the texts that I have read suggest doing the minimum calculation possible in the report itself (sometimes there is no other way however)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[eo]

I assume then I am not missing the trick...PHEW!!

I am still however battling with the original poster's question. How to recreate a CR like formula in SSRS, as we have established that it cannot always be done in an SP. Is Report > Report Properties > Code the place to create them. As you seem quite knowledgabe on this subject matter - would it be possible to point me at some notes on this? I have spent most of the morning trying to find info, but not much luck.

EO
Hertfordshire, England

 

[xlbo]

You can do it in 2 ways:

1: As you mention, you can write a code function that can perform some simple math / logic and output a result. The way to call a function built like that is

=Code.FunctionName(Arg1, Arg2)

2: You can create report variables by right clicking anywhere in the "Fields" viewer in the report design environment and choosing "Add". You then need to give it a name, select "Calculated field and then create your formula. Once done, it will appear as a field in teh dataset you have added to

Which is best depends on the type of functionality you require. If it is a set of choices / translations, you may be better using the code option as you can use the SELECT CASE construct rather than having a lot of IIF statements in a function. If, however, you simply want to perform some math on one of the returned firlds, it will proabably be better to use a calculated field (no. 2)

HTH

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question