Count of the stages and creating hierarchy and custom categories

[sas_it]

Please find the attached requirement and pls help its urgent.

[Sam_SAS]

Hello,

I'd like to help, but it is difficult to understand exactly what you want to do. Your requirements document doesn't quite match the sample data provided.

If you make a hierarchy of Division > District, you can get a crosstab with totals like this:

If you want the zeroes to be missing values, you can create calculated measures like this:

IF ( 'AS Completed'n = 0 )
RETURN .
ELSE 'AS Completed'n

If you want a different kind of result, please try to create an example of how it should look in Excel or another tool.

Thanks,

Sam

[sas_it]

I want total value of the variable to be displayed. But for me it displays 0 and 1 in filter. Instead I just want total.

Eg.

D completed

0

1

0

1

-----

2

So it should come 2 instead of 0 and 1.

[Sam_SAS]

It still is not clear what you want. In which type of visualization do you want to see the totals? In a list table? In a crosstab?

If the values (0, 1, 0, 1) are giving you 1, check to see if the aggregation for this measure is something other than Sum.

[sas_it]

Actually I want this variable total value(value of 1) in various objects like bar chart, pie chart as a hierarchy . Eg stage 1, stage 2, stage 3 etc. When I click on stage one , it shows 0 and 1 both which again I need to filter. But just i want total value.

[Sam_SAS]

Does it have to be a hierarchy?

What about a button bar that would enable the user to select the stage?

In this example, the "Completed" measure will show the total for the column that is selected in the button bar.

It takes a bit of work to implement this kind of selector, but I can show you how if this would meet your needs.

Sam

[sas_it]

Yes pls show me step by step completely.

[Sam_SAS · Accepted solution]

The selector I used is kind of a hack, but I'm not sure if there's a better way to do it.

For a button bar control, you need a column that has the values for the button labels. It doesn't really matter which rows off the table these values are in.

To create the label column, let's make a custom category:

1. On the Data pane, select New data item > Custom category
2. I'll base the category on the values of ApplicationID, which are probably unique. (I first converted ApplicationID from a measure to a category)
3. Create a value group for all the labels but one, and drag at least one value of ApplicationID into each group
4. To handle all the remaining values, I will group them as the last label.

So your Custom Category window should look like this:

The button bar will set the value of a parameter, so let's create that parameter now.

1. On the Data pane, select New data item > Parameter
2. The default settings will be fine. Click OK to make the parameter.

Now, we need to create the "Completed" calculated item.

1. On the Data pane, select New data item > Calculated item
2. Use an expression with nested IF statements to get the value of the column that matches the parameter value:

IF ( 'Parameter 1'p = 'ASCompleted' )
RETURN 'AS Completed'n
ELSE (
IF ( 'Parameter 1'p = 'DSCompleted' )
RETURN 'D sCompleted'n
ELSE (
IF ( 'Parameter 1'p = 'FSCompleted' )
RETURN 'FSCompleted'n
ELSE 'PSCompleted'n ) )

Now, we put it all together:

1. In the body of the report page, make a button bar control. The control must NOT be placed in the page prompt area, because then it would filter your data. We only want the control to set the value of our parameter.
2. On the Roles pane for the button bar, assign the custom category to the Category role and assign the parameter to the Parameter role.
3. On the same page, add the bar chart and any other visualizations that will be affected by the button bar. Use the calculated item as the Measure role for these visualizations.

Now you should be able to select the button bar and see the values in your visualizations change. In the sample data you provided, the first two Completed columns have the same total but the other two should show a change when you select them.

One of the limitations of this approach is that report filters might hide some of your button label values. A workaround to that is to make your label column by using a calculated expression using the endsWith() operator to set all the rows where ApplicationID ends in 1 to one label, ApplicationID ends in 2 to another label, and so on. You don't have to worry about this if you don't have report-level filters.

This is all a bit tricky and it would be easier if your data had a different structure. But that is not always something we can control.

Let me know if there is more I can help with,

Sam

[sas_it]

Thank you so much for your help but if I take application id as value for stages, the result may differ..

[Sam_SAS]

If you can edit the data source, you can generate a different column to use as the basis for the button labels.

[sas_it]

Yes we can edit using EG tool. If I create the calculated data item as character and check if ds=1 then completed else missing. Is tHis the correct way?

[Sam_SAS]

If you make it character, the values cannot be aggregated (1+0+0+1+1 = 3). If you want to have the totals, the values must be numeric.