"restricted key figure" type structure in Cyrstal

[andrew_mast3]

hi,

I have a number of reports that I need to build that are basically complex crosstabs using two hierarchies. I can't use the crosstab expert because I need to call out specific values for the rows and columns. I am using a BW query as the data source.

The layout is something like this (with ~35 rows and 14 columns)

Hier A node 1 HierA Node 4

Hier 1 Node A Key Figure Key Figure

Hier 1 Node E Key Figure Key Figure

I can isolate the nodes for the rows by using a string variable on node name and the using a an if then formula for each row (if string var = node name then node name else null) and then creating a group on each formula and formatting it to suppress if the value is null.

What I can't figure out is how to get the rows to function. I was hoping to have one formula per column that takes the key figure and filters it by the column node id.

I know I can do this in the BW report but I was hoping to be able to figure out how to use crystal to do this.

Thanks in advance for any help,

Andy

[Former Member · Accepted solution]

If I understand correctly: you want to see some rows, but only if the row name is a predetermined value?

If so, you can limit the rows that show by using cross-tab expert, selecting your row and choosing 'group options.' Change 'in ascending order' to 'in specified order' You can now specify what group will appear, or use a formula to determine what group will appear.

Once you get the groups you want, you should have a new tab named "other" that you can select "discard all others" to remove the unimportant rows.

HTH

[andrew_mast3 · Accepted solution]

Hi Ben,

Thanksd for the input. I just reread my question and realized I mispoke. I can take care of the rows with no problem. The issue I am having is isolating the node of the hierarchy for the columns. I realize I can do this by creating a restricted key figure in BW that restricts the key figure by the node but this means multiple key figures that are identical except for the node they are restricted by. I was hoping there was a way in crystal that I could create formulas that would allow me to take a key figure and restrict by the node. The more I look at this, though, the more it doesn't look like it is possible.

Thanks again for your input.

[Former Member · Accepted solution]

Have you tried the GridRowColumnValue function? It's the only one I've found that will let me hide values based upon the column or row changing...

[andrew_mast3 · Accepted solution]

Hi Ben,

I have a cross tab with fixed rows and columns. I have already figured out how to set the rows up the way I need them but am having trouble with the columns. THe columns are basically the same key figure being restriced by different nodesd on a hierarchy (Actual TYD restricted by Education, Actual YTD restriced by Grants, etc, etc). What I was tring to do was see hos I could handle that on the Crystal side rather than duplicating the key figure seven times in BW.

In any event, I think I will just do the key figure set up on the BW side.

THanks again for your help.