Crystal Reports Group Columns

Report Design

Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design

Topic: Group Columns

Author	Message
katfoxus Newbie Joined: 17 Oct 2011 Location: United States Online Status: Offline Posts: 19	Topic: Group Columns Posted: 11 Oct 2012 at 7:23am
	This is probably a very simple thing to do but i cannot seem to figure it out for the life of me. i have a report where group 2 has a formula that returns a value for that group. I need the total that diplays in that row to be in a column on group 1. ie. reasons would be group 1 and product would be group 2 g1. failed reports g2. data a g2. data b g1. successful reports g2. data a g2. data c i need it to lay out like reports status data a \| data b \| data c failed reports x x successful reports x x
	IP Logged


DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 11 Oct 2012 at 8:17am
	use a cross tab if you do not want to go that easy route you would have to use multiple formulas and then summarize weach of those 1 formulas to convert each field into a "column" //data_A if field='data a' then "X" //data_B if field='data b' then "X" then use maximums at teh grouip level 1 on each formual and palce it on GH1 maximum(@data_A,table.product) maximum(@data_B,table.product) Edited by DBlank - 11 Oct 2012 at 8:17am
	IP Logged

katfoxus Newbie Joined: 17 Oct 2011 Location: United States Online Status: Offline Posts: 19	Posted: 11 Oct 2012 at 8:32am
	unfortunately i cannot use a crosstab as the data is at the end of about 12 columns already on the report. the only issue i'm have with if field = 'data a' then 'X' is that in group 2 there is a distinct count of another field and if the count = 1 then it needs to equal {table.field} if it is <1 then it needs to be blank and if it >1 then it needs to show 'X'. I can get all of this to work in traditional grouping format but the client wants it laid out as strictly columns and it cannot be a dropdown.
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 11 Oct 2012 at 8:34am
	Can you give some sample data and use it to explain how you need it to appear
	IP Logged

katfoxus Newbie Joined: 17 Oct 2011 Location: United States Online Status: Offline Posts: 19	Posted: 11 Oct 2012 at 8:49am
	yes, i will explain this first though. i have my reasons as a command brought in from a self referencing table, and my products are another command using a self referencing table. then to join there there is yet another table that joins the reasons by their reason code and the product table by the l2 product code l1 reas \| l1 desc \| l2 reas \| l2 desc \| l3 reas \| l3 desc \| this would be my g1h and l3 reas is what i'm grouped on group 2 is my l1 prod code that can be associated to each reason so group 2 looks like this ABC 5 - shows the number of products associated to the subgroup DEF 3 GHI 1 where the numbers are the distinct count of the l2 product for the reasons. i need it to show up as. l1 reas \| l1 desc \| l2 reas \| l2 desc \| l3 reas \| l3 desc \| ABC \| DEF \| GHI \| c \| cust \| cu cld \| cust called \| cu ac\| upset \| X \| X \| shoe\|
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 11 Oct 2012 at 9:47am
	so I am not completely follwing your structure here but what comes to mind is that you can use Ruunningtotals to summarize something that was already summarized (e.g. distinctcounts). These of course have to be palced in a footer butyou can overlay the header to the footer making it appear to be ont he same row. Make sense?
	IP Logged

katfoxus Newbie Joined: 17 Oct 2011 Location: United States Online Status: Offline Posts: 19	Posted: 11 Oct 2012 at 10:02am
	i did try that but not with the running totals. the biggest issue is the self referencing tables. i have to use commands and it works in a traditional grouping. l1 reas has about 4 c, i, p, o complaint, inquiry, praise, other so it is the reason for a contact and then level 2 would would be something like op-operation, err-error and so on. then level 3 reason would be c op on off- complaint operation on off. then the products would the same type of tier where i have a connection of level 2 product to level 3 reason and then my group 2 would be l1 product with a distinct count of the level 2 products for each l1 product that can be associated to that reason. the problem is that there are quite a few columns for the reasons and they want to show the level 1 products for each reason as an X if there is more than 1 level 2 product for the level 1 product and if there is only 1 l2 prod for the l1 prod then they want to see the l2 code. i have tried subreports and crosstabs a plenty. i created stringvars for each l1 product and moved those to the group footer.
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 11 Oct 2012 at 10:19am
	shared strinvar formulas can act the same as Running Totals. can you show sample ow level (details) data and how you need it rolled up? You other sample data had it already aggragated i believe
	IP Logged

katfoxus Newbie Joined: 17 Oct 2011 Location: United States Online Status: Offline Posts: 19	Posted: 11 Oct 2012 at 10:38am
	ended up creating separate subreports for all the l1 products linking back to the reason. thanks for the help :)
	IP Logged

katfoxus Newbie Joined: 17 Oct 2011 Location: United States Online Status: Offline Posts: 19	Posted: 12 Oct 2012 at 4:09am
	Too many subreports and it takes too long to run. I have a bunch of reason codes like c op feat (complaint operation feature), I pur loc (inquiry purchase location) that is my first group. These are coming from a self referencing table that has been flattened out. This table links to another self referencing table that has a code that links the l2 prod to the reason code. The product codes are listed like sb rf rp (starbucks refreshers raspberry pomegranate) or sb per 24 (starbucks perculator 24 oz). Sb would be considered my l1 product and per would be my l2 product. So I have a product command that flattens out the products from the self referencing table. I have the reason command connected to the reference_id of my connecting table and my code_1 connects to my product command. Group 2 is the l1 product code. So in this case it would be SB. For group 2 there is a distinct count of the l2 product code. If the distinct count = 1 I want to display the l2 prod code (per, rf) and if it is more than 1 I need to display an x like in the examples above. I apologize but I have been working on this for 3 days and nothing seems to be working right.
	IP Logged

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum