pass single date to data item from date range

[SGD]

Hi,

We have one RS report in which we are fetching the data within provided date range. Date range is either Rolling 12 months or Calender year. However, we need to have only single date passed to two of the data items in same query. Data for those data items should not be calculated based on selected data range.

1st e.g.

Code Select Expand

CASE WHEN (PRODUCT = 'Soup') THEN (Revenue) ELSE (0) END

In the 1st example revenue should not be calculated for selected date range rather for 'Start_Month' of date range.

2nd e.g.

Code Select Expand

CASE WHEN (PRODUCT = 'Soup') THEN (Quantity) ELSE (0) END

In the 2nd example quantity should not be calculated for selected date range rather for 'End_Month' of date range.

We are using Cognos 10.2 FP2 and MS SQL server DB.

So, is it possible to achieve this functionality in RS? It would be great help if someone could please provide some clue on the above.

Thanks in advance.

[SGD]

Hi,

I am trying to achieve this functionality by writing SP. However, it would have been good if achieved through Cognos.

Could anyone please help to get the logic in Cognos report iteself?

[bdbits]

I don't see dates referenced in either of your examples.

If the problem is that the date range for the overall result set is different than what you are trying to select for the data items in your post, one approach would be to create two queries and union them (this is within Report Studio, not SQL!). There may be other ways to accomplish what you need, but personally I would want to know more about the data before making other suggestions.

You can nearly always create what is needed without resorting to SPs or any kind of SQL, provided the data is modeled properly in FM.

[SGD]

I don't see dates referenced in either of your examples.

If the problem is that the date range for the overall result set is different than what you are trying to select for the data items in your post, one approach would be to create two queries and union them (this is within Report Studio, not SQL!). There may be other ways to accomplish what you need, but personally I would want to know more about the data before making other suggestions.

You can nearly always create what is needed without resorting to SPs or any kind of SQL, provided the data is modeled properly in FM.

Thanks bdbits.

I am just giving an example of data items from the single query. The query contains multiple data items created based on different products which are showing [quantity] based on the selected date range e.g. between 201311 to 201410.

However, 'Soup' product should calculate within same query on single date i.e. [quantity] would be calculated for 201311 and [Revenue] would be calculated for 201410. These two data items should not show the date based on selected date range rather start and end month respectively. Below code is just provided as an example.

I have tried to create the separate query subject for each of the product. However required calculation as ([quantity]  for 201311) - ([revenue]  for 201311) not able to calculate.

Also Union will add the rows one below another for each product and I need to have those rows converted as columns for each product with selected measure in the Crosstab,

Hope I have clarified my requirement clearly this time.

[cognos810]

Hello SGD,
Hope I got your requirement correct....
If your date selection uses two prompts, ?Start_Month? and ?End_Month? for example, then your query filter should have [Month_Data_Item] betwee ?Start_Month? and ?End_Month?
Then, your Revenue measure should have the expression,
CASE [Product]
WHEN 'Soup' THEN CASE [Month_Data_Item] WHEN ?Start_Month? THEN [Revenue] ELSE 0 END
ELSE [Revenue]
END
Similarly for Quantity,
CASE [Product]
WHEN 'Soup' THEN CASE [Month_Data_Item] WHEN ?End_Month? THEN [Quantity] ELSE 0 END
ELSE [Quantity]
END

Hope it helps!!

-Cognos810