Generate Monthly Report

[former_member629712]

Hi,

I have to create a report based on user requirements as per below:

Questions:

1) How to create year parameter? When user filter by year - data will be display in monthly.

2) How to count total product for each month? The report will also display null values data and column header for month is static. Do I have to create a formula field to count total product for each month?

Fields that will be use in this report are location, product name, date (dateTime value). Need someone that can assist me to generate this report.

Thanks.

[abhilash_kumar]

1. The Custom SQL is added inside the report. Please refer to Dell's blog for more info on this subject:

https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/

2. A Custom SQL does not change the DB structure. This is merely a 'select' query added as a datasource.

3. Missing months can't be generated using a formula to be used in a Crosstab.

-Abhilash

[former_member629712]

It's complicated. I didn't have access right to edit or modify the database.

For the second alternative, there are a few questions regarding this:

1) Where should I create a Custom SQL query? It is in crystal report? ?If in Crystal report can you show to me how to create custom SQL query.

2) If I create custom SQL query, the structure of database will change or not?

It is possible if we using formula to count the product for each month in order to display all 12 months in report?

Many thanks.

[abhilash_kumar]

Crystal reports cannot report on data that does not exist in the database. What this means is, if there does not exist a single row for a particular month, then it won't be displayed in the Crosstab or elsewhere on the report.

To ensure there are no 'missing' dates/months, you'd need a 'Calendar' table in the database. Other alternatives include creating a Custom SQL query that generates a list of dates from a certain year in the past to a certain year in the future. You can then Left Join FROM this calendar table TO the original table that contains the Date field.

You'd then need to replace ALL instances of the original Date field on your report (including selection formulas, groups, crosstab etc) with the Date field from the Calendar table. This should all months irrespective of whether product data exists for that month or not.

-Abhilash

[abhilash_kumar]

Hi,

Try this please:

1. Create a number parameter for Year

2. Add a Record Selection Formula that looks like this:

Year({Database_field}) = {?Year Parameter}

If you're reporting against a Table/View, you could create a 'SQL Expression' field to extract the Year portion and use that in the selection formula instead. This will push the selection criteria to the database for better performance. The SQL Expression for SQL Server will be:

DatePart(year,"table"."DateField")

You can then use this field in the selection formula like so:

{%SQL Expression Name} = {?Year Parameter}

3. Insert a Crosstab. Use the Date field as the Column and set its Group Options to print for Each Month.

Use the Location Product field as the Row.

Use the Product field as the 'Summarized Field' with its function set to 'Count'.

-Abhilash

[former_member629712]

Thanks for your help.

The cross-tab looks fine but the problem is one of month column disappear when parameter was selected. How can I show all months in column header? I've tried to tick on Convert Database NULL Values to Default in Report option but the output still same.