How to show last 30 days even if no record exists

[fuhrj]

I need to display each day for the last 30 days in my report.

A simple query might give me the following data:

SELECT count(*) FROM orders where orderDate >= '1/1/2010' AND orderDate <= '1/31/2010'

1/1/2010 10
1/4/2010 5

The report displays this data with no problems. However, what I need it to display is this:

1/1/2010 10
1/2/2010 0
1/3/2010 0
1/4/2010 5

Research on the web says that this should be handled in the display logic and not the database. This I agree with.

In C#, I would set up a foreach loop and iterate through a DataView looking for a date. If the date is not found, then print 0, and advance to the next date.

Is it possible to something like this in a Report? I guess I would be overriding the Data band somehow?

[Jan]

Hello,

You can use CountData property of DataBand. Set this property from report script to required amount of days. For example:

Code: Select all

DataBand1.CountData = 12;

In result you receive required amount of iterations. After then you can receive index of iteration with help of DataBand1.Position property. For example:

Code: Select all

{DataBand1.Position + 1}

Thank you.

[fuhrj]

Thanks Jan, but I'm confused on how this code works.

Could you please elaborate?

Many thanks.

[Jan]

Hello,

The CountData property of the DataBand is used to specify the number of steps the reporting tool should do for this DataBand. For example, if to set the value to 100, this means that the reporting tool will output the DataBand 100 times. You may use the Position property of the DataBand to know the index of the output DataBand. For example, the following expression {DataBand1.Position} on the DataBand1 will output the number of rows (starts from 0) for each DataBand.

Thank you.