July 4, 2015 at 3:07 am
Hi everyone
I have a matrix report with STORES in the row group and DATES in the column group. The table sums on SALES. The DATES column is formatted like =format(Fields!DATES.Value, "MMM yyyy"). The table also has 2 parameters @Start and @End. This all works great but I then added a child report so that the user can click on the SALES value for any sale by month and store. The child report uses the @Start and @End parameters from the original report but this is where I run into problems. Rather than bringing me the sales details for a particular store and month it brings back everything from the time period selected with the original date parameters. So say I originally selected 2015-01-01 to 2015-06-30 with the parameters when I select on FEB 15 in my matrix report I get Febs data along with all the other months ie Jan-Jun 15. The DATES fields in both reports are in the same date format - in fact both reports use exactly the same dataset.
Can anyone provide any pointers? I realise it's something to do with the formatting of the DATE field not being recognised in the linked report but I can't for the life of me work out what.
As always any help would be really appreciated.
BO
July 5, 2015 at 10:28 am
An update....
When I add the format(parameter name, "MMM yyyy") to the linked report parameters it does bring back the months relevant data but only for the first day of the month ie all sales on 3rd May but none of the sales on 9th, 11th, 15th May etc. I'm guessing I need to do something to the expression in the @End parameter in the linked report but not sure what exactly....
BO
July 6, 2015 at 7:42 am
ByronOne (7/5/2015)
An update....When I add the format(parameter name, "MMM yyyy") to the linked report parameters it does bring back the months relevant data but only for the first day of the month ie all sales on 3rd May but none of the sales on 9th, 11th, 15th May etc. I'm guessing I need to do something to the expression in the @End parameter in the linked report but not sure what exactly....
BO
One rather common requirement for reporting is having a month and year combined into a single field for ease of selection. It's data denormalization, but it's often essential for useful reporting. I've always been fond of YYYY_MM, where a 4 digit year is followed by an underscore, then a 2 digit month. I generate that value as part of the query for the dataset, and use something like this to create it:
SELECT CAST(YEAR(DATEFIELD) AS char(4)) + '_' + RIGHT('0' + CAST(MONTH(DATEFIELD) AS varchar(2)), 2) AS YEAR_MONTH
With such a field in place in the dataset, translation of parameters is no longer necessary for things like subreports, as the value can go across unchanged. I usually also select the raw value of the date field as well in the dataset query, so that if something else comes up that needs to be dealt with as a change, it can be fairly quickly added to the report because the data is already there to support it.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 6, 2015 at 2:40 pm
Thanks for getting back Steve. I'm still struggling to get this to work. I create the new field as suggested and add a filter using this field in the child report but it still brings back all the results between the 2 parameter dates ??? Am I being completely stupid???
July 6, 2015 at 6:02 pm
ByronOne (7/6/2015)
Thanks for getting back Steve. I'm still struggling to get this to work. I create the new field as suggested and add a filter using this field in the child report but it still brings back all the results between the 2 parameter dates ??? Am I being completely stupid???
Does the child report have the same combined year and month field? You still need to use a parameter in the child report that links to the value coming from the parent report.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 7, 2015 at 1:04 pm
Steve - that's what was missing....
I really appreciate your help with this - it was driving me mad!!
Thanks again
BO
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply