Crosstab - 2 queries

[JuliaM]

Hi everybody!
Need help with my report. I use relational DB, where we have a current period from separate table. I need to show sales for current period, next period, next +1, and all future months. In one query I get current month and year and calculate all the rest.
In the second query I get sales and calculate sales.
I can't figure out how to get values from my first query in the second one.
I tried prompts without sucess.
Any ideas?
Thank you!

[RKMI]

Hi Julia,

Can you post the report spec (xml), so that we can look at the queries.

Thanks,
RK

[blom0344]

report specs do not contain queries unless hardcoded in SQL.  You can build nice 'composite' crosstabs by using unioned queries.  I posted loads of solutions on this approach. Works very well

[JuliaM]

Thank you for replying. I don't think I can figure out union, based on the request - it gets so confusing around end of the year.
Is there a way to kind of "hard code" first query values into prompts? Or maybe somehow create master detail relationship?
Thanks!

[blom0344]

I can't figure out how to get values from my first query in the second one.

This is the hard part to understand, what are you trying to achieve?

[JuliaM]

Sorry, let me try - I get current period from DB, current year from sysdate and calculate next period, next period +1, and the rest. Say, in November - current period is 11, next 12, and next+ 1 is 1, and then the rest. Then combined with year I would get 201211..201212 and 201301. These values I compare to the first query record Year+period to get sales volumes.

If in my second query I use current period from DB, I only get current year/period numbers (join thing - I have no frameworks/transformer access). I always used union query if I needed current/prior year, but in this case I can't.
Does it make sense?

[blom0344]

You initially fetch a list of periods from a prompt query.  You use a data query that reads the associated parameter of the prompt and fetch sales data at period level. This I can follow.

The bit about the second query I can't . Is the problem that you cannot fetch the right data? Can you not reuse the prompt set for the second query?

Prompt queries and associated parameters are there to 'feed' data queries.  You cannot feed output of dataqueries to other dataqueries

[JuliaM]

I am not sure how to receive current period from the first query (prompt or some other way) in the second query.
I was thinking to pass the parm value of 201301 into my second query and add condition - "record year+month" >= parm. And then maybe use if/then conditions, except it gets confusing if current period is 11 or 12.
I did not try this yet.

[blom0344]

The current period can be easily calculated:

extract(year,current_date)*100+extract(month,current_date)   yields  201301 for today (2013-1-28). If you make sure that you work with integer values,  you can easily use these in comparisons

[JuliaM]

Our current period doen't match calendar period, this is my problem (it starts 1-2 days before the month ends). It comes from separate table and is joined to the main data table by year.

[blom0344]

That is not really a problem.

The trick is to think in terms of prompt and data queries.  The prompt query fetches a value or  an array of values.  These are stored in a certain parameter. The parameter is used in a filter expression of 1 or more data queries.  It does not matter if you define umpteen data-queries, all can used the prompt parameter.
This basically surpasses the existing relation between data table and calendertable

So, fetch the special current period in a prompt query instead of my calculation suggestion and you should still have a workable solution

[JuliaM]

Thank you! I think I finally figured it out :)