Converting a Crystal Report 3

[pk400i]

I have to mimic an existing Crystal Report, using SSRS.

I have some basic questions, would appreciate any help. The question is how to do the following in SSRS.

1. I have 2 paramters, a start and end date, that are used in Crystal to calculate months periods, going three months back. (these are called Formulas in CR. An example: {@Start Date 1} - 1 which gives a month prior.
2. There is a third parameter called Customer. This entry is compared against a database field, and only those records that match, will be read for the report. In Crystal this is know as Record Selection something like this, start date 4 is a formula that goes four months back from Start date:

{Command.DOCDATE} in {@Start Date 4} to {@EndDate}
and {@Customer} = {Command.CPRCSTNM}

There are some more things like this but perhaps if someone can assist here or tell me where I should be looking for this type of guide to migrating a CR. there are much more complicated parts to this report but this is the starting point.

 

[acessn]

Your selection criteria in SSRS is pure SQL. Just add parameters to your report, and reference them in your query with @timeframeparameter and maybe just use a datediff.

You will have to create your new report from scratch.

Something like

Select * from mytable
where (datefield >=@startdate and <=@enddate)
and customerfield=@customer

 

[bessebo]

Hi pk400i,
You say:
1. I have 2 paramters, a start and end date, that are used in Crystal to calculate months periods, going three months back. (these are called Formulas in CR. An example: {@Start Date 1} - 1 which gives a month prior.

We typically pre-populate our start and end dates with the previous month's day 1 and the previous month's last day. For the parameter startdate we use the expression editor for the Default Value and set it to:

=DateSerial(
Year(dateadd("m",-1,Today))
,Month(dateadd("m",-1,Today))
,1)

for the enddate we would do the same thing but the expression looks like this:
=Parameters!startdate.Value.AddMonths(1).AddDays(-(Parameters!startdate.Value.Day))

You said:
2. There is a third parameter called Customer. This entry is compared against a database field, and only those records that match, will be read for the report. In Crystal this is know as Record Selection something like this, start date 4 is a formula that goes four months back from Start date.

You would simply set up a parameter called Customer and if the customer is a multi-value parameter you would set that in the parameter definition. In the SQL code you, if multi-value, it would look like:

where customer in (@customer)

if single-value it would look like:

where customer = @customer

for the startdate4 you could create a new dataset that would have a SQL query in it that is referenced from the startdate4 parameter definition and it would return the value 4 months back based on what you enter for the startdate.
One thing you will have to remember is that if @startdate4 is referencing @startdate it will have to follow it in the parameter list.
Try some of these suggestions and get back to this thread and we'll help you out.


Cheers,
Bessebo
(website:

http://www.datagonewild.com)

 

[pk400i]

OK I have a simple report going on the SRSS. Some basic questions I have.
1. For date parameters, the calender is not showing up, after I put the date in the SQL statement (where date>= @startdate, etc.)
2. I have to make some of the data into a chart form, is this possible?
3. How do I go about doing groupings on fields?

 

[bessebo]

Hi pk400i,
1) You have to ensure that the parameter is identified as a DateTime Data type under Report Parameters. One bug that I have found is that when you have a runtime error in the development environment (in my case Visual Studio) the date time report parameter sometimes switches to String. Just check it and change it back to DateTime.
2)We use Dundas charts but SSRS comes with a Chart object. Do some reading on the Chart Object and test it out. It is fairly intuitive.
3) You simply add a group by clicking on the left-hand side of the table, right-click, and you will see an option for Insert Group (it will put the group below the row you are on). You will then be presented with a dialog box that will allow you to group on a field or an expression. There are a number of tabs here where you can set the Sorting and Visibility, etc.

One book I recommend is Pro SQLServer 2005 Reporting Services. If you have SSRS 2008 there are other books you can find. One thing I also recommend is to download and restore the AdventureWorks database. You can use this for testing purposes.

Try working with the Chart object and the grouping and let us know if you have any specific problems.

Cheers,
Bessebo
(website:

http://www.datagonewild.com)

 

[pk400i]

Thank you Bessebo, you have been extremely helpful.

Just as an aside, how would I make a backup of tables on SQL Server 2005? Not with all the data just some. I want to get all the fields is the reason.

 

[bessebo]

I am not sure what you are asking. If you want all of the tables you should perform a full backup. If you are saying that you want only a few of the tables you could do this. You could create a new database in Access, export the tables from SQLServer that you want into Access. You could then create a new database in SQLServer and import the tables from Access into this new database.

I assume you want to use this database for a test environment for reporting? This should work. If I am off base with what you are asking please let me know.



Cheers,
Bessebo
(website:

http://www.datagonewild.com)

 

[pk400i]

One thing I am not seeing, is where do you make edits to a numerical field? IN the Layout section, it doesn't have anywhere to do this.

I want to present a number 1.0000000 as 1 for example.

 

[bessebo]

Hi pk400,
I always like to do this by clicking on the cell and in Properties under the Format attribute, just set it to N0.

Another way to do this would be to use the Expression editor and the FormatNumber function and enter something like this where 0 is the amount of decimal places you want the number to show.

=FormatNumber(Fields!Month1.Value,0)



Cheers,
Bessebo
(website:

http://www.datagonewild.com)

 

[pk400i]

I hate asking a question each time. Does the SSRS not show where all these possibilites are? For Expression, it assumes you know what you are doing rather than showing syntax as Crystal does.
For example, I put 'no' in format for some fields and 'no' appears in the report. I also wanted to know how to surpress the time from a data time field, for the report.

 

[xlbo]

N0 (zero, not "o")

For dates, dd/MM/yyyy

I'm on RS2000 and there is no syntax help. I was under the impression that there was in RS2005

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

 

[bessebo]

When you are using a function in the Expression editor there is Intellisense (I think that is what it is called) that shows the various options after you enter the function. You can also utilize the Help function in Visual Studio and limit the "Filtered by:" to SQL Server Reporting Services. Using an internet search on anything related to Reporting Services will typically lead you to an answer.

Cheers,
Bessebo
(website:

http://www.datagonewild.com)

 

[pk400i]

I put in the word zero for format but it just display the word zero.
I put in mmddyy but is returns a strange number like 000109

 

[bessebo]

Hi,

Where it says Format in properties you want to put in N0, where it says N followed by the number 0, not O. Just N0 and nothing else. If you wanted to have one decimal place showing just put in N1.

You could just use a d for the format for the date without the time. Hope this helps...



Cheers,
Bessebo
(website:

http://www.datagonewild.com)

 

[pk400i]

There we go, thank you for your patience with this newbie!

 

[bessebo]

Hopefully that helped. We all were newbies at one time. Don't hesitate to ask. There are a lot of real experienced people on tek-tips that have helped me immensely in the past..

Cheers,
Bessebo
(website:

http://www.datagonewild.com)