CODEWITHSUNDEEP
reporting-servicesssrs-2008
ChikisTrikisAlan
ChikisTrikisAlan

Reputation: 3

SSRS - Too many expression

I'm new to this whole SSRS business... and I'm running into two major issues while trying to create my first report...

-I know there is a probably better ways to do what I'm trying to accomplish -It takes 16 min to run

Problem: I need to get a count of "cards" that were active/deactivated during a given (user selected) accounting month (broke down by day) - One of my problems is that from the (DataSet1 - which contains must of the info I need) only contains the start and end date of the accounting month ( I don't have a calendar table)

So (From the screenshot) (column 1 "ACM START DT Short" I'm manually adding expressions (29) to get the dates I need..

enter image description here

Since I only have start and end date I'm using these properties on my first and last row and for the rest I'm using the following expression (increasing the day by 1)

=Fields!CUBD_CareDay_Period_End_DT_SHORT.Value
=DateAdd(DateInterval.Day,1,Fields!CUBD_CareDay_Period_Start_DT_SHORT.Value)
=DateAdd(DateInterval.Day,2,Fields!CUBD_CareDay_Period_Start_DT_SHORT.Value)
=DateAdd(DateInterval.Day,3,Fields!CUBD_CareDay_Period_Start_DT_SHORT.Value)

.
.
.
.
=DateAdd(DateInterval.Day,29,Fields!CUBD_CareDay_Period_Start_DT_SHORT.Value)
=Fields!CUBD_CareDay_Period_End_DT_SHORT.Value

I find this really dumb and I'm sure there are better ways to do this...

Is there a way (for instance) to write some code to do something like (see below) and dump the result (dates) into my first column

/what I mean with start_date + 1 is to increase the start_date by one day

i=1
If (start_date + i) < end_date
  i++
  then return start_date+1
          else return end_date

is it possible? If so, how? any articles that may help?

Upvotes: 0

Views: 107

Answers (1)

Mike Honey
Mike Honey

Reputation: 15037

I would abandon this design (SSRS Expressions) and resolve this in the Dataset - I hope it is a form of SQL.

If you do not have a Calendar table I would use a virtual numbers table instead e.g.

http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers

You can cross-join to the virtual numbers table to generate the range of rows you need.

This approach should also resolve your performance issue.

Upvotes: 1

Related Questions