Report Filtering

[goldminedatadiva]

I am new to SSRS2005, I am a semi-pro in Crystal.I also use GoldMine as the data source.
I now have to create a report in SSRS and have done it pretty well. Looks just like my Crystal.
Here's the deal I need to filter the data of history to be on my report to represent the last full week of data by user.
In crystal I created a formula field that would give me my start date
if DayOfWeek (CurrentDate)=1 then CurrentDate-7 else
if DayOfWeek (CurrentDate)=2 then CurrentDate-8 else
if DayOfWeek (CurrentDate)=3 then CurrentDate-9 else
if DayOfWeek (CurrentDate)=4 then CurrentDate-10 else
if DayOfWeek (CurrentDate)=5 then CurrentDate-11 else
if DayOfWeek (CurrentDate)=6 then CurrentDate-12 else
if DayOfWeek (CurrentDate)=7 then CurrentDate-13

and then to get my enddate
{@LastWeekStartDate}+6
I would then filter the history date to be between those 2 dates.

Question how to I accomplish this now??
I'm not sure how to enter this in my where SQL statement.
Can anyone point me in the right direction??
Jill

 

[compuveg]

Something like this in your stored procedure should return to the RDL the data you need. I do as little processing in the RDL as possible.

Select
case when DayOfWeek (CurrentDate)=1 then CurrentDate-7
when DayOfWeek (CurrentDate)=2 then CurrentDate-8
when DayOfWeek (CurrentDate)=3 then CurrentDate-9
when DayOfWeek (CurrentDate)=4 then CurrentDate-10
when DayOfWeek (CurrentDate)=5 then CurrentDate-11
when DayOfWeek (CurrentDate)=6 then CurrentDate-12
when DayOfWeek (CurrentDate)=7 then CurrentDate-13
else 0
end as StartDateColumn,
@LastWeekStartDate+6 as EndDateColumn
FROM SourceTable
Where
Conditions = 'True'

 

[goldminedatadiva]

Thanks, I guess I got to get better with Stored Procedures.
How about a function in the coding of the report itself?
Jill

 

[compuveg]

Well, everything CAN be done in Visual Studio/Report Designer/MRSS.

I like the reference in query analyzer better, so I use it. Having no formal training on MS-SQL, the docs are vitally necessary. I can often find what I'm looking for in Visual Studio's help, but I don't find it nearly as easily.

I also feel that formatting of data is the main function of the report, whereas the query should return the data that gets dropped into that report. Just a matter of opinion I guess.

 

[xlbo]

I'd go along with that

If you really want to do it in the report you can use either the code window or a user defined variable (right click on list of fields and choose "Add")

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

 

[goldminedatadiva]

thanks that was what I was looking for, how to add your own calculated fields.
Thanks guys.
Jill

 

[goldminedatadiva]

Ok, I'm confused now.
In SSRS, I don't see any field list to right mouse and choose add.
I know it is Crystal. But where in SSRS is it??
I am in the design mode of my report in Microsoft Visual Studio.
The tab Data holds the SQL statement to grab the fields, etc. there isn't any list there.
The layout tab shows the fields that you requested. So how do you create a userdefine field here??

 

[compuveg]

When in Visual Studio's report designer, any code to calculate a text box's contents can be input similarly to Excel, just typing "=" and the code. I believe that code to act on an object's properties, say to toggle it to be hidden, should be in the 'code' (View > Code, or F7) section.

Example:
The value field is in the 'data' section of properties.

TextBox13.Value = IIF( Fields!billing_individual.Value <>"",Fields!billing_individual.Value & vbCrLf ,"")
& IIF( Fields!billing_customer.Value = Fields!billing_individual.Value, "", Fields!billing_customer.Value & vbCrLf )
& IIF( Fields!customer.Value = Fields!billing_individual.Value OR Fields!customer.Value = Fields!billing_customer.Value, "", Fields!customer.Value & vbCrLf)
& IIF( Fields!adr_line1.Value <> "",Fields!adr_line1.Value & vbCrLf ,"")
& IIF( Fields!adr_line2.Value <> "",Fields!adr_line2.Value & vbCrLf ,"")
& IIF( Fields!adr_line3.Value <> "",Fields!adr_line3.Value & vbCrLf ,"")
& IIF( Fields!adr_city_state_code.Value <> "",Fields!adr_city_state_code.Value & vbCrLf ,"")
& IIF( Fields!adr_country.Value <> "" AND Fields!adr_country.Value <> "UNITED STATES",Fields!adr_country.Value & vbCrLf ,"")

 

[goldminedatadiva]

Thanks I shall try to get my head around that.
I appreciate the help

 

[xlbo]

IN SSRS / Vis Studio design mode, go View>Fields

This should appear as a vertical bar to the left of the report layout. Right click in this area and choose "Add"

et voila

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

 

[goldminedatadiva]

in Visual Studio 2005 View has
Code,Designer,open,server explorer,solution explorer,bookmark window,class view,code definition window, object browser, error list,output,properties window,task list,toolbox,find results, other windows, toolbars, full screen and datasets.
No Fields.

Am I missing something

 

[xlbo]

I am using 2003

In either case, if you can see a list of fields available to the report, right click on it and choose "Add"

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

 

[xlbo]

btw, in my version "Fields" is right at the bottom - after all the other items you have listed

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

 

[compuveg]

Sorry, I wasn't clear enough.

I was talking about the properties window in design view.

Place a text box, and when that's selected, the code would go in the 'value' property of that text box. If the properties aren't available, right-click the text box and select 'Properties'.

There's also a '<Expression..>' option when you drop down the 'Value' property that takes you to an expression builder where you can get some help calculating the value. If you right-click the text box and goto properties, the 'Fx' button takes you to the expression builder.

 

[goldminedatadiva]

NP. I was hoping that was it. thanks
Jill