Drill Down parameters

Question

Post reply

Drill Down parameters

davdam8

Hall of Fame

Points: 3620
More actions
October 11, 2012 at 10:07 am

#263189

It is possible to create drill down parameters?
Any example?
Thanks
Regards

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

Can you define what you mean by drill down parameters?

davdam8 Hall of Fame Points: 3620 More actions · Answer 2

Imagine a field with chapters and another with capacities.

Capacities are in chapters. For example:

Air Force (chapter)

Defense (capacity)

Protection (capacity)

Missil Acquisition (capacity)

Navy (chapter)

Material Acquisition (capacity)

I want my report to show the data related to the chapters or the capacity.

I need a parameter that ables the user to select 1 or + chapters and/or 1 or + capacities.

In the parameter I want to be able to drill down the chapters in order to select the capacities.

Not easy to explain. Hope you get it.

Thanks

Regards

H4K Hall of Fame Points: 3374 More actions · Answer 3

Multi select Cascading parameters...

Never tried on SSRS... worked easily in Cognos...

BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI

H4K Hall of Fame Points: 3374 More actions · Answer 4

By concept, Multi select parameters can't be auto submitted.

So you need to select what ever chapters you need, and those values must be passed to the capacity parameter so that you can select the capacity in the chapters selected.

So here maybe you can click "view report" button to submit the values to capacity parameter once chapter values are selected.

You could try it out.

BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI

davdam8 Hall of Fame Points: 3620 More actions · Answer 5

Thanks

ANd how do I do that? I've never done that before.

So I create 2 parameters: 1 with the chapter and another that shows all capacities related to the selected chapter. Is that possible?

Regards

davdam8 Hall of Fame Points: 3620 More actions · Answer 6

Basically what i want is to dynamically populate a parameter based on the selection of a previous parameter.

Solutions?

Regards!

H4K Hall of Fame Points: 3374 More actions · Answer 7

davdam8 (10/12/2012)
Basically what i want is to dynamically populate a parameter based on the selection of a previous parameter.
Solutions?

create dataset for 1st parameter (i.e., chapter) containing distinct chapters.

For Example: select distinct chapter from table

create dataset for 2nd paramater (i.e., capacity) containing distinct capacities where table_name.chapter in (@chapter)

For Example: select distinct capacity from table where chapter in (@chapter)

BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI

davdam8 Hall of Fame Points: 3620 More actions · Answer 8

Not working body.

I got this:

Dataset1

SELECT distinct chapter

FROM chapter_capacity_table

Dataset2

Select distinct capacity

FROM chapter_capacity_table

WHERE (chapter in @Chapter)

!?

Thanks

davdam8 Hall of Fame Points: 3620 More actions · Answer 9

Found the bug.

New Challenge:

So now I have 2 parameters, but instead, these 2 parameters, it is possible to have only 1 with the same values?

For example in 1 parameter:

Air Force (chapter)

-----Defensive Capacity

-----Air Protection Capacity

-----Airfield Maintenance Capacity

Navy (chapter)

-----Navy Maintenance Capacity

-----Navy Protection Strategy Capacity

Army (chapter)

-----Gun Maintenance Capacity

Only 1 parameter with both, chapters and capacities, where drilldown is possible for the chapters.

Doable?

Thanks

H4K Hall of Fame Points: 3374 More actions · Answer 10

i have done that on OLAP database. Don't know if that is possible in relational db.

Maybe some kind of sorting trick will get the data in a sequence as you wanted.

But then also you have to consider how you going to design the report to handle a case where if a chapter is selected and some capacities from different chapters are selected. I have some idea about it but i'm not sure till i try it out.

BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI

davdam8 Hall of Fame Points: 3620 More actions · Answer 11

This is something usefull to know.

If you get the solution for relational DB, post it here.

Thanks!

Regrdas