We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Solved
Capture a value in Crystal Reports
Posted on 2014-11-18
I have a report that displays warranty item count totals based on month. The totals change daily as new items are added (open) and existing items are completed (closed). I would like to capture those values at the end of each month, so that I can see a trend. Example:
As of 11:59 PM, January 31st, 2014. the total open warranty items was 58.
As of 11:59 PM, February 28th, 2014. the total open warranty items was 52.
As of 11:59 PM, March 31st, 2014. the total open warranty items was 46.
As of 11:59 PM, April 30th, 2014. the total open warranty items was 38.
etc.
The formula must capture the count of open items and then save that number as a value that I can reference as a static number.
Thanks in advance for any suggestions.
As of 11:59 PM, January 31st, 2014. the total open warranty items was 58.
As of 11:59 PM, February 28th, 2014. the total open warranty items was 52.
As of 11:59 PM, March 31st, 2014. the total open warranty items was 46.
As of 11:59 PM, April 30th, 2014. the total open warranty items was 38.
etc.
The formula must capture the count of open items and then save that number as a value that I can reference as a static number.
Thanks in advance for any suggestions.
[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
-
3
2
2
7 Comments
Active today
I assume you mean in the report and not elsewhere as in another application or in a SQL query.
What are you trying to do with the numbers?
You could produce counts (as summary values) for each month then use them in the report footer to show trends.
What data do you have available that the counts are based on?
mlmcc
What are you trying to do with the numbers?
You could produce counts (as summary values) for each month then use them in the report footer to show trends.
What data do you have available that the counts are based on?
mlmcc
Active today
Yes, I would want to display the results in the report.
I simply want to display a point in time count.
The count would be based on a completion date. If the item is open, the date value is 01/01/1900. When the item is completed, an actual date is assigned.
Jan Feb Mar
58 52 46
I want to capture those numbers at a point in time. I don't want the January and February count to change as items are completed. The formula would have to count how many items have a date of 01/01/1900 as of 1/31 and then store that count as "January Total".
Some of those item will then be completed over the next month, as well as new items added. Again, the formula would count how many items have a date of 01/01/1900 as of 2/28 and then store that count as "February Total", and so on.
I simply want to display a point in time count.
The count would be based on a completion date. If the item is open, the date value is 01/01/1900. When the item is completed, an actual date is assigned.
Jan Feb Mar
58 52 46
I want to capture those numbers at a point in time. I don't want the January and February count to change as items are completed. The formula would have to count how many items have a date of 01/01/1900 as of 1/31 and then store that count as "January Total".
Some of those item will then be completed over the next month, as well as new items added. Again, the formula would count how many items have a date of 01/01/1900 as of 2/28 and then store that count as "February Total", and so on.
You can run a report and calculate the totals in a cross tab in real time. I guess you have a Open and Closed date for each item . If yes , you can get the number of open items as of the end of each month. It is never a good idea to stash the data
Active today
Here's the problem if I count them in real time. As time goes by, the real time numbers will change. I want a static snapshot what was open at the end of each month, regardless of when the items were entered into the system. If I use real time data, as any open items are closed, I think the prior month totals will change. Again, I want a record and they should not change as time goes by.
Active today
If you have the appropriate data you can do that in Crystal.
I assume you have the following
Warranty Item Id
Date Opened
Date Closed
Warranty Problem Description
Warranty Item Status
With that you could with the help of formulas capture what you want.
If you don't have the closed date but just an indication of its status then you won't be able to do it in Crystal.
Crystal does with the aid of outside tools have the ability to write outputs to a database. You would have to have another report to use those outputs. I have never done anything like that but there are tools that can help
Visual Cut from Millet Software could do it for you
http://www.milletsoftware.com/Visual_CUT.htm
From the benefits
http://www.milletsoftware.com/visualcutBenefit.htm
ODBC Exports can replace or append to existing tables. This provides ETL (Extract, Transform, Load) and scheduled data snapshot functionality.
I expect there are other tools that can do it also.
I think it would probably be better to use an application to do the updates and capture that data into another table
mlmcc
I assume you have the following
Warranty Item Id
Date Opened
Date Closed
Warranty Problem Description
Warranty Item Status
With that you could with the help of formulas capture what you want.
If you don't have the closed date but just an indication of its status then you won't be able to do it in Crystal.
Crystal does with the aid of outside tools have the ability to write outputs to a database. You would have to have another report to use those outputs. I have never done anything like that but there are tools that can help
Visual Cut from Millet Software could do it for you
http://www.milletsoftware.com/Visual_CUT.htm
From the benefits
http://www.milletsoftware.com/visualcutBenefit.htm
ODBC Exports can replace or append to existing tables. This provides ETL (Extract, Transform, Load) and scheduled data snapshot functionality.
I expect there are other tools that can do it also.
I think it would probably be better to use an application to do the updates and capture that data into another table
mlmcc
I was part of the team , which developed R-Tag, which is using a newer technology and provides unbeatable number of options to export data. Nevertheless, these kind of tools are not designed to manage data. They are designed to run reports. What will happen if you run the report twice and it exports the data 2 times ?
You need to prepare your data in a way , which will allow you to generate reports without saving a copy. A sample structure will involve 2 dates - CreatedDate when the record was physically created and EffectiveDate when the record become effective. For example you may receive an email to close a record after hours on November 28th - Friday, but you will be able to close it on December 1st - Monday. The EffectiveDate will be November 28th and CreatedDate - December 1st,. Then, you can choose, which date to use in your report.
RecordID CreatedDate EffectiveDate Status
ABC 1/1/2014 1/1/2014 Open
ABC 12/1/2014 11/28/2014 Close
This is a classic AsOn - AsIfOn structure and will allow you to report by the status at a specific point in time. For example you can easily find that ABC was effective 1/1/2014-12/28/2014 and at the same time you can report your data by the time it is created
You need to prepare your data in a way , which will allow you to generate reports without saving a copy. A sample structure will involve 2 dates - CreatedDate when the record was physically created and EffectiveDate when the record become effective. For example you may receive an email to close a record after hours on November 28th - Friday, but you will be able to close it on December 1st - Monday. The EffectiveDate will be November 28th and CreatedDate - December 1st,. Then, you can choose, which date to use in your report.
RecordID CreatedDate EffectiveDate Status
ABC 1/1/2014 1/1/2014 Open
ABC 12/1/2014 11/28/2014 Close
This is a classic AsOn - AsIfOn structure and will allow you to report by the status at a specific point in time. For example you can easily find that ABC was effective 1/1/2014-12/28/2014 and at the same time you can report your data by the time it is created
Active today
I reviewed and demoed Visual Cut and found, that while cumbersome, it will do what I need in order to capture and export point in time information. I actively use another program, reportal (http://www.reportalsoftware.com/), which has similar schedule and export functionality, so my plan is to try using reportal first. If that doesn't work, I will go back to Visual Cut.
Thanks for the recommendation.
Thanks for the recommendation.
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String
2. Set the Stored Procedure Parameter to varchar(max) Â --<---- This part here is the key to it's success
Example:   @cst_key var…
Hi,
In my previous Article (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR).
Now it's tim…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment.
To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month9 days, 19 hours left to enroll
722 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.