question

muk avatar image
muk asked

formatting in ssrs based on data from last run time

Hi all, Basically what I am trying to do is create a report for someone that has a list of people. The report will be based off a production database so it will be realtime every time it is run. What i need it to do is remember what records came out the last time the person ran it and color the new ones differently. (or vice versa - color the old records differently and leave the new ones white) I basically just need a way to detect changes from the last time the report was run. Can anyone give me some guidance? Thanks!
sql-server-2008-r2ssrsreporting_servicessharepoint-2010
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jack3r avatar image
jack3r answered
Do you have some timestamp on the production database? If the case, you can only keep a track of the user who requesting the data with a timestamp of his request, and them compare with the timestamp in the production database. All records in production database older than his timestanmp should be new. Jacques
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

If the user call a Stored Proc to get his records, you can create a table, and for each user request you write into it to keep a record of the user and the request time, then at the next request you check in the table, and in the WHERE CLAUSE put something like ProductionDBDate > UserLastRequest.
1 Like 1 ·
@jack3r I do have a timestamp on when the record was added, I see what you are saying... almost like set a flag but how do I "keep a track of the user who requesting the data with a timestamp of his request"?
0 Likes 0 ·
I wonder whether you could use a linked server to link your production database to the Execution LogStorage table in the ReportServer database. That will give you the datetime the report was last executed and by whom.
0 Likes 0 ·
@GPO thanks for the response, that is really what I am looking for. Great idea! Anyone have any tips or articles on how to set up custom formatting?
0 Likes 0 ·
GPO avatar image
GPO answered
For conditional formatting in SSRS you can use expressions like iif() and switch(). In my view though, you're better to keep logic like this in the data. Say your SQL generates a column called SalesPerDay. You would also have a column in your data (let's call it SalesPerDayColour) that has the values, say "Red" for a certain range of sales and "Green" for the rest. Then in SSRS instead of selecting a colour for SalesPerDay field, select expression and select the SalesPerDayColour field. Note that your colours are case-sensitive so "red" does not equal "Red".
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.