Retrieve the Summarized field from the Crosstab
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=262
Printed Date: 19 May 2024 at 3:49pm
Topic: Retrieve the Summarized field from the Crosstab
Posted By: dhakshu
Subject: Retrieve the Summarized field from the Crosstab
Date Posted: 01 Mar 2007 at 12:30am
|
Hi All!
The crosstab report looks like this.
Dec Jan Feb
-----------------------------------------------------------------
sum %
-------------------------------------------------------------
aaa 7 53.84
bbb 2
ccc 1
ddd 3
Total 13
The report is grouped based on the programmes. Monthwise Airtime details of the programmes has to be shown.
Apart from the summarized field , I have to include a percentage field.
ie., the percentage is calculated based on a formula not on the total 13.
How to retreive the summarized field value from the crosstab report?
How to find the percentage based on a formula rather than the summarized field?
It would be great, If anyone can help me.
------------- Dhakshu |
Replies:
Posted By: Valmont
Date Posted: 04 Mar 2007 at 6:29am
|
How to retreive the summarized field value from the crosstab report? This is exactly what I am looking for as well. In fact, I'd like to retreive any field from a crosstab, even from crosstabs in a subreport. My chief would like to have a summary-report from all the subreports in a special format. I can do it only by formula's but the quest as mentioned above needs to be solved. |
Posted By: BrianBischof
Date Posted: 04 Mar 2007 at 9:16am
|
You can reference the cross-tab fields as the are being printed. However, you can't reference them afterwards. If you need to reference them afterwards, you need to create a global array and populate that array as it is printing the cross-tab. Then in your report you can reference the array as if you were referencing the cross-tab object. If you read my chapter on cross-tabs, you'll see the formulas for referencing the cross-tab cells and I think I have an advanced tutorial that walks you through some steps as well. ------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: Valmont
Date Posted: 04 Mar 2007 at 11:38am
|
What chapter. I didn't buy your book (yet). Also, I can't seem to find it on via this link: http://www.crystalreportsbook.com/CrystalReportsXI.asp |
Posted By: BrianBischof
Date Posted: 04 Mar 2007 at 1:52pm
|
The book isn't for sale yet. I have the chapters online to read for free. Chapter 10 discusses cross-tabs. Near the end is where I discuss how to reference specific cells in the cross-tab object. http://www.crystalreportsbook.com/Crystal_Reports_XI.asp?Page=10_7 - http://www.crystalreportsbook.com/Crystal_Reports_XI.asp?Page=10_7 The chapter is broken into multiple pages, so you will have to go back to the table of contents to click on the remaining sections. ------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: dhakshu
Date Posted: 04 Mar 2007 at 5:29pm
|
Hi Brian!
Thanks a lot for your reply.
Today , I will go through your book, and try to solve my problem.
------------- Dhakshu |
Posted By: Valmont
Date Posted: 05 Mar 2007 at 7:15am
|
Hello Brian, That page doesn't answer my question. Let me give you an example:
I have two crosstables in my report:
"Crosstable1" and "Crosstable2".
Both of them have various sub-totals in them.
Both of them are the way I want them.
I want to:
- Create formula manually in the formula field on the right pane.
- Type crystal syntax code to add subtotals from both cross-tables.
Or perhaps I want to sum the subtotal of "Crosstab1" with total-general of "Crosstab2".
So I need to reference any field in the crosstab from within a formula in the formula field on the right pane.
|
Posted By: BrianBischof
Date Posted: 05 Mar 2007 at 9:37am
|
Ah - I see. I added some more Advanced Tutorials to the chapter after I already published the chapter to the web. Basically, what you are trying to do isn't actually supported by Crystal Reports. In fact, there is no 'accepted' way of doing what you want. So you have to build some complex code to make it work. I simply don't have time to write the report for you, but I will give you the basics and let you run with it from there. You have to be VERY comfortable with Crystal syntax to get this working. First off, there is no way to reference each field in a cross-tab object. There is no functionality to talk to the cross-tab object. So what you have to do is build an array which mirrors the cross-tab object. As the cross-tab object is being built you have to populate each array element with the appropriate cell from the cross-tab object. Once the cross-tab is finished you can then use the array for the calculations that you wanted to use with the cross-tab object. Clearly, we're talking very advanced report design (which is why I ended up writing the tutorials after I originally finished the chapter - and now I might have to go back and add another tutorial to demonstrate this problem as well). The function to get the current value from the Cross-tab cell is CurrentFieldValue. Put that value in the array element. To find out which row or column you are in, use the function GridRowColumnValue(fieldname). To find out the row, put in the row's fieldname. To find out the column, put in the column's field name. By combining If-then statements with the GridRowColumnValue() function, you can determine which row and column you are on and put the value into the proper cell in the array. Now, this can get complicated, but what you are trying to do is pretty advanced. Hope this helps. ------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: Valmont
Date Posted: 05 Mar 2007 at 3:14pm
|
You bet this helps! I'll experiment with this tomorrow right away after I finished a daily report for our customer. You see, one of our customers would like us to hand over a daily report with all the critical data in it, ordered by day and month. Many unrelated tables are to be used. In fact, all tables are going to be used. Since this customer is a good customer, I want to give them whatever they ask. That's the trade. My solution to this combined report is to create a standard report with all kinds of crosstables in it. That's easy enough. But after that I need to create the "final" report by combining the crucial fields from all the subreports into a single "final" report. I'm not affraid, only unseasoned :) Thanks again, Val |
Posted By: BrianBischof
Date Posted: 05 Mar 2007 at 3:27pm
|
Well, I'm glad this helps. Sounds like a brutal report to work on. I don't envy you. ------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: RealQMan
Date Posted: 22 Aug 2007 at 12:36pm
|
Brian, Need more information to put your explanation to work.. I understand completely what you are saying and I still stuck on where to start exactly |
Posted By: BrianBischof
Date Posted: 22 Aug 2007 at 1:41pm
|
Unfortunately, it's pretty complicated and my previous explanation is the best I can do here. I wrote about it in the book and gave example code and it took over four pages to do so. Obviously, it's not feasible to retype four pages in a forum post. You need to use the functions I listed in the previous answer to get the current value and populate that in an array as the cross-tab prints. ------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: RealQMan
Date Posted: 22 Aug 2007 at 1:52pm
|
Brian, You mentioned the functions use to find out where i am in the cell and assign the values accordingly. I can imagine it might be place in like a load method of the report or something.... Where would that code be? inside the crystal report formula? inside the crystalreport.cs? |
Posted By: BrianBischof
Date Posted: 22 Aug 2007 at 1:55pm
|
Everything I mentioned earlier is CR based. Nothing refers to .NET code. You would put those function in the conditional formatting formula of the crosstab cell. That way, as the crosstab prints you can call the GridRowColumnValue() function to find out which cell is being printed. Then use that info to store the data in the proper array index.
------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: RealQMan
Date Posted: 22 Aug 2007 at 2:20pm
I am slowly seeing the whole picture... i certainly hope u don't mind sharing your wisdom.  When I start up a cross tab report using wizard, it request for a datasource to pull the data out. So, if I was to format the cross tab cell as it prints, I would probably need some data in advance??? Yes??? |
Posted By: BrianBischof
Date Posted: 22 Aug 2007 at 2:54pm
|
We have two different conversations going on right now. This is a CR XI topic only. Thus, you give it any datasource you want and it populates the crosstab and you can grab the current data or overwrite it. The other post you have refers to .NET. In that case, you build a manual dataset in your code and use that dataset as your datasource for the report. Then you print the crosstab object using that dataset. I just want to make sure that we are either having one conversation or if its two, then we stay clear on what they are about. ------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |