Hi,
Hello to all, i need the following display of report:
|SalesRepNo | Part# | Sales$ 2014| Sales$ 2015 |
The report needs to allow the user to select a date interval for 2014 (will impact Sales$ 2014 column) as well as a date interval for 2015 (will impact Sales$ 2015 column)
and will display appropriate sales.
Is this possible ?
Thank you for any valuable advices !
Hi Sanchoniathon,
Please follow below steps
>create two queries with required columns
>apply hard code filter in one query for 2014 and filter second query for 2015
Year=2014
Year=2015
>And also inside each query add date filter with parameters
Eg: Date between ?a? and ?b?
> Then create join on these two queries finally drag Sales query item from both queries into join query .Name them as 2014 Sales and 2015 Sales
Then use this join query to your report object.
Regards
Bvk
You did not specify whether your source is relational or dimensional. Response below assumes relational.
You can do this with one query. Include sales rep and part number, then add a query item for 2014 using this expression:
case
when [Your Date] between ?2014_Begin? and ?2014_End? then [YourSalesMeasure]
else 0
end
Do the same thing for 2015 replacing the parameter names as appropriate.
Create a filter for the query as follows:
[Your Date] between ?2014_Begin? and ?2014_End?
or
[Your Date] between ?2015_Begin? and ?2015_End?
I'd further suggest you can make this more generic by using Year1 and Year2 instead of 2014 and 2015. Then create a data item to extract the year from one of the year1 parameters and use it as the source for the column heading. Do the same for year2. In this way the report will work for any two years the user cares to compare.
The solution that bvk.cognoise provided is an option, but you might lose data if you do an inner join between the two queries and there are reps/part combinations in one year but not the other. Further, separate queries that are combined within Report Studio are going to mean the results get processed locally on the Cognos server rather than back at the database. Depending on the result set sizes this may not perform well.
Thanks to both of you for both suggestions !
I will surely try this soon enough.
:)
I had to add the [Date] column on the properties of my list object (because we don't need to display ) and therefore the 2 sales $ columns are now working correctly.
Thank you so much for your fast replys.
Other question:
I now, need to display as the last column the [Variation%] column but it doesn't seems to work as for when:
1- Create a new data item
2- and include the following calculation: CurrentYearSales$/LastYearSales
3- Add percent format to it
4- Add that newly create data item to my list to display it.
But i don't get the expected results.
Anyone has any advices ? Can i still manage to this simple data item calculation in the same query ?
Anyone have any advice? Can I still manage to this simple data item calculation in the same query?
Also, i want the following result display:
________________________________________________________
| Client# | ClientName | Sales2014 | Sales2015 | Variation% |
------------------------------------------------------------------------------
|100000 | JohnJohn | 7 000.00$ | 8 000.00 $ | % |
-------------------------------------------------------------------------------
|200000 | MonicaMonica | 4 000.00$ | 6 000.00$ | % |
But instead get the following more detailed list:
__________________________________________________________
| Client# | ClientName | Sales2014 | Sales2015 | Variation% |
--------------------------------------------------------------------------------
|100000 | JohnJohn | 3 000.00$ | 5 000.00 $ | % |
| 4 000.00$ | 3 000.00 $ | % |
---------------------------------------------------------------------------------
|200000 | MonicaMonica | 4 000.00$ | 6 000.00$ | % |
etc...
We have a grouping on the Cient# as well as on the ClientName. It looks like it reacts to the fact that i had to add the DATE column as a PROPERTY of the LIST as for i wasn't getting the correct numbers on the Sales columns. The DATE column is not displayed on the LIST (intentionnally)
Thanks in advance for any valuable hints.