cancel
Showing results for 
Search instead for 
Did you mean: 

All months of the calendar year

Former Member
0 Kudos

I have a reporting requirement. I need to report the number of transactions made for each month for this particular calendar year.

I am using Crystal Reports 2008

Which is the best way to go around this - cross-tab or create the columns manually.

Some months may not have transactions still I need to report that month name and add as zero count.

eg: JAN FEB MAR

number of trans. 12 0 5

which is the best approach, please give details in both formats(crosstab as well as ordinary manual tables)

Thanks.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

I think crosstab is the easier way to go. Base the report on an SQL Command something like (MS SQL):


declare @year_no int;
set @year_no = {?Selected Year}
select month(sales_date) as Month_no, @year_no as year_no, sum(sales) as sales
from sales_detail
where year(sales_date) = @year_no
union
select 1 as month_no, @year_no as year_no, 0 as sales
union
select 2 as month_no, @year_no as year_no, 0 as sales
union
select 3 as month_no, @year_no as year_no, 0 as sales
union
select 4 as month_no, @year_no as year_no, 0 as sales
union
select 5 as month_no, @year_no as year_no, 0 as sales
union
select 6 as month_no, @year_no as year_no, 0 as sales
union
select 7 as month_no, @year_no as year_no, 0 as sales
union
select 8 as month_no, @year_no as year_no, 0 as sales
union
select 9 as month_no, @year_no as year_no, 0 as sales
union
select 10 as month_no, @year_no as year_no, 0 as sales
union
select 11 as month_no, @year_no as year_no, 0 as sales
union
select 12 as month_no, @year_no as year_no, 0 as sales

and the rest of the report will practically write itself!

HTH,

Carl

Former Member
0 Kudos

Thank you. I will try it out. It will show the months even if there is no value right. Is this to be created as a formula..sorry for thise questions..because I am a bit new to the crystal reports.

One more thing is that will I be able to format the table or I mean put colors and draw additional rows about it..I mean I want to put Q1,Q2 etc for the 3 months each. Will i be able to play with the cross tab. If yes how?

Edited by: BA Posts on Nov 17, 2009 10:50 PM

Former Member
0 Kudos

BA,

I'm not going to disagree with Carl, I'll just say that I personally never liked working with cross tabs in CR. So just for reference here is how to do it without the cross tab... Again doing the Command in SQL (SQL Server).


DECLARE @Year INT;
SET @Year = {?Year};

SELECT
s.CategoryName,
SUM(CASE WHEN DatePart(mm, s.DateField) = 1 THEN s.SaleAmount END) AS JanSales,
SUM(CASE WHEN DatePart(mm, s.DateField) = 2 THEN s.SaleAmount END) AS FebSales,
SUM(CASE WHEN DatePart(mm, s.DateField) = 3 THEN s.SaleAmount END) AS MarSales,
SUM(CASE WHEN DatePart(mm, s.DateField) = 4 THEN s.SaleAmount END) AS AprSales,
SUM(CASE WHEN DatePart(mm, s.DateField) = 5 THEN s.SaleAmount END) AS MaySales,
SUM(CASE WHEN DatePart(mm, s.DateField) = 6 THEN s.SaleAmount END) AS JunSales,
SUM(CASE WHEN DatePart(mm, s.DateField) = 7 THEN s.SaleAmount END) AS JulSales,
SUM(CASE WHEN DatePart(mm, s.DateField) = 8 THEN s.SaleAmount END) AS AugSales,
SUM(CASE WHEN DatePart(mm, s.DateField) = 9 THEN s.SaleAmount END) AS SepSales,
SUM(CASE WHEN DatePart(mm, s.DateField) = 10 THEN s.SaleAmount END) AS OctSales,
SUM(CASE WHEN DatePart(mm, s.DateField) = 11 THEN s.SaleAmount END) AS NovSales,
SUM(CASE WHEN DatePart(mm, s.DateField) = 12 THEN s.SaleAmount END) AS DecSales
FROM tblSales AS s
WHERE DatePart(yy, s.DateField) = @Year
GROUP BY s.CategoryName;

Like I said, just another option...

Jason

Former Member
0 Kudos

Thanks.,

But Jason could u help me with this query..

Need to return Jan Feb Mar

count(distinct claimnumber) "total claims"

count(distinct claimnumber,linenumber) "total lines"

Yep I am aware of this method from sql query, but I wanted to full utilize the cross tab functionality of crystal reports.

Also I just not only want the sum of sales amount but also the count of distinct completed sales(use salesnumber). Can this integrated into this query using a UNION, in the query Jason posted

We could return the months grouped by rows..but i prefer to get in the same formate from sql query so that i can display directly in crystal reports.

I could do a UNION but the case syntax for count(distinct claimnumber,linenumber) Is creating problems.

On another note: But could anybody read my prvious post and just know how the formatting, if it can be done, in crosstab.

Edited by: BA Posts on Nov 18, 2009 5:22 PM

Edited by: BA Posts on Nov 18, 2009 6:41 PM

Former Member
0 Kudos

BA,

You can integrate any data that can be aggregated into either approach. In the UNION SELECT approach provided by simply adding the the necessary field to the select list. For a distinct count of orders, for example, you would want to use a primary key field to insure distinct orders.

Also bear in mind that, unless you have a really weird data structure, you won't actually need to use UNION SELECTs. A query like this would set you up for a cross tab in CR:


declare @year_no int;
set @year_no = {?Selected Year}

SELECT
DatePart(mm,DateField) AS month_no,
DatePart(yy,DateField) AS year_no,
SaleID,
Sales
FROM tblSales
WHERE DatePart(yy,DateField) = @year_no;

-


For the SQL cross tab approach you simply add extra lines to the SQL. Like this...


DECLARE @Year INT;
SET @Year = {?Year};
 
SELECT
s.CategoryName,
COUNT(CASE WHEN DatePart(mm, s.DateField) = 1 THEN s.SaleID END) AS JanSaleCount,
SUM(CASE WHEN DatePart(mm, s.DateField) = 1 THEN s.SaleAmount END) AS JanSales,
COUNT(CASE WHEN DatePart(mm, s.DateField) = 2 THEN s.SaleID END) AS FebSaleCount,
SUM(CASE WHEN DatePart(mm, s.DateField) = 2 THEN s.SaleAmount END) AS FebSales,
------------------------------------------------------------------
-- repeat the same process for the remaining months --
------------------------------------------------------------------
FROM tblSales AS s
WHERE DatePart(yy, s.DateField) = @Year
GROUP BY s.CategoryName;

HTH,

Jason

Former Member
0 Kudos

As far as this part...

Need to return Jan Feb Mar

count(distinct claimnumber) "total claims"

count(distinct claimnumber,linenumber) "total lines"

Not sure what you are wanting to do here...

Do you want a simple count of the unique claim numbers along with the total number of lines?

If so, try this:


SELECT
count(distinct claimnumber) "total claims",
count(linenumber) "total lines"
FROM ???
WHERE ...

Or are you looking for a list of unique claim numbers and a count of line numbers that correspond with those claim numbers?

If so, try this:


SELECT
claimnumber,
COUNT(linenumber) AS "totallines"
FROM ???
WHERE...
GROUP BY claimnumber

If I'm off base on both scenarios just let me know.

Jason

Former Member
0 Kudos

Hi Jason,

I have a similar issue but I want to display all the transactions between date1 and date2.

Some dates may not have any transaction.

Is there an easy way to do this without using a crosstab? Please refer to post "Re: Crosstab Design" if you can.

Thanks

Edited by: Sharma Hari on Nov 18, 2009 8:31 PM

Former Member
0 Kudos

Sharma,

The way to display transactions that occur between 2 dates in SQL is with the use of the WHERE clause

For example:


DECLARE @date1, @date2
SET @date1 = {?BeginDate}; SET @date2 = {?EndDate

SELECT *
FROM tblSales
WHERE OrderDate BETWEEN @date1 AND @date2 +1

note: I used date2 + 1... Most RDBMS will default to midnight of the specified date if no time value is supplied. This is fine for the date1 but will eliminate date2. The "+ 1" allows date2 to be included in the selection

-


You don't need to use the cross tab at all. All the cross tab does is "convert" row based data into columns. So unless you want a separate column for each date or date range there is no need to even consider cross tabs.

-


If you are not comfortable writing SQL Commands you can simply use the CR Select Expert. It can accomplished the same thing without the need to write your own SQL commands.

HTH,

Jason

Former Member
0 Kudos

Jason,

Thank you for the reply.

I need to display each day as a column and not as a row.

I also need to display categories with 0 transacation count for each day.

I do not know if this is possible with SQL in command object as my date range may be for 2 months or more. I think it would be possible for a week. I might have to use a crosstab if I have a date range of more than few weeks. Do you agree?

Thanks

Former Member
0 Kudos

I agree with most of what Jason has said, but there are a few things that might be clarified a bit:

Adding 1 to the end date would be necessary if (a) the database field includes a time value as well as a date, and (b) if the end date parameter does not. You would not want to do that if either of these two conditions do not hold, otherwise you'll be adding a day's worth of data to the report, perhaps unknowingly. (Actually adding 1 might cause extra records to be selected if there is data for midnight the day after the end date, so you really should subtract a millisecond after adding 1, or force the time portion of the end date to 23:59:59.99999, which my preferred method, as it is obvious what you are doing.)

Sometimes CrossTabs can be a blessing, and make report writing a whole bunch easier. Other times, they can turn into a huge pain in the neck. It really depends on what you are trying to accomplish, what your data looks like, and what's easier to get from there to here. If your requirements are for a grid of counts or sums with one (or a few) types of values (like month, year, product, whatever) going across the page, and one (or a few) types of data going down the page, a cross tab is probably the easiest way to go. If the result needs to be more complicated, a regular report is often easier. One rule of thumb that I use is "If you don't know at design time how many columns are required, go with a cross tab. Otherwise, use a regular report."

And lastly, although I agree that the selection expert and other crystal tools can achieve anything that an SQL Command can do, run times are frequently much faster using a properly written SQL Command. By pushing all of the record selection, table joins, and aggregation to the database, far less data gets returned to Crystal. Databases are designed to do these types of tasks with great performance; Crystal is designed to do these tasks because it has to. (That's a bit harsh, but having Crystal do these tasks will never be as fast as having a database do them.) Also, if the database is not on the same machine as where the report is running from, you also have the issue of having to pump all of the data across the network. Minimizing that data results in a much faster runtime. Crystal does try to push as much of the work to the database as it can, but Crystal doesn't always do a perfect job of that (mainly because it has to do it in a general manner).

HTH,

Carl

Former Member
0 Kudos

Sharma,

If you need to display each day as it's own column AND the number of days will vary, then a cross tab is defiantly the way to go. The real question is... Does anyone really want to try to derive meaningful information by looking at 30 - 70 columns of data?

You can display all of your available categories, even the ones that have no transactions... Here's how:


SELECT 
c.CategoryName,
t.*
FROM tblCategories AS c
LEFT OUTER JOIN tblTransactions AS t ON c.CategoryID = t.CategoryID

The use of an LEFT OUTER JOIN will display ALL records from the table on the left (tblCategories) and only those records from the right table (tblTransactions) that match records on the left. You shouldn't loose any tblTransactions records because there shouldn't be transactions being made on categories that don't exist in tblCategories.

Jason

Former Member
0 Kudos

Thanks Jason. I will use Crosstab.

You are right about the 30 to 70 columns of data but the requirement is to provide options to select any date range.

Former Member
0 Kudos

Carl,

I think you may be my long lost brother. LOL I agree that I should have a better explanation of the "+1" thing and how check and see if it's actually needed.

I agree 100% with the statement:


One rule of thumb that I use is "If you don't know at design time how many columns are required, go with a cross tab. Otherwise, use a regular report."

I'm a control freak. I don't like not knowing how many columns are going on the report at design time. I'm usually pretty good at convincing those requesting reports that they don't either. 😄

I'll disagree with the statement:


... the selection expert and other crystal tools can achieve anything that an SQL Command can do...

As soon as you need to start nesting SQL statement to create complex data sets it becomes clear that a human (who knows how to write SQL) can do far more than CR. Sorry if implied anything to the contrary. I only mentioned the Select Expert because not everyone can write their own SQL and as long as there are no commands or SPs involved, CR will push the selection criteria back to the server.

That said... If writing reports is a significant part of you job, learning the write SQL will be a VERY worthwhile investment. It's fast and easy to learn, and for the most part it's portable. That means that learning the SQL for 1 RDBMS doesn't leave you out in the cold if you have to move to a different one.

Jason

Former Member
0 Kudos

Thank you Jason. Sorry if I havent acknowledge others..because I havent had the time to read everything because this crystal reports is bugging me!

Jason about this query

SELECT

count(distinct claimnumber) "total claims",

count(linenumber) "total lines"

FROM ???

WHERE ...

this is not what I want..

I want something similar to the first one u gave me regarding CASE, something like this

,COUNT(DISTINCT (CASE WHEN DatePart(mm, CD.datesubmitted) = 1 THEN CD.claimnumber,CD.Linenumber END)) 'Jan'

I need to get the count of the lines processed..but claimnumber,linenumber is not unique in the table, so i need to take a distinct. The above syntax doesnt work and also I tried putting distinct after THEN..that too doesnt work..

so could we do this any way. I think I need to post in SQL server forums..but Jason u can give a try:)

Former Member
0 Kudos

Hey BA,

You are close with...


,COUNT(DISTINCT (CASE WHEN DatePart(mm, CD.datesubmitted) = 1 THEN CD.claimnumber,CD.Linenumber END)) 'Jan'

The problem is that you can't bring in 2 fields into the THEN portion of your statement... at least w/o concatenating them into 1 field.

This will give you the distinct claim numbers for the month of January...


,COUNT(DISTINCT (CASE WHEN DatePart(mm, CD.datesubmitted) = 1 THEN CD.claimnumber END)) 'Jan_claimnumbers'

I'm still guessing as to what you are trying to do with the line numbers. You are aggregating the claim number so I know that you aren't trying to find the number of lines for each claim number... The leaves me thinking that you are looking for the count of distinct Linenumbers that are associated with with the claimnumbers.

See if this does the trick...


,COUNT(DISTINCT (CASE WHEN DatePart(mm, CD.datesubmitted) = 1 THEN CD.Linenumber END)) 'Jan_Linenumbers'

If not I'm going to have to see some sample data and know exactly what you are trying to do.

HTH,

Jason

Former Member
0 Kudos

But distinct linenumber is not the solution I guess..

I have

ClaimNumber LineNumber Version

a 1 1

a 1 2

a 2 1

a 3 1

b 1 1

b 2 1

c 1 1

c 2 1

c 3 1

c 4 1

c 4 2

c 4 3

For this the distinct linenumber will not get me the total line processed. The thing is that the table doesnt the claimnumber or linenumber as the composite primary key or also there will be a primary key but there is an version column which says its the latest version or not..so to get the total number or lines processed in jan i need to somehow retrieve the distinct Claimnumber,linenumber in Jan.

Help me..Jason u can do it:)

In the above example the answer i need is - 9

Former Member
0 Kudos

Jason..or anybody if u see this post please reply that atleast you have seen this post, because I am not sure that this post has become stale and nobody is seeing it or is it nobody has solutions..so to let me know post any reply for this.

Former Member
0 Kudos

BA,

OK. Just to spell it out in plain English... For LineNumber you want a distinct count grouped by ClaimNumber. This is doable.

Just 2 more questions...

1) Look at the sample data you provided, CliamNumber "C", LineNumber "4" to be specific.

If C 4 1 & C 4 2 were entered in the month of January and C 4 3 was entered in February, do you want C 4 to show in January's numbers or did the C 4 3 transaction "nullify" the previous 2?

2) Does the table we are looking at have a single column primary key available? (The answer to the 1st question will determine weather or not we need to sue a sub-query. A good PK will help if it's necessary.)

... And don't worry BA either way it will work.

Jason

JWiseman
Active Contributor
0 Kudos

hi all,

another method if you don't want to use a command is to:

1) create a formula called 01_Jan which is similar to if month({Orders.Order Date}) = 1 then {Orders.Order Amount}

2) duplicate this formula into 02_Feb and edit it to if month({Orders.Order Date}) = 2 then {Orders.Order Amount}

3) repeat the above to 12_Dec

4) in a cross tab put in all 12 formulae as summaries

5) in the cross tab options, customize tab, change the summarized fields to horizontal display

using this method you can go directly against your tables and can easily modify your record set if needed.

cheers,

jamie

Former Member
0 Kudos

I am really sorry guys..been very busy so couldnt thank anybody or check the results!!

Please pardon me for this..

Answers (0)