Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.
COURSE of the MONTH
8 days, 4 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Crystal Report Financial
Posted on 2010-11-29
For simplicity sake, let's say I have 2 tables as a data source for this report;
ActiveTable, customer, invoice date, invoice amount
HistoryTable, customer, invoice date, invoice amount
I'm trying to do the following;
1- Group By Customers
2- Run the report based on "ActiveTable.invoice date" parameter I'm generating
3- Bring out the "ActiveTable.Invoice amount" and the "HistoryTable.invoice amount" side by side.
4- Group on the customer
5- Group on the month
6- Calculate a YTD for current and past year
Now this is a simple view of what I want, the rest is bit testy, because in the "ActiveTable" each customer could have multiple invoices within a month, so even if I group on the "Customer" and "Month", mgmt would still like to see the invoice # when the report is drilled down.
Your thoughts please.
Thanks
ActiveTable, customer, invoice date, invoice amount
HistoryTable, customer, invoice date, invoice amount
I'm trying to do the following;
1- Group By Customers
2- Run the report based on "ActiveTable.invoice date" parameter I'm generating
3- Bring out the "ActiveTable.Invoice amount" and the "HistoryTable.invoice amount" side by side.
4- Group on the customer
5- Group on the month
6- Calculate a YTD for current and past year
Now this is a simple view of what I want, the rest is bit testy, because in the "ActiveTable" each customer could have multiple invoices within a month, so even if I group on the "Customer" and "Month", mgmt would still like to see the invoice # when the report is drilled down.
Your thoughts please.
Thanks
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
7-
7
14 Comments
Active today
When you say you want the active and history side-by-side, I assume you just want the total for the month or do you want to see the details for a given date?
if you just need to see summaries then i would use a COMMAND for the data source like
SELECT * FROM tblActive WHERE Year({Datefield}) = Year(CurrentDate)
UNION
SELECT * FROM tblHistory WHERE Year({Datefield}) = Year(CurrentDate) AND {DateField} <= Date(Year(CurrentDate), Month(CurrentDate),Day(Cur
mlmcc
if you just need to see summaries then i would use a COMMAND for the data source like
SELECT * FROM tblActive WHERE Year({Datefield}) = Year(CurrentDate)
UNION
SELECT * FROM tblHistory WHERE Year({Datefield}) = Year(CurrentDate) AND {DateField} <= Date(Year(CurrentDate), Month(CurrentDate),Day(Cur
mlmcc
Active today
Yes, mlmcc, the object is to SUM the Invoice Amount by Month/Year by Customer, it's the sum that I need to display side-by-side, current year next to last year.
As for your recommendation, I've had some success running a similar one from a TSQL client, but to integrate such logic into Crystal Report requires some dancing (unless you can recommend some moves)
Lastly, how about the YTD for current / past years? any nuggest of wisdom?
Thx
As for your recommendation, I've had some success running a similar one from a TSQL client, but to integrate such logic into Crystal Report requires some dancing (unless you can recommend some moves)
Lastly, how about the YTD for current / past years? any nuggest of wisdom?
Thx
Active today
With the filter i added you will only have data for current YTd and last YTD
Create a new report
Click to ADD NEW DATA SOURCE
Choose the database connection method
Navigate to the database
Choose ADD COMMAND
Use the SQL I presented above changing to use the proper table and field names
mlmcc
Create a new report
Click to ADD NEW DATA SOURCE
Choose the database connection method
Navigate to the database
Choose ADD COMMAND
Use the SQL I presented above changing to use the proper table and field names
mlmcc
Active today
mlmcc;
Please see the attached file for full picture of what I'm after, in a nutshell these are the immediate tables involved with their relevant columns and datatypes, and for extra info I added the relation via the arrows. The information stored in CurrentTable have the following flow/logic, Invoice Number 11233, was for customer ABC123, for the amount of $3000, the customer paid that in chunks, each instance / payment / chunk is recorded separately as follows;
Customer number Invoice number Amount Payment date
ABC123 11233 $500 10/1/2010
ABC123 11233 $1000 10/2/2010
ABC123 11233 $1300 10/3/2010
ABC123 11233 $200 10/4/2010
So to get the total from the “CurrentTable” you’ll have to group on the “InvoiceNumber” on the “CustomerNumber”, then by Month and Year based on “PaymentDate”
The way I was tackling this is that I built separate queries for each table, got the data I want out and spun the query code around and built Views of these tables, as I began to pull from these views, I started questioning that approach as it’s limiting me when it comes to aggregation, summation and the like, so I thought to drop you a line.
Please review and let me know, also, what do you mean by Year(CurrentDate) in the solution you, I must have missed something here, please explain.
Thx
YearToYear.xlsx
Please see the attached file for full picture of what I'm after, in a nutshell these are the immediate tables involved with their relevant columns and datatypes, and for extra info I added the relation via the arrows. The information stored in CurrentTable have the following flow/logic, Invoice Number 11233, was for customer ABC123, for the amount of $3000, the customer paid that in chunks, each instance / payment / chunk is recorded separately as follows;
Customer number Invoice number Amount Payment date
ABC123 11233 $500 10/1/2010
ABC123 11233 $1000 10/2/2010
ABC123 11233 $1300 10/3/2010
ABC123 11233 $200 10/4/2010
So to get the total from the “CurrentTable” you’ll have to group on the “InvoiceNumber” on the “CustomerNumber”, then by Month and Year based on “PaymentDate”
The way I was tackling this is that I built separate queries for each table, got the data I want out and spun the query code around and built Views of these tables, as I began to pull from these views, I started questioning that approach as it’s limiting me when it comes to aggregation, summation and the like, so I thought to drop you a line.
Please review and let me know, also, what do you mean by Year(CurrentDate) in the solution you, I must have missed something here, please explain.
Thx
YearToYear.xlsx
Active today
mlmcc,
NO it does not, and that's my dilemma, this was a Foxpro database that was converted to MSSQL by an outside group, no reliable ER, no PK FK structure, just wierd and I'm trying to work with what I got.
I'll take any bone you throw my way at this stage.
NO it does not, and that's my dilemma, this was a Foxpro database that was converted to MSSQL by an outside group, no reliable ER, no PK FK structure, just wierd and I'm trying to work with what I got.
I'll take any bone you throw my way at this stage.
Active today
Year(CurrentDate) extracts the year from the current date. This was so I could get the data for last year.
WHat is in the history table?
You could link them on the customeer number and put the history report in the group (customer) footer.
mlmcc
WHat is in the history table?
You could link them on the customeer number and put the history report in the group (customer) footer.
mlmcc
Active today
Not the kind that ignores valuable feedback, they had me putting out other fires, I'll try your approach mlmcc and see where it takes me, in the meantime I'll accept the solution proposed and revisit at a later time if needed.
Many Thanks
Many Thanks
Active today
Is the answer not valid, if so I'll reopen. If you just need to ask a new question then do so.
mlmcc
mlmcc
Active today
Sorry for not getting back any faster, and the best way to answer your question is to explain my project better so that you have a better prospective.
**************************
What you have here are two views,
The "ClosedPeriods" view has the following columns;
ccustno (for customer number)
InvoiceMonth
InvoiceYear
PastSales
The "OpenPeriods" view has the following columns;
ccustno
InvoiceMonth
InvoiceYear
CurrentSales
**************************
The essences of this project is to capture the sales from both views and display the results in a columnized fashion, for example;
Customer, June2010, June2009, YTD2010, YTD2009
A100 , $2000 , $1000 , $11233.00, $10988.00
The YTD logic aggregates the total sales up to the month of the report was run, in our case here, if I was to run the report today, the YTD2010 will reflect all sales starting December 2009 through December 2010, and from the "ClosedPeriods" it'll be December 2008 through December 2009, my code below gives me everything correctly except the YTD for both open and closed periods.
**************************
SELECT OpenPeriods.ccustno, OpenPeriods.InvoiceMonth, OpenPeriods.InvoiceYear, OpenPeriods.CurrentSales,
ClosedPeriods.InvoiceMonth
OpenPeriods.CurrentSales - ClosedPeriods.PastSales AS DifferenceByPeriod, SUM(CASE WHEN (OpenPeriods.InvoiceYear) <= YEAR(GetDate())
THEN (OpenPeriods.CurrentSales)
= ClosedPeriods.InvoiceYear THEN (ClosedPeriods.PastSales) END) AS PastYTD, CASE WHEN (dbo.ClosedPeriods.PastSal
(dbo.OpenPeriods.CurrentSa
* 100 ELSE 0 END AS MonthlyDifference
FROM OpenPeriods INNER JOIN
ClosedPeriods ON OpenPeriods.InvoiceMonth = ClosedPeriods.InvoiceMonth
OpenPeriods.InvoiceYear - 1 = ClosedPeriods.InvoiceYear
GROUP BY OpenPeriods.ccustno, OpenPeriods.InvoiceMonth, OpenPeriods.InvoiceYear, ClosedPeriods.InvoiceMonth
OpenPeriods.CurrentSales, ClosedPeriods.PastSales
**************************
Your thougths please.
Thx
**************************
What you have here are two views,
The "ClosedPeriods" view has the following columns;
ccustno (for customer number)
InvoiceMonth
InvoiceYear
PastSales
The "OpenPeriods" view has the following columns;
ccustno
InvoiceMonth
InvoiceYear
CurrentSales
**************************
The essences of this project is to capture the sales from both views and display the results in a columnized fashion, for example;
Customer, June2010, June2009, YTD2010, YTD2009
A100 , $2000 , $1000 , $11233.00, $10988.00
The YTD logic aggregates the total sales up to the month of the report was run, in our case here, if I was to run the report today, the YTD2010 will reflect all sales starting December 2009 through December 2010, and from the "ClosedPeriods" it'll be December 2008 through December 2009, my code below gives me everything correctly except the YTD for both open and closed periods.
**************************
SELECT OpenPeriods.ccustno, OpenPeriods.InvoiceMonth, OpenPeriods.InvoiceYear, OpenPeriods.CurrentSales,
ClosedPeriods.InvoiceMonth
OpenPeriods.CurrentSales - ClosedPeriods.PastSales AS DifferenceByPeriod, SUM(CASE WHEN (OpenPeriods.InvoiceYear) <= YEAR(GetDate())
THEN (OpenPeriods.CurrentSales)
= ClosedPeriods.InvoiceYear THEN (ClosedPeriods.PastSales) END) AS PastYTD, CASE WHEN (dbo.ClosedPeriods.PastSal
(dbo.OpenPeriods.CurrentSa
* 100 ELSE 0 END AS MonthlyDifference
FROM OpenPeriods INNER JOIN
ClosedPeriods ON OpenPeriods.InvoiceMonth = ClosedPeriods.InvoiceMonth
OpenPeriods.InvoiceYear - 1 = ClosedPeriods.InvoiceYear
GROUP BY OpenPeriods.ccustno, OpenPeriods.InvoiceMonth, OpenPeriods.InvoiceYear, ClosedPeriods.InvoiceMonth
OpenPeriods.CurrentSales, ClosedPeriods.PastSales
**************************
Your thougths please.
Thx
Active today
What is the start date for the YTD you want to use?
YTD only works on the current year. and it starts at 1 Jan 2010.
If you need the prior years or a different start date, then you will have to use formulas.
What is wrong?
mlmcc
YTD only works on the current year. and it starts at 1 Jan 2010.
If you need the prior years or a different start date, then you will have to use formulas.
What is wrong?
mlmcc
Active today
That's what I was hoping to get your thoughts on, The Formula for PriorYTD, now I have some idea but only in my head at this time but and I was interested in seeing your approach to such formula
Featured Post
Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Working with Network Access Control Lists in Oracle 11g (part 2)
Part 1: http://www.e-e.com/A_8429.html
Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access. For many…
How to Create User-Defined Aggregates in Oracle
Before we begin creating these things, what are user-defined aggregates? They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work. It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses
Course of the Month8 days, 4 hours left to enroll
765 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.