Limiting Crosstab Columns based on Prmopt selection

[poondru]

I have requirement to develop crosstab report and columns need to be from user prompt selection.Example if they select Jan and Feb then it need to display only Jan and Feb and if they select all months..then it need to pull all months(columns) data.

Please suggest.

Srini

[MFGF]

Hi,

Do you have a relational or dimensional package? If it is a relational package, drag your Month query item in to the columns area of your crosstab, then add a detail filter with the expression [Your Month Item] in ?mth?

If you have a dimensional package, drag a Query Calculation to the columns area of your crosstab, and use the expression [Your Month Level] -> ?mth?

Regards,

MF.

[poondru]

My data is coming from Planning Source and saved as Float_Jan,Float_feb...Float_Dec.I don't have one " Month" Column and have 12 month values.I can give static values or something else in prompt but had tough time to restrict user selected values in crosstab columns.

SR

[PRIT AMRIT]

do u mean u have 12 columns i.e.Float_Jan,Float_feb...Float_Dec?

If yes, then perform UNION and create a DATA ITEM called 'Month'. So now you would have only one column for 'Month, which would have values Float_Jan,Float_feb...Float_Dec.

You can use this 'Month' item both for your Prompt query and filter for report.

Does it make any sense?

Thanks
P

[poondru]

I am getting this error

Invalid coercion from 'value' to 'memberSet' for '[float_Jan]' in 'union([float_Jan],[float_Feb])'.

Thank You
S

[PRIT AMRIT]

Looks like there is a datatype issue.

Can you convert these to 'Char' and then try to Union?

I am sure you are doing the Union Properly though, still let me bring it on

You have to create 12 queries. And name the queries as Jan, Feb,---- Dec. for your reference.
Create a data item called 'Month' in each query.
Then under Jan Query, inside Month item put your 'JAN' queryitem.
Then under Feb Query, inside Month item put your 'Feb' queryitem.
Similarly follow the steps for all the months.

Then UNION.

Let me know if it is ok?

Thanks