Forums (Obsolete)

Member

ECAbacus - 8/30/2012 11:12:42 AM

Creating an PublishedDate filter for News items

New to Kentico and attempting to build a "Month YYYY (total)"-style list of published News items. I'm aware the blog framework has a similar feature but it appears to just query against the BlogMonth table, i.e. if you change the published date to a different month it won't move to the new archive month.

Now, I know how to do this using a simple SQL group by statement:

SELECT MONTH(NewsReleaseDate) AS 'Month',
	YEAR(NewsReleaseDate) AS 'Year',
	COUNT(*) AS 'Count'
FROM
	CONTENT_News
(##WHERE##)
GROUP BY 
	YEAR(NewsReleaseDate), MONTH(NewsReleaseDate)
ORDER BY 
	YEAR(NewsReleaseDate), MONTH(NewsReleaseDate) DESC

However how do I properly implement this through Kentico? My initial approach was to create a QueryDataSource and bind it to a repeater, but am hitting issues, i.e. nothing's appearing. Can anyone offer any suggestions / direction as to how best to approach this?

Certified Developer 8

Jiveabillion - 8/30/2012 11:49:27 AM

RE:Creating an PublishedDate filter for News items

It looks like all your query is returning is the Month, Year, and Count. Are you wanting to display News item content in your repeater? If so, you need to return all of the columns you have in your transformation, as well as the required columns (I can't remember them of the top of my head, but they include NodeID, NodeAliasPath, etc.)

If you're just wanting to display the Month, Year, and Count, then your transformation needs to make use of those columns.

Member

ECAbacus - 8/30/2012 11:59:10 AM

RE:Creating an PublishedDate filter for News items

Correct. The end goal here is similar to a tag cloud, i.e. append a querystring value such as "?m=082012" that I can use to filter a DocumentDataSource. I've created a QueryDataSource and given it the aforementioned query to process, and am then grabbing the results with a Repeater using this transformation:

<a href="?range=<%# Eval("Month", true) %><%# Eval("Year", true) %>"><%# Eval("Month", true) %> <%# Eval("Year", true) %>(<%# Eval("Count", true) %>)</a><br />

However, the values don't seem to be rendering.


Certified Developer 8	Jiveabillion - 8/30/2012 1:14:09 PM RE:Creating an PublishedDate filter for News items Then I think your problem likely lies within your implementation of the QueryDataSource and your repeater. Have you tried using a SQL DataSource?

Member

ECAbacus - 8/30/2012 2:09:08 PM

RE:Creating an PublishedDate filter for News items

Actually, I managed to kill two birds with one stone by using a QueryRepeater with a query of:

SELECT 
	MONTH(NewsReleaseDate) AS 'Month',
	YEAR(NewsReleaseDate) AS 'Year',
	COUNT(*) AS 'Count'
FROM
	CONTENT_News
INNER JOIN
	View_CMS_Tree_Joined
ON
	View_CMS_Tree_Joined.DocumentForeignKeyValue = CONTENT_News.NewsID 
WHERE
	View_CMS_Tree_Joined.SiteName = 'MySite'
AND
	View_CMS_Tree_Joined.Published = 1
GROUP BY 
	YEAR(NewsReleaseDate), MONTH(NewsReleaseDate)
ORDER BY 
	YEAR(NewsReleaseDate) DESC, MONTH(NewsReleaseDate) DESC

And a transformation like:

<a href="?range=<%# Eval("Month", true) %><%# Eval("Year", true) %>">
    <%# System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName((int)Eval("Month")) %> 
    <%# Eval("Year", true) %>(<%# Eval("Count", true) %>)
</a>

Thanks for the assist though, and if you have any suggestions on improving this happy to listen!