Chris McDonald
asked on
Crystal Reports - One Column Data into Two Rows
If there is any insight that you could give me on this situation, I would appreciate it. I feel like I am really close to an answer after days of progress.
My challenge is that I want to display data from one column in two different places:
Here is what I am shooting for:
Diagram 1: Pipeline Shipments for 'TK101':
Tank Product Max Net Product Pipeline Receipts
TK101 #CRUDE 209,003.15 95,555 3,045.00
The 'PipelineReceipts' is a sum of transfers that feed into TK101.
This is the equation that I am using to get the “PipelineReceipts”:
Diagram 2: “PipelineReceipts” Formula in Crystal Reports:
If {spRecShipInv;1.RecShipID}
then
{spRecShipInv;1.MeasLiqVol
Else 0
The equation produces the following:
Diagram 3: Results from Equation:
Transfer-63786 RECOVERED SLOP OIL 0260 0.00 0.00 2,601.00
Transfer-63787 RECOVERED SLOP OIL 0260 0.00 0.00 444.00
The two results from the transfers give me the total that I need.
Here is a copy of the relevant tables from the data source ‘spRecShipInv’
Diagram 4: spRecShipInv Table for ‘Transfers’:
FlowID FlowMeter FlowType RecShipID InService MeasLiqVol RecShipSrc RecShipDest
Transfer-63786 Transfer-63786 R PipelineReceipts IS 2601 PipelineReceipts TK101
Transfer 63787 Transfer 63787 R PipelineReceipts IS 444 PipelineReceipts TK101
If it was possible to sum this data and put it beside ‘TK101’ then I’d be laughing:
As I mentioned at the start, the tank (‘TK101’) and the desired transfers (ending in ‘63786’ and ‘63787’ respectively) come from the same column in the ‘spRecShipInv ‘table:
Diagram 5: TK101 Items:
FlowID FlowMeter FlowType RecShipID InService MeasLiqVol RecShipSrc RecShipDest
TK101 NULL T NULL IS 0 NULL NULL
Please let me know if there is any other information (copies of databases, additional screenshots, ) that I can provide and I’ll do so swiftly.
Thank you again for your time.
Chris
My challenge is that I want to display data from one column in two different places:
Here is what I am shooting for:
Diagram 1: Pipeline Shipments for 'TK101':
Tank Product Max Net Product Pipeline Receipts
TK101 #CRUDE 209,003.15 95,555 3,045.00
The 'PipelineReceipts' is a sum of transfers that feed into TK101.
This is the equation that I am using to get the “PipelineReceipts”:
Diagram 2: “PipelineReceipts” Formula in Crystal Reports:
If {spRecShipInv;1.RecShipID}
then
{spRecShipInv;1.MeasLiqVol
Else 0
The equation produces the following:
Diagram 3: Results from Equation:
Transfer-63786 RECOVERED SLOP OIL 0260 0.00 0.00 2,601.00
Transfer-63787 RECOVERED SLOP OIL 0260 0.00 0.00 444.00
The two results from the transfers give me the total that I need.
Here is a copy of the relevant tables from the data source ‘spRecShipInv’
Diagram 4: spRecShipInv Table for ‘Transfers’:
FlowID FlowMeter FlowType RecShipID InService MeasLiqVol RecShipSrc RecShipDest
Transfer-63786 Transfer-63786 R PipelineReceipts IS 2601 PipelineReceipts TK101
Transfer 63787 Transfer 63787 R PipelineReceipts IS 444 PipelineReceipts TK101
If it was possible to sum this data and put it beside ‘TK101’ then I’d be laughing:
As I mentioned at the start, the tank (‘TK101’) and the desired transfers (ending in ‘63786’ and ‘63787’ respectively) come from the same column in the ‘spRecShipInv ‘table:
Diagram 5: TK101 Items:
FlowID FlowMeter FlowType RecShipID InService MeasLiqVol RecShipSrc RecShipDest
TK101 NULL T NULL IS 0 NULL NULL
Please let me know if there is any other information (copies of databases, additional screenshots, ) that I can provide and I’ll do so swiftly.
Thank you again for your time.
Chris
ASKER
Thank you for responding Raghavendra.
Each field in the 'RecShipID' would have to be summed up for the tanks. For 'TK101' I am looking for the 'PipelineReceipts' in this example. There is also a 'PipelineShipments' item in the 'RecShipID' column. I would be looking to sum up the 'MeasLiqVol' totals for each of the 'RecShipID' fields for each of the tanks. This would be the total 'MeasLiqVol' assigned based on the 'RecShipID' for each tank.
The current Crystal report format doesn't have any groupings for it. The 'FlowID' lists both the tanks and the transfers (used for the 'Shipments' and 'Receipts'). As noted in 'Diagram 4' above, each transfer has a 'RecShipDest' and a 'RecShipSrc'. Receipts have the tank (TK101) listed in the 'RecShipDest.' Shipments have the tank listed in the 'RecShipSrc.'
So 'TK101' could have receipts and shipments on the same day.
I greatly appreciate the help.
Please let me know if you need any more info.
Chris
Each field in the 'RecShipID' would have to be summed up for the tanks. For 'TK101' I am looking for the 'PipelineReceipts' in this example. There is also a 'PipelineShipments' item in the 'RecShipID' column. I would be looking to sum up the 'MeasLiqVol' totals for each of the 'RecShipID' fields for each of the tanks. This would be the total 'MeasLiqVol' assigned based on the 'RecShipID' for each tank.
The current Crystal report format doesn't have any groupings for it. The 'FlowID' lists both the tanks and the transfers (used for the 'Shipments' and 'Receipts'). As noted in 'Diagram 4' above, each transfer has a 'RecShipDest' and a 'RecShipSrc'. Receipts have the tank (TK101) listed in the 'RecShipDest.' Shipments have the tank listed in the 'RecShipSrc.'
So 'TK101' could have receipts and shipments on the same day.
I greatly appreciate the help.
Please let me know if you need any more info.
Chris
Hi,
Can you try as below?
1. insert a grouping on RecShipID, retain the currently displayed fields in respective sections.
2. Insert a summary on MeasLiqVol using 'Sum' option and select summary location as Group1.
3. Remove the Group value getting displayed in group header section and move the summary (created in step 2) to group header part where it is required to be displayed.
4. Right click on group header section and navigate to "Section Expert" and check "Underlay following sections" checkbox. This allows us to display the summary value in the first row of each unique RecShipID fields values.
Hope this meets your requirement. Please makes changes and confirm if any changes are needed.
Can you try as below?
1. insert a grouping on RecShipID, retain the currently displayed fields in respective sections.
2. Insert a summary on MeasLiqVol using 'Sum' option and select summary location as Group1.
3. Remove the Group value getting displayed in group header section and move the summary (created in step 2) to group header part where it is required to be displayed.
4. Right click on group header section and navigate to "Section Expert" and check "Underlay following sections" checkbox. This allows us to display the summary value in the first row of each unique RecShipID fields values.
Hope this meets your requirement. Please makes changes and confirm if any changes are needed.
Agree. A summary should provide what you want. If you are moving it between group headers it may not produce the expected value.
mlmcc
mlmcc
ASKER
This is what I ended up with after following the suggested steps:
So far I have summaries for the totals by the transfers. This transfer total still needs to be applied to the respective Tank.
I got a summary for the transfers:
If I could have this total assigned to the tank (TK101) that the transfers are receipts for that would be the next step:
'TK101' is also in the 'FlowID' column:
My next step is to try to find the right combination of groupings and headers and/or summaries to see if I can get the values to associate with the field 'TK101' from the 'FlowID' column (@TankGroup = spRecShipInv;1.FlowID).
Any suggestions would be welcome.
I am very grateful for your help.
Chris.
So far I have summaries for the totals by the transfers. This transfer total still needs to be applied to the respective Tank.
I got a summary for the transfers:
If I could have this total assigned to the tank (TK101) that the transfers are receipts for that would be the next step:
'TK101' is also in the 'FlowID' column:
My next step is to try to find the right combination of groupings and headers and/or summaries to see if I can get the values to associate with the field 'TK101' from the 'FlowID' column (@TankGroup = spRecShipInv;1.FlowID).
Any suggestions would be welcome.
I am very grateful for your help.
Chris.
Hi,
Can you share the screenshot with complete data for Transfers and Receipts for the details you have shared and the required values?
How do you differentiate the transfers and receipts currently?
Thanks,
Raghavendra
Can you share the screenshot with complete data for Transfers and Receipts for the details you have shared and the required values?
How do you differentiate the transfers and receipts currently?
Thanks,
Raghavendra
ASKER
Here is the results from the' spRecShipInv' table. The 'RecShipID' column lists the different receipts and shipments.
The 'RecShipSrc' column has the tank for 'shipments' . The 'RecShipDest' has the tank for 'receipts':
The 'RecShipSrc' column has the tank for 'shipments' . The 'RecShipDest' has the tank for 'receipts':
Hi Chris,
I forgot to ask the output from Crystal instead of excel as it's more confusing because of null values.
Seems there is no common field which can be used for getting correct data for Transfers and Receipts from what we can see in the screenshot.
If we use RecShipID for grouping, the data for different flow id's will be clubbed. Do you agree with this or is it different from what we can see in Crystal output (without nulls)?
Please ensure all the details for flow id's TK101, TK102 are displayed in Crystal and share the screenshot.
I forgot to ask the output from Crystal instead of excel as it's more confusing because of null values.
Seems there is no common field which can be used for getting correct data for Transfers and Receipts from what we can see in the screenshot.
If we use RecShipID for grouping, the data for different flow id's will be clubbed. Do you agree with this or is it different from what we can see in Crystal output (without nulls)?
Please ensure all the details for flow id's TK101, TK102 are displayed in Crystal and share the screenshot.
ASKER
Here is the Crystal Report Layout after following the given suggestions:
Here is screenshot of the preview copy (page 2):
Here is a following page showing the transfers listed that arrive at the correct total, but do not have the total listed beside TK101:
I have experienced the exclusion of some of the values for the others.
Thank you again for your help.
Chris
Here is screenshot of the preview copy (page 2):
Here is a following page showing the transfers listed that arrive at the correct total, but do not have the total listed beside TK101:
I have experienced the exclusion of some of the values for the others.
Thank you again for your help.
Chris
Chris,
From what we see in the excel output and the data getting displayed in Crystal seems to be correct.
Flow ID's ending with 786 and 787 are having RecShipId " PipelineReceipts" and the MeasLiqVol values of 2601 and 444 and summation of those is 3045, which is correctly displayed. If you think it's not, then possibly to check the query and joins for correctness.
Please share your thoughts.
From what we see in the excel output and the data getting displayed in Crystal seems to be correct.
Flow ID's ending with 786 and 787 are having RecShipId " PipelineReceipts" and the MeasLiqVol values of 2601 and 444 and summation of those is 3045, which is correctly displayed. If you think it's not, then possibly to check the query and joins for correctness.
Please share your thoughts.
Since the report is grouped by Tank, the summaries can only go to that group.
I don't see how you associate the lines circled in blue with the tank TK101
mlmcc
I don't see how you associate the lines circled in blue with the tank TK101
mlmcc
ASKER
Would writing a temporary table into the SQL Server Management Studio stored procedure ('spRecShipInv') help?
Is there a way to have the Tank associated with the transfer in that table based on the 'RecShipSrc' and 'RecShipDest' as they compare to the 'FlowID'? Would you have advise for how to code the table to produce the 'PipelineReceipts' based on the association with the 'RecShipID' and the above mentioned columns?
Thank you,
Chris
Is there a way to have the Tank associated with the transfer in that table based on the 'RecShipSrc' and 'RecShipDest' as they compare to the 'FlowID'? Would you have advise for how to code the table to produce the 'PipelineReceipts' based on the association with the 'RecShipID' and the above mentioned columns?
Thank you,
Chris
Hi Chris,
Points to clarify based on the excel output screenshot shared:
1. Apart from PipelineReceipts and PipelineShipments, any other codes used to distinguish Receipts, Transfers, Shipments etc?
2. Assuming these are the only 2, can the columns RecShipSrc and RecShipDest be checked for values other than PipelineReceipts and PipelineShipments (like TK101) and replace FlowID (e.g Flow ID ending with 63786 to be replaced by actual Flow ID TK101 from column RecShipDest) if that is correct and helps to achieve the required values?
I think you are the best person to decide on the data since you have all the details.
Please check and revert if this is feasible or the assumptions are wrong.
Thanks,
Raghavendra
Points to clarify based on the excel output screenshot shared:
1. Apart from PipelineReceipts and PipelineShipments, any other codes used to distinguish Receipts, Transfers, Shipments etc?
2. Assuming these are the only 2, can the columns RecShipSrc and RecShipDest be checked for values other than PipelineReceipts and PipelineShipments (like TK101) and replace FlowID (e.g Flow ID ending with 63786 to be replaced by actual Flow ID TK101 from column RecShipDest) if that is correct and helps to achieve the required values?
I think you are the best person to decide on the data since you have all the details.
Please check and revert if this is feasible or the assumptions are wrong.
Thanks,
Raghavendra
ASKER
Raghavendra,
Thank you for the follow-up questions.
Here are the remaining vales for the rest of the Receipts and Shipments to the follow-up answer question (1):
For the second question, the values for the transfers would need to be attributable to the Tanks. In the example I have provided, the ('TK101') is listed for the 'Pipeline Receipts' would have to have the transfers ('63736' and '63737') associated with the Tank. Could this even be a separate column created in the stored procedure for SQL Server Management Studio?
The 'RecShipDest' for the transfers themselves ('63736' and '63737') would have 'TK101' (seen in previous screen shot - re-shown below).
If it was a 'shipment,' the tank would be listed in the 'RecShipSrc'. My hope was that this could be referenced to link it to a potentially new column in the SQL Query to move the 'MeasLiqVol' value to the Tank under 'Pipeline Receipts' rather than the transfers that are listed in the above examples.
Thank you again for your time.
Chris
Thank you for the follow-up questions.
Here are the remaining vales for the rest of the Receipts and Shipments to the follow-up answer question (1):
For the second question, the values for the transfers would need to be attributable to the Tanks. In the example I have provided, the ('TK101') is listed for the 'Pipeline Receipts' would have to have the transfers ('63736' and '63737') associated with the Tank. Could this even be a separate column created in the stored procedure for SQL Server Management Studio?
The 'RecShipDest' for the transfers themselves ('63736' and '63737') would have 'TK101' (seen in previous screen shot - re-shown below).
If it was a 'shipment,' the tank would be listed in the 'RecShipSrc'. My hope was that this could be referenced to link it to a potentially new column in the SQL Query to move the 'MeasLiqVol' value to the Tank under 'Pipeline Receipts' rather than the transfers that are listed in the above examples.
Thank you again for your time.
Chris
ASKER
There is a 'FlowType' column with 'T' for each tank and transfers listed as either a Shipment with an 'S' or a Receipt with an 'R'. Would this be important to note in any copying or porting of information to a new column either in SQL or in a formula within Crystal reports?
ASKER
Hi Chris,
Apologies for the delay in responding.
I think the Flow Type may not help as the value is 'R' for RecShipDest value 'TK101' since the records are categorized under 'PipelineReceipts'.
Would it be possible to share the report file with export of tables and respective data from database as a last try?
Also, you had mentioned:
"If it was a 'shipment,' the tank would be listed in the 'RecShipSrc'. My hope was that this could be referenced to link it to a potentially new column in the SQL Query to move the 'MeasLiqVol' value to the Tank under 'Pipeline Receipts' rather than the transfers that are listed in the above examples. "
I think the values of MeasLiqVol are already referenced for 'PipelineReceipts' only, isn't it?
Apologies for the delay in responding.
I think the Flow Type may not help as the value is 'R' for RecShipDest value 'TK101' since the records are categorized under 'PipelineReceipts'.
Would it be possible to share the report file with export of tables and respective data from database as a last try?
Also, you had mentioned:
"If it was a 'shipment,' the tank would be listed in the 'RecShipSrc'. My hope was that this could be referenced to link it to a potentially new column in the SQL Query to move the 'MeasLiqVol' value to the Tank under 'Pipeline Receipts' rather than the transfers that are listed in the above examples. "
I think the values of MeasLiqVol are already referenced for 'PipelineReceipts' only, isn't it?
ASKER
Please find the attached .rpt file with Data included. There is only the 'PipelineReceipts' attempted as a formula, but there would likely be formulas for the other Receipts and Shipments Categories ('Tank Receipts, 'Barge Receipts,' 'Pipeline Shipments' ...) once the connection would be figured out. The tanks would not need a 'MeasLiqVol' amount. That value would only be for transfers.
Please let me know if you need any further information or in different formats.
Thank you,
Chris
One_Column_Data_in_Two_Rows.rpt
Please let me know if you need any further information or in different formats.
Thank you,
Chris
One_Column_Data_in_Two_Rows.rpt
Hi Chris,
Report is not with saved data, kindly re-share with "Save Data with Report" enabled from file menu. Also, could you provide the syntax and data being retrieved from the Stored Proc?
Report is not with saved data, kindly re-share with "Save Data with Report" enabled from file menu. Also, could you provide the syntax and data being retrieved from the Stored Proc?
Can you show us the stored procedure it is using?
mlmcc
mlmcc
ASKER
OK. Here is the rpt after I have chosen the "Save with Data" option in the File Menu. There should also be a preview available.
Thank you,
Chris
Thank you,
Chris
ASKER
My Apologies. I left before hitting "Upload." Here is the file.
One_Column_Data_in_Two_Rows.rpt
One_Column_Data_in_Two_Rows.rpt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I think that a 1 on 1 would be the best for this situation. Can I schedule that in the "Live Consultants" window options?
That is the way to do it.
mlmcc
mlmcc
ASKER
Raghavendra answered my question through the live one-on-one session. The answer had to do with grouping according to the destinations and sources and renaming the transfers to reflect their sources or destinations.
This formula was used to group the values that all had the same name.
This formula was used to group the values that all had the same name.
Per my understanding, we need a summed up value for each RecShipID field. Please confirm.
Can you provide the details of groups used in Crystal Reports? Is there a group on RecShipID? I suppose the RecShipID is the unique identifier for us to group the data?
Also, do you want the added value on all the records of RecShipID or is it fine to display it on first record of group of similar RecShipID's?
Thanks,
Raghavendra