How to display data filtered by Datetime

Hi and thank you for reading,

I have a list of orders by different customers. With the help of a Cross Tab I have counted the number of orders per customer and created a list with the top customers in descending order.

Now I would like to let the user filter this data by date. This means I would like the user to choose DateTimes in between of which this top customer list should be calculated. I tried to do this with the "Report Parameter"and then a Filter for this Report Parameter, but the problem is that there is no option to select the OrderDateTime in the Filtering dialog.

My OrderDateTime is in the same table as the Orders and Customers of this Orders. How can I choose this column for my filter in order to filter the top customers?

Thanks a lot for all help

Evgeny

Find more posts tagged with

Comments

mwilliams

Hi Evgeny,

You should be able to limit your dataSet with a filter or a SQL dataSet parameter using your report parameter, depending on if you're using an SQL type dataSet or another type. This would then limit the report data to only include the dates that your users choose. What type of dataSet are you using?

evgeny

Dear mwilliams,

thank you for your reply.

I have extracted my data with an SQL query on a JDBC connection. From the table which contains order_number, client_id and order_datetime, i have extracted the last two.
Pardon me, but I am not quite sure what you are proposing to do. If it is to enable the user to filter at query run time, than I am not really looking for that since this means more cpu usage on the database side which I would like to avoid. In an another case I would like to ask you to give me some further details on a solution.

Thank you in advance

Evgeny

mwilliams

Evgeny,

Yes, I was suggesting adding a clause to your SQL query that limited the query based on the user's parameter selection. I don't think this would slow any processing. It should actually make the report faster to do it this way, I would think, by not making BIRT process all of your database data, just the necessary portions.

To filter a crosstab by date, date would have to be part of the crosstab. So, if yours isn't set up that way and you want to go the filtering of the crosstab route, you'll have to set it up that way. Can you provide a small sample of data that includes all of your fields that I can put in a .csv file? This way I can provide a quick example of different ways to filter your data. Thanks.

evgeny

Hi and thanks in advance for the help!

I attached a sample file (hope it is in the right format).

Ideally I would like to have the following procedure:

1. Customer chooses the timeframe (all data between two dates)
2. A report is shown, which returns the following (the numbers are made up)

CustombrD 20
CustomerA 15
CustomerC 9
CustomerB 4
CustomerH 2
CustomerE 1
CustomerJ 1

Thanks again for your help.

Evgeny

PS: I saw that you last uploaded report sample was in the Birt version 3.2.20 (which is not available to download yet?), while I have version 3.2.17 (downloaded a few days ago). My Birt/Eclipse couldn't open that report due to some version issues. Thanks.

mwilliams

Evgeny,

The version I used for that report was BIRT 2.5, which is released. I thought I saw in one of your posts that you had downloaded 2.5. You must've accidentally downloaded an earlier version of BIRT/Eclipse. You can try changing the version info in the XML source of the one I posted to the same version info from a report you've created's XML source. You might be able to open it then. Let me know.

mwilliams

Evgeny,

You may want to just upgrade to the newest all in one version of Eclipse BIRT, version 2.5. It doesn't seem that inheriting data from a crosstab works correctly for a chart in 2.3.2.2.

evgeny

yep, thats what I have just done. And things work. thanks.

If you would have some advices on the problem with the date filter I described above, that would be really helpful.
Because I am counting the number of occurrences of each client I cant include the date column in my report, but I would like to take just the data within a certain time frame.
The reason I don't want to do it on DB side is due to our proprietary DB...

Thanks again

Evgeny

evgeny

Hi,

in case some people will try to do the same, here is the solution:

You need to create Data Set Parameters:
1. Create startData and endDate parameter in "Parameters" in "Data Explorer"
(Datatype: Date, Display type: Text Box, Default value: choose a value)
2. Go to the "Edit Data Set" window
3. Go to "Parameters"
4. Click "New..."
5. Enter => Date Type: Date, Direction: Input, Linked to: startDate
(same for endDate)
6."Query"
7. Enter:
select Customer_ID, Order_Date
from Table
where Order_Date between ? and ?

best

Evgeny

mwilliams

Evgeny,

Exactly what I was suggesting. Guess I should have described more about the dataSet parameter and report parameter linking process.

rahulh

Hello ,

I am creating a Birt report from a java application . When i try to open it the XML source says version 3.2.20 . I get a error message as "The report file of version 3.2.20 is not supported". I am using birt 2.5 . Please help me on this issue as early as possible .

evgeny

check for updates in the eclipse "Check for updates". This should update birt to the latest version.

Evgeny