Setting Data Item to equal to Prompt Value

[fkli222]

Hi everyone,

I am trying to create a variance analysis between 2 months but having some trouble doing so.

My approach is to create 2 data items and set (DataItem1)-(DataItem2)=Variance.

I am having trouble setting my prompt values into my data items. I created 2 prompts, Month1 and Month2. I then dragged Month1 and Month2 into the column of my crosstab. When running the report, it prompts me to select Month1 and Month2. When I hit run, I get no data.

Am I doing something wrong in between? Thank you in advance!

[Lynn]

What type of data source do you have? Dimensional or relational?

It doesn't look like you've connected your prompt response to anything in the time dimension of your package.

For relational you'd want an expression something like:
[data item] = ?Month1?

For dimensional you'd want something like:
[level or hierarchy]->?Month1?

[fkli222]

What type of data source do you have? Dimensional or relational?

It doesn't look like you've connected your prompt response to anything in the time dimension of your package.

For relational you'd want an expression something like:
[data item] = ?Month1?

For dimensional you'd want something like:
[level or hierarchy]->?Month1?

My database is relational.

I am still a bit confused. How do I connect my prompt response to a time dimension? I created my Data Items and referenced it to the prompts I created. So for my 2 data items I referenced the 2 prompts that I created.

It looks like when I use only one data item in my columns it works fine, but it is when I drag over both into the column it gets no Data. Is it trying to filter both values somehow?

[Lynn]

It might help if you explain how you created your data items and connected them to the prompts. You could also take a look at the tabular data for your underlying query and the generated SQL to see if that provides any clues.

[fkli222]

It might help if you explain how you created your data items and connected them to the prompts. You could also take a look at the tabular data for your underlying query and the generated SQL to see if that provides any clues.

1) I created my data items in Query Explorer by dragging them over from the Toolbox.
2) Then I double clicked on each of my data items (Month1 and Month2) and selected the Month1 and Month2 Prompts like in the screenshot above.
3) I then dragged the 2 data items into the crosstab columns
4) I ran the report (ex. Selecting Jan for Month1 and Feb for Month2) but no data is shown
5) If I delete one of the data items out of the crosstab and only use one, it does work and display one of the months

Sorry but I am fairly new to alot of funtions in Report Studio.

Thank you again!

[Lynn]

So the expression for the Month1 data item in your query just says ?Month?. How is this connected to any date in your package? Did you look at the tabular data for your query? I would expect, based on what you are describing, that every single row coming back from the database will show the same value for Month1 and the same value for Month2, each corresponding to whatever you specify when answering the prompt. They will have no relation to any dates in your query and are nothing more than constants.

When you use just one month you say it works, but are the values correct? Does the value of your measure for each of the rows reflect the expected value for the selected month?

What are you specifying when you respond to the month prompts? Are these just string values? How do you connect these prompt responses to data in your package to limit the results accordingly? You must have a date or a month field somewhere in your package. What data type is it and how are you relating that value in your database to the prompt values you provide?

Sorry if I'm missing something fundamental.

[fkli222]

So the expression for the Month1 data item in your query just says ?Month?. How is this connected to any date in your package? Did you look at the tabular data for your query? I would expect, based on what you are describing, that every single row coming back from the database will show the same value for Month1 and the same value for Month2, each corresponding to whatever you specify when answering the prompt. They will have no relation to any dates in your query and are nothing more than constants.

When you use just one month you say it works, but are the values correct? Does the value of your measure for each of the rows reflect the expected value for the selected month?

What are you specifying when you respond to the month prompts? Are these just string values? How do you connect these prompt responses to data in your package to limit the results accordingly? You must have a date or a month field somewhere in your package. What data type is it and how are you relating that value in your database to the prompt values you provide?

Sorry if I'm missing something fundamental.

Sorry if this is confusing.

?Month? is linked to a date in my package "Month Name."


When selecting the parameter in my DataItem, I go to the Parameters Tab and select the Month Parameter that I created above.


When I selected one month, I was able to verify that the data was correct.

[Francis aka khayman]

you are creating this logic:

[MMC_PL_t].[Months].[Month Name] = ?Month? = ?Month2?

of course it will have blank if you give two different values as ?Month? <> ?Month2?



one data item should be
[Month1] = if ([MMC_PL_t].[Months].[Month Name] = ?Month?) then ([Measure]) else (0)
[Month2] = if ([MMC_PL_t].[Months].[Month Name] = ?Month2?) then ([Measure]) else (0)


[Variance] = [Month1] - [Month2]

.... or however you want to calculate for variance

[MFGF]

Hi,

Exactly as Khayman says!

Just to explain a little more about what you have currently and why it doesn't work...

You currently have a filter [MMC_PL_t].[Months].[Month Name] = ?Month?. If you (for example) respond to this and choose January, your filter expression becomes [MMC_PL_t].[Months].[Month Name] = 'January'
You also have a second filter [MMC_PL_t].[Months].[Month Name] = ?Month2?. If you (for example) respond to this and choose February, your filter expression becomes [MMC_PL_t].[Months].[Month Name] = 'February'

Where you have multiple filters like this in your query, these get ANDed together, so the logical filter expression used by the report is:

[MMC_PL_t].[Months].[Month Name] = 'January' AND [MMC_PL_t].[Months].[Month Name] = 'February'

Now you can see why no rows are getting returned. There are no rows of data where the Month Name is both January and February at the same time.

Instead of adding these filters, you could replace them with a single filter expression [MMC_PL_t].[Months].[Month Name] in (?Month?, ?Month2?). This would limit your rows to only those with either January OR February in the Month Name.

The next trick is to separate these out as individual month items in your query. This is what Khayman's expressions are doing. You will need two query calculations - one for each desired month, and each having the expression Khayman included below. Where he refers to [Measure] this is the default measure you are using in your crosstab.

Cheers!

MF.

[fkli222]

Thank you everyone for explaining what is going on and the logic behind it. Everything works now!