Thursday vs Thursday report!

[alamrin]

I have a requirement where in I have to display several metrics one after another for selected date against last week date.

There is only one dynamic prompt in which the user selects the date.

The report output should be for the selected date and corresponding week of that particular date.

for e.g.

if the user selects 5th april 2012 in the prompt

then the report should display data for 5th april and 29th of march as it is its corresponding  last week

so the report would be thursday vs thursday


following is the report view:

  prompt select date: <prompt>
================================I
        I                     I                 I
        I (29th march)          I     (5th Apr)      I
   I  prev week           I   sel date              I      
=== I=============I===============I
mI   I << singleton>>   I << singleton>>         I
   I                         I                   I
-================I===============I

I have used singletons to display the metric.

If I apply the _add_days(<date>,-7)  the date changes but the corresponding data in not obtained properly.

so essentially I am getting april 5th data for march 29 th as well.

I have tried union and other things but it doesnt return any data.

Any help would be much appreciated.

Thanks

[pricter]

Could you give a hint how the the corresponding data in not obtained properly?

Did you tried to use a crosstab and use a filter between?

[alamrin]

The data should appear as follows:

METRICS         Prev week(03/29/12)     Prompt date(04/05/12)
Revenue                        $5000                            $15000
Quantity                             20                                     30

There is only one prompt on the report where the user selects the date(in this case 04/05/12)
So the data of 5th april and 29th march should display side by side as depicted.

But i am getting 15000 instead of the 5000 for 29th march.

_add_days(<date>,-7) reflects on the date but doesnt filter the data corresponding to the date.

[goose]

You need a filter on the query for the previous week:

[My Date Attribute] = _add_days(?Dateparam?,-7)

[Lynn]

Is this a relational or dimensional source? If you are using singletons then presumably you have a separate query for each metric? Or maybe you have a single base query with query references against it? If you are using a single query you'd need to bucket the different time periods in different query items.

What is the exact syntax of your filter expression? For a relational source I'd expect to see something like below. If your source is relational this may not be what you need.

Code Select Expand


[YourPackageDateQueryItem] = _add_days(?PromptDate?,-7)


I think if you describe the structure of your report in terms of queries and expressions it might be easier to help.

[alamrin]

I was circling around the same solution but i was using

?date?=_add_days(<date>, -7)

and was not getting any data..

But now it works out well.

Thanks Lynn and Angus!!!     The provided solution returns desired data.

[alamrin]

For the purpose of easy understanding i had simplified the query.


but the actual report is giving the following error:


XQE-PLN-0161

      The prompt 'param_date' is used multiple times with conflicting data types


The solution works perfectly in case of list even for dynamic prompting of date.


But I am using singletons to display data for the provided metrics.


The metrics and the date are all present in the same table in the pkg.

It is just a user requirement for the displaying data.

_add_days(?param_Date?, -7)
Returns the date or datetime, depending on the format of "date_expression", that results from adding "integer_expression" days to "date_expression".

the date format is same across the previous week  and present date;

Any idea where I can address the conflicting data types.

[Lynn]

It sounds like you are using param_date somewhere other than your filter and treating it as a different data type. Your use of param_date in the _add_days function implies a date data type, so maybe you are using it elsewhere in a string function or something? Are you using a date prompt control for it?

If you search for expression text (Edit > Find) using your parameter name you should find all the places it is referenced to help you troubleshoot.