web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Using Temporary Tables...
Microsoft Dynamics AX (Archived)

Using Temporary Tables for AX 2009 SSRS reports

(0) ShareShare
ReportReport
Posted on by Saurabh P Singh 890

Hello All,

 

I have been facing huge problems while generating a SSRS report from AX 2009. I initially used the AX query to generate the SSRS report, but the query was huge and involved several Views in order to generate the query. I thought it will be very easy if i dump all the data in a TempTable and then use this in SSRS.

 

I know that this is possible in AX 2012, but i want to implement in AX 2009. Please suggest a solution. This is a bit urgent.

Thanks,

 

Saurabh  

*This post is locked for comments

I have the same question (0)
  • skaue Profile Picture
    skaue Moderator on at
    Re: Using Temporary Tables for AX 2009 SSRS reports

    Hehe... Not sure what you mean, but if the problem is solved, then *thumbs up*!

  • Saurabh P Singh Profile Picture
    Saurabh P Singh 890 on at
    Re: Using Temporary Tables for AX 2009 SSRS reports

    Hey Skaue,

    I have pulled the car our of the ditch, but the windows are still dirty.

    The report still shows no value in SSRS, but when i deploy it back in AX the data flow in properly. The concept is still unknown to me, but the job is done.

    Thanks for the help.

    Regards,

    Saurabh

  • Saurabh P Singh Profile Picture
    Saurabh P Singh 890 on at
    Re: Using Temporary Tables for AX 2009 SSRS reports

    I have used the same query in a View, just to check that the data is coming out properly. And, in the view it does.

    I have not used any grouping, etc on the SSRS report yet. I just wanted to know that the data is flowing properly. but, sadly it does not.

    Regards,

    Saurabh

  • skaue Profile Picture
    skaue Moderator on at
    Re: Using Temporary Tables for AX 2009 SSRS reports

    Sounds to me like your query is simply broken and fails gracefully. I've seen that in some queries using grouping in the wrong way. You won't get any errors, but there is no data returned.

    Can you tare down your query to the bare minimum and slowly rebuild it step by step? Just to narrow it down to where it just doesn't work any longer?

  • Saurabh P Singh Profile Picture
    Saurabh P Singh 890 on at
    Re: Using Temporary Tables for AX 2009 SSRS reports

    Well, i am currently not worried about the mileage, but when i currently run my report it does not at all run. I just get the headers.

    Slow is yet a positive sign, but it get nothing at all.

    Saurabh

  • skaue Profile Picture
    skaue Moderator on at
    Re: Using Temporary Tables for AX 2009 SSRS reports

    This is a good opportunity to use the YMMV-abbreviation. ;-)

    Your query can be a dinosaur, but still run quickly. Your result might be ready swiftly but take ages to render.

    So in other words, it is hard to know for sure where your bottleneck is.

    Have a look at this blogpost for pointers on figuring out where it is:

    www.keepitsimpleandfast.com/.../more-tips-to-improve-performance-of.html

    Notice there are three measurables:

    * Time to retrieve the data (TimeDataRetrieval).

    * Time to process the report (TimeProcessing)

    * Time to render the report (TimeRendering)  

    It could be that you would increase the timeout on the SSRS Server.

    One thing you might want to check out is the memory pressure on the SSRS server itself. SSRS works in that way it will slow down if there is barely enough memory, and more often SSRS is running on a system with little spare memory. The standard threshold before it goes into "slow mode" is normally 80% memory left (if I remember correctly). Make sure the SSRS service has enough system memory available, or it will run super slow even if your report is rendered quickly.

    As for your report and it's huge queries, you might want to consider redesigning how it collects the data. Maybe you need to move and organize its data to a different table prior to running the report.

  • Saurabh P Singh Profile Picture
    Saurabh P Singh 890 on at
    Re: Using Temporary Tables for AX 2009 SSRS reports

    Thanks Skaue..

    So, i have a huge SQL query which i have to use to generate an AX query for the SSRS report.. So for developing this query (Whch even involved SQL pivot), i had to create 3 queries and 2 veiws. The final query used the 2 views. So, when i am using this query in SSRS there is no data. But, if i put the same query in a view in AX it fetches me all the requried data.

    My question is, do queries formed by several views fail to work in SSRS somehow.

    Also, is there any method by which i can call a SQL SSRS report using a menu item in AX.

    Thanks a lot for the help.

    Saurabh

  • Verified answer
    skaue Profile Picture
    skaue Moderator on at
    Re: Using Temporary Tables for AX 2009 SSRS reports

    AX2009 support for Temporary tables are only for "in-memory" tables. You won't be able to use these in SSRS reports in AX2009. Your only option in AX2009 is regular tables or Queries/Business Logic.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Dynamics 365 Community Calls Return
Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft
Responsible AI policies for the Community

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Andrés Arias – Community Spotlight

We are honored to recognize Andrés Arias as our Community Spotlight honoree for…

Congratulations to the August Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Syed Haris Shah Profile Picture

Syed Haris Shah 9

#2
Martin Dráb Profile Picture

Martin Dráb 2 Most Valuable Professional

#2
Community Member Profile Picture

Community Member 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans