I’ve got years of financial data that I want to pull from a database. All data is associated with a month end date. Using DataLink, I can write the SQL statement to limit the data to one year, but I would rather have the option to pull in 5 years of data and use a category (YEAR ?) to manipulate the display of data in the matrix.
I’ll assume I have to write the SQL statement to pull 5 years. Having monthly dates (1/31/2011, 2/28/2011, etc.), how did I dynamically add YEAR as a category and have it associated with the monthly dates?
Any guidance would be appreciated!!
If I am following you what you are looking for is to have both the month and the year as categories – correct?
And your difficulty is that you only have one source for both the month and the year in your STATEMENT_DATE field. What you can do is in your datalink bring in your STATEMENT_DATE as a category and the rest of the data as items. You can then add items (Month, Year) and write formulas to pull out the month and year from your STATEMENT_DATE and then have another matrix you can use the “using as” or select formulas to create Year and Month categories to get the data in the format you want to see.
Hope that is what you are after?
Thanks for the help. I have setup end month dates as a category and that park works fine. And I figured out how to use the BETWEEN qualifier. Thank you. And I do understand how to select FISCAL year. What I can’t associate is Month Dates and Years. The date field is called STATEMENT_DATE and exist for every record. Do I pull that date TWICE or can I create TWO “linked” categories for a single field.
My goal would be that I can select months OR years to present my data without having to create GROUPS of the months.
In datalink in addition to writing a SQL statement you can also you the Qualifiers tab which essentially writes a SQL statement for you. If you select your date and then pick BETWEEN from the drop down list you should be able to pick a date you want to start from and then another date that you want to end at and get all the data you want to bring in. You can then pull in date as a category and have your month end dates as a category, or if you just want year as you go through the datalink wizard you will be asked to choose a number format for the date, you can pick fiscal year as the type.
Not exactly clear if you want all the month end dates to be part of the category or just the year? Or by month and year?
Hope that helps if you need more info just let me know.