dynamic crosstab using DMR(with prompted dimension and measures)

[cham]

Hi Gurus,

I am having some problem to generate dynamic crosstab using DMR.

I have 4 dimensions and few measures. All dimensions and measures are prompted( to pick and choose) for user.Dimensions are Regions, country, time and Product. Measures are A, B, C and D.

the requirement is as follows,
1. Time, Region and country dimensions are default for all measures. So, first user will choose values for these three dimensions.
2. Prompt for Measure. IF measure in (A,B), then prompt for dimension Product and user will pick and choose for the product values. but if measure in (C, D) , it will not ask for product.

what I did : I made to two conditional report , one with Product and another without product. depending on the measure selected by the user conditionally I am redering the report.

the problem is the default report(without Product dimension) is working fine. but the report with Product dimension is not rendering any values for the selected measure. I am not sure what is the problem. Please help me in this regard ASAP.

thank you.

[cham]

Please help. I need it urgently.

[Arpitagrawal9]

So in both the report at a time are you showing more than 1 dimension/Metric

[Arpitagrawal9]

Instead of creating 2 Separate reports what you can do is When User select Measure C,D you can hide the product prompt using the render variable,So user wont get any option to choose any product.

If this does not work
You need to create 2 Separate Cross-tabs with 2 serrate Queries the one which requires Product filer can have that criteria.And you can drive the conditional block using the Measures prompt.

Hope this helps.Feel free to get back to me

[cham]

thank you. I could able to do it using conditional block.

But this is working only for 1 measure. now I am facing problem rendering multiple measure dynamically(user wants the flexibility to pick and choose multiple measures).  Please provide me some tricks. thank you for your help.

[Arpitagrawal9]

1.You can create a Stored procedure in the database which would return you Dimension,MetricValue,Metric Name

So in that SP you would send the Metric ID for the resepective measure through the prompt selection Made by user.And the Sp will return you value like

Product    Metric Name MetricValue
A              Revenue              1000
A              Gross Margin          50
B              Revenue              2000
B              Gross Margin         100

If you need any help in creating the logic of SP just let me know.
You can drag metric value to Measures in Crosstab and the name can be dragged into Rows.

Hence you can achieve Dynamic measure selections

2.You can use Javascript/Render variable to show and hide the measures

Hope this helps :)

[PRIT AMRIT]

the requirement is as follows,
1. Time, Region and country dimensions are default for all measures. So, first user will choose values for these three dimensions.
2. Prompt for Measure. IF measure in (A,B), then prompt for dimension Product and user will pick and choose for the product values. but if measure in (C, D) , it will not ask for product.

How many Prompt pages you have in your report?

[cham]

Hi Arpit,

Can you please help me creating the SP? I can understand the concept what you mentioned. but now sure how to implement it. I appreciate your help. thanks.

Hi PRIT,

I have two prompt pages.

[cham]

Hello Arpit,

I am not sure how this SP will help me to renden multiple measures in a cross tab. suppose I want to create a crosstable with product in rows and time in columns and user will pick and choose multiple measures through prompt. I am considering the dimensions as fixed(not dynamic). So, dynamic rendering is required only for multiple measures. Now can u please explain me step by step in details how to handle multiple measures(users choice)  to render in this crosstab?

thank u very much for your help.

[Arpitagrawal9]

Hi Cham,

I haqve one Doubt wanted to know whether User is going to select on measure at a time or you are providing user to choose multiple measure.As Implementing both the scenarios are completely different.

[cham]

Hi Arpit,

I have created a value prompt for all the measures. User will pick and choose(one or multiple) as per their requirement. So, the crosstab has to able to render all the metrics picked by the user(one or multiple).

I hope this is now clear to you. Presently I could able to render only one metric at a time. So, I need to implement so that it can handle multiple metrics.

Thank you very much for all your help. Please let me know if you need to know anything.

[Arpitagrawal9]

Your procedure can handle the Multiple selection if u use a crosstab,First create a multiselect prompt which would pass the resulted ID as comma seprated values.The SP based on these Metric IDS will returns rows which can be dynamic

[cham]

Hi Arpit,

thanks for the reply. sorry couldn't reply on time because I was out of town.

Can you please explain it in details? Please help me with the SP to be written and link the SP with the report logic.

I will look forward to your response. If you want you can also let me know your email id so we can communicate through email.

thank you.

[Arpitagrawal9]

Hi,

Just email me on Arpitagrawal9@yahoo.co.in with the List of columns that you want with your SP along with Some sample data so that i can give u the Script

Thanks,
Arpit