.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Using "As Of" date in a report model

Posted By:      Posted Date: September 16, 2010    Points: 0   Category :Sql Server
Hi Everyone. I have an organisation structure table that contains start and end dates so that I can use it to sequence financial reports with the structure "as of" a set date. Along with this, I also have some slow moving dimension tables related to chart of account structure that also have start and end dates. Now, I can build a model with this OK and when I create a report, I can add the selections ( "as of date between start orgstructure date and end orgstructure date and as of date between account structure start and end structure start) without any problem but its much to complicated for my ad-hoc report writers to use. Is there any way of setting up a parameter field in a model so that I can build the selections into the model itself so my users only need to feed it the date?  

View Complete Post

More Related Resource Links

The report cannot be opened because the data source associated with it is not a report model.


i tried to open a report i created in sql business intelligence development studio in the report builder in wss3 and it gave me this error:

Microsoft.ReportingServices.ReportBuilder.ReportModel.Report: The report 'http://servername:port#/Reports/report.rdl' cannot be opened because the data source 'http://servername:port#/DataSource Library/datasource.rsds' associated with it is not a report model.

This report was not created in Report Builder. Try opening this report in Report Designer instead.

can i fix this so i can open my reports in report builder?


Report Model/Report Builder/Out of Memory Exceptions

We are getting out of memory exceptions on our server. We are using Report Builder against a Report Model in RS2008. Here is part of the errors we are getting. I cannot find any information for an error 200. Here is part of the error log:   processing!ReportServer_0-2!17d8!08/25/2010-10:07:29:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: An unexpected error occurred in Report Processing., ;  Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: An unexpected error occurred in Report Processing. ---> System.TypeInitializationException: The type initializer for 'Microsoft.ReportingServices.ReportIntermediateFormat.Sorting' threw an exception. ---> System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.    at Microsoft.ReportingServices.ReportIntermediateFormat.Sorting..cctor()    --- End of inner exception stack trace ---    at Microsoft.ReportingServices.ReportIntermediateFormat.Sorting..ctor(ConstructionPhase phase)    at Microsoft.ReportingServices.ReportIntermediateFormat.Persistence.ProcessingRIFObjectCreator.CreateRIFObject(ObjectType objectType, IntermediateFormatReader& context)    at Microsoft.ReportingServices.ReportIntermediateFormat.Persistence.IntermediateFormatReader.ReadRIFObject(Boolean ve

How to get "Current Date" on SharePoint Designer for Invoice Ageing Data View Report??

Hi Experts, I would like to know how can i get/show "Current Date" in SharePoint Designer ?? Any good tutorial ? FYI, I need this "Current Date" for my Ageing Data View Report, Purpose To offset with "Invoice Receive Date" to get total days of payment payable for the invoice. Please assists, Thanks

model report from two different data sources SQL server and access DB

Can we merge two model reports into one . One model report is from SQL server and another one from Access DB. How is that possible. Can you please give a idea how to do that. I need to display data from sql server as well as access DB using model report.  I need to get data from two different sources like sql and oracle or SQL and access db. So can combine tables from these data sources into the DSV or model. I need to use it in a report   Thanks Madhavi

problem deploying report model to wss3 site

hi, im having trouble deploying my report model. when i click on deploy i have to enter a user name and password (i asume that it is the username and password i use to enter my wss 3 site) after that it begin to deploy then i get these 2 errors: Error 1 The required field UserName is missing from the input structure. c:\documents and settings\administrator\my documents\visual studio 2005\projects\essreportproject\essreportproject\ESSP.ds 0 0  Error 2 The path of the item '/Data Sources/ESSP' is not valid. The full path must be less than 260 characters long; other restrictions apply. If the report server is in native mode, the path must start with slash. c:\documents and settings\administrator\my documents\visual studio 2005\projects\essreportproject\essreportproject\ESSP.smdl 0 0  when i go to my page in wss3 i can see the datasource and model directory, but i cant use them can someone help me with this and explain it for me.  

Consolidated report across more than one model

Hi, We are using  Great plains  as our ERP applicatioon  and exploring the options of using report builder as ad hoc reporting too for end user reporting.  In GP each company has an exclusive database.  I have created a report model for each of the company and users are good to go with reporting for each company. However fthe finance department would like to consolidated report  accross all companies. How can I consolidate each of these company models into one giant model. Regards Vincent        

Unble to pass date parameter to "Go to Report " ?


Hi ,

I have Report A need to go from Report A to Report B passing multivalued parameter and date parameter as well , I do not have any problem passing  Multivalued parameter but cant pass date parameter from Report A and Report B .

Report A : Date parameter properties

name : date

Data Type : Date/Time

Default value =Today()

Report B : Date parameter properties

name : date

Data Type : Date/Time

Default value =Today()

Go to Report action ,Expression used to pass date parameter  from Report A to Report B


Unable to pass appropriate date parameter if changed from default value from ReportA to ReportB .

Assistance would highly be appreciated !!

Thanks .

Rajkumar Yelugu

Report Builder 2.0 Strange column when using model

Hi, We have build a simple dsv and model and are writing reports in report builder. Every time we add in a linked table we are getting a column appearing with strange values e.g. "AH.gEAAA=" these appear to come from no where and have a column name the same as the table, Can anyone please explain what they are and how to get rid of them please.

Date picker format -> Preview vs Report


I'm at my wits end!

When previewing a report on one of our machines, the date format for a parameter insists that en-GB dates are invalid (dd-mm-yyyy-). Instead, it requires en-US (mm-dd-yyyy). When deploying the report, it goes back to the correct format of en-GB.

Bear in mind, this works on all of the rest of our development machines exactly as required. Only this one machine doesn't work.

I've tried: -

  • Setting the report properties to United Kingdom - it already was
  • Changing ReportViewer.aspx to include --> <%@ Page Language="C#" AutoEventWireup="true" Inherits="Microsoft.ReportingServices.WebServer.ReportViewerPage" Culture="en-GB" %>
  • Ensuring that regional settings (control panel->regional and language options) is setup to English -United Kingdom. It all already was (regional, location, language and advanced tabs all setup to UK)
  • Ensuring the browser on the machine is setup to English -United Kingdom, it already was
  • Adding <globalization culture="en-GB" uiCulture="en-GB" /> to web.config
  • Updating BIOS (not my idea, but was getting to the point where I'd try anything! Basically, the BIOS has the date in the US format, so despite my reasoning that it's irrelevant due to regional settings, we upda

changes to report builber model



I have a report deployed using report builder v1.

 Now I have changed the model and re-deployed it and the report now crashes.

Is there a way to fix this problem as our clients will bebuilding reports using Report Builder V1 and deploying them. We will make changes to the report model and send out new releases of the model without knowing what reports are out there on the client side.




Report Model based on Analysis Services Cube doesn't work


Hi all,

I am working in BIDS and would like to create a Report Model based on a cube.

Creating a Data Source based on SSAS goes well, but when I want to make a Data Source View, the earlier created Data Source doesn't show up, which is necessary for the final resulting Report Model.

Could somebody help me out in solving this issue? (And also tell me why this is issue is rising?)


SQL Server 2008 & BIDS 2008 (Version 9.0.3x)

How to get share point Subsite Last Accessed date using Object Model in moss(Not Modified date)


Hi Guys,

My task is to check last accessed date to a sub site in share point like "http://asc-hv1-mossvm:4233/murali121". its not depended on particular log in user (so i.e any user is valied except system account).

if anybody have any idea please share with this post.

Thanks in Advance.



Report Model--is there a limit on the number of rows?

Is there a limit on the number of rows that a report model will support? Our report model has tables that have millions of rows of data. When we regenerate the model it starting to take hours to regenerate. Is that expected? Is there a limit on the number of rows that can be used with a model?
Kim B.

several date ranges in one report - first/last function?



    I´m working on a report with SSRS 2008 which is meant to show a lot of figures aggregated for the whole calendar year. In all datasets I have the date as a parameter (date with hierarchy: year - month). So to get the sums for the whole calendar year I only have to select all the months of the wanted year and SSRS shows the total for that period. That works fine. The problem:

Some figures (for which separate datasets are used) are not meant to be aggregated, eg the number of open cases at the beginning of the period or the number of open cases at the end of the period.

I tried using the First/Last-functions on that figures but that doesn´t work because the first/last function doesn´t seem to refer to the date-parameter but to other dimensions (in my case: region because it shows the summed up values for the first region instead of the not-aggregated values for the first month).

How can I acchieve that my report shows the first/last value dependent on the choosen date-parameters while summing up the other values for the whole choosen date-parameters?


Your help would be much appreciated.



Separate Date and Time report parameters in SSRS 2005



Is it possible to declare separate report parameters in SSRS 2005 as Date and Time?

I want the user to be able to enter a date and a time.

When I declare the parameters in my stored procedure as datetime, in SSRS the parameter just shows as a Date only.


Report Builder returning #Error value in empty fields and working with Date field


Hey guys,

I am trying to build a report using the date field from CRM and only wanting to return the date I am using this expression "=Fields!new_date.Value.ToShortDateString()" but when there is an empty fiel I get #Error. How do I solve this?

Also is there anyway to return the date field with the actual month showing instead of the numerical value? i.e. instead of 1/31/2010 Have it show January 31, 2010 (Doesn't have to be exactly in that format, but want the name value for the month.)

ssrs report with todays date and end of month


Hello All,

Iam bit sruggling to get an idea how to create report which needs to populate data based on every day to end of month,

what i mean is , dataset

1  day of month data to end day of month data

2nd  dayof month to end day of month data




and so on

I.e i need generate a report based on two paramerts which should take current date for evry day and end  day of month 


Any ideas will be of great help 





ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend