March 29, 2007 at 5:16 pm
I'm trying to write a report which displays the last 24 months' sales for a customer (as a month-on-month comparison for the last twelve months, then a difference in % terms). The report is against Analysis Services, and I know very little about MDX. I have however managed to come up with a query to get me the last 24 months. See this thread for more info: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=17&messageid=353410
Now, the problem I have is that the names Reporting Services assigns to the field match the names of the months returned by the query (so a name will be "Time_Fiscal_2007_March", for example). This field name is useless to me in the report, since next month I'll have to shuffle all the fields along by one month. If I rename the field to something like "ThisMonth", next month the name will stick with March and I'll have a "Time_Fiscal_2007_April" at the top of the list.
So you can see my dilemma. I need to have a series of field names such as "24MonthsAgo", "23MonthsAgo"... "LastMonth". These need to update dynamically as the months roll over. What is the best way to do this?
March 29, 2007 at 6:24 pm
OK, I've been able to "stringify" the field name using the following formula in the Report Properties Code section:
--------
Public Shared Function MonthField(ByVal MonthsAgo As Integer) As String
Dim EvalDate As Date
EvalDate = DateAdd("m",-1*MonthsAgo, Now)
MonthField = "Fields!TimeNJ_Fiscal_All_TimeNJ_" & Iif(Month(EvalDate) > 6, Year(DateAdd("yyyy",1,EvalDate)), Year(EvalDate)) & "_" & MonthName(Month(EvalDate))
End Function
-------
I can reference this code in the report using "=Code.MonthField(3)" and it will return the string of the correct field name.
How can I use this value as a field name and not as a string??
March 29, 2007 at 7:03 pm
Woohoo!
I've taken the "Fields!" prefix out of the returned value in the formula above. Then using =Fields(Code.MonthField(24)).Value, in a table, against the customer, I get the value I'm after.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply